Bulk update got slower after conversion of database tables to InnoDB

Question

After I converted the database tables to InnoDB engine from MyISAM, I noticed some operations became very slow, especially on updating multiple nodes in bulk for administrative work. Front end performance seems to be good.

I have tried configuration settings suggested in these articles without any luck: MySQL > YourSQL, and MySQL Performance Blog.

My test script would load 25 different nodes (node_load()) and save them right away (node_save()) in a loop. This script runs for 2 seconds on the server with MyISAM engine; after DB conversion to InnoDB, it now takes 13 seconds.

It also takes much longer to restore InnoDB database from a SQL dump of 500MB; 20 minutes (InnoDB) vs. 2 minutes (MyISAM).

Is this what I should expect from InnoDB engine?

New to InnoDB engine, I'd like to know what I might still be missing in configuring and optimizing InnoDB.

Answer 1

Thanks for the nice tips. I tried most of the suggested InnoDB tuning tips and it was not enough to remedy my problem.

Here is how I was able to fix my issue. This may be a special case that is isolated to my situation, but I hope that this could help other developers who are in a similar situation.

When I ran node_save() in a loop with 25 different nodes, it took 2 seconds with MyISAM and 13 seconds with InnoDB.

I captured 80 some database queries generated by node_save() in a single loop. Some of them came from running node_load(), which is needed before node_save(). I used devel module's feature of database query info collection. I put the queries side by side. The same queries from InnoDB were slightly faster than MyISAM especially for simple SELECT queries, which took majority portion of all queries.

I identified an INSERT query from InnoDB side that took more than a 0.5 second when total 80 queries took 0.6 second. The query was from notifications module, whose hook implementation was fired every time a node was saved.

I narrowed it down to the table notifications_fields in the query and decided to create an index on the table for the column value. And that made all the difference. After creating an index on value, saving 25 nodes now took only 0.2 seconds (down from 13 seconds), even ten times faster than MyISAM's! (BTW, creating index on MyISAM table also made it run ten times faster than before)

In this case, I have to admit that InnoDB's under performance gave clue to find out that an index was needed. I would not have found out about the index with MyISAM because it was relatively fast enough to hide the situation.

There have been several cases like this that adding indexes improved site performance quite a lot. However, actual application and result from it can differ in different sites. Back to my case with notifications_fields table, adding another index on field column -- hoping to strech performance boost -- ruined the effect and actually made it slow again. So one needs to be careful with adding indexes and understand that one fix on a site may not work the same way on another site.

Answer 2

Especially DDL (creation of tables etc.) statements are slower in InnoDB. That's just the way it is.

Another difference is that InnoDB uses transactions and MyISAM doesn't. So by default, if auto-commit is set to on, it will do an explicit commit after each update/insert/delete query.

The easiest way to speed that up is to explicitly start a transaction before your bulk updates and then commit it at the end.

Unfortunately, the D6 database API does not have a way to do that in a database agnostic way, so you will have to rely on the actual SQL comments which are for MySQL:

db_query('START TRANSACTION');

and

db_query('COMMIT');

See http://dev.mysql.com/doc/refman/5.0/en/commit.html for more details.

That should already greatly speed up these updates.