[ themattreid ]

This is a follow up to my previous post titled “MySQL analytics: information_schema polling for table engine percentages”. Here’s an updated query with more output and quicker execution time. What you get: innodb table space utilization percentage, data+index usage total and per innodb/myisam engine, innodb data/index/percentage, myisam data/index/percentages, and overall percentage values. Rather useful for profiling your table engine usage.

Sample output:
innodb_tablespace_utilization_perc: 100
total_size_gb: 26.275011910126
index_size_gb: 2.994891166687
data_size_gb: 23.280120743439
innodb_total_size_gb: 6.751220703125
innodb_data_size_gb: 5.2576751708984
innodb_index_size_gb: 1.4935455322266
myisam_total_size_gb: 19.523791207001
myisam_data_size_gb: 18.02244557254
myisam_index_size_gb: 1.5013456344604
perc_index: 11.3982
perc_data: 88.6018
innodb_perc_index: 22.1226
innodb_perc_data: 77.8774
myisam_perc_index: 7.6898
myisam_perc_data: 92.3102
innodb_perc_total_index: 49.8698
innodb_perc_total_data: 22.5844
myisam_perc_total_index: 50.1302
myisam_perc_total_data: 77.4156

 
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