MySQL Table Engine Data Usage statistics
Came across a useful MySQL query at themattreid.com blog to use MySQL information_schema to calculate MySQL table engine data usage statistics for Innodb and MyISAM.
select round(sum(innodb_data_size + innodb_index_size) / (innodb_data_free + sum(innodb_data_size + innodb_index_size))) * 100 as 'innodb_tablespace_utilization_perc' , (data_size + index_size) / gb as total_size_gb , index_size / gb as index_size_gb , data_size / gb as data_size_gb , sum(innodb_index_size + innodb_data_size) / pow(1024,3) as innodb_total_size_gb , innodb_data_size / pow(1024,3) as innodb_data_size_gb , innodb_index_size / pow(1024,3) as innodb_index_size_gb , sum(myisam_index_size + myisam_data_size) / pow(1024,3) as myisam_total_size_gb , myisam_data_size / pow(1024,3) as myisam_data_size_gb , myisam_index_size / pow(1024,3) as myisam_index_size_gb , index_size / (data_size + index_size) * 100 as perc_index , data_size / (data_size + index_size) * 100 as perc_data , innodb_index_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_index , innodb_data_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_data , myisam_index_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_index , myisam_data_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_data , innodb_index_size / index_size * 100 as innodb_perc_total_index , innodb_data_size / data_size * 100 as innodb_perc_total_data , myisam_index_size / index_size * 100 as myisam_perc_total_index , myisam_data_size / data_size * 100 as myisam_perc_total_data from ( select sum(data_length) data_size, sum(index_length) index_size, sum(if(engine = 'innodb', data_length, 0)) as innodb_data_size, sum(if(engine = 'innodb', index_length, 0)) as innodb_index_size, sum(if(engine = 'myisam', data_length, 0)) as myisam_data_size, sum(if(engine = 'myisam', index_length, 0)) as myisam_index_size, sum(if(engine = 'innodb', data_free, 0)) as innodb_data_free, pow(1024, 3) gb from information_schema.tables ) a\G |
the resulting output looks like this
mysql> select round(sum(innodb_data_size + innodb_index_size) / (innodb_data_free + sum(innodb_data_size + innodb_index_size))) * 100 as 'innodb_tablespace_utilization_perc' -> , (data_size + index_size) / gb as total_size_gb -> , index_size / gb as index_size_gb -> , data_size / gb as data_size_gb -> , sum(innodb_index_size + innodb_data_size) / pow(1024,3) as innodb_total_size_gb -> , innodb_data_size / pow(1024,3) as innodb_data_size_gb -> , innodb_index_size / pow(1024,3) as innodb_index_size_gb -> , sum(myisam_index_size + myisam_data_size) / pow(1024,3) as myisam_total_size_gb -> , myisam_data_size / pow(1024,3) as myisam_data_size_gb -> , myisam_index_size / pow(1024,3) as myisam_index_size_gb -> , index_size / (data_size + index_size) * 100 as perc_index -> , data_size / (data_size + index_size) * 100 as perc_data -> , innodb_index_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_index -> , innodb_data_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_data -> , myisam_index_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_index -> , myisam_data_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_data -> , innodb_index_size / index_size * 100 as innodb_perc_total_index -> , innodb_data_size / data_size * 100 as innodb_perc_total_data -> , myisam_index_size / index_size * 100 as myisam_perc_total_index -> , myisam_data_size / data_size * 100 as myisam_perc_total_data -> from ( select sum(data_length) data_size, -> sum(index_length) index_size, -> sum(if(engine = 'innodb', data_length, 0)) as innodb_data_size, -> sum(if(engine = 'innodb', index_length, 0)) as innodb_index_size, -> sum(if(engine = 'myisam', data_length, 0)) as myisam_data_size, -> sum(if(engine = 'myisam', index_length, 0)) as myisam_index_size, -> sum(if(engine = 'innodb', data_free, 0)) as innodb_data_free, -> pow(1024, 3) gb from information_schema.tables ) -> a\G *************************** 1. row *************************** innodb_tablespace_utilization_perc: NULL total_size_gb: 30.0126212192699 index_size_gb: 10.6734915226698 data_size_gb: 19.3391296966001 innodb_total_size_gb: 0 innodb_data_size_gb: 0 innodb_index_size_gb: 0 myisam_total_size_gb: 29.9825009061024 myisam_data_size_gb: 19.3215826703236 myisam_index_size_gb: 10.6609182357788 perc_index: 35.563343300 perc_data: 64.436656600 innodb_perc_index: NULL innodb_perc_data: NULL myisam_perc_index: 35.557134700 myisam_perc_data: 64.442865200 innodb_perc_total_index: 0 innodb_perc_total_data: 0 myisam_perc_total_index: 99.882200800 myisam_perc_total_data: 99.909266700 1 row in set (0.03 sec) |
Another 3 queries I use myself are below where databasename is your database name:
Calculate Total Database Index Size
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'databasename'; |
Calculate the Total Size of Table Data
SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024), 2), ' MB') AS 'Total Data Size' FROM information_schema.TABLES WHERE table_schema LIKE 'databasename'; |
Per Table Sizes
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows,2),' Rows') AS 'Number of Rows', ENGINE AS 'Storage Engine', CONCAT(ROUND(data_length/(1024*1024),2),'MB') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024),2),'MB') AS 'Index Size' , CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'MB') AS'Total' FROM information_schema.TABLES WHERE table_schema LIKE 'databasename'; |
These queries can be run outside of mysql client command line as well in normal SSH telnet via mysql -e just remember to change databasename to the name of your database and change mysqlusername to your mysql username:
MyISAM and InnoDB storage engine data usage
mysql -u mysqlrootusername -p -e "select round(sum(innodb_data_size + innodb_index_size) / (innodb_data_free + sum(innodb_data_size + innodb_index_size))) * 100 as 'innodb_tablespace_utilization_perc' , (data_size + index_size) / gb as total_size_gb , index_size / gb as index_size_gb , data_size / gb as data_size_gb , sum(innodb_index_size + innodb_data_size) / pow(1024,3) as innodb_total_size_gb , innodb_data_size / pow(1024,3) as innodb_data_size_gb , innodb_index_size / pow(1024,3) as innodb_index_size_gb , sum(myisam_index_size + myisam_data_size) / pow(1024,3) as myisam_total_size_gb , myisam_data_size / pow(1024,3) as myisam_data_size_gb , myisam_index_size / pow(1024,3) as myisam_index_size_gb , index_size / (data_size + index_size) * 100 as perc_index , data_size / (data_size + index_size) * 100 as perc_data , innodb_index_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_index , innodb_data_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_data , myisam_index_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_index , myisam_data_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_data , innodb_index_size / index_size * 100 as innodb_perc_total_index , innodb_data_size / data_size * 100 as innodb_perc_total_data , myisam_index_size / index_size * 100 as myisam_perc_total_index , myisam_data_size / data_size * 100 as myisam_perc_total_data from ( select sum(data_length) data_size, sum(index_length) index_size, sum(if(engine = 'innodb', data_length, 0)) as innodb_data_size, sum(if(engine = 'innodb', index_length, 0)) as innodb_index_size, sum(if(engine = 'myisam', data_length, 0)) as myisam_data_size, sum(if(engine = 'myisam', index_length, 0)) as myisam_index_size, sum(if(engine = 'innodb', data_free, 0)) as innodb_data_free, pow(1024, 3) gb from information_schema.tables ) a\G" |
Calculate Total Database Index Size
mysql -u mysqlusername -p -e "SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';" |
Calculate the Total Size of Table Data
mysql -u mysqlusername -p -e "SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024), 2), ' MB') AS 'Total Data Size' FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';" |
Per Table Sizes
mysql -u mysqlusername -p -e "SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows,2),' Rows') AS 'Number of Rows',ENGINE AS 'Storage Engine',CONCAT(ROUND(data_length/(1024*1024),2),'MB') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024),2),'MB') AS 'Index Size' ,CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'MB') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';" |
Each command will prompt you for your mysqlusername’s password to proceed.
