DB import tweaks for InnoDB

I think this is a good idea. Any objections? Would be good to get a patch.

Yeah. I don't know anything at all about innodb, but it looks like it runs under mysql, and therefore uses mysqldump, etc.; I presume that there are cli options that you can pass to mysqldump to get those options to be inserted in your dump file?

We have this duplicate issue here: #518184: Allow Users To Set myqldump Arguments In drushrc.php (for drush sql dump). If that were implemented, it could be used to provide this functionality. Alternately, we could put innodb-specific options to drush sql-dump / sql-sync. A patch would be most welcome.

Good deal; marking as fixed.

DB imports still seem to take a long time. E.g. A 12MB dump file is taking me over 800 seconds to import. After doing some research, I found an article that mentions turning-off autocommit:

http://stackoverflow.com/questions/2167522/innodb-takes-over-an-hour-to-import-600mb-file-myisam-in-a-few-minutes/2167641#2167641

After adding a short SQL statement to the top of the dump the import took less than 30 seconds:

SET autocommit=0;

In the article above, it did mention outputing the dump using "--opt" and the dump file should import faster, but after debugging Drush I saw it's already using this parameter. It wasn't improved until I added the code above.

Not sure if there are any negative side effects to this, but if not perhaps it would make sense to add to the dumps created by Drush? Someone with more experience have any input on this?

I'm not really in favor of post-processing the dump file. Surely there must be a cli option or some other way to get mysqldump to put in the needed options.

The "--no-autocommit" option for mysqldump should accomplish something similar to what was suggested.

Background info on autocommit for those who don't know:
By default, innodb commits changes after every statement (really not necessary when importing a dump). alexfisher's method would only commit once after the entire import is finished (not necessarily a bad thing). The mysqldump option provides a reasonable compromise between the two (commit once per table) that should still greatly improve import performance.

To clarify, I wasn't specifically suggesting post-processing. Just wanted to explain the line of SQL had the desired effect of speeding import up. I think jkaskins suggestion is more along the lines of what I was envisioning.

committed --no-autocommit to 4.x and 5.x. thanks.