Posted by gdtechindia on November 12, 2009 at 8:49am
We were considering conversion of Tables to Innodb. I have read at many places about the side effects and positive things about Converting to Innodb.
Can we have a list of tables here, which we can convert to Innodb and those which should be kept to MyIsam.
The site is am working on, is having high load and high number of pages are delivered.
On an average, 300 nodes are added everyday. (Not much of Commenting activity)
The pages are served via Boost Module to Anonymous users.
The logged in user activity is also low.
Maybe, if we can build a list of tables here, it will help other users having similar problem with overloading.
Best Regards And Thanks
Comments
If you've got lots of
If you've got lots of available RAM (>= 4GB) and running on a 64bit platform so you can take advantage of that RAM, then you can probably convert everything to InnoDB. If not then the best candidates are cache tables (if you are not already using APC or Memcache for object caching) since switching to InnoDB removes MyISAM's table-level locking. Other candidates are anything that is included in complex JOINs as InnoDB can often perform faster since it stores data on the index tree. A really rough statement is that MyISAM is better for tables with many writes, InnoDB for tables with many writes. If you don't have lots of memory available then you should avoid using InnoDB for the search tables. But if you have the memory available you should go with all InnoDB so that you can tune your server so as to give only minimal resources to MyISAM and everything else to InnoDB.
InnoDB has a few caveats. Namely you cannot mysqlhotcopy for backups, no full-text index, and more difficult to tune. But mysqltunner should aid in that last area.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I fudged that one line,
I fudged that one line, should've been:
A really rough statement is that MyISAM is better for tables with many writes and few reads, InnoDB for tables with few writes and many reads.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Under the Database is hosted
Under the Database is hosted on a different server.
This server has 4 Core Processor and 4 GB RAM. Nothing else is on this server except the Drupal DB for one website.
There's no easy math to
There's no easy math to determine if that's enough for you. The trick is if all your InnoDB tables fit into memory (size in memory != size on disk), or at least the most commonly used ones. But with 4GB of RAM with a bit of tunning you can most likely see a performance gain over MyISAM. Try it out and use mysqltunner to get all the caches and buffers tweaked. Ideally you would run load testing before and after to confirm that you are actually making an improvement. Just something simple with apache bench in the off hours should be sufficient.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
If InnoDB is so clearly
If InnoDB is so clearly beneficial for at least some tables (e.g. cache*), why does Drupal ship with only MyISAM tables? Anybody knows?
---
Tomáš J. Fülöpp
http://twitter.com/vacilandois
IMO, Drupal in its basic form
IMO, Drupal in its basic form isn't designed to cater for high performance. And there are very few websites, which will use it for high performance.
Most of the users (i guess 85%) of drupal will have low traffic sites.
Maybe, they will give option for Innodb in next versions.
Drupal 7 defaults to
Drupal 7 defaults to InnoDB:
http://drupal.org/node/301362
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
Modules imcompatible with Innodb?
I've heard there are a few modules that are not compatible with Innodb. Anyone know more info on this?
Lets have a list of some
Lets have a list of some Tables which can be converted to InnoDB without any trouble (or making any custom things). I have read that if one wants to change Search Table, one has to make some patch to core.
AFAIK no patches are required
AFAIK no patches are required for Drupal 6 to make InnoDB work. I've done several high performance sites where we switched everything to InnoDB without issues.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
We are still with Drupal 5.
We are still with Drupal 5. And i tried several times to update it to Drupal 6, it doesn't work as easy.
So, for the time being, we are fighting with Drupal 5.
Why change? MyISAM fetches
Why change?
MyISAM fetches data faster than INNOdb.
If most of your traffic is anonmyous, and most of the user activity is simply viewing nodes, then you would probably suffer a performance penalty switching to innodb-- speaking off the top of my head...no data to back up that fact....:)
In any case 300, new nodes a day doesn't warrant need to switch db configuration.
I have a site running drupal 6 that adds 4000-5000 nodes per day from rss feeds, albeit little traffic since it is still in development, and it hums right along with the boost module enabled.
Sounds to me you have reached an impasse with Drupal 5. I would put priority over upgrading your site to drupal 6--with upgradability to drupal 7 in mind-- rather than changing MSQL engines.
Fun with Gmap
Sure MyISAM will be faster
Sure MyISAM will be faster than InnoDB for simple queries against flat tables. But in Drupal those queries are rarely an issue. The problem queries are more likely to be the big ones involving lots of joins to CCK tables on non-primary indexes and the node access table. For those my experience is that InnoDB is by far the better choice. Also MyISAM may be faster on simple queries, but only if there isn't a lot of concurrency on those tables that causes table locking. So the cache tables are another place where switching to InnoDB can make a tremendous difference. These two things are some of the main reasons why InnoDB is the default engine for Drupal 7.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Pressflow
Have you looked into using Pressflow? It is meant to be used with InnoDB tables and modifies certain count queries to play nice with InnoDB.
http://fourkitchens.com/blog/2009/03/01/what-makes-pressflow-scale-1-fas...
--
Nathan Rambeck
InnoDB is better when you
InnoDB is better when you have write-loaded environment. For example you have great users activity like writing comments or blog posts. In this case you need to switch to InnoDB, because MyISAM table-level locks will ruin your performance. Also at some point InnoDB becomes faster than MyISAM (still because of locking mechanism) See the SQL on how to switch to InnoDB in Drupal.
There's a module for that!
http://drupal.org/project/dbtuner
Drupal 7 big database
I am using Drupal 7 and my tables are now in InnoDB.
My site has now some 1.700.000 nodes and growing, i write once and only update in a percentage lower than 10% one of the content. So my site is mostly read. Will in this case be better switch to MyISAM the tables for fields, nodes and taxonomy?