[SOLVED] MyISAM is about 3 times faster then InnoDB on massive write operations

UPDATE

Ok folks! Can't wait to share it with you - a solution of the problem which I and probably many of you had experienced. Now it's done!

Symptoms

Drupal runs very slowly on massive database operations like rebuilding it's registry. It executes more then thousand of queries and performs probably hundreds of commits (which are by the way are not displayed in Devel and Devel's query statistics extremely inaccurate).

Optimization

Fixing innodb_flush_log_at_trx_commit

Open mysql console and check the value of innodb_flush_log_at_trx_commit variable:

mysql> show variables like "innodb_flush_log_at_trx_commit";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

According to MySQL 5.5. documentaion:

When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.

When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.

By default this value is set to 1:

The default value of 1 is required for full ACID compliance.

But this is definitely not what you want. You should change its value to 2, which will not trigger flushing to disk on every commit. You can do this in your my.cnf file by adding line to [mysqld] section:

innodb_flush_log_at_trx_commit = 2

After I changed this value to 2 I got speed boost exactly 3 times on my pretty fast desktop. Again, this concerns only COMMITS, and when a user walks your site almost no commits occur. But as developer your DX will speedup dramatically.

Setting proper size if transaction log

to be continued...

Kudos

I want to thank thumbs and threnody from #mysql IRC channel who guided me and helped to locate and fix this problem. Thank you guys!

Blames

I have to blame Devel query logging and counter. It was confusing me all the way, pretending DB takes 10 and 20 times less time then the real workload.

======================================

ORIGINAL POST

Hi all!

Today I've been tuning my InnoDB engine to get the maximum speed when working with a Drupal 7 website. Jumping ahead of myself I can say that I failed with that task and no performance grow I've been witnessed so far.

Certainly, the operation I've been testing is no among the frequent ones - it's about enabling/disabling a module on the /admin/modules page.

At the beginning, the test operation of disabling a module took about 30 seconds.
After 6 hours of fruitless manipulations over my.cnf, I finished with exactly same 30 seconds.

Out of despair, I'd converted my database to MyISAM and was filled with amazement seeing 9 seconds where just been 30!
I repeated it many times with same outcome.
Then I switched back to InnoDB and got 30 seconds back.

I haven't tested the rest of admin/site building tasks yet, and I want to ask the community - have you tried MyISAM instead of InnoDB while development? Indeed using InnoDB on a production website is better and with that I won't argue. But we are developers and spend rather more time on Drupal admin pages, so our primary concern is the speed of the development process.

Look forward to see your comments, firends!

best
Artiom