MySQL8 write performance degrades
Hi,
We are running performance test between MySQL 5.7 and MySQL 8. The topology is one master and 2 slaves.
We setup the environment on AWS, so we can make sure all MySQL use the same OS, CPU/RAM/Disk. We also make sure both environments receive the similar data with same through input.
From the result of "show engine innodb status", the number of insert/update/delete rows are very close during the whole test (about 24 hours). But the "OS file writes" and "OS fsyncs" on MySQL8 is 3x of MySQL5.7.
=======================================================
For MySQL 5.7
Process ID=20449, Main thread ID=140536631523072, state: sleeping
Number of rows inserted 4085861, updated 3108647, deleted 1024718, read 1507019710
122309 OS file reads, 8055960 OS file writes, 3396984 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 81.84 writes/s, 43.43 fsyncs/s
======================================================
For MySQL8
Process ID=23772, Main thread ID=140174189745920 , state=sleeping
Number of rows inserted 4071275, updated 3134809, deleted 1001634, read 1488377798
Pending flushes (fsync) log: 0; buffer pool: 432
3186 OS file reads, 22457680 OS file writes, 11971793 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 138.35 writes/s, 77.29 fsyncs/s
The final result is that the cost of the same insert on MySQL8 is degrades about 60 ~ 80% after running 24 hours. Take following result for example, we collect the average cost of each hour, at the first few hours, the gap is small. But when more hours past, MySQL degrades worse.
MySQL5.7 MySQL 8
2023-01-0706 10.99 11.6283
2023-01-0707 10.2604 10.2709
2023-01-0708 10.2866 10.3909
2023-01-0709 10.5978 10.7169
2023-01-0710 10.6058 10.94
2023-01-0711 10.8816 10.9636
2023-01-0712 10.9877 11.6655
2023-01-0713 10.9551 12.1642
2023-01-0714 11.2553 13.1958
2023-01-0715 11.8562 13.5543
2023-01-0716 12.0772 14.8228
2023-01-0717 12.2067 14.3541
2023-01-0718 12.5294 15.7468
2023-01-0719 12.7891 16.6558
2023-01-0720 12.6323 18.1368
2023-01-0721 12.8472 18.2248
2023-01-0722 12.7537 19.7497
2023-01-0723 12.2938 18.0381
2023-01-0800 11.7402 17.1949
2023-01-0801 11.8995 17.465
2023-01-0802 12.0881 18.2304
2023-01-0803 12.6831 20.7729
We wonder what could be the reason of this problem. Please share your comment