Is it worth to switch from MyIsam to InnoDB for a Drupal 7 site that has 2000 visits per day? Would it be worth if it has 10000 visits in the future?

How much faster can a site become with InnoDB if it has mostly reads and not writes? And how much if it has a lot of writes in the database?

I'm asking all this because some articles I have read are negative about InnoDB, and opinions are split.

So, does the generally spoken statement "InnoDB is better always" reflect reality?

  • Drupal 7 uses innodb in default. – ram4nd Sep 29 '11 at 6:56
  • Imho, Innodb is nomore an option nowadays.. – Strae Sep 29 '11 at 7:36
  • This what I have also read in the past, but as I mention drupal 7 installed all my sites using myisam! Did they regret having innodb as default? But even if they have something as default, my question remains what is the best. The default options do not always mean they are they best. – john Sep 29 '11 at 7:57
  • What do you mean it's not an option anymore? – john Sep 29 '11 at 7:58
  • is InnoDB enabled on the server where Drupal 7 installed using MyIsam? that could be one of the reasons it did. – zerolab Sep 29 '11 at 8:31

The general rule of thumb was to use InnoDB to write intensive tables (due to its row locking nature) and MyISAM for the read-heavy loads. I have seen a few very busy sites fully on InnoDB running without any hiccups or drop in performance too.

Drupal 7 defaults to InnoDB as the database abstraction layer has been rewritten and immensely improved.

You could look into MariaDB which is a drop-in replacement for MySQL, faster and has extra engines. There is also Percona, another high performance replacement. They both address some of the downsides of InnoDB.

The reason I am listing the alternatives is that Oracle made a few very suspicious moves and it may be that pretty soon we won't be able to use MySQL for free. (see http://arstechnica.com/business/news/2011/09/oracle-may-fork-itself-with-recent-mysql-moves.ars)

If bigger changes are not viable/possible, I'd recommend making a clone of the site using InnoDB and load test it using tools like Apache Benchmark, siege, New Relic

  • Is mariadb and percona fully compatible with drupal? Do any of these need that I change the way that I work etc> Are they compatible with all modules? Are there any web config tools like phpmyadmin? Will drush backup work? Thanks for your nice answer – john Sep 29 '11 at 14:38
  • 1
    On drupal.org/requirements#database MariaDB is one of the requirements. Also, drupal.org/node/861192#comment-3261836 has a pretty nice write-up of MariaDB. Haven't heard of any problems with Percona, but I'd do a bit more research. If it is all good, it will give you some boost which combined with apc, varnish can make your site fly (rbayliss.net/linode-drupal) – zerolab Sep 29 '11 at 16:05
  • I don't think that mixing the table types is a great idea. To make MySQL fast you need to dedicate RAM to the buffer pools and having to give RAM to both InnoDB and MyISAM won't let either one have the run of the park. – Stewart Robinson Oct 3 '11 at 18:55
  • I tend to agree with that, Stewart. Which is why I wrote "the rule of thumb was" - imho, people learnt these things the hard way, but luckily we are getting somewhere now :) – zerolab Oct 3 '11 at 19:53

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.