How to query information_schema to get database & table sizes
8:57 PM
1) To get the database sizes run the following query:
2) To get table sizes in each database run:
mysql>SELECT table_schema "Data Base Name", sum( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema; Sample output: +-------------------+---------------------+ | Data Base Name | Data Base Size in MB | +-------------------+---------------------+ | authentication | 40.57812500 | | billing | 34143.17463207 | | dbbench | 0.03125000 | | devdrupal | 2392.32812500 | | devphpbb | 17.90625000 | | devwordpress | 8.52945709 | | devwordpressdb | 29.04991531 | | dev_portal | 109.46152496 | | everyonewordpress | 1.51562500 | | information_schema | 0.00390625 | | infradb | 2457.31250000 | | jbilling | 0.96446705 | | jbillingreports | 1.93750000 | | masterdb | 2958.42187500 | | mysql | 0.50733185 | | opsourcemon | 0.01562500 | | phpbb | 1.21875000 | | restdb | 0.21875000 | | salesforce | 532.84375000 | | sfwordpress | 4.66656113 | | supwordpress | 2.46875000 | | userdb | 43791.23437500 | | utildb | 94.62500000 | | wordpress | 6.55899048 | +-------------------+---------------------+ 24 rows in set (17.99 sec)
2) To get table sizes in each database run:
mysql>SELECT s.schema_name,CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free, IFNULL(ROUND((((SUM(t.data_length) +SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY pct_used DESC\G Sample Output: +--------------------------------------------------------------------------+--------------+ | Table Name | DBNAME | Quant of Rows | Total Size Kb | +--------------------------------------------------------------------------+--------------+ | CHARACTER_SETS | information_schema | NULL | 0.00 | | COLLATIONS | information_schema | NULL | 0.00 | | COLLATION_CHARACTER_SET_APPLICABILITY | information_schema | NULL | 0.00 | | COLUMNS | information_schema | NULL | 1.00 | | COLUMN_PRIVILEGES | information_schema | NULL | 0.00 | | INDEX_STATISTICS | information_schema | NULL | 0.00 | | KEY_COLUMN_USAGE | information_schema | NULL | 0.00 | | ROUTINES | information_schema | NULL | 1.00 | | SCHEMATA | information_schema | NULL | 0.00 | | SCHEMA_PRIVILEGES | information_schema | NULL | 0.00 | | STATISTICS | information_schema | NULL | 0.00 | | TABLES | information_schema | NULL | 0.00 | | TABLE_CONSTRAINTS | information_schema | NULL | 0.00 | | TABLE_PRIVILEGES | information_schema | NULL | 0.00 | | TABLE_STATISTICS | information_schema | NULL | 0.00 | | TRIGGERS | information_schema | NULL | 1.00 | | USER_PRIVILEGES | information_schema | NULL | 0.00 | | USER_STATISTICS | information_schema | NULL | 0.00 | | VIEWS | information_schema | NULL | 1.00 | | authdata | authentication | 84683 | 41152.00 | | cryptkeys | authentication | 317 | 384.00 | | tokens | authentication | 3 | 16.00 | | Error | billing | 1 | 3060.13 | | account | billing | 79661 | 21600.00 | | account_plan | billing | 13 | 16.00 | | account_plan_composition | billing | 129 | 48.00 | | account_plan_composition_extension | billing | 39 | 48.00 | | account_plan_default_extension | billing | 13 | 48.00 | | account_plan_usage_history | billing | 50033 | 6688.00 | | activity_reason | billing | 1 | 16.00 |
0 comments