March 17, 2008

Researching your MySQL table sizes

Posted by peter |

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.

Posted by peter @ 7:28 pm :: community, production, tips  
Print This Post Print This Post book mark Researching your MySQL table sizes in del.icio.usdel.icio.us :: submit Researching your MySQL table sizes to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :Mail clients and Databases::Using Multiple Key Caches for MyISAM Scalability::Why InnoDB index cardinality varies strangely:
 

11 Comments »

  1. 1. Artem Russakovskii

    Some very handy queries here, Peter. Thank you. This is going to my bookmarks.

    Comment :: March 17, 2008 @ 11:06 pm

  2. 2. ryan

    These have defiantly gone into my snippets list, thanks.

    Also, BTW…
    Your syntax highlighter has a spelling error. When you switch to ‘PLAIN TEXT’, the option to switch back to the highlighted version is tagged ‘HILITED HTML’ – ought to be ‘HIGHLIGHTED HTML’.

    Comment :: March 18, 2008 @ 5:09 am

  3. 3. Artem Russakovskii

    ryan, that’s just an American slang version of the word, not really a misspelling.

    Comment :: March 18, 2008 @ 7:18 am

  4. 4. ryan

    Hum, learned two things today then :-)

    Comment :: March 18, 2008 @ 1:36 pm

  5. 5. Prashanth Gajra

    Anybody can let me know how can i get the total number of tables in database without using a SELECT query but by using a stored procedure?

    Comment :: March 27, 2008 @ 11:19 pm

  6. 6. peter

    Well what is the problem with select query ?
    You can wrap it around by stored procedure if you need to.

    Comment :: March 28, 2008 @ 2:48 pm

  7. 7. Alexey Kupershtokh

    Could you remove all the “->” in order to allow people copy-paste right from here?

    Comment :: September 12, 2008 @ 8:20 am

  8. 8. Neil

    Why does SUM(table_rows) fluctuate so wildly? I run this query:

    SELECT CONCAT(SUM(table_rows)/1000000,’M') AS no_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA = ”;

    and no_rows was first 2.73M, then 2.83M, then 2.81M, then 2.78M, then 2.81M…?

    Why does this vary?

    Comment :: November 6, 2008 @ 10:05 am

  9. 9. Artem Russakovskii

    It’s a restriction of InnoDB. See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html. It gives an approximate count, which fluctuates a lot.

    Comment :: November 6, 2008 @ 10:37 am

  10. 10. Taha Patel

    This is really cool. Thanks.

    Comment :: January 28, 2009 @ 9:14 am

  11. 11. Gerry

    Another vote for putting the queries on one line for copy and pasting.

    Comment :: July 24, 2009 @ 2:56 pm

 

Subscribe without commenting

Trackbacks/Pingbacks