I recently had the need to do some troubleshooting on some of my larger MySQL databases. It seems one of our developers turned off innodb_file_per_table = 1 in the /etc/my.cnf. This feature tells MySQL to for every Innodb table to create a separate file, if it’s not turned on, it stores all the tables in 1 large file. This can be extremely painful by itself to maintain as it grows. Anyway, I had the need to do a little research on database and table sizes and came across a post by Peter from MySQLPerformanceBlog.com
In this first example, I was able to see which Storage Engines where using the most disk space, the number of tables, etc:
- 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)
In this next example, I’m able to see which databases are consuming the most:
- 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)
