3

When I upgraded from Drupal 6 to Drupal 7 using the upgrade script I was under the impression MyISAM tables would be converted to InnoDB. This doesn't seem to be the case. Tables like comment, node, and block and many others remain as MyISAM. Tables like cache and field_* are InnoDB.

As far as I can tell it's best practice to use InnoDB. My website is a blog and forum, so users are posting comments, creating nodes, and voting on content. The site serves up an average of 30k pageviews a day.

Assuming everything was InnoDB I configured MySQL with: innodb_log_file_size = 256M and innodb_buffer_pool_size = 11G.

I believe it makes sense to convert all my tables to InnoDB. I wanted to ask here to see if anyone ran into this issue (conversion not taking place) or if anyone knew why the tables weren't converted. And, if I do convert everything to InnoDB, is there anything I need to know?

Improve this question

1 Answer 1

Reset to default
4

It does make sense to convert all tables to InnoDB. The core 6 -> 7 update does not change the engine type; so the reason they weren't converted is that it was not programed to do so.

Use READ COMMITTED for MySQL transactions https://drupal.org/node/1650930#comment-8437127 Use 7.25 Core or higher https://drupal.org/node/937284

By doing both of these you will significantly reduce the number of deadlocks that occur on your database.

Using a non database cache backend like memcache is a good idea, as explained in this presentation: http://www.percona.com/resources/mysql-webinars/drupal-and-mysql-performance

Improve this answer
7
  • I don't understand the READ COMMITTED link. Is it just a patch? (I've never patched any Drupal code before.) I use APC for my caching, would you recommend memcached instead? Or is it OK to run both together (seems redundant.) Although, I do have an issue with what I believe is a previously cached page showing up after a node or comment is edited. Although I was going to save that for a subsequent question. I appreciate the help. Thank you!
    – guinness74
    Commented Feb 6, 2014 at 23:26
  • APC vs Memcache: drupal.stackexchange.com/questions/63954/…
    – mikeytown2
    Commented Feb 6, 2014 at 23:32
  • Currently there is no viable patch for READ COMMITTED. In the settings.php file where your $databases array is located you can add in the init_commands key after the prefix key. ostraining.com/blog/drupal/settings-php
    – mikeytown2
    Commented Feb 6, 2014 at 23:36
  • I understand. I didn't realize READ COMMITTED was a MySQL setting. Do you recommend a best practice for converting from MyISAM to InnoDB?
    – guinness74
    Commented Feb 7, 2014 at 0:17
  • 1
    I got it back online. However, in order to make it responsive I had to disable READ COMMITTED in my settings file and I had to disable (drupal.org/node/797346) then clear my caches. At the time I was running 7.24. I turned off Apache, then used drush to update to 7.26, then converted the tables to InnoDB. When I turned the server back on it was dead. It's back online now, but is sluggish. The performance for InnoDB doesn't seem as good as MyISAM.
    – guinness74
    Commented Feb 10, 2014 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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