Mar
18
2008
Researching your MySQL table sizes
Posted by MySQL Performance Blog on Tue 18 Mar 2008 02:28 UTC

I posted a simple INFORMATION_SCHEMA query to find largest 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

PLAIN TEXT SQL:

  1. mysql> SELECT count(*) TABLES,
  2.     ->        concat(round(sum(table_rows)/1000000,2),'M') rows,
  3.     ->        concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  4.     ->    concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  5.     ->    concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  6.     ->    round(sum(index_length)/sum(data_length),2) idxfrac
  7.     ->    FROM information_schema.TABLES;
  8. +--------+----------+---------+--------+------------+---------+
  9. | TABLES | rows     | DATA    | idx    | total_size | idxfrac |
  10. +--------+----------+---------+--------+------------+---------+
  11. |   1538 | 1623.91M | 314.00G | 36.86G | 350.85G    |    0.12 |
  12. +--------+----------+---------+--------+------------+---------+
  13. 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:

PLAIN TEXT SQL:

  1. mysql> SELECT count(*) TABLES,
  2.     ->        concat(round(sum(table_rows)/1000000,2),'M') rows,
  3.     ->        concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  4.     ->    concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  5.     ->    concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  6.     ->    round(sum(index_length)/sum(data_length),2) idxfrac
  7.     ->    FROM information_schema.TABLES
  8.     ->    WHERE  table_name LIKE "%performance_log%";
  9. +--------+---------+---------+-------+------------+---------+
  10. | TABLES | rows    | DATA    | idx   | total_size | idxfrac |
  11. +--------+---------+---------+-------+------------+---------+
  12. |    120 | 370.29M | 163.97G | 0.00G | 163.97G    |    0.00 |
  13. +--------+---------+---------+-------+------------+---------+
  14. 1 row IN SET (0.03 sec)

Find biggest databases

PLAIN TEXT SQL:

  1. mysql> SELECT
  2.     ->        count(*) TABLES,
  3.     ->        table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows,
  4.     ->        concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  5.     ->    concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  6.     ->    concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  7.     ->    round(sum(index_length)/sum(data_length),2) idxfrac
  8.     ->    FROM information_schema.TABLES
  9.     ->    GROUP BY table_schema
  10.     ->    ORDER BY sum(data_length+index_length) DESC LIMIT 10;
  11. +--------+--------------------+-------+-------+-------+------------+---------+
  12. | TABLES | table_schema       | rows  | DATA  | idx   | total_size | idxfrac |
  13. +--------+--------------------+-------+-------+-------+------------+---------+
  14. |     48 | cacti              | 0.01M | 0.00G | 0.00G | 0.00G      |    0.72 |
  15. |     17 | mysql              | 0.00M | 0.00G | 0.00G | 0.00G      |    0.18 |
  16. |      4 | pdns               | 0.00M | 0.00G | 0.00G | 0.00G      |    1.00 |
  17. |      2 | test               | 0.00M | 0.00G | 0.00G | 0.00G      |    0.12 |
  18. |     16 | information_schema | NULL  | 0.00G | 0.00G | 0.00G      |    NULL |
  19. +--------+--------------------+-------+-------+-------+------------+---------+
  20. 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.

PLAIN TEXT SQL:

  1. mysql> SELECT engine,
  2.     ->        count(*) TABLES,
  3.     ->        concat(round(sum(table_rows)/1000000,2),'M') rows,
  4.     ->        concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
  5.     ->    concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
  6.     ->    concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  7.     ->    round(sum(index_length)/sum(data_length),2) idxfrac
  8.     ->    FROM information_schema.TABLES
  9.     ->    GROUP BY engine
  10.     ->    ORDER BY sum(data_length+index_length) DESC LIMIT 10;
  11. +------------+--------+---------+---------+--------+------------+---------+
  12. | engine     | TABLES | rows    | DATA    | idx    | total_size | idxfrac |
  13. +------------+--------+---------+---------+--------+------------+---------+
  14. | MyISAM     |   1243 | 941.06M | 244.09G | 4.37G  | 248.47G    |    0.02 |
  15. | InnoDB     |    280 | 682.82M | 63.91G  | 32.49G | 96.40G     |    0.51 |
  16. | MRG_MyISAM |      1 | 13.66M  | 6.01G   | 0.00G  | 6.01G      |    0.00 |
  17. | MEMORY     |     14 | 0.00M   | 0.00G   | 0.00G  | 0.00G      |    NULL |
  18. +------------+--------+---------+---------+--------+------------+---------+
  19. 4 rows IN SET (14.02 sec)

Trivially but handy.

Entry posted by peter | 5 comments

Add to: | | | |