Announcement

Announcement Module
Collapse
No announcement yet.

sync_binlog=0 affects MySQL performance?but sync_binlog=1 works well

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • sync_binlog=0 affects MySQL performance?but sync_binlog=1 works well

    Hello , right now I found some strange things in one MySQL instance .

    backgroup
    we use SSD to store datafile , but the binlog files are stored in the SAS disk
    sync_binlog=0
    TPS 4000 insert+update per second
    max_binlog_size[500M]

    when MySQL creates new binlog file , we found that some transcations commit statement would be written into slow.log


    and when I set sync_binlog 0->2000->1000->200->100 .
    Finally , when the sync_binlog=0 , the commit statement disappear in the slow.log .

    Hope for you help . I am really appreciated.

  • #2
    Hi,

    I'm bit confused here. Your subject says " sync_binlog=0 affects MySQL performance,but sync_binlog=1 works well" but your above message says "Finally , when the sync_binlog=0 , the commit statement disappear in the slow.log" means your performance problem resolved by setting sync_binlog=0. Can you please explain?

    AFAIK, sync_binlog=1 can affect the performance because extra fsync() probably matters for "slowness", and this is expected. It depends on filesystem too as ext3 is not working properly with sync_binlog. You can get more information here.

    http://www.mysqlperformanceblog.com/2009/01/21/beware-ext3-a nd-sync-binlog-do-not-play-well-together/

    Comment

    Working...
    X