By mudanoman on

Hello,

I heard there were some benefits of converting certain high read/write tables from myisam storage to innodb and wanted to see what tables people have converted over to innodb. Right now I am thinking about converting the sessions, watchdog, cache, and url alias tables to innodb. Just want to hear what you guys think as it seems like a effortless modification :P

Best.

⋅ Categories: Drupal 4.7.x

Comments

mudanoman’s picture

mudanoman commented

PS. here are some benchmarks regarding innodb and myisam = http://www.innodb.com/bench.php

mediumgrade’s picture

mediumgrade commented

I believe MyISAM tables are the faster of the two because they don't incur the ACID compliance overhead that InnoDB tables do. You're will probably loose a significant amount of performance on tables with a high number of inserts if you make that switch.

boris mann’s picture

boris mann commented

That would seem to be the case, but in Drupal, we've seen the reverse: MyISAM locks the entire table on insert. On, say, the comments table, on a busy site where lots of queries are trying to do inserts, this is REALLY slow. InnoDB does row level locking, so you're set -- each comment completes quickly.

AltaVida’s picture

AltaVida commented

I agree with Boris here, I had the same problem with the cache table getting locked until I switched it to InnoDB.

From what I've researched, the best solution is actually to use both types (that is, if you really want to tweak your setup).

MyISAM is very fast and excellent for (mostly) read-only tables
InnoDB has row level locking and should be used on tables with frequent writes.

Lastly, you should know that InnoDB tables don't support MySQL fulltext indexing which I believe is only relevant if you are using 4.6 or a handful of search related modules.

mudanoman’s picture

mudanoman commented

Thanks for the replies guys. I would like to do the innodb switch to the cache, sessions, and watchdog tables. Just to confirm, I would just need to go into phpmyadmin, select the table, and go into operations and switch from myisam to innodb engine. Please correct me if I am wrong.

Cheers,

Ivan

drupalninja99’s picture

drupalninja99 commented

You see A LOT more locking on a high traffic site with myisam.

ballmdr’s picture

ballmdr commented

I switched to innodb and it seem insert faster than myisam.
If your website is many commenting or publishing node, you should convert to innodb.

But innodb use too much ram. Therefore you can't use shared hosting.