0

I have a server (Dell PE2950) with a battery-backed write cache on the raid controller.

Is it safe for me to set innodb_flush_log_at_trx_commit=0 on a mysql server with data files stored on a raid0 volume?

Would there be a different answer if the volume were raid1/5/10?

Thanks!

EEAA
  • 109,363
  • 18
  • 175
  • 245
mikewaters
  • 1,175
  • 1
  • 14
  • 27

2 Answers2

2

If you are worried about losing data, it is not safe. According to the manual, a transaction may have been committed, but not written to disk yet - being only in memory. This means a mysqld crash or operating system crash or power failure may cause loss of transactions.

In some systems, it may be OK to lose transactions, as long as a transaction is either completely lost or completely kept. In that case you can switch this setting off.

The relevance of the battery-backed RAID controller is safe write caching on the controller. If the controller would cache writes but lose power, without a battery, it would be lose the data in the write cache. But, MySQL would already expect this to be safely on disk. So the battery allows safe use of write caching in the RAID controller. Usually the controller has a setting to disable write caching automatically if the battery level is too low.

The RAID level is not directly relevant. If the data is on disk, it is on disk. Should you lose power, it will be safe. Where RAID levels do matter is if a disk dies - a completely different mode of failure. In case if RAID0, there is obviously instant loss of all data. In case of RAID5, you can continue with a large performance hit. With RAID1/10 you can continue with a very small performance hit.

But, do not mistake RAID for a backup. There are dozens of failure modes for a database server, and RAID handles only one of them: disk failure. Your filesystem could crash, you could accidentally delete data, your datafiles could break due to an obscure MySQL bug. So, do not forget to keep backups of important data using mysqldump. Never copy the datafiles, unless MySQL is stopped, as they may be inconsistent.

0

For safe and performant you should have innodb_flush_log_at_trx_commit=1 and RAID 10.

Cheers

HTTP500
  • 4,833
  • 4
  • 23
  • 31