| Bug #103911 | MySQL 8.0 Sysbench Benchmark (oltp_write_only) lower than MySQL 5.7.28 | ||
|---|---|---|---|
| Submitted: | 4 Jun 2021 10:15 | Modified: | 7 Jun 2021 1:56 |
| Reporter: | Demon Chen | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 8.0.20 | OS: | Linux (centos7) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | benchmark test, mysql5.7, mysql8.0, sysbench | ||
[4 Jun 2021 10:16]
Demon Chen
perf snapshot:mysql-8.0.20
Attachment: 8.0.png (image/png, text), 393.11 KiB.
[4 Jun 2021 10:16]
Demon Chen
perf snapshot:mysql-5.7.28
Attachment: 5.7.png (image/png, text), 362.96 KiB.
[4 Jun 2021 10:19]
Demon Chen
mysql 8::my.cnf
Attachment: 8.0cnf.txt (text/plain), 5.58 KiB.
[4 Jun 2021 10:21]
Demon Chen
mysql 5.7:my.cnf
Attachment: 5.7cnf.txt (text/plain), 5.26 KiB.
[4 Jun 2021 12:21]
MySQL Verification Team
Hi Mr. Chen, Thank you very much for your bug report. However, there are, already, about 50 verified bugs that have proven that 5.7 is faster on the same SQL statements, then mysql-8.0 We have found one of those, so we are setting your report as a duplicate of the following one: https://bugs.mysql.com/bug.php?id=94387 The cause of slowdown are many new features which have been implemented in 8.0, which made entire code more complicated. One of the workarounds to remedy the situation is to make full use of all new features in mysql-8.0.
[6 Jun 2021 13:05]
MySQL Verification Team
there have been improvements in semisync replication performance in 5.7.33 and 8.0.23. so better to try newest versions of 8.0.x and 5.7.x and see if they're any improved compared to the numbers here. 8.0.20 is fairly old in terms of number of bugs fixed in 8.0.25.
[7 Jun 2021 1:56]
Demon Chen
Hi, Any suggestion changes about the config file to indroduce to me? And There's only master to run the benchamark, no slave at all.
Description: 1.Here is my test scenario: (1)sysbench command sysbench --mysql-host=*** --mysql-port=3306 --mysql-db=sysbench --db-driver=mysql --mysql-user=*** --mysql-password=<db-password> --threads=1024 --time=1000 --table_size=500000 --rand-type=uniform --report-interval=1 --percentile=99 --skip_trx=on --db-ps-mode=disable --tables=100 --mysql-ignore-errors=all oltp_write_only.lua run (2)mysql version - MySQL Binary: 8.0.20,5.7.28 (3)System env 64C 192G 7T SSD (4)result 1)512 threads: 5.7. tps:7191.39 qps:28765.61 avg:71.17 tp99:94.10 8.0: tps:6528.61 qps:26114.46 avg:78.40 tp99:114.72 2)1024 threads without binlog compress: 5.7: tps:7259.81 qps:29039.31 avg:70.51 tp99:92.42 8.0：tps:6635.61 qps:26542.48 avg:77.14 tp99:110.66 3)1024 threads without binlog compress,slow_log:off 5.7: tps:10789.58 qps:43158.46 avg:94.85 tp99:125.52 8.0：tps:7271.66 qps:29086.74 avg:140.71 tp99:196.89 It seems that MySQL5.7.28 performs better than MySQL8.0.20 , which is quite unexpected. I also found mysql-8.0.20 consume more cpu load than mysql-5.7.28 and it's sys consumption. The perf top snapshot are shown below: mysql 8.0.20: - 26.85% 0.07% [kernel] [k] entry_SYSCALL_64_after_hwframe ? 3.62% entry_SYSCALL_64_after_hwframe ? - do_syscall_64 ? - 2.57% __se_sys_futex ? - 2.95% do_futex ? - 2.10% futex_wait ? - 1.71% futex_wait_setup ? + 9.42% _raw_spin_lock ? - 1.49% futex_wake ? - 5.60% _raw_spin_lock ? 5.53% native_queued_spin_lock_slowpath mysql 5.7.28: - 18.72% 0.11% [kernel] [k] entry_SYSCALL_64_after_hwframe ▒ 3.12% entry_SYSCALL_64_after_hwframe ▒ - do_syscall_64 ▒ - 1.14% __se_sys_futex ▒ - 1.62% do_futex ▒ - 0.99% futex_wake ▒ - 0.97% futex_wait I wanna know whethere I have some mistakes or ? Could you give me some guidance? How to repeat: The sysbench script show before