Description:
We are comparing MySQL5.7 (5.7.38) innodb cluster and MySQL8 (8.0.31) innodb cluster (write) performance.
We setup environments on AWS ec2 instances. Following are the detail information
1. Both clusters includes one primary db and 2 slave dbs
2. The instance type of these mysql nodes is m4.xlarge (4core, 16G ram), and the ebs volume is gp3 (3000 IOPS, 125M/s)
So we make suree that the hardware (cpu, ram, disk) is same between 2 clusters.
Following are the mysql8 variables we configured so far
#innodb_log_file_size = 250M
max_allowed_packet = 512M
innodb_buffer_pool_size = 4096M
max_connections=2500
transaction-isolation=READ-COMMITTED
auto_increment_increment=7
#new in mysql 8.0.31, innodb_log_file_size is deprecated.
innodb_redo_log_capacity=536870912
innodb_use_fdatasync=1
innodb_log_writer_threads=ON
innodb_flush_neighbors=1
innodb_max_dirty_pages_pct=75
innodb_max_dirty_pages_pct_lwm=0
innodb_flush_method=O_DIRECT_NO_FSYNC
We run the sysbench write_only test case with following 2 commands.
sysbench \
--db-driver=mysql \
--mysql-user="sbtest_user" \
--mysql-password="$password" \
--mysql-db="sbtest" \
--mysql-host="$host" \
--mysql-port="6446" \
--tables="16" \
--table-size="100000" \
/usr/share/sysbench/oltp_write_only.lua prepare
sysbench \
--db-driver=mysql \
--mysql-user="sbtest_user" \
--mysql-password="$password" \
--mysql-db="sbtest" \
--mysql-host="$host" \
--mysql-port="6446" \
--tables="16" \
--table-size="100000" \
--threads=16 --events=0 --report-interval=10 --time=86400 /usr/share/sysbench/oltp_write_only.lua
While running the test, we monitor some metrics every one minute. E.g. how much data is written, how many writes/fsyncs calls, etc.
MySQL5.7
--------|--------|----- Innodb row operation -----|-- Buffer Pool operation -------|------ InnoDB file i/o ------|--- InnoDB log i/o -------------|
---Time---|---QPS---| read inserted updated deleted | logical_r physical_r logical_w| reads writes fsyncs written| logical physical fsyncs written
08:14:00 | 404968|197499 67281 131012 65514| 3177974 0 1457236| 0 34405 22498 609955| 198724 19655 19665 162015
08:15:00 | 405244|197728 67328 131201 65559| 3176589 0 1458232| 0 34499 22758 604927| 198986 19952 19961 162939
08:16:00 | 405210|197787 67319 131205 65609| 3169262 0 1459142| 0 34432 22801 602426| 199184 19911 19924 161846
MySQL8
--------|--------|----- Innodb row operation -----|-- Buffer Pool operation -------|------ InnoDB file i/o ------|--- InnoDB log i/o -------------|
---Time---|---QPS---| read inserted updated deleted | logical_r physical_r logical_w| reads writes fsyncs written| logical physical fsyncs written
03:10:24 | 404224|196775 67203 130533 65294| 3099647 0 1390588| 0 68268 23696 416716|1181308 49599 23689 120168
03:11:24 | 404048|196756 67172 130539 65270| 3105626 0 1387081| 0 67869 23448 416295|1179545 49163 23441 119955
03:12:24 | 405410|197563 67402 131051 65563| 3120064 0 1396455| 0 67925 23398 417775|1185506 49129 23344 120459
NOTE: For Innodb log i/o, logical means Innodb_log_write_requests, physical means Innodb_log_writes.
On MySQL8, even though we configure innodb_flush_method to O_DIRECT_NO_FSYNC, mysql8 still always has more Innodb_log_write_requests (5x) and Innodb_log_writes (2.5x) compared to mysql5.7. We suspect that it may degrade MySQL8's
write performance. We have another performance test environment running with our application, we notiice that the physical writes count gap is even bigger, and in that environment, mysql8's write performance degrades more than 50% after running 24 hours.
MySQL8
----------|---------|----- Innodb row operation -----|-- Buffer Pool operation -------|------ InnoDB file i/o ------|--- InnoDB log i/o -------------|
---Time---|---QPS---| read inserted updated deleted | logical_r physical_r logical_w| reads writes fsyncs written| logical physical fsyncs written
01:31:36 | 40467|2748785 4997 1960 233| 3441105 0 61034| 0 18163 5349 109629| 40118 11930 5346 24054
01:32:36 | 66965|5603891 12105 3029 287| 7326149 0 163523| 0 35364 9839 205640| 86133 26959 9831 81783
01:33:36 | 44494|2170395 10277 2528 194| 2914932 0 98634| 0 30992 8472 207199| 59464 19159 8465 43481
01:34:36 | 28848|673555 4565 1492 165| 877579 0 42660| 0 17144 4307 128153| 28137 8702 4305 16158
01:35:36 | 36669|2373326 3070 2031 170| 3078528 0 66007| 0 18554 5153 118131| 34282 12388 5150 34145
MySQL5.7
----------|---------|----- Innodb row operation -----|-- Buffer Pool operation -------|------ InnoDB file i/o ------|--- InnoDB log i/o -------------|
---Time---|---QPS---| read inserted updated deleted | logical_r physical_r logical_w| reads writes fsyncs written| logical physical fsyncs written
01:31:57 | 52245|2990732 10216 2626 269| 1384559 0 92903| 0 7674 3281 198917| 84350 2228 2256 38822
01:32:57 | 66275|5794001 12636 3133 296| 2640655 0 146281| 0 10257 4453 287838| 164546 3169 3197 79294
01:33:57 | 32014|1223206 7145 1910 131| 699440 0 64576| 0 9053 2589 266321| 70149 995 1026 30050
01:34:57 | 28201|1247419 2794 1553 194| 605975 0 36323| 0 7904 2189 217063| 29854 1072 1106 13783
01:35:57 | 29440|1251741 2939 1869 122| 697259 0 48412| 0 7039 2222 204203| 60888 1050 1082 28251
And we wonder if there is any variables could be tuned to reduce the write frequency on MySQL8 so that we can verify if it has impact on the write performance.
How to repeat:
We run the sysbench write_only test case with following 2 commands.
sysbench \
--db-driver=mysql \
--mysql-user="sbtest_user" \
--mysql-password="$password" \
--mysql-db="sbtest" \
--mysql-host="$host" \
--mysql-port="6446" \
--tables="16" \
--table-size="100000" \
/usr/share/sysbench/oltp_write_only.lua prepare
sysbench \
--db-driver=mysql \
--mysql-user="sbtest_user" \
--mysql-password="$password" \
--mysql-db="sbtest" \
--mysql-host="$host" \
--mysql-port="6446" \
--tables="16" \
--table-size="100000" \
--threads=16 --events=0 --report-interval=10 --time=86400 /usr/share/sysbench/oltp_write_only.lua