TokuDB heavy syncing to disk when log_bin enabled, tokudb_commit_sync=0 ineffective
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.7 |
Fix Released
|
Undecided
|
George Ormond Lorch III |
Bug Description
When log_bin is enabled, TokuDB does twice as many disk writes then normally when tokudb_commit_sync = 1.
Setting tokudb_commit_sync = 0 seems completely ineffective when log_bin is enabled.
Tested with simple OLTP sysbench test against Percona Server 5.7.18. IOPS checked as follows (check syscw in particular):
tokudb_commit_sync = 1
log_bin = OFF
before sysbench
$ sudo cat /proc/36361/io
rchar: 6292581
wchar: 16578195
syscr: 1380
syscw: 67
read_bytes: 8704
write_bytes: 17227776
cancelled_
after sysbench (10k trx)
$ sudo cat /proc/36361/io
rchar: 74137694
wchar: 58352602
syscr: 3540
syscw: 10103
read_bytes: 67845120
write_bytes: 181890560
cancelled_
tokudb_commit_sync = 0
log_bin = ON
sync_binlog=0
before sysbench:
$ sudo cat /proc/36704/io
rchar: 49890268
wchar: 16554177
syscr: 3235
syscw: 67
read_bytes: 8704
write_bytes: 17219584
cancelled_
after sysbench:
$ sudo cat /proc/36704/io
rchar: 120845781
wchar: 64862148
syscr: 5459
syscw: 20096
read_bytes: 70959616
write_bytes: 106491392
cancelled_
With:
tokudb_commit_sync = 0
log_bin = OFF
$ sudo cat /proc/11180/io
rchar: 80837522
wchar: 16578195
syscr: 2849
syscw: 67
read_bytes: 1077248
write_bytes: 25927680
cancelled_
After sysbench
$ sudo cat /proc/11180/io
rchar: 106204552
wchar: 132469377
syscr: 3665
syscw: 168
read_bytes: 1101824
write_bytes: 205230080
cancelled_
This is likely a result of https:/ /jira.percona. com/browse/ TDB-70 and https:/ /github. com/percona/ percona- server/ pull/1882/ files which fixes TokuDB interaction with binlog group commit algorithm and copies InnoDB logic. Disabling durability within the binlog group commit (and 2pc since multiple engines are in use, particularly when GTID is involved). Prior to this fix in 5.7, tokudb_commit_sync = 1 did not do much, but then, TokuDB was also no properly following the binlog group commit protocol correctly so was more or less always operating in reduced durability.
You do not seem to be setting tokudb_ fsync_log_ period > 0 which is required now when tokudb_commit_sync == 0. tokudb_ fsync_log_ period == 0 basically means to TokuDB to fsync the log anytime it is required for maximum durability.
See the code in the pull request for the logic, specifically this bit in tokudb_flush_logs : group_commit ||
( tokudb: :sysvars: :fsync_ log_period == 0 &&
tokudb: :sysvars: :commit_ sync(NULL) )) { >log_flush( db_env, NULL);
assert_ always( error == 0);
else if (!binlog_
error = db_env-
}