[Mediawiki-l] MYISAM or InnoDB best for Mediawiki?

[Rolf Lampa]

Rob Church wrote:
> On 12/04/07, Rolf Lampa <rolf.lampa at 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