Getting History of Table Sizes in MySQL

December 9, 2010

Author

Peter Zaitsev

Insight for DBAs

MySQL

Share this Post:

One data point which is very helpful but surprisingly few people have is the history of the table sizes. Projection of data growth is very important component for capacity planning and simply watching the growth of space used on partition is not very helpful.

Now as MySQL 5.0+ has information schema collecting and keeping this data is very easy:

create database stats;<br>use stats;<br>CREATE TABLE `tables` (<br>`DAY` date not null,<br>`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',<br>`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',<br>`ENGINE` varchar(64) DEFAULT NULL,<br>`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,<br>`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,<br>`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,<br>`DATA_FREE` bigint(21) unsigned DEFAULT NULL,<br>`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,<br>PRIMARY KEY(DAY,TABLE_SCHEMA,TABLE_NAME),<br>KEY(TABLE_SCHEMA,TABLE_NAME)<br>) ENGINE=INNODB DEFAULT CHARSET=utf8;<br>

1

create database stats; use stats; CREATE TABLE `tables` ( `DAY` date not null, `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', `ENGINE` varchar(64) DEFAULT NULL, `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL, `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL, `DATA_FREE` bigint(21) unsigned DEFAULT NULL, `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL, PRIMARY KEY(DAY,TABLE_SCHEMA,TABLE_NAME), KEY(TABLE_SCHEMA,TABLE_NAME) ) ENGINE=INNODB DEFAULT CHARSET=utf8;

And use this query to populate it:

INSERT INTO stats.TABLES<br>SELECT DATE(NOW()),<br>       TABLE_SCHEMA,<br>       TABLE_NAME,<br>       ENGINE,<br>       TABLE_ROWS,<br>       DATA_LENGTH,<br>       INDEX_LENGTH,<br>       DATA_FREE,<br>       AUTO_INCREMENT<br>FROM   INFORMATION_SCHEMA.TABLES;<br>

1	INSERT INTO stats.TABLES<br>SELECT DATE(NOW()),<br> TABLE_SCHEMA,<br> TABLE_NAME,<br> ENGINE,<br> TABLE_ROWS,<br> DATA_LENGTH,<br> INDEX_LENGTH,<br> DATA_FREE,<br> AUTO_INCREMENT<br>FROM INFORMATION_SCHEMA.TABLES;<br>

I put it to the cron to run nightly as:

1  0   *  *    *    mysql -u root -e "INSERT INTO stats.tables SELECT DATE(NOW()),TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES"<br>

1	1 0 * * * mysql -u root -e "INSERT INTO stats.tables SELECT DATE(NOW()),TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES"<br>

Though if you’re looking to keep it completely inside MySQL you can create appropriate event in MySQL 5.1+

Unless you’re having millions of tables this is something you can set it up and forget but when a year later when someone asks you about growth rate for individual table you will have it handy.

If you’re having large number of MySQL servers, especially in Sharded environment it makes sense to modify the script so you store data on one central server this allows you to do a lot of nice queries to see how evenly data is distributed among shards etc. In replicated environment you should put this cron on Master only. If you use statement based replication it will automatically pick up sizes and even different tables on master and slave.