How to prevent high memory, CPU and time consumption by MySQL restore?

Question

I have a local test machine with 2 GB RAM and a dual core processor.

I imported a dump on that using

mysql -uuser -psecret < script.sql

script.sql is a 700 MB file which contains a single database with more than 100 tables.

Restore took approximately 25 minutes.

Question: Why it took so long time? I have already optimized my server settings. The dump also contains extended inserts and disables keys). Is 700 MB is too large for this kind of machine? mysqld was using 100% of one of the CPU when I checked using htop and 22% of the memory.

Now what actually I want is to convert all my tables of that database to InnoDB. So for that I executed a simple shell command:

mysql -uuser -psecret -Bse "select Concat('Alter Table ' , TABLE_SCHEMA ,'.',TABLE_NAME , ' Engine= \'InnoDB\' ;') from information_schema.tables where ENGINE='myisam' and TABLE_SCHEMA='db_name';" > Innodb.sql

so Innodb.sql contains the script for converting tables to InnoDB.

Now when I run Innodb.sql one of my table takes more than 20 minutes for conversion. It contains only 1,378,397 records.

In total it takes more than 30 minutes. In the meantime memory usage by mysqld daemon was 73%. The CPU usage was OK at this time.

How can I minimize the time consumtion? Should I go for changing MySQL server settings or anything else?

If anybody wants my my.cnf setting I will share that.

Answer 1

I think the second point more or less shows you where your bottleneck is. I would be willing to bet that if you look at top or equivalent on your platform you will see high I/O wait time.

In essence you are probably waiting for your hard disks as tables are rewritten. Also innodb has some oddities regarding locks here so it isn't clear that these are all sequential writes.

I can't tell you about expected MySQL performance other than that you seem to be hitting write performance bottlenecks. I can tell you that a 700MB dump won't restore super-fast on any db with ordinary hard drives, but also that 25 min does seem a bit high based on my experience on PostgreSQL.