Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I was told that in mysql synchronization to ensure that everything is being replicated and data is safe, the syn_binlog setting (in my.conf) should be set to 1.

Only this kills performance, what is the best way to solve this?

I also read that this is mostly the cause of ext3 filesystem on linux. is it safe to set sync_binlog to 0?

Basically the real question is, do you need to turn on sync_binlog if you don't want to lose data?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

The performance hit is the cost of the additional seek and writes. sync_binlog = 1 basically tells MySQL how many binary log writes to buffer before flushing the log writes to disk. If it's set to 1, then every write is flushed. 0 tells MySQL to let the filesystem handle it. So, if you have a disk failure you may lose some indeterminate number of binary log writes that are part of the last transaction.

Depending on your storage set up (SAN with battery backed cache, direct attached drives, etc.) a setting of 0 may be safe, or it may not. How much data loss can you and your application tolerate?

to quote the MySQL reference:

The default value of sync_binlog is 0, which does no synchronizing to disk. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

share|improve this answer
    
So if I understand correctly, if we would switch to a battery-backed raid card, we can safely set sync_binlog to 0? –  Cecil Zorg Sep 21 '10 at 19:27
    
That is the theory. Of course, it always depends on the details. We use a pricey SAN with dual battery backed controllers with loads of battery backed cache. We are comfortable with sync_binlog=0 in our situation. –  Craig Sep 21 '10 at 20:18
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.