[Mediawiki-l] MYISAM or InnoDB best for Mediawiki?

[Rob Church]

On 12/04/07, Rolf Lampa <rolf.lampa at rilnet.com> wrote:
> I didn't find any informative help or discussions on whether to set up
> the database for MediaWiki with MYISAM tables or InnoDB. Any differences
> for local win32 machines (XAMPP) compared to "real" web servers (using
> Debian & Ubuntu).

It doesn't matter so much for a personal test machine, because there's
going to be less load and less concurrent access.

> Any links to read, or do you have any suggestions to give right here?
> General advice regarding "optimal" settings (which of course takes some
> context, but some contexts may be typical... no?). Etc.

We set up most tables using InnoDB as the preferred storage engine.
InnoDB implements proper (ACID-compliant) transactions, which help us
to avoid expensive locks on the page, revision and text tables that
can often lead to deadlocks and so forth under high load. InnoDB also
supports larger tables (row-wise) and longer indexes, which is helpful
given that we store UTF-8 data in a latin1 schema as-is.

For the "hitcounter" table, which is a temporary write cache for the
page view counters, we use a HEAP table, which saves data in memory;
this provides faster access all round, and the data isn't critical, so
if the server is restarted, losing it won't cause any significant
problems.

The "searchindex" table used by the default search engine is a MyISAM
table due to the use of the MyISAM full-text indexing and matching
operations, which InnoDB doesn't support. Transactions aren't
desperately needed for this table, since again, it's not critical, so
it's an acceptable compromise.


Rob Church