EmergencyEMERGENCY? Get 24/7 Help Now!

Getting History of Table Sizes in MySQL

 | December 8, 2010 |  Posted In: Insight for DBAs, MySQL


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:

And use this query to populate it:

I put it to the cron to run nightly as:

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.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


  • Very useful tip. Instead of inserting the records in a table, I would prefer saving it to a text file by adding the standard and error out to the cron entry.
    … >> /home/mysql_log.txt 2>> /home/mysql_log_err.txt

    When I need to know the number of records, I can now use grep
    grep -w tbl_name mysql_log.txt

  • “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.”

    Depending upon what your environment looks like, the information_schema query can easily run a server out io (amongst other things) for long periods of time. I have had to clean up a rather large mess caused by a query like the one suggested to.

    I suggest that the query above should only be used if there is a relatively small number of tables. I would probably not use it if there were more than a thousand tables on a server.


  • I might suggest a small modification to cut out the noise from information_schema / mysql databases:


    It’s not a big deal – but these infrequently change.

  • Rob,

    Indeed performance schema queries can have different impact to server performance. It is not just about number of tables but also their memory fit and number of indexes for Innodb tables as stats would need to be recomputed. How much impact it is going to do on server depends on how powerful server is – what it does is a lot of random IO, which can impact server response to other queries. If you’re in such situation you can use more complex script to do show tables (which is very fast) and when get size table by table with sleep in between.

  • Maatkit’s mk-find has features to iterate the tables and insert various information from them, just as you’ve described. Another advantage of this is that you won’t get false-positive checksum differences, which you’ll get if you let INSERT..SELECT replicate.

  • very cool and helpful tool but the issue with that is table_rows field does not contain accurate numbers, it contains an estimate values

  • Realize that for large InnoDB schemas (e.g. over 1TB, tables over 500GB etc), accessing the INFORMATION_SCHEMA.TABLES information for InnoDB tables will cause random index scans of the data for statistics purpose. This is one reason why these queries can take minutes to run.

Leave a Reply