mysql server mostly stops working when binlog disabled

Question

Performance was a little slower than I'd like on a mysql server, and we don't use replication and don't need point-in-time restores. So one recommended way to improve performance seems to be turning off bin-logging. I have the following in my my.cnf:

    ## Replication / Transaction Logging #
    binlog_format           = row
    log-bin                 = /var/lib/mysql/mysql-bin.log
    expire_logs_days        = 3
    sync-binlog             = 1

I commented out all four lines and restarted mysql. The service started fine, there is nothing to indicate any problems in any error logs or slow query logs. Everything appears fine, except that write performance slows to unusable levels. It does continue to work though. Un-commenting the above four lines and restarting the service immediately restored the previous performance levels.

The previous performance level are not what I want though, I want the performance improvement that should hopefully come with turning off bin-logging.

Why did this happen? How can I successfully turn off bin logging to gain performance?

Specs:

all tables are innodb (some fairly large)
mysql Ver 14.14 Distrib 5.6.19, for debian-linux-gnu (x86_64) using EditLine wrapper
Ubuntu 12.04 LTS server

score 0 · Accepted Answer · answered Jan 06 '15 at 19:27

sync-binlog

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.

InnoDB

Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)

InnoDB Plumbing

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)

[mysqld]
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 8
innodb_log_buffer_size = 128M
innodb_log_file_size = 1G

Epilogue

If you want to disable the binary log, tuning InnoDB to flush better with compensate

mysql server mostly stops working when binlog disabled

1 Answers1

sync-binlog

InnoDB

Epilogue