When you commented out
sync-binlog = 1, what happened ?
You made sync_binlog default to 0. What happens then ? The MySQL Documentation says:
The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file.
This means that mysqld is at the mercy of the OS to flush disk changes to the binary logs. This indicates that there must be lots of files open (run
lsof) that the OS has to flush to disk. The current binary log for MySQL has to be in the middle of that log jam of open files that need periodic flushing to disk, especially if a lot of those open files are being written regularly.
The MySQL Documentation further says:
A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).
This simply means that the flushing to disk performed by
sync-binlog=1 gave the server some stability since disk changes were being flushed to disk and freed up the OS the perform a little better. Without it, mysqld had the same priority as any other process (application or OS) that had open files.
Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)
You may want to coerce InnoDB to flush to disk more aggressively and still perform well.
- Set innodb_flush_method to
O_DIRECT makes InnoDB handle its own flushing of changes to disk. Given the picture, InnoDB will explicitly flush to the Double Write Buffer in the System Tablespace (ibdata1) as well as the
.ibd files of tables from the Buffer Pool. On a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache.
- Set innodb_write_io_threads to 8 (or 16)
- Set innodb_log_buffer_size to 128M to reduce disk I/O
- Set innodb_log_file_size to 1G
Therefore, add these settings to my.cnf (restart required)
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 8
innodb_log_buffer_size = 128M
innodb_log_file_size = 1G
If you want to disable the binary log, tuning InnoDB to flush better with compensate