At what level of concurrency do MySQL 5.7 and 8.0 become faster than 5.6?

Are MySQL 5.7 and 8.0 faster than 5.6? That depends a lot on the workload -- both types of SQL and amount of concurrency. Here I summarize results from sysbench on a larger server (48 cores) using 1, 4, 6, 8, 10, 20 and 40 clients to show how things change.

tl;dr

• the workload here is microbenchmarks with a database cached by InnoDB
• 5.7.44 is faster than 8.0.x at all concurrency levels on most microbenchmarks
• for 5.6.51 vs 8.0.x
• for point queries, 5.6.51 is faster at <= 8 clients
• for range queries without aggregation 5.6.51 is always faster
• for range queries with aggregation 5.6.51 is faster except at 40 clients
• for writes, 5.6.51 is almost always faster at 10 or fewer clients (excluding update-index)

Performance summaries

For point queries:

• 5.7.44 is always faster than 8.0
• 8.0.28 suffers from bug 102037 - found by me with sysbench, fixed in 8.0.31
• at what level of concurrency do most things get faster in 5.7 & 8.0 vs 5.6?
• 5.7.44 becomes faster than 5.6.51 at 6+ clients
• 8.0.x becomes faster than 5.6.51 at between 10 and 20 clients
• Two of the microbenchmarks are always faster in 5.6.51 - both do non-covering queries on a secondary index

For range queries without aggregation

• 5.7.44 is always faster than 8.0x
• 5.6.51 is always faster than 5.7.44 and 8.0.x

For range queries with aggregation

• 5.7.44 is almost always faster than 8.0.x
• 5.7.44 becomes faster than 5.6.51 at 6+ clients
• 8.0.x becomes faster than 5.6.51 at 40 clients

For writes

• For update-index
• 5.7.44 and 8.0.x are always faster than 5.6.51 at 4+ clients
• There is an odd drop from ~6X to ~3X for 8.0.32 and 8.0.39 at 20 clients
• 5.7.44 is mostly faster than 8.0.x for 1 to 20 clients and they have similar results at 40 clients
• 5.7.44 & 8.0.x are always faster than 5.6.51 at 20+ clients

Builds, configuration and hardware

I compiled MySQL from source for versions 5.6.51, 5.7.44, 8.0.28, 8.0.32, 8.0.39 and 8.0.41.

The server is an ax162-s from Hetzner with 48 cores (AMD EPYC 9454P), 128G RAM and AMD SMT disabled. It uses Ubuntu 22.04 and storage is ext4 with SW RAID 1 over 2 locally attached NVMe devices. More details on it are here. At list prices a similar server from Google Cloud costs 10X more than from Hetzner.

The configuration files are named my.cnf.cz11a_c32r128 and here for 5.6.51, 5.7.44, 8.0.28, 8.0.32, 8.0.39 and 8.0.41.

Benchmark

I used sysbench and my usage is explained here. To save time I only run 27 of the 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by InnoDB.

The tests run with 8 tables and 10M rows/table. There are 40 client threads, read-heavy microbenchmarks run for 180 seconds and write-heavy run for 300 seconds.

The command lines to run all tests are:

bash r.sh 8 10000000 180 300 md2 1 1 1

bash r.sh 8 10000000 180 300 md2 1 1 4

bash r.sh 8 10000000 180 300 md2 1 1 6

bash r.sh 8 10000000 180 300 md2 1 1 8

bash r.sh 8 10000000 180 300 md2 1 1 10

bash r.sh 8 10000000 180 300 md2 1 1 20

bash r.sh 8 10000000 180 300 md2 1 1 40

Results

For the results below I split the microbenchmarks into 4 groups: point queries, range queries without aggregation, range queries with queries, writes. The spreadsheet with all data is here. Files with performance summaries for relative and absolute QPS are here. Values from iostat and vmstat per microbenchmark are here for 1 client, 4 clients, 6 clients, 8 clients, 10 clients, 20 clients and 40 clients. These help to explain why something is faster or slower because it shows how much HW is used per query.

The relative QPS is the following where $version is >= 5.7.44.

(QPS for $version) / (QPS for MySQL 5.6.51)

The numbers in the spreadsheets are the relative QPS. When the relative QPS is > 1 then $version is faster than MySQL 5.6.51. When it is 3.0 then $version is 3X faster than the base case.

Results: charts 

Notes on the charts

• the y-axis shows the relative QPS
• the y-axis starts at 0.80 to make it easier to see differences
• in some cases the y-axis truncates the good outliers, cases where the relative QPS is greater than 1.5. I do this to improve readability for values near 1.0. Regardless, the improvements are nice.

Results: point queries

Summary

• 5.7.44 is always faster than 8.0
• 8.0.28 suffers from bug 102037 - found by me with sysbench, fixed in 8.0.31
• at what level of concurrency do most things get faster in 5.7 & 8.0 vs 5.6?
• 5.7.44 becomes faster than 5.6.51 at 6+ clients
• 8.0.x becomes faster than 5.6.51 at between 10 and 20 clients
• Two of the microbenchmarks are always faster in 5.6.51 - both do non-covering queries on a secondary index

Results: range queries without aggregation

Summary

• 5.7.44 is always faster than 8.0x
• 5.6.51 is always faster than 5.7.44 and 8.0.x

Results: range queries with aggregation

Summary

• 5.7.44 is almost always faster than 8.0.x
• 5.7.44 becomes faster than 5.6.51 at 6+ clients
• 8.0.x becomes faster than 5.6.51 at 40 clients

Results: writes

The relative speedup for the update-index microbenchmark is frequently so large that it obscures the smaller changes on other microbenchmarks. So here I truncate the y-axis for some of the charts (for 6+ clients) and the section that follows has the charts without truncation.

Summary

• For update-index
• 5.7.44 and 8.0.x are always faster than 5.6.51 at 4+ clients
• There is an odd drop from ~6X to ~3X for 8.0.32 and 8.0.39 at 20 clients but you can't see that on the charts in this section because of the truncation. It is visible in the next section. From vmstat I see an increase in CPU/operation (cpu/o) and context switches /operation (cs/o) at 20 clients but not at 40 clients.
• 5.7.44 is mostly faster than 8.0.x for 1 to 20 clients and they have similar results at 40 clients
• 5.7.44 & 8.0.x are always faster than 5.6.51 at 20+ clients

Results: charts for writes without truncation

The y-axis is truncated the the charts for writes in the previous section for 6+ clients. This section has those charts without truncation.

Postgres 17.4 vs sysbench on a large server, revisited

I recently shared results for Postgres vs sysbench on a large server. The results were mostly boring (it is rare for me to spot regressions in Postgres) but there was one microbenchmark where there was a problem. The problem microbenchmark does a range scan with aggregation and the alleged regression arrived in Postgres 11. With advice from Postgres experts it looked like the problem was an intermittent change in the query plan.

In this post I explain additional tests that I did and in this case the alleged regression was still there, but like many things in DBMS-land it depends, there is nuance. For now I assume the problem is from a change in the query plan and I will run more tests with more instrumentation to investigate that. Here the alleged regression might be ~5% and only at the highest concurrency level (40. clients).

Postgres the DBMS and community are not fans of query plan hints and the sysbench tests that I use don't add hints for queries. Query plan hints are possible in Postgres via the pg_hint_plan extension.  Query plan hints have been good to me with web-scale MySQL. For some of the web-scale workloads that I support the SQL and schema doesn't change much and query plan hints have two benefits -- plan stability and CPU reduction. By CPU reduction I mean that the CPU overhead from the optimizer is reduced because it has less work to do.

tl;dr

• There might be a regression for some range queries, but it is small here (~5%) and only occurs at the highest concurrency level (40 clients). I assume this is from a change in the query plan.
• I have yet to explain the alleged regression
• I like query plan hints

The problem microbenchmark

The microbenchmarks in question do a range scan with aggregation and have a name like read-only_range=$X where X has been 10, 100 and 1000. The value of X is the length of the range scan. These are run via oltp_read_only.lua and uses these SQL statements.

Build, configuration, hardware, benchmark

These are described in my previous post. But a few things have changed for this report

• I only tested Postgres versions 10.23, 11.0 and 11.10
• I repeated the benchmark for 1, 10, 20 and 40 client threads. Previously I only ran it for 40.
• I ran the read-only_range=$X microbenchmark for X in 10, 100, 1000, 2000, 4000, 8000, 16000 and 32000. Previously I ran it for X in 10, 100, 10000.

What didn't change is that the tests are run with 8 tables and 10M rows/table, read-heavy microbenchmarks run for 180 seconds and write-heavy run for 300 seconds.

Results: overview

For the results below I split the microbenchmarks into 4 groups -- 1 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, one group has queries without aggregation and the other has queries with aggregation. The spreadsheet with all data and charts is here. It has a tab for 1, 10, 20 and 40 clients (named dop=$X for X in 1, 10, 20 and 40).

Files with performance summaries are here. These include summaries of results from vmstat and iostat for each microbenchmark which are here for 1 client, 10 clients, 20 clients and 40 clients.

The relative QPS is the following where $version is either 11.0 or 11.10.

(QPS for $version) / (QPS for Postgres 10.23)

The numbers in the spreadsheets are the relative QPS. When the relative QPS is > 1 then $version is faster than Postgres 10.23.  When it is 3.0 then $version is 3X faster than the base case.

Notes on the charts

• the y-axis shows the relative QPS
• the y-axis starts at 0.90 to make it easier to see differences
• there are 4 charts per section, one for each of 1, 10, 20 and 40 clients

Results: point queries

Summary

• Postgres 11.x is always faster than 10.23 and usually about 3% faster

Results: range queries without aggregation

Summary

• There are no regressions
• Postgres 11.0 & 11.10 get up to 11% more QPS than 10.23 for the range-covered and range-notcovered microbenchmarks
• For the scan microbenchmark QPS is mostly unchanged between Postgres 10.23, 11.0 and 11.10 but in one cases Postgres 11 was slightly slower (relative QPS for 11.0 was 0.99 at 20 clients). 

Results: range queries with aggregation

I repeated the read-only microbenchmark using range queries of length 10, 100, 1000, 2000, 4000, 8000, 16000 and 32000.

Summary:

• In general, the advantage for Postgres 11.0 & 11.10 vs 10.23 was largest for the longest range scans (16000 & 32000) and next largest for the shortest range scans (10 & 100).
• The comparison for range scans of length 1000, 2000, 4000 and 8000 was interesting. Here the benefit for Postgres 11.0 & 11.10 was not as large and in one case (range=8000 at 40 clients) there was a small regression (~5%). Perhaps there is a change in the query plan.

Results: writes

Summary:

• Postgres 11.0 & 11.10 were almost always faster than 10.23 and up to 1.75X faster
• In one case (update-one microbenchmark at 20 clients) Postgres 11.0 & 11.10 were ~5% slower than 10.23. And this is odd because 11.0 and 11.10 were ~1.7X faster at 40 clients on the same microbenchmark. I can only wave my hands for this one. But don't think this is a regression.
• The update-one microbenchmark is run by oltp_update_non_index.lua (the name means it updates non-indexed columns), the SQL for the update is here and the schema is here.
• From vmstat and iostat metrics for 20 clients and for 40 clients and looking at the CPU /operation (cpu/o) and context switches /operation (cs/o)
• For 20 clients these are slightly larger for 11.0 & 11.10 vs 10.23
• For 40 clients these are significantly small for 11.0 & 11.10 vs 10.23
• The microbenchmarks that aren't read-only are run for 600 seconds each and it is possible for performance variance to come from write debt (writeback, vacuum, etc) inherited from microbenchmarks that preceded update-one. The order in which the update microbenchmarks are run is here and is update-inlist, update-index, update-nonindex, update-one, update-zipf. From the dop=20 tab on the spreadsheet, throughput is 1.1X to 1.3X larger for the 3 update microbenchmarks that precede update-one so inherited write debt might explain this results.

Postgres 17.4 vs sysbench on a large server

Postgres has done a great job at avoiding performance regressions over time. It has results from sysbench and a large server for all point releases from Postgres 17.x and then the latest point release from Postgres 10 through 16.

This work was done by Small Datum LLC and not sponsored.

tl;dr - over time ...

• For the 27 microbenchmarks there is one regression that arrives in 11.x and remains through 17.x
• Postgres has many big improvements
  

Updates:

• I am still trying to explain the regression but the problem isn't obvious.
• The regression first arrives in Postgres 11.0
• Flamegraphs don't show an obvious problem
• Perf HW counters show an increase in memory system stalls

Builds, configuration and hardware

I compiled Postgres from source using -O2 -fno-omit-frame-pointer for versions  10.23, 11.22, 12.22, 13.20, 14.17, 15.12, 16.8, 17.0, 17.1, 17.2, 17.3 and 17.4.

The server is an ax162-s from Hetzner with 48 cores (AMD EPYC 9454P), 128G RAM and AMD SMT disabled. It uses Ubuntu 22.04 and storage is ext4 with SW RAID 1 over 2 locally attached NVMe devices. More details on it are here. At list prices a similar server from Google Cloud costs 10X more than from Hetzner.

The configuration files for the large server are in the pg* subdirectories here with the name conf.diff.cx10a_c32r128.

Benchmark

I used sysbench and my usage is explained here. To save time I only run 27 of the 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres.

The tests run with 8 tables and 10M rows/table. There are 40 client threads, read-heavy microbenchmarks run for 180 seconds and write-heavy run for 300 seconds.

The command line to run all tests is:  bash r.sh 8 10000000 180 300 md2 1 1 40

Results

For the results below I split the microbenchmarks into 4 groups -- 1 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.

Values from iostat and vmstat divided by QPS are here. This can help to explain why something is faster or slower because it shows how much HW is used per request.

The relative QPS is the following where $version is >= 11.22.

(QPS for $version) / (QPS for Postgres 10.23)

The numbers in the spreadsheets are the relative QPS. When the relative QPS is > 1 then $version is faster than Postgres 10.23.  When it is 3.0 then $version is 3X faster than the base case.

Results: charts 

Notes on the charts

• the y-axis shows the relative QPS
• the y-axis starts at 0.80 to make it easier to see differences
• in some cases the y-axis truncates the good outliers, cases where the relative QPS is greater than 1.5. I do this to improve readability for values near 1.0. Regardless, the improvements are nice.

Point queries

• performance is stable over time in most cases
• performance gets much better for hot-points with Postgres 17.0

Range queries, part 1

• performance is stable over time with small improvements for most tests
• performance gets ~1.2X better over time on the scan test

Range queries, part 2

• these do a short range scan with aggregation and the =10, =100 and =10000 is the number of rows scanned per query
• performance is stable over time on the tests that do shorter range scans (=100, =10)
• performance drops by ~10% starting in 11.22 on the test with a longer range scan (=10000)

Writes

• depending on the test, performance over time is either stable, has a small improvement or has a large improvement

A possible regression!

I have yet to explain this.

For the read-only_range=10000 tests where QPS drops by ~10% starting in 11.22 the problem is that Postgres uses more CPU per query starting in 11.22 (see the cpu/o column which stands for CPU per operation or CPU per query).

Metrics per test from vmstat and iostat are here and I highlighted results for the read-only_range=X tests that do a range scan with aggregation. 

Things I have checked for read-only_range=10000

• output from ps for 10.23, 11.22 and 12.22 looks similar
• output from vmstat for 10.23, 11.22 and 12.22 looks similar with a few small differences. This output is from the middle of the microbenchmark.
• both swpd and free are larger in 11.22 and 12.22
• both buff and cache are larger in 10.23
• for 10.23, 11.22 and 12.22 those values don't change during the microbenchmark
• Flamegraphs for 10.23, 11.22 and 12.22 are here and look similar. The numbered (*.1.svg, *.2.svg) files are taken in sequence and then all are combined to create the *.all.svg file.
• Output with perf HW counters for 10.23, 11.0 and 11.10 are here. These show there are more memory system stalls starting in 11.0. I haven't explained the file naming scheme, but the data in the table below is from this file.
• Flat (no flamegraphs) perf profiles are here for 10.23, 11.0 and 11.10. There are ~8 samples per DBMS version and the 7th sample is here for 10.23, for 11.0 and for 11.10. From a quick check of them I don't see obvious problems.

I repeated tests for Postgres 11.0 and 11.10 and they both show a regression from 11.23. Then I repeated tests for 10.23, 11.0 and 11.10 while running perf to get the values of HW counters per microbenchmark and several show an increase in memory system stalls:

Perf HW counters that show more overhead staring in Postgres 11.0. The numbers below are the value relative to Postgres 10.23, when it is 1.257 below that that counter is 1.257X larger in 11.0 (or 11.10) then it was in 10.23.

pg11.0  pg11.10 counter

1.257   1.288   branch-misses

1.309   1.345   branch-miss-pct

1.222   1.120   iTLB-load-miss-pct

1.166   1.174   L1-dcache-load-misses

1.162   1.145   L1-dcache-load-miss-pct

1.207   1.207   L1-icache-load-miss-pct

1.254   1.136   L1-icache-loads-misses