March 17, 2008
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
SQL:
-
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:
SQL:
-
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
SQL:
-
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.
SQL:
-
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.
Entry posted by peter |
One comment
Add to: 
| 
| 
| 
| 
