[support] MyISAM vs InnoDB

[Pierre Rineau]

Le lundi 23 mars 2009 à 16:32 +0100, Daniel Carrera a écrit :
> Pierre Rineau wrote:
> > PostgreSQL is a way better than MySQL, about all topics.
> 
> Data integrity too? I thought the main benefit of Postgres was features 
> (triggers, stored procedures, etc).

It's true, PostgreSQL really has a lot of cool features. But it's also
true that it provides a fully ACID (Atomicity, Consistency, Isolation,
Durability) compliant database, which MySQL does not until the future 6
release.
In PG database, every request, even a single simple one, is executed in
a transactionnal context. This is also true with triggers and
procedures.

I quote "PostgreSQL is fully ACID-compliant, while MySQL's InnoDB
storage engine provides engine-level ACID-compliance" (see
http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#ACID_Compliance).

Even with clustering, PostgreSQL seems to be a lot more powerfull, since
you can use cross databases constraints between tables from more than
one PG instance. You can also do table partitioning, and a lot of other
cool features to help you when you start having *A LOT* of data :)

> > The only thing on which MySQL could be better, is maybe it's faster on
> > read operations on small databases.
> 
> That shouldn't be underestimated. It means that MySQL is the right 
> choice for most websites.
> 
> > If you have to choose between MyISAM and InnoDB, if I were you, I'd
> > choose InnoDB, it's a lot more stable. MyISAM may be faster and eat less
> > disk space, but you may have really bad surprises with your data
> > integrity.
> 
> Thanks. I'm leaning toward switching to InnoDB. I'll certainly move my 
> own work to InnoDB. Our website has both a Drupal site and a custom web 
> app that I wrote. The custom app is more critical than Drupal, so it is 
> a better candidate for InnoDB.
> 
> 
> > In the company I work for, we used to make a MySQL intensive use, the
> > first monthes, we abandon MyISAM for a lot of obvious reasons.
> 
> Actually, I would love to know what those reasons are. Is it just data 
> integrity?

Data integrity is a sufficient reason. But, we also had a lot of
problems with MySQL query cache bugs (with concurent access to the
database, some data were not refreshed, but was written in database).

I think now, most of thoses bugs have been resolved. But still, we
sometime continue to find some strange behaviors when you write your
queries in the wrong order (performance problems most of them), things
that happens less often with recent PostgreSQL releases (it remains that
this is the developer responsability to write correct queries).

> Our web host doesn't offer Postgres, so I can't seriously consider that 
> option, but I would be happy to hear more about the pros and cons, just 
> out of personal curiosity. You seem to suggest that MySQL has faster 
> reads for small databases. How small is "small"? My boss likes speed. :)

You might want to search google for this. In fact, most of posts you'll
read will take position for one of those, be aware when reading them.
But, if you read a lot of them, you could do your own opinion.

I still think that testing both for a long time is the better way to do
your choice.

> Cheers,
> Daniel.
> --
> [ Drupal support list | http://lists.drupal.org/ ]

Regards,
Pierre.