6

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.

  • It is Drupal 6 site. – Ki Kim May 2 '11 at 22:09
9

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.

  • 4
    I strongly suggest you give that back to the notifications project, preferable with a patch that updates the schema and adds a update function so that others can benefit from your findings too. This sounds like a default behavior that is the same on all sites. – Berdir May 5 '11 at 13:13
  • 2
    Thanks for the suggestion. I posted an issue at drupal.org/node/1151930 – Ki Kim May 9 '11 at 19:00
  • Thanks for the tip about adding index on value. What engine have you finally choosen for notifications_fields? – alfish Jun 10 '13 at 0:13
7

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.

  • Are you suggesting we disable auto commit, and don't need to define transactions explicitly? – Justin May 4 '11 at 21:46
  • 1
    No, I am not. Disabling auto commit means that you would need to explicitly commit everything, what Drupal doesn't (obviously). – Berdir May 4 '11 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.