Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Some interesting metric would be cpu spent in iowait, or the output of iostat. –  Roy Nov 9 '12 at 10:07
    
Yeah. Smells like another "Low grade VPS with dead slow IO subsystem". –  TomTom Jul 7 '13 at 15:04
add comment

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

share|improve this answer
    
700mb would be about - 10 seconds on my low end database server using 4 x 450gb SAS 10RPM drives. I regularly pull / write more than 100mb/second during backups. But note that this is a 4 disc Raid 10.... likely a LOT faster than what the OP has. –  TomTom Jul 7 '13 at 15:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.