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
SELECT ENGINE, SUM(data_length+index_length) RIBPS
FROM information_schema.tables AAA
GROUP BY ENGINE
HAVING RIBPS != 0
Now I have that information I can adjust my INNODB buffers and reduce MyISAM caches
I started playing around with using xtrabackup (or more specifically innobackupex) to backup MySQL. Most of our tables are now innodb so it didn’t make sense to keep dumping everything out via mysqldump.
I had a clone of our master db server in our virtual environment that I was trying to restore the backup onto, but it was taking hours (using innobackupex –copy-back /backup/). I figured that the IO on my virtual servers was just crap and I’d have to grin and bear it. There doesn’t seem to be much around about restoring using innobackupex, even the command options are limited for restores so I thought –copy-back was the only way.
It seems that if your backup is on the same filesystem as where it’s going to end up then it’s a lot faster to use the –move-back option. This changed my restore time from hours to seconds.
innobackupex –move-back /backup/