Should we need to use Barriers on a production database (MySQL/InnoDB)?

Question

Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended?

e.g

mount -o barrier=1 /dev/sda /mntpnt

Reference:

• http://lwn.net/Articles/283161/
• https://serverfault.com/questions/403891/journaled-filesystems-and-power-failure

Answer 1

Yes it is always safer. Thought the chance of actually getting corruption is low. When the filesystem gets corrupted it is very likely that repairing it will be successful. ACID compliant databases like InnoDB also do fsyncs/barriers to make sure committed changes are permanently stored on disk.

Don't forget that in a production environment you should be using a high quality server using redundant PSU and or UPS so the chance you would actually ever need the journal is already fairly small.

A reason not to enable it might be performance. Of course, this only affects write performance. But these kinds of things should be measured as they are highly dependent on your hardware. For instance when running with battery-backed write cache on a raid controller the performance penalty will be as good as zero because of the writeback caching of the controller.

So all in all I believe the risk to be very small but it depends on what is at stake. Personally, I think that if the risk is high enough to warrant this, you should be looking at replication to a second server. You already are making regular backups of course.

BTW: fsync is inefficient on ext3 as it always syncs all files! ACID compliant databases tend to do a lot of fsyncs so it is better to use ext4 if you can.

Answer 2

Your question lends itself to this: Is my concern for MySQL data performance or consistency?

If consistency is the prime thing, then keep InnoDB's ACID settings.

Your innodb_flush_log_at_trx_commit should be 1. You can set it to 0 or 2 for increased performance but at the risk of data loss in case of a crash.

You can tweek innodb_flush_method to augment how MySQL handled flushing data. If you are not sure what to set it to, leave its default setting.

You could set sync_binlog to 1 to assure transactions are being recorded to binary logs at commit time.

According to the link you gave on ext3 journaling and barriers, this is the algorithm:

• The log blocks are written to the journal.
• A barrier operation is performed.
• The commit record is written.
• Another barrier is executed.
• Metadata writes begin at some later point.

Yuck !!! That feels like ACID compliance at the disk level.

Imagine running a VMWare on a Cloud Server, and then creating VMWare servers out of it? Cloud running Cloud would be performance-challenged indeed. That's what you essentially get performance-wise with ext3 barriers along with InnoDB set to sync_binlog=1 and innodb_flush_log_at_trx_commit=1. This is the safest and SLOWEST approach in terms of InnoDB and ext3 individually and collectively.

To compensate for this, you may need to focus on performance tuning InnoDB.

For starters, leave sync_binlog at 0.

Next, configure InnoDB to get it to use more CPUs and more cores. Also, bigger InnoDB log files would help.

Here are some of my past links on such tunning:

• About single threaded versus multithreaded databases performance
• Possible to make MySQL use more than one core?
• Insert-heavy InnoDB table won't use all my CPU
• Clarification on MySQL innodb_flush_method variable
• How to safely change MySQL innodb variable 'innodb_log_file_size'?

If you are MySQL 5.1 or prior, you should upgrade to MySQL 5.5 or Percona Server 5.5.

Answer 3

Yes, barriers are required to ensure powerloss protection. Your database can be completely wiped out upon power fail. Not worth the risk.

You can disable barriers only if your disk has a battery backed cache.