MySQL analytics: information_schema polling for table engine percentages

If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!

select 
(select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb, 
(select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb, 
(select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, 

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) as perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='innodb') as innodb_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='innodb') as innodb_perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='myisam') as myisam_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='myisam') as myisam_perc_data,

(select (select sum(INDEX_LENGTH) from tables where ENGINE='innodb') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_innodb,
(select (select sum(DATA_LENGTH) from tables where ENGINE='innodb') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_innodb,

(select (select sum(INDEX_LENGTH) from tables where ENGINE='myisam') / (select sum(INDEX_LENGTH) from tables))*100 as perc_total_index_myisam,
(select (select sum(DATA_LENGTH) from tables where ENGINE='myisam') / (select sum(DATA_LENGTH) from tables))*100 as perc_total_data_myisam

from tables limit 1\G

This will output something along the lines of:

          total_size_gb: 2.7505537783727
         total_index_gb: 0.018660545349121
          total_data_gb: 2.7318932330236
             perc_index: 0.6784
              perc_data: 99.3216
      innodb_perc_index: 0.6502
       innodb_perc_data: 99.3498
      myisam_perc_index: 17.5120
       myisam_perc_data: 82.4880
perc_total_index_innodb: 95.6713
 perc_total_data_innodb: 99.8607
perc_total_index_myisam: 4.3287
 perc_total_data_myisam: 0.1393

4 thoughts on “MySQL analytics: information_schema polling for table engine percentages”

Suresh Kuna says:

2010/09/24 at 6:11 am

Good one

Roland Bouman says:

2010/09/27 at 10:45 am

Hi!

while I think the idea is nice, the query as you propose it does a lot of unnecessary work. Normally I am not big fan of premature optimization, but from experience I learned to avoid or at least be very careful with accessing MySQL’s information_schema tables multiple times in the same statement.

For example, after repeatedly running your statement (excluding the first run) I find it takes about 2min44sec on average to run it. With a slight modification to access information_schema.tables only once:

select (data_size + index_size) / gb as total_size
, index_size / gb as index_size
, data_size / gb as data_size
, 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
, pow(1024, 3) gb
from tables
) a