2013/3/14 Kristian Nielsen <knielsen@knielsen-hq.org>
Correct, sync_binlog=1 is still required to ensure consistency between binlog and innodb. With sync_binlog=1, each group commit is fdatasynced (with a single binlog fdatasync per group), no further configuration is needed.
You also need innodb_flush_log_at_trx_commit=1, of course.
Yes, it's set.
I am a bit surprised that you got duplicates with SIGKILL of mysqld. I would have expected crashing the OS kernel (ie. power failure) would be needed for fdatasync to make any difference?
With "kill -9 mysqld", and sync_binlog=0, I'm not really surprised since mysql will not fdatasync after each commit, right ? To be more precise, actually the master run with sync_binlog=<number of sources>, here 4 sources including main. Some config vars and status : MariaDB [(none)]> SHOW STATUS LIKE 'binlog_%commits'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | Binlog_commits | 3239987 | | Binlog_group_commits | 3210932 | +----------------------+---------+ 2 rows in set (0.00 sec) MariaDB [(none)]> SHOW variables LIKE '%trx%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_trx_commit | 1 | +--------------------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW variables LIKE 'sync_binlog'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 4 | +---------------+-------+ 1 row in set (0.00 sec) Seconds_Behind_Master: 1013 Some stats from mytop : MariaDB on localhost (10.0.1-MariaDB-log) up 0+03:35:22 [20:55:58] Queries: 18.9k qps: 1 Slow: 1.0 Se/In/Up/De(%): 33/13881/2497/559 Sorts: 0 qps now: 3 Slow qps: 0.0 Threads: 3 ( 3/ 1) 23/8885/577/315 Handler: (R/W/U/D) 5622/ 236/ 215/ 88 Tmp: R/W/U: 75/ 75/ 0 ISAM Key Efficiency: 91.2% Bps in/out: 127.1k/250.2k Now in/out: 128.2k/241.9k iostat -xm 10 : Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util sda 0.00 324.20 3.30 205.80 0.04 2.23 22.29 1.12 5.36 6.06 5.35 4.63 96.80 drbd0 0.00 0.00 3.30 528.90 0.04 2.24 8.76 2.74 5.16 6.06 5.15 1.81 96.24 Do you need more informations ? How can I help more ? -- Greg