patternsqlMinor
Should we need to use Barriers on a production database (MySQL/InnoDB)?
Viewed 0 times
needproductioninnodbbarriersdatabasemysqlshoulduse
Problem
Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended?
e.g
Reference:
e.g
mount -o barrier=1 /dev/sda /mntpntReference:
- http://lwn.net/Articles/283161/
- https://serverfault.com/questions/403891/journaled-filesystems-and-power-failure
Solution
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.
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.
Context
StackExchange Database Administrators Q#20409, answer score: 4
Revisions (0)
No revisions yet.