Archive

Archive for October, 2014

Show MySQL engine tablespace size

October 22nd, 2014 No 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:

Speeding up percona xtrabackup restores

October 10th, 2014 No comments

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.

e.g.
innobackupex –move-back /backup/

Categories: Tech Problems Tags: