Researching your MySQL table sizes

March 18, 2008

Author

Peter Zaitsev

Insight for DBAs

Share this Post:

I posted a simple INFORMATION_SCHEMA query to find largest MySQL tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:

Find total number of tables, rows, total data in index size for given MySQL Instance

SELECT count(*) tables,<br>  concat(round(sum(table_rows)/1000000,2),'M') rows,<br>  concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,<br>  concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,<br>  concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,<br>  round(sum(index_length)/sum(data_length),2) idxfrac<br>FROM information_schema.TABLES;<br>+--------+----------+---------+--------+------------+---------+<br>| tables | rows     | data    | idx    | total_size | idxfrac |<br>+--------+----------+---------+--------+------------+---------+<br>|   1538 | 1623.91M | 314.00G | 36.86G | 350.85G    |    0.12 |<br>+--------+----------+---------+--------+------------+---------+<br>1 row in set (52.56 sec)<br>

SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES; +--------+----------+---------+--------+------------+---------+ | tables | rows | data | idx | total_size | idxfrac | +--------+----------+---------+--------+------------+---------+ | 1538 | 1623.91M | 314.00G | 36.86G | 350.85G | 0.12 | +--------+----------+---------+--------+------------+---------+ 1 row in set (52.56 sec)

Find the same data using some filter
I often use similar queries to find space used by particular table “type” in sharded environment when multiple tables with same structure and similar name exists:

SELECT count(*) tables,<br>       concat(round(sum(table_rows)/1000000,2),'M') rows,<br>       concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,<br>       concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,<br>       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,<br>       round(sum(index_length)/sum(data_length),2) idxfrac<br>       FROM information_schema.TABLES<br>       WHERE  table_name like "%performance_log%";<br>+--------+---------+---------+-------+------------+---------+<br>| tables | rows    | data    | idx   | total_size | idxfrac |<br>+--------+---------+---------+-------+------------+---------+<br>|    120 | 370.29M | 163.97G | 0.00G | 163.97G    |    0.00 |<br>+--------+---------+---------+-------+------------+---------+<br>1 row in set (0.03 sec)<br>

SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_name like "%performance_log%"; +--------+---------+---------+-------+------------+---------+ | tables | rows | data | idx | total_size | idxfrac | +--------+---------+---------+-------+------------+---------+ | 120 | 370.29M | 163.97G | 0.00G | 163.97G | 0.00 | +--------+---------+---------+-------+------------+---------+ 1 row in set (0.03 sec)

Find biggest databases

SELECT<br>        count(*) tables,<br>        table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows,<br>        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,<br>        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,<br>        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,<br>        round(sum(index_length)/sum(data_length),2) idxfrac<br>        FROM information_schema.TABLES<br>        GROUP BY table_schema<br>        ORDER BY sum(data_length+index_length) DESC LIMIT 10;<br>+--------+--------------------+-------+-------+-------+------------+---------+<br>| tables | table_schema       | rows  | data  | idx   | total_size | idxfrac |<br>+--------+--------------------+-------+-------+-------+------------+---------+<br>|     48 | cacti              | 0.01M | 0.00G | 0.00G | 0.00G      |    0.72 |<br>|     17 | mysql              | 0.00M | 0.00G | 0.00G | 0.00G      |    0.18 |<br>|      4 | pdns               | 0.00M | 0.00G | 0.00G | 0.00G      |    1.00 |<br>|      2 | test               | 0.00M | 0.00G | 0.00G | 0.00G      |    0.12 |<br>|     16 | information_schema | NULL  | 0.00G | 0.00G | 0.00G      |    NULL |<br>+--------+--------------------+-------+-------+-------+------------+---------+<br>5 rows in set (0.32 sec)<br>

SELECT count(*) tables, table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; +--------+--------------------+-------+-------+-------+------------+---------+ | tables | table_schema | rows | data | idx | total_size | idxfrac | +--------+--------------------+-------+-------+-------+------------+---------+ | 48 | cacti | 0.01M | 0.00G | 0.00G | 0.00G | 0.72 | | 17 | mysql | 0.00M | 0.00G | 0.00G | 0.00G | 0.18 | | 4 | pdns | 0.00M | 0.00G | 0.00G | 0.00G | 1.00 | | 2 | test | 0.00M | 0.00G | 0.00G | 0.00G | 0.12 | | 16 | information_schema | NULL | 0.00G | 0.00G | 0.00G | NULL | +--------+--------------------+-------+-------+-------+------------+---------+ 5 rows in set (0.32 sec)

Data Distribution by Storage Engines
You can change this query a bit and get most popular storage engines by number of tables or number of rows instead of data stored.

SELECT engine,<br>        count(*) tables,<br>        concat(round(sum(table_rows)/1000000,2),'M') rows,<br>        concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,<br>        concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,<br>        concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,<br>        round(sum(index_length)/sum(data_length),2) idxfrac<br>        FROM information_schema.TABLES<br>        GROUP BY engine<br>        ORDER BY sum(data_length+index_length) DESC LIMIT 10;<br>+------------+--------+---------+---------+--------+------------+---------+<br>| engine     | tables | rows    | data    | idx    | total_size | idxfrac |<br>+------------+--------+---------+---------+--------+------------+---------+<br>| MyISAM     |   1243 | 941.06M | 244.09G | 4.37G  | 248.47G    |    0.02 |<br>| InnoDB     |    280 | 682.82M | 63.91G  | 32.49G | 96.40G     |    0.51 |<br>| MRG_MyISAM |      1 | 13.66M  | 6.01G   | 0.00G  | 6.01G      |    0.00 |<br>| MEMORY     |     14 | 0.00M   | 0.00G   | 0.00G  | 0.00G      |    NULL |<br>+------------+--------+---------+---------+--------+------------+---------+<br>4 rows in set (14.02 sec)<br>

SELECT engine, count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10; +------------+--------+---------+---------+--------+------------+---------+ | engine | tables | rows | data | idx | total_size | idxfrac | +------------+--------+---------+---------+--------+------------+---------+ | MyISAM | 1243 | 941.06M | 244.09G | 4.37G | 248.47G | 0.02 | | InnoDB | 280 | 682.82M | 63.91G | 32.49G | 96.40G | 0.51 | | MRG_MyISAM | 1 | 13.66M | 6.01G | 0.00G | 6.01G | 0.00 | | MEMORY | 14 | 0.00M | 0.00G | 0.00G | 0.00G | NULL | +------------+--------+---------+---------+--------+------------+---------+ 4 rows in set (14.02 sec)

Trivial but handy.