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:
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)
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.
This entry comes from 本站原创 and has been read for 226 times.
