Poor Performance After Enabling Repliction Due to sync_binlog

Posted on July 15th, 2008 in General,Linux System Administration,MySQL,Programming by Brandon

I was pretty happy with myself with setting up some fairly complicated MySQL circular replication the other night.  I did it far after peak hours so as not to disturb any visitors if it caused any problems.   Everything appeared to be working great until I started watching things the next morning.

I started to notice that the main MySQL server seemed to be running really slow.   One process that we have usually completes in a couple hours, ended up taking well over 16 hours to complete.   I spent the whole day troubleshooting it, which got me familiar with all sorts of handy tools.   ‘mytop‘ is a handy version of ‘top’ for MySQL queries.  I got familiar with iostat for watching disk I/O performance.

In the end, after a whole day of troubleshooting it came down to the ‘sync_binlog‘ setting that I had enabled because I read some howto that mentioned it was useful for the replication master.  My understanding now of the setting is that it causes the operating system to tell the disk to sync the file to disk after each write to the binary log (every UPDATE, INSERT, or DELETE).   The idea is that when the data is sync’d to disk, the drive physically writes it to the drive, instead of keeping it in a cache.    My application does a ton, of inserts, so it was killing performance.

One Response to 'Poor Performance After Enabling Repliction Due to sync_binlog'

Subscribe to comments with RSS or TrackBack to 'Poor Performance After Enabling Repliction Due to sync_binlog'.

  1. Rolando Edwards said,

    on August 6th, 2008 at 2:02 pm

    Looking up the definition of sync_binlog in http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html, I saw that it mentions the use of battery-backed cache.

    Jeremy Cole talked about this in this URL : http://www.mysqlperformanceblog.com/2006/05/27/jeremy-cole-on-mysql-replication/

    Read carefully and have fun with it !!!

Post a comment