G3: InnoDB or MyISAM?

[Steve Lacy]

Hi all,

The current g3 beta 2 forces tables (at create time) to use the InnoDB storage backend.   I've heard both good and bad things about InnoDB, which I'd summarize as:

* InnoDB may have some performance boost in some situations.  (see link to mysqlperformanceblog)
* InnoDB supports row-level locking, so if your application needs it, InnoDB is a must.
* InnoDB has issues with garbage collection of the ibdata files.  This is such that they never shrink even when data is deleted.  (see bug link below)
* The InnoDB Backends can be turned of via the my.cnf parameter "skip-innodb" at which point I'm not sure what the g3 installer will do.
* There are several other engines that some sites may use, and they may be preferrable to InnoDB.

For reference, take a look at:

http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/
http://bugs.mysql.com/bug.php?id=36943 (Note the "not a bug" response)

My current thinking is:

Don't specify an "ENGINE=" clause in the create table statements, and let the site manager choose the default storage engine via my.cnf.   If they want InnoDB, then they can have it.     This is the tact that most web applications take.  I like to think of storage engine choice as a performance optimization step done by the site administrator. 

When I have a few spare minutes, I'm going to duplicate my gallery3 install and tables, and switch one copy to MyISAM and leave the existing copy on InnoDB, and you're welcome to do some benchmarking on the external site, which I'll add to this thread when the work is done.

BTW, it looks like you're using MyISAM for everything but the search_records table.  Is there a reason behind this, or did you just forget the "ENGINE=" clause at create table time?

Steve


------------------------------------------------------------------------------
Enter the BlackBerry Developer Challenge  
This is your chance to win up to $100,000 in prizes! For a limited time,
vendors submitting new applications to BlackBerry App World(TM) will have
the opportunity to enter the BlackBerry Developer Challenge. See full prize  
details at: http://p.sf.net/sfu/Challenge
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Bharat Mediratta]

This is a great conversation.  I don't have time to respond to all of it
right now (and others probably have better opinions than I do here) but
I'll respond to this one point:

> BTW, it looks like you're using MyISAM for everything but the
> search_records table.  Is there a reason behind this, or did you just
> forget the "ENGINE=" clause at create table time?

You probably mean "it looks like you're using InnoDB for everything..."
instead of MyISAM there.  We're using MyISAM for search_records because
it supports boolean search and InnoDB doesn't.

------------------------------------------------------------------------------
Enter the BlackBerry Developer Challenge  
This is your chance to win up to $100,000 in prizes! For a limited time,
vendors submitting new applications to BlackBerry App World(TM) will have
the opportunity to enter the BlackBerry Developer Challenge. See full prize  
details at: http://p.sf.net/sfu/Challenge
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Steve Lacy]

Yeah, sorry, I phrased that one wrong. :)  search_records is the *only* table that's MyISAM.

Steve

On Mon, Jul 20, 2009 at 3:16 PM, Bharat Mediratta <bharat@...> wrote:

This is a great conversation.  I don't have time to respond to all of it right now (and others probably have better opinions than I do here) but I'll respond to this one point:

BTW, it looks like you're using MyISAM for everything but the search_records table.  Is there a reason behind this, or did you just forget the "ENGINE=" clause at create table time?

You probably mean "it looks like you're using InnoDB for everything..." instead of MyISAM there.  We're using MyISAM for search_records because it supports boolean search and InnoDB doesn't.

------------------------------------------------------------------------------
Enter the BlackBerry Developer Challenge  
This is your chance to win up to $100,000 in prizes! For a limited time,
vendors submitting new applications to BlackBerry App World(TM) will have
the opportunity to enter the BlackBerry Developer Challenge. See full prize  
details at: http://p.sf.net/sfu/Challenge
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]

[Bharat Mediratta]

I've created a ticket for this so that it doesn't get forgotten:
http://sourceforge.net/apps/trac/gallery/ticket/597

Stephen Lacy wrote:

> Yeah, sorry, I phrased that one wrong. :)  search_records is the *only*
> table that's MyISAM.
>
> Steve
>
> On Mon, Jul 20, 2009 at 3:16 PM, Bharat Mediratta <bharat@...
> <mailto:bharat@...>> wrote:
>
>
>     This is a great conversation.  I don't have time to respond to all
>     of it right now (and others probably have better opinions than I do
>     here) but I'll respond to this one point:
>
>
>         BTW, it looks like you're using MyISAM for everything but the
>         search_records table.  Is there a reason behind this, or did you
>         just forget the "ENGINE=" clause at create table time?
>
>
>     You probably mean "it looks like you're using InnoDB for
>     everything..." instead of MyISAM there.  We're using MyISAM for
>     search_records because it supports boolean search and InnoDB doesn't.
>
>

------------------------------------------------------------------------------
__[ g a l l e r y - d e v e l ]_________________________

[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]