Rob Church wrote:
On 12/04/07, Rolf Lampa rolf.lampa@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
Thank you very much for your elaborate answer, it is full of useful info, this was actually exactly the kind of info I was looking for!
<thumbs up>
Regards,
// Rolf Lampa