Home > Uncategorized > Show MySQL engine tablespace size

Show MySQL engine tablespace size

October 22nd, 2014 Leave a comment Go to comments

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

Categories: Uncategorized Tags:
  1. No comments yet.
  1. No trackbacks yet.