Bug #109290 WRITESET causing performance issue for replication on a replica

• Submitted: 6 Dec 2022 8:28
• Modified: 13 Jan 2023 6:16
• Reporter: Charles Thompson
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S5 (Performance)
• Version: 5.7.39
• OS: Ubuntu
• CPU Architecture: x86

[6 Dec 2022 8:28] Charles Thompson

Description:
We have a set of replicas running 5.7.30 that has its replication performance decrease overtime when we dynamically set:
binlog_transaction_dependency_tracking = 'WRITESET'
transaction_write_set_extraction = 'XXHASH64'

If we dynamically set them back to defaults, replication performance stays bad, but if we restart with the variables at default, replication performance returns back to normal. We have tested this numerous times on our replicas and each time, it's proven to be the case.

How to repeat:
Have a heavy write load and have these variables set on a replica:
binlog_transaction_dependency_tracking = 'WRITESET'
transaction_write_set_extraction = 'XXHASH64'

1. Measure replication lag
2. Dynamically turn them off
3. Measure replication lag
4. Restart with variables:
binlog_transaction_dependency_tracking = 'COMMIT_ORDER'
transaction_write_set_extraction = 'OFF'
5. Measure replication lag

There should be a difference in performance as we've proven with our workload and testing

[6 Dec 2022 8:30] Charles Thompson

Sorry - version 5.7.39, not 5.7.30

[6 Dec 2022 15:48] MySQL Verification Team

Hi,

How are you analyzing the "performance issue"? You look at Seconds_Behind_Master or some other metric?

thanks

[7 Dec 2022 0:14] Charles Thompson

Yes, you can check the replication performance via Seconds Behind. That is how we were checking it on our end.

[7 Dec 2022 0:14] Charles Thompson

We were also using pt-heartbeat as well for a more precise measurement.

[13 Jan 2023 6:16] MySQL Verification Team

Hi,

I managed to verify this but it's not happening every time for me. Also I was not able to reproduce this on 8.0 at all.

Thank you for the report