(didn't seem to get through to the mailing list, trying again)
Hi all,
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).
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.
(well, data import (sql, extended inserts) seems to be way much faster on MYISAM, but that's of course not all there's to it)
Regards,
// Rolf Lampa
Just use the defaults, they are like that for a reason.
On 12/04/07, Rolf Lampa rolf.lampa@rilnet.com wrote:
(didn't seem to get through to the mailing list, trying again)
Hi all,
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).
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.
(well, data import (sql, extended inserts) seems to be way much faster on MYISAM, but that's of course not all there's to it)
Regards,
// Rolf Lampa
MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/mediawiki-l
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
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
Rob Church wrote:
On 12/04/07, Rolf Lampa rolf.lampa@rilnet.com wrote:
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.
One thing that puzzles me is why the import to InnoDB is so incredible slow compared to MYISAM. I use the mysql.exe < datafile.sql - import. I also (think I) have tried "all the tricks" regarding performance settings* found on the MySQL sites etc. But no success, InnoDB simply remain magnitudes slower than MYSIAM in importing the same data, on the same machine. I have tried (among other things):
1. Sorting the data, 2. SET AUTOCOMMIT=0; 3. SET UNIQUE_CHECKS=0; 4. SET FOREIGN_KEY_CHECKS=0; 5. ALTER TABLE `text` DISABLE KEYS; ... 6. Extended inserts (like 256KBs)
And not to mention settings for buffers here and buffers there, but to no avail, InnoDB takes 10, 20 times longer than MYSIAM (I quit import with innodb after like ~15 hrs, with much more to go) and it simply refuses to import any faster. I don't seem to improve even a lttle, so that means I'm not even on the track...!
With MYISAM it just says swoop! 10GB of data in little more than half an hour (on the same machine).
Strange. I really don't have a clue. <scratching head>
// Rolf Lampa
Rolf Lampa wrote:
(I quit import with innodb after like ~15 hrs, with much more to go) and it simply refuses to import any faster. I don't seem to improve even a little, so that means I'm not even on the track...!
With MYISAM it just says swoop! 10GB of data in little more than half an hour (on the same machine).
Oh, I run on a Dell lap top XP-SP2, 1.8GHz, 1GB, NTFS, XAMPP, PHP5.1.6, MySQL 5.0.24a.
Regards,
// Rolf Lampa
Hello
Besides performance questions may I emphasize on the non robustness of the innodb mode. Reccntlty I have lost the whole content of a Wiki (naively, how could I know?) saved while mysql was running. The other wiki using MyISAM has been recovered. This very fragile innodb behaviour is inacceptable.
Best regards Francois Colonna
Hello,
Besides performance questions may I emphasize on the non robustness of the innodb mode. Reccntlty I have lost the whole content of a Wiki (naively, how could I know?) saved while mysql was running. The other wiki using MyISAM has been recovered. This very fragile innodb behaviour is inacceptable.
Did you remove 'ibdata' file manually? That is the major common reason of 'data lost' with InnoDB.
Domas Mituzas a écrit :
Hello,
Besides performance questions may I emphasize on the non robustness of the innodb mode. Reccntlty I have lost the whole content of a Wiki (naively, how could I know?) saved while mysql was running. The other wiki using MyISAM has been recovered. This very fragile innodb behaviour is inacceptable.
Did you remove 'ibdata' file manually? That is the major common reason of 'data lost' with InnoDB.
--Domas Mituzas -- http://dammit.lt/ -- [[user:midom]]
I copied the whole mysql directory, manually. A practice i used for years, because mysqldump is very lunatic. I was working fine. Then I installed a new Wiki 1.9.3. After a system crash I recovered the old Wiki but not the new one. That is very bad, how could I be aware of this innodb things ?
Best,
Francois Colonna
I copied the whole mysql directory, manually. A practice i used for years, because mysqldump is very lunatic.
lunatic? what is wrong with it?
Copying the directory manually (with a running server) isn't something what people should do. Of course InnoDB may refuse to start, as you're having inconsistent snapshot.
After a system crash I recovered the old Wiki but not the new one. That is very bad, how could I be aware of this innodb things ?
Have you ever tried what happens when you shoot a bullet into your head? :) To some people that also turns out to be bad.
Frames Project wrote:
I copied the whole mysql directory, manually. A practice i used for years, because mysqldump is very lunatic.
If you shut down the mysql server before copying the directory, that could work. Otherwise, you're backing up MySQL's internal data structures as they're changing, which guarantees an inconsistent backup (if someone happens to edit a page during the copy). If it's worked to date, that's just by luck.
What is wrong with mysqldump?
* It is the recommended backup procedure (at least one of them)
* it does the job correctly (not copying changing internal data structures, but making a consistent SQL snapshot)
* it works very well for me -- I've used it to clone my wiki quite a few times with no problems (just watch out for character encoding issues; see http://www.mediawiki.org/wiki/Manual:Backing_up_a_wiki)
Here's the backup command I use:
mysqldump -u root -pfoobar --lock-tables \ --add-drop-database --skip-extended-insert -r "$sqlfile" --databases wikidb
This command locks the database for write to guarantee a consistent backup. The wiki works as normal during the backup, except that someone trying to save an edit will be blocked until the backup is done. On my wiki, with about 100 meg of data, it takes 11 seconds.
Ian
mediawiki-l@lists.wikimedia.org