Bug #80674 Slave does over two times more disk writes to ib_logfileX then master

Submitted: 9 Mar 2016 13:29 Modified: 9 Mar 2016 16:11
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.6.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: replication

[9 Mar 2016 13:29] Przemyslaw Malkowski
Description:
During simple sysbench test, a slave, having same settings like master, does over two times more disk writes to the InnoDB log files.
I can observe this in both STATEMENT and ROW format, when both sync_binlog=1/0, *info_repository either FILE or TABLE.
Seems the only important setting to consistently reproduce the problem is innodb_flush_log_at_trx_commit=1.

How to repeat:
* use innodb_flush_log_at_trx_commit=1, innodb_flush_method = O_DIRECT on both master and slave
* Restart both the slave and master to reset P_S counters,
* note /proc/diskstats to compare after the test,
* run simple oltp sysbench on master
* check the diskstats after the test and also P_S stats for disk writes to InnoDB logs, like this example view for sys schema:

master [localhost] {msandbox} (sys) > select * from io_global_by_file_by_bytes where file like '%ib_logfile%';
+-----------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| file                  | count_read | total_read | avg_read  | count_write | total_written | avg_write | total     | write_pct |
+-----------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| @@datadir/ib_logfile0 |          6 | 68.00 KiB  | 11.33 KiB |        8378 | 36.20 MiB     | 4.42 KiB  | 36.27 MiB |     99.82 |
| @@datadir/ib_logfile1 |          0 | 0 bytes    | 0 bytes   |           0 | 0 bytes       | 0 bytes   | 0 bytes   |      0.00 |
+-----------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
2 rows in set (0.15 sec)

slave1 [localhost] {msandbox} (sys) > select * from io_global_by_file_by_bytes where file like '%ib_logfile%';
+-----------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| file                  | count_read | total_read | avg_read  | count_write | total_written | avg_write | total     | write_pct |
+-----------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| @@datadir/ib_logfile0 |          6 | 68.00 KiB  | 11.33 KiB |       20215 | 45.47 MiB     | 2.30 KiB  | 45.54 MiB |     99.85 |
| @@datadir/ib_logfile1 |          0 | 0 bytes    | 0 bytes   |           0 | 0 bytes       | 0 bytes   | 0 bytes   |      0.00 |
+-----------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
2 rows in set (0.07 sec)

The count_write is always 2-4 times bigger on the slave, depending on the workload, also bytes written, depending on workload, may be much bigger on the slave.

Suggested fix:
Doing the same job should not be more expensive on the slave versus the master. With current implementation, the chance for ending with slave lag is too high.
[9 Mar 2016 16:11] MySQL Verification Team
This is a justified feature request in replication.

Hence, it is hereby verified.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.