As a sidekick for my previous post, I came up with a snippet of code that generates the Google Chart URL to visualize table size for the current database. For example, for the sakila sample database, we get URL's like this:
Abstract - As described by Walter Heck, MySQL database size
can be visualized using Google
Charts. With a minor code improvement the URL for the chart
can be obtained twice as fast. With some more modification, the
number of lines can be cut down resulting in a function that is
half as long.
Hi!It's been a while since I posted - I admit I'm struggling for a bit to balance time and attention to the day job, writing a book, preparing my talks for the MySQL user's conference and of course family life.
A month ago or so I …
When using Oracle, the data dictionary provides us with tons of
tables and views, allowing us to fetch information about pretty
much anything within the database. We do have information like
that in MySQL 5.0 (and up) in the
database, but it’s scattered through several different tables.
Sometimes a client asks us to change the datatype of a column, but forgets to mention the schema name, and sometimes even the table name. As you can imagine, having this kind of information is vital to locate the object and perform the requested action. This kind of behaviour must be related to Murphy’s Law.
In any case, I’d like to share with you a simple stored procedure that has helped us a lot in the past.
CREATE DATABASE IF NOT EXISTS dba; USE dba; DROP PROCEDURE IF EXISTS `dba`.`get_objects`; DELIMITER $$ …[Read more]
In a nutshell: What’s New in MySQL 5.1.
Release notes: Changes in release 5.1.x (Production).
And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.
To play the video directly, go to http://technocation.org/node/663/play. To download the 146 Mb video to your computer for offline playback, go to http://technocation.org/node/663/download. The slides …[Read more]
During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:
SELECT table_name article_attachment,
WHERE table_schema = ‘dbname’
ORDER BY 3 desc;
A generally lite version is:
select table_schema, table_name, (data_length)/pow(1024,2) AS ‘Data Size in Meg’, (index_length)/pow(1024,2) AS ‘Index Size in Meg’ from tables order by 3 desc;
You can add or remove columns etc and but this query shows the table size (data wise) index size, approx number of rows, size in MB etc. If you would like to know what else is available to add to this query, just do a “desc tables” while using the …[Read more]
A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.
I will present queries for:
The following query returns the total size per engine per
database. For example, it is common that in a given database, all
tables are InnoDB. But once in a while, and even though
default-engine is set to InnoDB, someone creates a MyISAM table.
This may break transactional behavior, or may cause a
mysqldump --single-transaction to be ineffective.
See …[Read more]
I’m in the process of converting some very large data tables to partitioned tables. By “In the process” I mean “scripts are running as we speak and I’m monitoring what’s going on.” When I did this in our test environment (2 or 3 times to be sure), I got familiar with the information_schema.partitions table. There […]
As I putter around the MySQL
am finding lots of undocumented behavior for fields that should
be straightforward. For example, the
holds information about views, and the
VIEW_DEFINITION field contains the view definition,
Well, when I was looking at the
today, I noticed an odd thing. Even though I had permissions to
see the view definition (as proven by the
VIEW command), the
table sometimes came up blank for the
VIEW_DEFINITION. I had to figure out why, and now
that I know, I’m not sure if it’s a bug or a feature…..can you
figure it out?
mysql> USE INFORMATION_SCHEMA; Database changed mysql> SELECT TABLE_NAME,VIEW_DEFINITION FROM VIEWS WHERE TABLE_SCHEMA='sakila'; +----------------------------+-----------------+ | TABLE_NAME …[Read more]
A while ago I wrote about MySQL 5.1 information schema
At the time, I wrote a plug-in to report the contents of the query cache, but for all kinds of reasons, I never found the time to write a decent article about it, nor to release the code.
I am not sure if I'll ever find the time to write that article, but I just tidied up the code, and installed it in a new MySQL 5.1.26-rc server. It seems to work there, so I put the code up on the web.
Inside the source file, there's instructions to build and deploy it. If all goes well, you can do …
MySQL® 6.0.5-alpha, the latest version of the 6.x branch of the Database Server, is available for download from the SUN|MySQL Web Site.
Metadata (data about the data) are very important, especially for software developers. In this article we will see what’s new in FALCON metadata handling doing some comparison with the old 6.0.4-alpha version.
New tables in the
As you know, the source for metadata is the database `information_schema`. To start, let’s see which tables related with FALCON metadata are included in that database:
mysql> SELECT VERSION();
| VERSION() |