You have AUTO_INCREMENT columns. How far are you pushing the limits? Are you going to run out of AUTO_INCREMENT values soon? Perhaps you wonder whether you should ALTER from INT to BIGINT?
The answer is …[Read more...]
Giuseppe has a great post about the Evolution of MySQL metadata, and I thought I’d have a look at what we have in Drizzle. It’s pretty easy to work out how many tables are in each schema, we just query the standard INFORMATION_SCHEMA.TABLES view:
drizzle> select table_schema,count(table_name) -> from information_schema.tables -> group by table_schema; +--------------------+-------------------+ | table_schema | …[Read more...]
I was looking at the latest MySQL versions, and I happened to
notice that there has been a great increment in the number of
metadata tables, both in the information_schema and
performance_schema databases. So I made a simple count of both
schemas in the various versions, and draw a graph. The advance
The announcement of MySQL 5.5 released as GA has outlined the
improvements in this version, which indeed has enough good
new features to excite most any user.
There are two additions, though, that were lost in the noise of the bigger features, and I would like to spend a few words for each of them.
The first addition is something that users of stored routines …[Read more...]
You may have read a previous post of mine, back in April this
year, where I wrote about using the MySQL 5.5 Audit interface to
SQL Statement monitoring. There was a bunch of comments and some
ideas, but not much happened. Until now that is.
Hereby I release the first version of SQLStats, which is a much enhanced version of what I described in the previous post. This is a MySQL Plugin for MySQL 5.5 that allows you to monitor the most recently executed, and the most frequently executed statements using 2 …
Here’s a few thoughts on current status and further possibilities for Facebook’s Online Schema Change (OSC) tool. I’ve had these thoughts for months now, pondering over improving oak-online-alter-table but haven’t got around to implement them nor even write them down. Better late than never.
The tool has some limitations. Some cannot be lifted, some could. Quoting from the …[Read more...]
If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!
select (select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb, (select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb, (select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, (select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index, (select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) …[Read more...]
Everybody knows that parsing the output of SHOW ENGINE INNODB STATUS is hard, especially when you want to track the information historically, or want to aggregate any of the more dynamic sections such as the TRANSACTIONS one.
Within the InnoDB plugin the INFORMATION_SCHEMA.INNODB_TRX table was added, which allowed you to at least get some of the information on each transaction, but not the full breadth of information that SHOW ENGINE INNODB STATUS …[Read more...]
I wanted to get examples of some of the extra information that
the Percona server has in its INFORMATION_SCHEMA metadata, and in
doing so, I stumbled across an interesting MySQL
bug/feature/point — INFORMATION_SCHEMA tables (which are actually
system views) are case sensitive when used in comparisons:
mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select @@version; +--------------------+ | @@version | +--------------------+ | 5.1.36-xtradb6-log | +--------------------+ 1 row in set (0.00 sec) …[Read more...]