Rob Church wrote:
On 12/04/07, Rolf Lampa rolf.lampa@rilnet.com wrote:
General advice regarding "optimal" settings (which of course takes some context, but some contexts may be typical... no?). Etc.
We set up most tables using InnoDB as the preferred storage engine. InnoDB implements proper (ACID-compliant) transactions, which help us to avoid expensive locks on the page, revision and text tables that can often lead to deadlocks and so forth under high load.
One thing that puzzles me is why the import to InnoDB is so incredible slow compared to MYISAM. I use the mysql.exe < datafile.sql - import. I also (think I) have tried "all the tricks" regarding performance settings* found on the MySQL sites etc. But no success, InnoDB simply remain magnitudes slower than MYSIAM in importing the same data, on the same machine. I have tried (among other things):
1. Sorting the data, 2. SET AUTOCOMMIT=0; 3. SET UNIQUE_CHECKS=0; 4. SET FOREIGN_KEY_CHECKS=0; 5. ALTER TABLE `text` DISABLE KEYS; ... 6. Extended inserts (like 256KBs)
And not to mention settings for buffers here and buffers there, but to no avail, InnoDB takes 10, 20 times longer than MYSIAM (I quit import with innodb after like ~15 hrs, with much more to go) and it simply refuses to import any faster. I don't seem to improve even a lttle, so that means I'm not even on the track...!
With MYISAM it just says swoop! 10GB of data in little more than half an hour (on the same machine).
Strange. I really don't have a clue. <scratching head>
// Rolf Lampa