Skip to main content

Show MySQL engine tablespace size

ยท One min read

I have been trying to migrate everything in MySQL to use INNODB (death to all MyISAM), but was unsure of how much data was being stored in each storage engine. You can use the following query to give a total usage for all engines:

SELECT ENGINE, CONCAT(FORMAT(RIBPS/POWER(1024,pw),2),SUBSTR(' KMGT',pw+1,1)) `Usage` FROM
(
    SELECT ENGINE,RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT ENGINE, SUM(data_length+index_length) RIBPS
		FROM information_schema.tables AAA
		GROUP BY ENGINE
		HAVING RIBPS != 0
    ) AA
) A;

Now I have that information I can adjust my INNODB buffers and reduce MyISAM caches