MySQL
sync_binlog
option variable is used to control how often binary log may be
synchronized to disk. By default, it is not synchronized to disk at each
write. So if the operating system or machine (not only the MySQL
server) crashes, there is a chance that the last statements of the
binary log are lost. To prevent this, you can make the binary log
be synchronized to disk after every N writes to the binary log,
with the
sync_binlog system variable. With value of 1 - every single statement is written
to disk, it means that the performance suffers, usually a lot unless
the disk has a battery-backed cache enabled, which makes
synchronization fast.
I performed some load tests to see how it may hurt database performance when the battery-backed disk cache is missing:
Total 40,000 records inserted into table using 4 parallel db connections:
| sync_binlog |
time |
|---|
| 0 |
0m17.972s |
| 1 |
1m4.599s |
| 2 |
0m44.364s |
| 3 |
0m34.197s |
| 4 |
0m18.693s |
|
|
|---|
sync_binlog with value of 3 simply means: only fsync binlog every third transaction.
The higher you increase the value of sync_binlog, the closer the results will be to sync_binlog=0 (no fsyncing).
Value of 1 is safest of course, but as you see, without decent battery backed write caching controller, performance is very bad.
Test case :
Tools used:
-- MySQL 5.1.56
-- mysqlslap
-- platform -
Red Hat 4.1.2-50 x86_64
, RAID 5 (8 disks, 67.75 GB each, Vendor DELL)
, Intel(R) Xeon(R) CPU 5148 @ 2.33GHz, 4 CPUs
, 8G RAM
# Connect to MySQL client and perform:
drop database if exists test_case_9;
create database test_case_9;
use test_case_9;
CREATE TABLE t (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`content` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=800001 DEFAULT CHARSET=utf8;
drop function if exists insert_data;
delimiter //
CREATE DEFINER=`root`@`localhost` FUNCTION `insert_data`() RETURNS int(11)
DETERMINISTIC
BEGIN
INSERT
INTO t(id,content) values (null,REPEAT('bla bla',5000));
RETURN ROW_COUNT();
END
//
delimiter ;
-- Change sync_binlog to value 0
mysql> set global sync_binlog=0;
mysql> quit
# Run following steps from a shell prompt
-- load data using mysqlslap tool
$ time mysqlslap --user=root --query="select test_case_9.insert_data();" --concurrency=4 --iterations=10000;
Benchmark
Average number of seconds to run all queries: 0.001 seconds
Minimum number of seconds to run all queries: 0.001 seconds
Maximum number of seconds to run all queries: 0.229 seconds
Number of clients running queries: 4
Average number of queries per client: 1
real 0m17.972s
user 0m1.306s
sys 0m3.096s
A) -- Change sync_binlog to value 1 and empty test table 't'
$ mysql -uroot -S /mnt/database/mysql_5.1/mysql.sock test_case_9 -e"truncate table t; set global sync_binlog=1;"
B) -- load data using mysqlslap tool
$ time mysqlslap --user=root --query="select test_case_9.insert_data();" --concurrency=4 --iterations=10000;
Benchmark
Average number of seconds to run all queries: 0.006 seconds
Minimum number of seconds to run all queries: 0.003 seconds
Maximum number of seconds to run all queries: 0.018 seconds
Number of clients running queries: 4
Average number of queries per client: 1
real 1m4.239s
user 0m1.672s
sys 0m4.203s
Repeat steps A and B to run load tests using Value 2, 3, and 4 with synch_binlog
Warning]