[support] MyISAM vs InnoDB

[Pierre Rineau]

Le lundi 23 mars 2009 à 09:24 -0700, Michael Prasuhn a écrit :
> On Mar 21, 2009, at 1:32 PM, Daniel Carrera wrote:
> > I just wrote another post that explains my hosting environment. I am  
> > interested in speed and reliability (isn't everybody?). My  
> > understanding is that:
> >
> > 1) InnoDB is more reliable.
> > 2) InnoDB reads faster, if the primary keys and indexes are set right.
> > 3) InnoDB writes slower.
> > 4) InnoDB requires more disk space and more RAM.
> 
> A lot of confusion here, from others, not just Daniel. Overall the  
> biggest benefit is that InnoDB supports row-level locking versus table- 
> locking on writes. For high traffic sites this can make some obvious  
> benefits for tables like access logs, sessions, users, comments,  
> node_comment_statistics and the like.
> 
> The downside is that for reading large amounts of simple data, InnoDB  
> is slower than MyISAM. In fact MyISAM is probably one of the fastest  
> engines out there in terms of reading data.

I don't think speed is really the problem here. There are a lot of wide
architectures that uses PostgreSQL or other DBMS's under very high load.
There are now a lot of solution to support high loads, and most of them
are based on redondancy and large amount of memory. Use a fast DBMS, ok,
but use a REAL DBMS, they are, in fact, fast enough.

The fact MyISAM reads fast, is the concequencies of its lack of security
and features.
Don't kill the kitten, don't use MyISAM.

Indeed, keeping such engine alive is an heresy, because it's not ACID
compliant, even if Drupal does not use transactions or referencial
integrity, this is a very bad habit to continue to use it.

> In short, unless you know you have large waits doing common tasks such  
> as insert comments when the load on the site is high, or you have a  
> way to examine how many connections are waiting on table locks before  
> completion, you are probably best off sticking with MyISAM.
> 
> Also, while InnoDB is widely available these days, you may still run  
> into hosts that don't support it, but do support MyISAM.

Your conclusion makes sens, some hosters continues to use outdated
technos such as MyISAM. But in case you are starting a new architecture
of your own somewhere, I really disencourage you to use MyISAM.

> 
> -Mike
> __________________
> Michael Prasuhn
> 503.488.5433 office
> 714.356.0168 cell
> 503.661.7574 home
> mike at mikeyp.net
> http://mikeyp.net
> 
> 
> 
> 
> 
> --
> [ Drupal support list | http://lists.drupal.org/ ]