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.


Previous:
Next:



MaxCDN Site Acceleration

Get Cost Effective Content Delivery
Unbeatable speed, stats, & price
http://www.maxcdn.com



No Comments

Trackbacks/Pingbacks

  1. vBulletin 4.x upgrade / installs - MyISAM and InnoDB tables database size | vbtechsupport.com - [...] using MyISAM or InnoDB for the above 6 listed tables, you can use these specific queries listed at http://vbtechsupport.com/462/Example, …
Add Comment Register

Leave a Comment