Wednesday, June 29, 2011

Why the mysql write speed drop down when we set sync_binlog=1

when we set sync_binlog=1,the mysql writing speed will drop down,why?
following is root cause:

sync_binlog=1 is sometimes required for critical applications. If the sync_binlog
is not set to 1, there is a risk of corruption of the binary log if the
server crashes. But, in many filesystems/OS, sync_binlog=1 is currently way
too slow (sometimes 2-4 times slower than set to 0).
the cause of the performance problem that one of the big causes is architecture of writing to binlog, "extending file per
each commit"
Currently binlog file is not pre-allocated, but extending per each transaction
commit. In contrast, InnoDB log file is pre-allocated and file size is not
changed. In other words, binlog adopts appending architecture, InnoDB log
adopts overwriting architecture.
For most of operating systems/file systems (including Solaris ufs and Linux
ext3), overwriting is much faster than appending. So binlog should pre-allocate
large file size (e.g. 128MB at once), then overwriting per commit.

Here is a result of preallocating binlog patch(this feather will add into MySQL5.6).

mysqlslap insert throughput (InnoDB)

1) preallocate, sync_binlog=1
1  conn: 3,707 commits/sec
20 conn: 7,696 commits/sec
50 conn: 8,156 commits/sec

2) normal, sync_binlog=1
1 conn: 359 commits/sec
20 conn: 798 commits/sec
50 conn: 760 commits/sec

The difference between 1) and 2) is huge (10 times faster) so
preallocating binlog is really helpful for people using sync_binlog=1 (no
outstanding difference when using sync_binlog=0).

Here is a low-level benchmarking result on Linux (RHEL5.3, ext3).

1. creating an empty file, then writing 128*1024 times with fdatasync()
  fdatasync per second: 3085.94321
  (Emulating current binlog (sync-binlog=1) behavior)

2. creating a 1GB data file, then writing 128*1024 times with fdatasync()
  fdatasync per second: 12330.47268
  (Emulating current InnoDB log file behavior)

3. zero-filling 1GB, then writing 128*1024 times with fdatasync()
immediately
  fdatasync per second: 6569.00072
  (Zero-filling causes massive disk writes so killing application performance)

4. zero-filling 1GB, sleeping 20 seconds, then writing 128*1024
times with fdatasync()
  fdatasync per second: 11669.81157

Apparently 2 and 4 are much better than 1 or 3.

How to resolve this issue?

method 1)use cache battery and set sync_binlog=0;
method 2)use Solaris ZFS and set sync_binlog=0;
method 3)wait the MySQL 5.6 version and upgrade it.
method 4)use Percona MySQL version which has resolved this issues.

No comments:

Post a Comment