Description:
Set up S1, S2 in mysql master-to-master replication mode(As shown in the figure),
S2 would apply relay-log-events fetched from S1, in commit phase of replay, S2 log those events
in its own binlog-file, and send them back to S1.
In terms of the final results, S1 will never handle duplicated log-events sent by S2
(may not even write them into relay-log-file).
Sending back duplicate log-events cause waste of bandwidth, this is particularly apparent for
replication mode on cross city data centers.
Before optimization:
app
| (write)
----------- -----------
| S1 | ------> | S2 |
----------- <------ -----------
duplicate log events
How to repeat:
Non existing, it's a feature request.
Suggested fix:
I put the patch here.
This patch introduced the ability to identify duplicate log-event which was generated by master ,
and stop send it back.
This ablility save network bandwidth in both master-to-master semisynchronous and asynchronous mode.
With optimization:
app
| (write)
----------- -----------
| S1 | ------> | S2 |
----------- <--X--- -----------
duplicate log events
And some performance test result post here:
===================
32 Cores(Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz), 64G mem, SSD
=== START OF INFORMATION SECTION ===
Vendor: HP
Product: LOGICAL VOLUME
Revision: 3.04
User Capacity: 300,035,497,984 bytes [300 GB]
Logical block size: 512 bytes
Rotation Rate: Solid State Device
Logical Unit id: 0x600508b1001c43438914d1b8882416e7
Serial number: PCQVU0BRH3L0B1
Device type: disk
Local Time is: Tue Sep 18 19:36:44 2018 CST
SMART support is: Available - device has SMART capability.
SMART support is: Enabled
Temperature Warning: Disabled or Not Supported
===================
Mysql version: 5.7.23
benchmark tools: sysbench with my own lua script,
256 thread, total 5000000 events route to S1(as shown in figures) in master-to-master semisynchronous replication.
1 compelete trasaction include: one insert query and one update query.
record size is about 1K~2K(avg).
This is S2(as shown in figures) bandwidth usage:
Before optimization:
-------- -QPS- -TPS- -Hit%- ------threads------ -----bytes----
time | ins upd del sel iud| lor hit| run con cre cac| recv send|
17:56:41| 7946 7944 0 1 15890| 241013 100.00| 2 3 0 0| 21.6m 21.3m|
17:56:42| 7762 7764 0 1 15526| 237148 100.00| 2 3 0 0| 20.7m 20.9m|
17:56:43| 8321 8321 0 1 16642| 252494 100.00| 2 3 0 0| 22.8m 22.4m|
17:56:44| 8258 8256 0 1 16514| 252625 100.00| 2 3 0 0| 22.6m 22.2m|
17:56:45| 8398 8399 0 1 16797| 255436 100.00| 2 3 0 0| 22.0m 22.6m|
17:56:46| 8046 8047 0 1 16093| 244888 100.00| 2 3 0 0| 21.7m 21.6m|
After optimization:
-------- -QPS- -TPS- -Hit%- ------threads------ -----bytes----
time | ins upd del sel iud| lor hit| run con cre cac| recv send|
17:57:57| 8590 8590 0 1 17180| 270360 100.00| 2 3 0 1| 23.2m 72k|
17:57:58| 8546 8546 0 1 17092| 270911 100.00| 2 3 0 1| 23.1m 70k|
17:57:59| 8412 8410 0 1 16822| 272607 100.00| 2 3 0 1| 22.7m 72k|
17:58:00| 8069 8071 0 1 16140| 261694 100.00| 2 3 0 1| 21.8m 66k|
17:58:01| 8354 8352 0 1 16706| 270664 100.00| 2 3 0 1| 22.6m 69k|
Note:
Bindwidth:
S2's network flow is reduced from 21.3m/sec to 70K/sec
TPS:
Without Write-set-replication-optimization(before version 5.7.22), the transaction performance
has greate improvement in semisynchronous replication(49.42% improvement, MySQL-5.7.21)
In version 5.7.23, write-set-optimization's effect is more appreciable than this patch;
In theory, this patch will improve performance of log replay in 'semisynchronous' mode, because S2
can instant commit after write binlog-file without wating response from S1.
Description: Set up S1, S2 in mysql master-to-master replication mode(As shown in the figure), S2 would apply relay-log-events fetched from S1, in commit phase of replay, S2 log those events in its own binlog-file, and send them back to S1. In terms of the final results, S1 will never handle duplicated log-events sent by S2 (may not even write them into relay-log-file). Sending back duplicate log-events cause waste of bandwidth, this is particularly apparent for replication mode on cross city data centers. Before optimization: app | (write) ----------- ----------- | S1 | ------> | S2 | ----------- <------ ----------- duplicate log events How to repeat: Non existing, it's a feature request. Suggested fix: I put the patch here. This patch introduced the ability to identify duplicate log-event which was generated by master , and stop send it back. This ablility save network bandwidth in both master-to-master semisynchronous and asynchronous mode. With optimization: app | (write) ----------- ----------- | S1 | ------> | S2 | ----------- <--X--- ----------- duplicate log events And some performance test result post here: =================== 32 Cores(Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz), 64G mem, SSD === START OF INFORMATION SECTION === Vendor: HP Product: LOGICAL VOLUME Revision: 3.04 User Capacity: 300,035,497,984 bytes [300 GB] Logical block size: 512 bytes Rotation Rate: Solid State Device Logical Unit id: 0x600508b1001c43438914d1b8882416e7 Serial number: PCQVU0BRH3L0B1 Device type: disk Local Time is: Tue Sep 18 19:36:44 2018 CST SMART support is: Available - device has SMART capability. SMART support is: Enabled Temperature Warning: Disabled or Not Supported =================== Mysql version: 5.7.23 benchmark tools: sysbench with my own lua script, 256 thread, total 5000000 events route to S1(as shown in figures) in master-to-master semisynchronous replication. 1 compelete trasaction include: one insert query and one update query. record size is about 1K~2K(avg). This is S2(as shown in figures) bandwidth usage: Before optimization: -------- -QPS- -TPS- -Hit%- ------threads------ -----bytes---- time | ins upd del sel iud| lor hit| run con cre cac| recv send| 17:56:41| 7946 7944 0 1 15890| 241013 100.00| 2 3 0 0| 21.6m 21.3m| 17:56:42| 7762 7764 0 1 15526| 237148 100.00| 2 3 0 0| 20.7m 20.9m| 17:56:43| 8321 8321 0 1 16642| 252494 100.00| 2 3 0 0| 22.8m 22.4m| 17:56:44| 8258 8256 0 1 16514| 252625 100.00| 2 3 0 0| 22.6m 22.2m| 17:56:45| 8398 8399 0 1 16797| 255436 100.00| 2 3 0 0| 22.0m 22.6m| 17:56:46| 8046 8047 0 1 16093| 244888 100.00| 2 3 0 0| 21.7m 21.6m| After optimization: -------- -QPS- -TPS- -Hit%- ------threads------ -----bytes---- time | ins upd del sel iud| lor hit| run con cre cac| recv send| 17:57:57| 8590 8590 0 1 17180| 270360 100.00| 2 3 0 1| 23.2m 72k| 17:57:58| 8546 8546 0 1 17092| 270911 100.00| 2 3 0 1| 23.1m 70k| 17:57:59| 8412 8410 0 1 16822| 272607 100.00| 2 3 0 1| 22.7m 72k| 17:58:00| 8069 8071 0 1 16140| 261694 100.00| 2 3 0 1| 21.8m 66k| 17:58:01| 8354 8352 0 1 16706| 270664 100.00| 2 3 0 1| 22.6m 69k| Note: Bindwidth: S2's network flow is reduced from 21.3m/sec to 70K/sec TPS: Without Write-set-replication-optimization(before version 5.7.22), the transaction performance has greate improvement in semisynchronous replication(49.42% improvement, MySQL-5.7.21) In version 5.7.23, write-set-optimization's effect is more appreciable than this patch; In theory, this patch will improve performance of log replay in 'semisynchronous' mode, because S2 can instant commit after write binlog-file without wating response from S1.