InnoDB Testing, Discussion Results

[Benton, Kevin]

> -----Original Message-----
> From: developers-owner at bugzilla.org
[mailto:developers-owner at bugzilla.org]
> On Behalf Of Max Kanat-Alexander
> Sent: Tuesday, August 08, 2006 2:58 AM
> To: developers at bugzilla.org
> Subject: InnoDB Testing, Discussion Results
> 
> 
> 	I did some performance testing of InnoDB vs. MyISAM on Bugzilla,
> using
> my load-testing script from bug 347520.
> 
> 	As far as I can tell, under heavy load there's little difference
> between the speed of MyISAM and the speed of InnoDB. This is probably
> because we still use whole-table locks in the Bugzilla code.
> 
> 	Looking into it (using MySQL's slow query log), I did find that
> under
> high numbers of simultaneous users, certain buglist searches do spend
a
> lot of time waiting for WRITE locks on the database to release. That
is,
> every time somebody uses process_bug or post_bug, it's going to slow
> down queries. If we were under transactions, this would end. So InnoDB
> probably is a big plus in that area.
> 
> 	I've read very good things about the stability of InnoDB, and
for
> our
> uses its speed seems entirely comparable to the speed of MyISAM.
> Overall, I see more benefit in moving to InnoDB than I see problems.
> 
> 	From our discussion I've learned some valuable things, and I'm
sure
> that many of them would be of interest to our users, and should be
> mentioned in the Release Notes. And it's true that we will have
certain
> hurdles to jump in making our InnoDB support perfect, but we always
have
> hurdles to jump when making improvements.
> 
> 	Also, for those interested, here's the bug with the patch that
> actually
> changes Bugzilla to use InnoDB:
> 
> 	https://bugzilla.mozilla.org/show_bug.cgi?id=347475
> 
> 	I'm going to wait a day or two, and then probably commit it,
unless
> the
> approvers object, or unless somebody explains a very definite reason
> that moving to InnoDB would cause more harm than good for the majority
> of our users.

Where InnoDB suffers that MyISAM doesn't is that MyISAM is specifically
designed for high-speed queries.  This is especially important for
"big-wigs" in corporate America.  It seems to me that we need to give
administrators the ability to decide which is more important - faster
searches, or data integrity if the server goes off-line during a write.
I don't think it's fair for us to make that decision for administrators
by default without giving them the option to choose.  Loosing fulltext
indexes in some cases is clearly not worth it when searching full bug
histories (attachments, for example), or other text fields.  On the
other hand, if the installation rarely does searching, InnoDB seems to
make a lot of sense if the goal is to make sure no data is lost.  Please
be aware, however, that preventing data loss can be accomplished in an
alternative way - by turning on the binlog.  That allows administrators
to replay any action that modifies the server without respect to storage
engine.

It also seems to me to be a bad idea to make tables that very rarely
change suffer from the overhead of transactional storage engines.  That
seems to me to be another administrator-based decision.

Personally, I would hope that in the process of making it possible to
use InnoDB or MyISAM, we would also work to make it possible to use
NDBCluster so installations that need row-level-locking *and* high
availability could get it.  The only problem with NDBCluster that could
prevent an admin from using it is in 5.0 and before, NDBCluster must
have enough RAM to load entire tables.  I would hope that we'd give
admins the ability to do this at a few different levels - semi-globally,
selectively by pre-programmed group, or in a per-table basis (for the
most advanced admins).

---
Kevin Benton
Perl/Bugzilla Developer/Administrator, Perforce SCM Administrator
AMD - ECSD Software Validation and Tools
 
The opinions stated in this communication do not necessarily reflect the
view of Advanced Micro Devices and have not been reviewed by management.
This communication may contain sensitive and/or confidential and/or
proprietary information.  Distribution of such information is strictly
prohibited without prior consent of Advanced Micro Devices.  This
communication is for the intended recipient(s) only.  If you have
received this communication in error, please notify the sender, then
destroy any remaining copies of this communication.