October 31, 2014

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.

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

Comments

  1. Klaus says:

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

  2. stewart says:

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

  3. bob says:

    Nice. Thanks.

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

  5. Rob Wultsch says:

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

    YMMV

  6. Daniël van Eeden says:

    Maybe using events could be nice instead of cron.

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

    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 WHERE TABLE_SCHEMA NOT IN (‘mysql’, ‘information_schema’);

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

  8. peter says:

    Morgan,

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

  9. peter says:

    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.

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

  11. shazamo says:

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

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

  13. Alex says:

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

Speak Your Mind

*