maria-discuss team mailing list archive

[Greg <skygreg@xxxxxxxxx>]

2013/3/14 Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>

> 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