List:General Discussion« Previous MessageNext Message »
From:Eric S Date:April 5 2002 11:09pm
Subject:Re: InnoDB is better than MyISAM ?
View as plain text  
On Fri, 5 Apr 2002, BD wrote:

> At 01:54 PM 4/5/2002, you wrote:
> >I have seen many people saying that InnoDB is a great deal, that InnoDB
> >rocks, etc. and I am concerced about how much better InnoDB is compared to
> >MyISAM tables. Can someone tells me wich one is better ? I know that InnoDB
> >have foreign keys support, but I deal very well without then since now.
> >
> >My interests are justified becaus eI got out of a very old struct ( DBM +
> >Text Files ) and jumped head first into MySQL - MyISAM tables, but my site
> >has a good deal of visitors ( about 30.000 unique visitors by day ) and speed
> >and reliability are my primary concerns. I plan to use replication in MySQL,
> >and I would like to know if InnoDB is better than MyISAM for this.

One note here is that transactions aren't preserved for replication with
InnoDB, so you loose part of one of the major advantages of InnoDB.
Rollbacks are O.K., since I don't think that goes out to the slaves until
the commit, but if the master or slave goes down after part of a
transaction is sent to the slave, you get a partially committed
transaction on the slave.  How critical this is depends on the
application, and still is no worse than MyISAM which has no transactions
to begin with.

I think Heikki Tuuri has mentioned plans to get this fixed, though I think
I remember that he said that the problem was in MySQL, not in the actual
InnoDB code, which makes sense.

> Have you ever heard the old saying, "If it ain't broke, don't fix it?".<bg>
>
> If your website is mainly for read access to your database then you're not
> going to need InnoDb.

Agreed 100%, but it doesn't hurt too much (except for index sizes) on
readonly databases, so on our production system, we standardized on InnoDB
for all tables for consistency, though we will allow for exceptions for
tables that need features that aren't in InnoDB yet, such as full text
searching.

> InnoDb inserts (for a single user) are much slower (for me it is
> around 10x slower) than MyISAM because InnoDb does a lot more work.

This was not my experience.  Without batching the commits, InnoDB lost out
to MyISAM on our initial testing by about 1.5x rather than 10x.

However, when I committed every 100 or so inserts, InnoDB beat out MyISAM
by a small (25%) margin.  This was with a single user hitting the
database, a perl program that read in a text file, split it into fields,
and stuffed it into the database one record at a time (identical programs
except for handling the commits()).

Now, this wasn't normal database activity, pure inserts into a freshly
created table, but the results were still quite impressive.  Also, I'm
dealing with single-user activity, so there may have been some
differences there as well.

Thread
InnoDB is better than MyISAM ?João Paulo Vasconcellos5 Apr
Re: InnoDB is better than MyISAM ?BD5 Apr
  • Re: InnoDB is better than MyISAM ?Eric S6 Apr
Re: InnoDB is better than MyISAM ?Heikki Tuuri6 Apr
  • Re: InnoDB is better than MyISAM ?Patrick Hsieh6 Apr
Re: InnoDB is better than MyISAM ?Heikki Tuuri6 Apr