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.
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.