Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

share|improve this question
add comment

3 Answers 3

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.

share|improve this answer
1  
And lately, several versions of MySQL have some optimizations on how they are doing fsyncs, to improve performance. See: MariaDB: Group commit for the binary log –  ypercube Jul 11 '12 at 8:23
1  
Also this benchmark which compares Facebook's patch and MariaDB's: Group commit in MariaDB is fast. Rumors say that MySQL 5.6 will include a similar optimization: Binary Log Group Commit in MySQL 5.6 –  ypercube Jul 11 '12 at 8:28
1  
And the blog of Kristian Nielsen, who has worked on these optimizations: kristiannielsen.livejournal.com –  ypercube Jul 11 '12 at 8:30
    
And the 2 years old post by Mark Callaghan, describing the issue and the Facebook patch. –  ypercube Jul 11 '12 at 8:41
    
It seems to me not many are using barrier=1 in production server, right? –  Yoga Jul 13 '12 at 16:08
add comment

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:

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

share|improve this answer
    
I alway use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1, but my problem is should I also use barrier=1? –  Yoga Jul 13 '12 at 16:07
    
IMHO something's gotta give. Either 1) sync_binlog=1,barrier=0 or 2) sync_binlog=0,barrier=1. –  RolandoMySQLDBA Jul 13 '12 at 16:18
    
If you want ACID, go with option 1. If you want performance, go with option 2. –  RolandoMySQLDBA Jul 13 '12 at 16:19
    
but why (2) is faster than (1)? btw, "sync_binlog=1" seems not required by ACID.. Juts more safe. –  Yoga Jul 15 '12 at 3:09
    
While not required for ACID compliance, sync_binlog=1 is an instant bottleneck because disk I/O will dramatically increase for a high-write environment. If such a box were a Master in Master/Slave Replication, the Slave would have everything the Master's binlogs would have. Such a Slave could still experience replication lag even though everything replication wise is OK. Without replication, such box would just live with constant server load. You want to avoid having mysqld being responsible for disk I/O as much as possible. –  RolandoMySQLDBA Jul 15 '12 at 3:35
add comment

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.

share|improve this answer
    
Could you expand on this? There's nothing here that isn't already contained in one of the other answers. –  Jon Seigel Oct 11 '13 at 17:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.