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
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.