MySQL 8.0.38 vs cached Sysbench on a medium server

This has benchmark results for MySQL 8.0.38 and a few other 8.0 releases using Sysbench with a cached database on a medium server. By small, medium or large server I mean < 10 cores for small, 10 to 19 cores for medium, 20+ cores for large. Results from the Insert Benchmark in the same setup are here.

tl;dr

• Performance for many range scans might be about 10% lower in 8.0.38 than in 8.0.26. The regression is much larger for the scan benchmark where the performance drop is about 22%. The regressions arrived in 8.0.30 and 8.0.31. Percona PS-8822 and MySQL 111538 are open for this.
• Performance for writes is up to 9% slower in 8.0.38 vs 8.0.26 with one exception. Performance for the update-index microbenchmark is ~23% slower in 8.0.38. The update-index regression arrives in 8.0.30. I assume it is related to changes for the InnoDB redo log (see innodb_redo_log_capacity and the release notes). It is odd that the large regression is limited to the update-index microbenchmark, although this benchmark requires secondary index maintenance which might mean there is more stress on redo.
• There is a huge improvement, almost 2X, for several microbenchmarks in the point-2 microbenchmark group. Bug 102037 was fixed in MySQL 8.0.31. I reported that bug against MySQL 8.0.22 thanks to sysbench.

Builds, configuration and hardware

I compiled from source MySQL versions 8.0.26 through 8.0.38.

The server is a c2d-highcpu-32 instance type on GCP (c2d high-CPU) with 32 vCPU, 64G RAM and SMT disabled so there are 16 cores. It uses Ubuntu 22.04 and storage is ext4 (data=writeback) using SW RAID 0 over 2 locally attached NVMe devices.

The my.cnf file is here for MySQL 8.0.30+ and is here for 8.0.26 through 8.0.28.

Benchmark

I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. The database is cached by InnoDB.

The benchmark is run with 12 threads, 8 tables and 10M rows per table.. Ech microbenchmark runs for 300 seconds if read-only and 600 seconds otherwise. Prepared statements were enabled.

The command lines for my helper scripts were:

#  8 tables, 10M rows/table, 12 threads
bash r.sh 8 10000000 300 600 md0 1 1 12

Results

For the results below I split the 42 microbenchmarks into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. The spreadsheet with all data is here. For each microbenchmark group there is a table with summary statistics. I don't have charts because that would use too much space, but the results per microbenchmark are in the spreadsheets.

The numbers in the spreadsheets and the tables below are the relative QPS which is (QPS for my version) / (QPS for base case). When the relative throughput is > 1 then that version is faster than the base case.

For all results below the base case is InnoDB from MySQL 8.0.26

Results: summary statistics

Each table has summary statistics per microbenchmark group. The numbers are the relative QPS for MySQL 8.0.38 which is (QPS for 8.0.38 / QPS for 8.0.26).

The results are mixed using the median values.

• Performance for many range scans might be about 10% lower in 8.0.38 than in 8.0.26. The regression is much larger for the scan benchmark where the performance drop is about 22%. The regressions arrived in 8.0.30 and 8.0.31. Percona PS-8822 and MySQL 111538 are open for this. From bug comments and browsing the code, the root cause might be a change in how function inlining is done for InnoDB.
• Performance for writes is up to 9% slower in 8.0.38 vs 8.0.26 with one exception. Performance for the update-index microbenchmark is ~23% slower in 8.0.38. The update-index regression arrives in 8.0.30. I assume it is related to changes for the InnoDB redo log (see innodb_redo_log_capacity and the release notes).
• There is a huge improvement, almost 2X, for several microbenchmarks in the point-2 microbenchmark group. Bug 102037 was fixed in MySQL 8.0.31. I reported that bug against MySQL 8.0.22 thanks to sysbench.
• The releases at which regressions occur are visible in the spreadsheet

	min	max	avg	median
point-1	0.93	1.06	1.00	1.02
point-2	0.95	1.97	1.31	1.02
range-1	0.78	0.91	0.89	0.90
range-2	0.91	0.93	0.92	0.92
writes	0.77	0.96	0.92	0.94

Results: charts

The y-axis starts at 0.70 instead of 0 to improve readability. The charts can make it easier to see trends and to see when regressions or improvements arrive.

I suspect the regressions for point queries are related to PS-8822 and MySQL 111538.

I suspect the regressions for point queries are related to PS-8822 and MySQL 111538. The large improvement to the random-points microbenchmarks are from fixing bug 102037. 

I suspect the regressions for range queries are related to PS-8822 and MySQL 111538 and the worst-case regression occurs for the scan microbenchmark.

I suspect the regressions for range queries are related to PS-8822 and MySQL 111538 

The regressions for most write microbenchmarks is <= 10% with one exception -- update-index. The update-index regression arrives in 8.0.30. I assume it is related to changes for the InnoDB redo log (see innodb_redo_log_capacity and the release notes).

Debugging the regression in update-index

The regression for update-index arrives in 8.0.30. 

From vmstat metrics I see:

• more CPU per operation in 8.0.30
• The cpu/o column is CPU /operation and it increases from .002615 in 8.0.28 to .002979 in 8.0.30. The cpu in cpu/o is derived from the sum of vmstat us and sy.
• more context switches per operation in 8.0.30
• The cs/o column is context switches /operation and it increases from 10.726 in 8.0.28 to 12.258 in 8.0.30. There appears to be more mutex contention in 8.0.30.

I repeated tests with MySQL 8.0.28 with a my.cnf changed to use the same number and size of redo log files as used by 8.0.30+. The goal was to determine whether more+smaller redo log files were the issue for the update-index regression. Alas, it was not and 8.0.28 with that alternate my.cnf still was much better at update-index than 8.0.30+. With 8.0.30+ the my.cnf has redo_log_capacity =50G and MySQL uses 32 files which are each ~1.6G (50G / 32). To match that in the repeated tests with 8.0.28 I used: 

innodb_log_files_in_group=32

innodb_log_file_size=1638M

I then repeated tests and used PMP to collect and aggregate thread stacks. One stack I see in 8.0.30 that doesn't occur in 8.0.28 is below and I confirmed that log_free_check_wait exists in 8.0.28. Alas, this thread stack also doesn't show up with 8.0.31 or 8.0.32.

 __GI___clock_nanosleep
__GI___nanosleep
std::this_thread::sleep_for<long,,wait_for<log_free_check_wait(log_t&)::<lambda(bool)>
log_free_check_wait
log_free_check,log_free_check
log_free_check,row_upd
row_upd_step
row_update_for_mysql_using_upd_graph
ha_innobase::update_row
handler::ha_update_row
Sql_cmd_update::update_single_table
Sql_cmd_dml::execute
mysql_execute_command
Prepared_statement::execute
Prepared_statement::execute_loop
mysqld_stmt_execute
dispatch_command
do_command
handle_connection
pfs_spawn_thread
start_thread
clone

Debugging the regression in scan

From vmstat metrics for a range query I see that the CPU overhead increases ~5% from 8.0.28 to 8.0.30 while QPS drops ~5%. I don't have good vmstat data for the scan microbenchmark but in the past the problem has been more CPU. This looks like Percona PS-8822 and MySQL 111538 and I still have hope it will be fixed.