Re: wikipedia down, slashdot covering, mysql mentioned

[Heikki Tuuri]

Peter,

a buggy fsync() in Linux is one of  the possible reasons here. If an InnoDB 
tablespace gets corrupt in a power outage, it is most probably caused by a 
bad fsync() implementation or configuration in the operating system or 
hardware. An fsync() call should write the data physically to disk or to a 
battery-backed, non-volatile disk cache. If it fails to do so, then any 
database, PostgreSQL or anything, can get corrupt.

In the Wikipedia case, James Day posted an error message that InnoDB 
printed. It showed that the page checksum was wrong. Probably fsync() had 
failed to flush some part of a 16 kB page to disk.

Unfortunately, fsync() is badly implemented in many, or most, OS/hardware 
configurations. Some ways to improve the situation:

- use hdparm, or proprietary disk configuration tools of the disk vendor to 
switch off write caching in the SCSI controller and the disk itself;
- use a battery-backed disk cache in the SCSI disk controller;
- use certified hardware from one vendor: for example, Solaris/Sparc might 
work more reliably than an arbitrary Linux/x86 configuration;
- do not use disk striping, software RAID, NFS, or anything exotic;
- use the MySQL replication to keep a backup server.

None of the above is guaranteed to remove corruption problems, and switching 
off write caching can seriously lower performance. In the Wikipedia case, 
battery-backed SCSI controllers did not help at all. Thus, using an 
Uninterrupted Power System is probably the best way to prepare against power 
outages.

It is possible to implement fsync() correctly and efficiently in the 
operating system and hardware. There are no physical limitations that would 
make it impossible. But, unfortunately, in practice, many implementations 
are seriously flawed.

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/


----- Original Message ----- 
From: "Peter Wilm" <listen@stripped>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, February 22, 2005 12:40 PM
Subject: Re: wikipedia down, slashdot covering, mysql mentioned


> Could this possibly be a problem with a bad fsync implementation in
> linux (< 2.6.5)?
> See: http://www.ussg.iu.edu/hypermail/linux/kernel/0403.2/0527.html
>
> Scott Haneda schrieb:
>> There is a thread over at /. about WikiMedia being out due to a power 
>> outage
>> <http://slashdot.org/articles/05/02/22/0151213.shtml?tid=95>
>>
>> MySql is getting bashed pretty hard in some cases as apparently, in power
>> failures, you get database corruption.  (this is all from the posts, not 
>> my
>> opinion at all, I am asking only to learn more) Also, it is mentioned the
>> MySql devs just do not have data corruption from power failure a high
>> priority.
>>
>> Many are leaning on Postgres.  For me, MySql has never let me down (Knock 
>> on
>> PSU) but I have to ask, for more data on this matter.  I run MySql on OS 
>> X,
>> I sometimes have to reboot, it comes back up fine, I do however, do 
>> nothing
>> special to shut down mysql, should I be?
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
>