Possibly moving to InnoDB

[Bradley Baetz]

Warning - semi-rant follows: ;)

Its *insanely* slow, although that may be because the on disk size is
2-3 times as large. You need to be careful tuning the innodb
cache/buffer/etc sizes, too.

Also, there are lots of deadlock problems (documented as 'features'),
mostly to do with next key locking (which is also documented as a
feature)

eg http://www.xaprb.com/blog/2006/08/03/a-little-known-way-to-cause-a-database-deadlock/
http://bugs.mysql.com/bug.php?id=20134
just search through their bug database for 'not a bug' bugs with
deadlock in the title.

You can turn that off, but then you break replication unless you use
5.1 and row-level replication.

They also have a really really annoying deadlock detection setup - if
a query takes too long, its assumed to be due to a deadlock. So if you
have any long running query (and buglist.cgi does!), a concurrent
insert (which has to block if the select scans a row 'near' it, due to
next-key locking) may assume its in a deadlock. This is really painful
in replication setups, so there are a couple of options for the
replication thread to retry a few times (eww!) Which helps a fair bit,
until you run a really really long query on the slave....

Also, be wary of mixing table types in queries, especially with locking.

On the flip side, I've yet to see corrupt tables with innodb. I've yet
to see them with postgres either, though....

Bradley

On 05/08/06, Max Kanat-Alexander <mkanat at bugzilla.org> wrote:
>         Hey guys. I'm probably going to have Bugzilla start using the InnoDB
> storage engine in MySQL for all tables except "bugs" and "longdescs,"
> because those have FULLTEXT indexes (and InnoDB doesn't support FULLTEXT
> indexes).
>
>         Before I do this, I just wanted to ask if anybody had any particular
> reasons that we shouldn't do it, or if there's anything we should warn
> users about after we switch. That is, if anybody has any experience
> working with InnoDB in large installations, I'd appreciate knowing about
> it. :-)
>
>         -Max