Bug #117782 Semi-synchronous replication causes significant performance degradation

• Submitted: 25 Mar 2:54
• Modified: 25 Mar 9:42
• Reporter: Peter Parker
• Status: Won't fix
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.7.44
• OS: Any
• CPU Architecture: Any

[25 Mar 2:54] Peter Parker

Description:
Semi-synchronous replication causes significant performance degradation compared to a standalone deployment.The performance of standalone deployment is 20,000 while semi-synchronous replication is 4,500.The performance increased to 15,000
when set innodb_spin_wait_delay=60000 in replica.

How to repeat:
1.Deploy primary-replica semi-synchronous replication.
2.Test using sysbench 1.0.20, The test command is "sysbench --mysql-host=127.0.0.1 --mysql-port=3309 --mysql-user=root --mysql-password=123456 --mysql-db=test_3309 --mysql-storage-engine=innodb --mysql-ignore-errors=1062,1213,1205,1020 --tables=128 --table-size=800000 --time=120 --events=0 --report-interval=1 --rand-type=uniform --db-driver=mysql --percentile=95 oltp_read_write --forced-shutdown=off --db-ps-mode=disable --threads=256 run".
3.Test again after stopping replication.

Suggested fix:
I found that the main reason for this problem is the relay log lock contention between the IO thread and the SQL thread and 8.0 version has fixed this issue. 
The commit is "https://github.com/mysql/mysql-server/commit/dbd2ca2f6e14ce0ec19e743eb2f0cfdb20df6573".I verified with version 8.0.25 and there is no problem.
what I wonder is why 5.7 version doesn't fix it？

[25 Mar 9:42] MySQL Verification Team

Hello Peter Parker,

Thank you for the report and feedback.
As you rightly confirmed this issue is not reproduciable in 8.0 and I suggest you to upgrade to 8.0/8.4 as MySQL 5.7 reached its end-of-life (EOL) in October 2023, meaning it no longer receives bug fixes etc. Thank you.

regards,
Umesh