Getting History of Table Sizes in 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.

Share this post

Comments (13)

  • Klaus

    Thanks Peter, what a helpful little tool, fully automated!

    December 8, 2010 at 11:38 pm
  • stewart

    Makes me think of keeping this as a table stat to have better expansion of data files.

    December 9, 2010 at 12:53 am
  • bob

    Nice. Thanks.

    December 9, 2010 at 2:47 am
  • Shantanu Oak

    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

    December 9, 2010 at 5:26 am
  • Rob Wultsch

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


    December 9, 2010 at 6:22 am
  • Daniël van Eeden

    Maybe using events could be nice instead of cron.

    December 9, 2010 at 7:35 am
  • Morgan Tocker

    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.

    December 9, 2010 at 8:11 am
  • peter


    Yeah. You can typically skip these databases. You probably would skip performance_schema in MySQL 5.5 too.

    December 9, 2010 at 9:27 am
  • peter


    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.

    December 9, 2010 at 9:31 am
  • Baron Schwartz

    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.

    December 9, 2010 at 3:28 pm
  • shazamo

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

    December 9, 2010 at 5:20 pm
  • Ronald Bradford

    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.

    December 12, 2010 at 6:14 pm
  • Alex

    Sad, but it doesn’t provide correct info for barracuda tables. Any workarounds?

    December 28, 2010 at 2:00 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.