Researching your MySQL table sizes
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
-
mysql> 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:
-
mysql> 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
-
mysql> 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.
-
mysql> 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)
Trivially but handy.
10 Comments











del.icio.us
digg
Some very handy queries here, Peter. Thank you. This is going to my bookmarks.
Comment :: March 17, 2008 @ 11:06 pm
[...] Via MySQL Performance Blog [...]
Pingback :: March 18, 2008 @ 3:05 am
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
ryan, that’s just an American slang version of the word, not really a misspelling.
Comment :: March 18, 2008 @ 7:18 am
Hum, learned two things today then
Comment :: March 18, 2008 @ 1:36 pm
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
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
[...] MySQL Performance blog he encontrado algunas consultas MySQL muy útiles a la hora de controlar el tamaño y [...]
Pingback :: May 14, 2008 @ 7:52 am
[...] Vía: MySQL Performance Blog [...]
Pingback :: May 14, 2008 @ 7:05 pm
[...] MySQL Performance blog he encontrado algunas consultas MySQL muy útiles a la hora de controlar el tamaño y [...]
Pingback :: June 8, 2008 @ 3:08 pm