peter_zaitsev (peter_zaitsev) wrote,

MySQL: Make sure fsync() works

I've seen people many times people loosing latest transaction(s) because of their broken OS fsync() implementation,
Problem probably was not that bad as no database corruption was happening due to the fact the problem normally was flushing
drive cache which seems to only affected sequential writes, so pages going via Innodb doublewrite buffer made it to the disk.

It looks however as it is not always the case. We got customer loosing a lot of data on MacOS X which known to have fake fsync(). I do not know if MacOS X fsync() was broken more than usual or some hardware could loose data for longer time
frame, still recommendation is the same - if you're storing sensitive data in MySQL make sure you platform is as safe as you need.

Poweroff boxes few times during some disk intensive multi use benchmark running and see if you get database corruption -
run CHECK TABLE to see if they are fine. Also try simple sequential insert application to make sure last insert record is
always where after poweroff (if you need such guaranties). If you're using external SCSI you can also try disconnecting
SCSI cables and other torches to emulate hardware failures you might have. Spend the time or you will spend much more time recovering your data if problem happens.

By the way this problem is not specific to MySQL at all, more or less it should affect all on disk databases.
  • Post a new comment


    Comments allowed for friends only

    Anonymous comments are disabled in this journal

    default userpic