Small Datum: December 2025

Saturday, December 20, 2025

IO-bound sysbench vs MySQL on a 48-core server

This has results for an IO-bound sysbench benchmark on a 48-core server for MySQL versions 5.6 through 9.5. Results from a CPU-bound sysbench benchmark on the 48-core server are here.

tl;dr

the regressions here on read-only tests are smaller than on the CPU bound workload, but when they occur are from new CPU overheads
the large improvements here on write-heavy tests are similar to the CPU bound workload

Builds, configuration and hardware

I compiled MySQL from source for versions 5.6.51, 5.7.44, 8.0.43, 8.0.44, 8.4.6, 8.4.7, 9.4.0 and 9.5.0.

The server is:

an ax162s from Hetzner with an AMD EPYC 9454P 48-Core Processor with SMT disabled
2 Intel D7-P5520 NVMe storage devices with RAID 1 (3.8T each) using ext4
128G RAM
Ubuntu 22.04 running the non-HWE kernel (5.5.0-118-generic)

The config files are here: 5.6.51, 5.7.44, 8.0.4x, 8.4.x, 9.x.0

Benchmark

The read-heavy microbenchmarks are run for 600 seconds and the write-heavy for 900 seconds. The benchmark is run with 40 clients and 8 tables with 250M rows per table. With 250M rows per table this is IO-bound. I normally use 10M rows per table for CPU-bound workloads.

The purpose is to search for regressions from new CPU overhead, mutex contention and IO stress.

Results

The microbenchmarks are split 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.

I provide charts below with relative QPS. The relative QPS is the following:

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

When the relative QPS is > 1 then some version is faster than MySQL 5.6.51. When it is < 1 then there might be a regression. When the relative QPS is 1.2 then some version is about 20% faster than MySQL 5.6.51.

Values from iostat and vmstat divided by QPS are here. These can help to explain why something is faster or slower because it shows how much HW is used per request, including CPU overhead per operation (cpu/o) and context switches per operation (cs/o) which are often a proxy for mutex contention.

The spreadsheet and charts are here and in some cases are easier to read than the charts below.

Results: point queries

This has two charts. The y-axis is truncated on the second chart to improve readability for all tests but hot-points which is a positive outlier.

Summary:

the improvement for hot-points is similar to the CPU-bound results
the regressions here for the IO-bound tests are smaller than for the CPU-bound results
the regression in point-query is from new CPU overhead, see cpu/o here which is 1.37X larger in 9.5.0 vs 5.6.51
the regression in points-covered-si is from new CPU overhead, see cpu/o here which is 1.24X larger in 9.5.0 vs 5.6.51. This test is CPU-bound, the queries don't do IO because the secondary indexes are cached.

Results: range queries without aggregation

Summary:

the regressions here for the IO-bound tests are smaller than for the CPU-bound results, except for the scan test
the regressions in scan are from new CPU overhead, see cpu/o here, which is 1.38X larger in 9.5.0 vs 5.6.51

Results: range queries with aggregation

Summary:

the regressions here for the IO-bound tests are smaller than for the CPU-bound results
the regressions in read-only-count are from new CPU overhead, see cpu/o here, which is 1.25X larger in 9.5.0 vs 5.6.51

Results: writes

Summary:

the improvements here for the IO-bound tests are similar to the CPU-bound results
the largest improvement, for the update-index test, is from using less CPU, fewer context switches, less read IO and less write IO per operation -- see cpu/o, cs/o, rKB/o and wKB/o here

Wednesday, December 17, 2025

Performance regressions in MySQL 8.4 and 9.x with sysbench

I have been claiming that I don't find significant performance regressions in MySQL 8.4 and 9.x when I use sysbench. I need to change that claim. There are regressions for write-heavy tests, they are larger for tests with more concurrency and larger when gtid support is enabled.

By gtid support is enabled I mean that these options are set to ON:

Both of these are ON by default in MySQL 9.5.0 and were OFF by default in earlier releases. I just learned about the performance impact from these and in future tests I will make probably repeat tests with them set to ON and OFF.

This blog post has results from the write-heavy tests with sysbench for MySQL 8.0, 8.4, 9.4 and 9.5 to explain my claims above.

tl;dr

Regressions are most likely and larger on the insert test
There are regressions for write-heavy workloads in MySQL 8.4 and 9.x

Throughput is typically 15% less in MySQL 9.5 than in 8.0 for tests with 16 clients on the 24-core/2-socket srever
Throughput is typically 5% less in MySQL 9.5 than 8.0 for tests with 40 clients on the 48-core server

The regressions are larger when gtid_mode and enforce_gtid_consistency are set to ON

Throughput is typically 5% to 10% less with the -gtid configs vs the -nogtid configs with 40 clients on the 48-core server. But this is less of an issue on other servers.
There are significant increases in CPU, context switch rates and KB written to storage for the -gtid configs relative to the same MySQL version using the -nogtid configs

Regressions might be larger for the insert and update-inlist tests because they have larger transactions relative to other write-heavy tests. Performance regressions are correlated with increases in CPU, context switches and KB written to storage per transaction.

What changed?

I use diff to compare the output from SHOW GLOBAL VARIABLES when I build new releases and from that it is obvious that the default value for gtid_mode and enforce_gtid_consistency changed in MySQL 9.5 but I didn't appreciate the impact from that change.

Builds, configuration and hardware

I compiled MySQL from source for versions 8.0.44, 8.4.6, 8.4.7, 9.4.0 and 9.5.0.

The versions that I tested are named:

8.0.44-nogtid

MySQL 8.0.44 with gtid_mode and enforce_gtid_consistency =OFF

8.0.44-gtid

MySQL 8.0.44 with gtid_mode and enforce_gtid_consistency =ON

8.4.7-notid

MySQL 8.4.7 with gtid_mode and enforce_gtid_consistency =OFF

8.4.7-gtid

MySQL 8.4.7 with gtid_mode and enforce_gtid_consistency =ON

9.4.0-nogtid

MySQL 9.4.0 with gtid_mode and enforce_gtid_consistency =OFF

9.4.0-gtid

MySQL 9.4.0 with gtid_mode and enforce_gtid_consistency =ON

9.5.0-nogtid

MySQL 9.5.0 with gtid_mode and enforce_gtid_consistency =OFF

9.5.0-gtid

MySQL 9.5.0 with gtid_mode and enforce_gtid_consistency =ON

The servers are:

8-core

The server is an ASUS ExpertCenter PN53 with and AMD Ryzen 7 7735HS CPU, 8 cores, SMT disabled, 32G of RAM. Storage is one NVMe device for the database using ext-4 with discard enabled. The OS is Ubuntu 24.04.
my.cnf for the -nogtid configs are here for 8.0, 8.4, 9.4, 9.5
my.cnf for the -gtid configs are here for 8.0, 8.4, 9.4, 9.5
The benchmark is run with 1 thread, 1 table and 50M rows per table

24-core

The server is a SuperMicro SuperWorkstation 7049A-T with 2 sockets, 12 cores/socket, 64G RAM, one m.2 SSD (2TB, ext4 with discard enabled). The OS is Ubuntu 24.04. The CPUs are Intel Xeon Silver 4214R CPU @ 2.40GHz.
my.cnf for the -nogtid configs are here for 8.0, 8.4, 9.4, 9.5
my.cnf for the -gtid configs are here for 8.0, 8.4, 9.4, 9.5
The benchmark is run with 16 threads, 8 tables and 10M rows per table

48-core

The server is ax162s from Hetzner with an AMD EPYC 9454P 48-Core Processor with SMT disabled and 128G of RAM. Storage is 2 Intel D7-P5520 NVMe devices with RAID 1 (3.8T each) using ext4. The OS is Ubuntu 22.04 running the non-HWE kernel (5.5.0-118-generic).
my.cnf for the -nogtid configs are here for 8.0, 8.4, 9.4, 9.5
my.cnf for the -gtid configs are here for 8.0, 8.4, 9.4, 9.5
The benchmark is run with 40 threads, 8 tables and 10M rows per table

Benchmark

I used sysbench and my usage is explained here. I now run 32 of the 42 microbenchmarks listed in that blog post. Most test only one type of SQL statement. Benchmarks are run with the database cached by InnoDB. While I ran all of the tests, I only share results from a subset of the write-heavy tests.

The read-heavy microbenchmarks are run for 600 seconds and the write-heavy for 900 seconds.

The purpose is to search for regressions from new CPU overhead and mutex contention. The workload is cached -- there should be no read IO but will be some write IO.

Results

The microbenchmarks are split into 4 groups -- 1 for point queries, 2 for range queries, 1 for writes. Here I only share results from a subset of the write-heavy tests.

I provide charts below with relative QPS. The relative QPS is the following:

(QPS for some version) / (QPS for MySQL 8.0.44)

When the relative QPS is > 1 then some version is faster than MySQL 8.0.44. When it is < 1 then there might be a regression. When the relative QPS is 1.2 then some version is about 20% faster than MySQL 8.0.44.

Values from iostat and vmstat divided by QPS are here for the 8-core, 24-core and 48-core servers. These can help to explain why something is faster or slower because it shows how much HW is used per request, including CPU overhead per operation (cpu/o) and context switches per operation (cs/o) which are often a proxy for mutex contention.

The spreadsheet and charts are here and in some cases are easier to read than the charts below. The y-axis doesn't start at 0 to improve readability.

Results: 8-core

Summary

For many tests there are small regressions from 8.0 to 8.4 and 8.4 to 9.x
There are small improvements (~5%) for the -gtid configs vs the -nogtid result for update-index
There is a small regression (~5%) for the -gtid configs vs the -nogtid result for insert
There are small regression (~1%) for the -gtid configs vs the -nogtid result for other tests

From vmstat metrics for the insert test where perf decreases with the 9.5.0-gtid result

CPU per operation (cpu/o) increases by 1.10X with the -gtid config
Context switches per operation (cs/o) increases by 1.45X with the -gtid config
KB written to storage per commit (wKB/o) increases by 1.16X with the -gtid config

From vmstat metrics for the update-index test where perf increases with the 9.5.0-gtid result

CPU per operation (cpu/o) decreases by ~3% with the -gtid config
Context switches per operation (cs/o) decrease by ~2% with the -gtid config
KB written to storage per commit (wKB/o) decreases by ~3% with the -gtid config
This result is odd. I might try to reproduce it in the future

Results: 24-core

Summary

For many tests there are regressions from 8.0 to 8.4 and 8.4 to 9.x and throughput is typically 15% less in 9.5.0 than 8.0.44
There are large regressions in 9.4 and 9.5 for update-inlist
There is usually a small regression (~5%) for the -gtid configs vs the -nogtid result

From vmstat metrics for the insert test comparing 9.5.0-gtid with 9.5.0-nogtid

Throughput is 1.15X larger in 9.5.0-nogtid
CPU per operation (cpu/o) is 1.15X larger in 9.5.0-gtid
Context switches per operation (cs/o) are 1.23X larger in 9.5.0-gtid
KB written to storage per commit (wKB/o) is 1.24X larger in 9.5.0-gtid

From vmstat metrics for the update-inlist comparing both 9.5.0-nogtid and 9.5.0-nogtid with 8.0.44-nogtid

The problems here look different than most other tests as the regressions in 9.4 and 9.5 are similar for the -gtid and -nogtid configs. If I have time I will get flamegraphs and PMP output. The server here has two sockets and can suffer more from false-sharing and real contention on cache lines.
Throughput is 1.43X larger in 8.0.44-nogtid
CPU per operation (cpu/o) is 1.05X larger in 8.0.44-nogtid
Context switches per operation (cs/o) are 1.18X larger in 8.0.44-nogtid
KB written to storage per commit (wKB/o) is ~1.12X larger in 9.5.0

Results: 48-core

Summary

For many tests there are regressions from 8.0 to 8.4
For some tests there are regressions from 8.4 to 9.x
There is usually a large regression for the -gtid configs vs the -nogtid result and the worst case occurs on the insert test

From vmstat metrics for the insert test comparing 9.5.0-gtid with 9.5.0-nogtid

Throughput is 1.17X larger in 9.5.0-nogtid
CPU per operation (cpu/o) is 1.13X larger in 9.5.0-gtid
Context switches per operation (cs/o) are 1.26X larger in 9.5.0-gtid
KB written to storage per commit (wKB/o) is 1.24X larger in 9.5.0-gtid

Thursday, December 11, 2025

Sysbench for MySQL 5.6 through 9.5 on a 2-socket, 24-core server

This has results for the sysbench benchmark on a 2-socket, 24-core server. A post with results from 8-core and 32-core servers is here.

tl;dr

old bad news - there were many large regressions from 5.6 to 5.7 to 8.0
new bad news - there are some new regressions after MySQL 8.0

Normally I claim that there are few regressions after MySQL 8.0 but that isn't the case here. I also see regressions after MySQL 8.0 on the other larger servers that I use, but that topic will explained in another post.

Builds, configuration and hardware

I compiled MySQL from source for versions 5.6.51, 5.7.44, 8.0.43, 8.0.44, 8.4.6, 8.4.7, 9.4.0 and 9.5.0.

The server is a SuperMicro SuperWorkstation 7049A-T with 2 sockets, 12 cores/socket, 64G RAM, one m.2 SSD (2TB, ext4 with discard enabled). The OS is Ubuntu 24.04. The CPUs are Intel Xeon Silver 4214R CPU @ 2.40GHz.

The config files are here for 5.6, 5.7, 8.0, 8.4 and 9.x.

Benchmark

The read-heavy microbenchmarks are run for 600 seconds and the write-heavy for 900 seconds. The benchmark is run with 16 clients and 8 tables with 10M rows per table.

The purpose is to search for regressions from new CPU overhead and mutex contention. The workload is cached -- there should be no read IO but will be some write IO.

Results

I provide charts below with relative QPS. The relative QPS is the following:

(QPS for some version) / (QPS for base version)

When the relative QPS is > 1 then some version is faster than the base version. When it is < 1 then there might be a regression. When the relative QPS is 1.2 then some version is about 20% faster than the base version.

I present two sets of charts. One set uses MySQL 5.6.51 as the base version which is my standard practice. The other uses MySQL 8.0.44 as the base version to show

The spreadsheet and charts are here and in some cases are easier to read than the charts below. Converting the Google Sheets charts to PNG files does the wrong thing for some of the test names listed at the bottom of the charts below.

Results: point queries

Summary

from 5.6 to 5.7 there are big improvements for 5 tests, no changes for 2 tests and small regressions for 2 tests
from 5.7 to 8.0 there are big regressions for all tests
from 8.0 to 9.5 performance is stable
for 9.5 the common result is ~20% less throughput vs 5.6

Using vmstat from the hot-points test to understand the performance changes (see here)

context switch rate (cs/o) is stable, mutex contention hasn't changed
CPU per query (cpu/o) drops by 35% from 5.6 to 5.7
CPU per query (cpu/o) grows by 23% from 5.7 to 8.0
CPU per query (cpu/o) is stable from 8.0 through 9.5

Results: range queries without aggregation

Summary

from 5.6 to 5.7 throughput drops by 10% to 15%
from 5.7 to 8.0 throughput drops by about 15%
from 8.0 to 9.5 throughput is stable
for 9.5 the common result is ~30% less throughput vs 5.6

Using vmstat from the scan test to understand the performance changes (see here)

context switch rates are low and can be ignored
CPU per query (cpu/o) grows by 11% from 5.6 to 5.7
CPU per query (cpu/o) grows by 15% from 5.7 to 8.0
CPU per query (cpu/o) is stable from 8.0 through 9.5

Results: range queries with aggregation

Summary

from 5.6 to 5.7 there are big improvements for 2 tests, no changes for 1 tests and regressions for 5 tests
from 5.7 to 8.0 there are regressions for all tests
from 8.0 through 9.5 performance is stable
for 9.5 the common result is ~25% less throughput vs 5.6

Using vmstat from the read-only-count test to understand the performance changes (see here)

context switch rates are similar
CPU per query (cpu/o) grows by 16% from 5.6 to 5.7
CPU per query (cpu/o) grows by 15% from 5.7 to 8.0
CPU per query (cpu/o) is stable from 8.0 through 9.5

Results: writes

Summary

from 5.6 to 5.7 there are big improvements for 9 tests and no changes for 1 test
from 5.7 to 8.0 there are regressions for all tests
from 8.4 to 9.x there are regressions for 8 tests and no change for 2 tests
for 9.5 vs 5.6: 5 are slower in 9.5, 3 are similar and 2 are faster in 9.5

Using vmstat from the insert test to understand the performance changes (see here)

in 5.7, CPU per insert drops by 30% while context switch rates are stable vs 5.6
in 8.0, CPU per insert grows by 36% while context switch rates are stable vs 5.7
in 9.5, CPU per insert grows by 3% while context switch rates grow by 23% vs 8.4

The first chart doesn't truncate the y-axis to show the big improvement for update-index but that makes it hard to see the smaller changes on the other tests.

This chart truncates the y-axis to make it easier to see changes on tests other than update-index.

Wednesday, December 10, 2025

The insert benchmark on a small server : MySQL 5.6 through 9.5

This has results for MySQL versions 5.6 through 9.5 with the Insert Benchmark on a small server. Results for Postgres on the same hardware are here.

tl;dr

good news - there are no large regressions after MySQL 8.0
bad news - there are many large regressions from 5.6 to 5.7 to 8.0

Builds, configuration and hardware

I compiled MySQL from source for versions 5.6.51, 5.7.44, 8.0.43, 8.0.44, 8.4.6, 8.4.7, 9.4.0 and 9.5.0.

The server is an ASUS ExpertCenter PN53 with and AMD Ryzen 7 7735HS CPU, 8 cores, SMT disabled, 32G of RAM. Storage is one NVMe device for the database using ext-4 with discard enabled. The OS is Ubuntu 24.04. More details on it are here.

The config files are here: 5.6.51, 5.7.44, 8.0.4x, 8.4.x, 9.x.0.

The Benchmark

The benchmark is explained here and is run with 1 client and 1 table. I repeated it with two workloads:

cached - the values for X, Y, Z are 30M, 40M, 10M
IO-bound - the values for X, Y, Z are 800M, 4M, 1M

The point query (qp100, qp500, qp1000) and range query (qr100, qr500, qr1000) steps are run for 1800 seconds each.

The benchmark steps are:

l.i0

insert X rows per table in PK order. The table has a PK index but no secondary indexes. There is one connection per client.

create 3 secondary indexes per table. There is one connection per client.

l.i1

use 2 connections/client. One inserts Y rows per table and the other does deletes at the same rate as the inserts. Each transaction modifies 50 rows (big transactions). This step is run for a fixed number of inserts, so the run time varies depending on the insert rate.

l.i2

like l.i1 but each transaction modifies 5 rows (small transactions) and Z rows are inserted and deleted per table.
Wait for S seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of S is a function of the table size.

qr100

use 3 connections/client. One does range queries and performance is reported for this. The second does does 100 inserts/s and the third does 100 deletes/s. The second and third are less busy than the first. The range queries use covering secondary indexes. If the target insert rate is not sustained then that is considered to be an SLA failure. If the target insert rate is sustained then the step does the same number of inserts for all systems tested. This step is frequently not IO-bound for the IO-bound workload.

qp100

like qr100 except uses point queries on the PK index

qr500

like qr100 but the insert and delete rates are increased from 100/s to 500/s

qp500

like qp100 but the insert and delete rates are increased from 100/s to 500/s

qr1000

like qr100 but the insert and delete rates are increased from 100/s to 1000/s

qp1000

like qp100 but the insert and delete rates are increased from 100/s to 1000/s

Results: overview

The performance reports are here for:

Cached

IO-bound

The summary sections from the performances report have 3 tables. The first shows absolute throughput by DBMS tested X benchmark step. The second has throughput relative to the version from the first row of the table. The third shows the background insert rate for benchmark steps with background inserts. The second table makes it easy to see how performance changes over time. The third table makes it easy to see which DBMS+configs failed to meet the SLA.

Below I use relative QPS to explain how performance changes. It is: (QPS for $me / QPS for $base) where $me is the result for some version $base is the result from MySQL 5.6.51.

When relative QPS is > 1.0 then performance improved over time. When it is < 1.0 then there are regressions. The Q in relative QPS measures:

insert/s for l.i0, l.i1, l.i2
indexed rows/s for l.x
range queries/s for qr100, qr500, qr1000
point queries/s for qp100, qp500, qp1000

Below I use colors to highlight the relative QPS values with yellow for regressions and blue for improvements.

Results: cached

Performance summaries are here for all versions and latest versions. I focus on the latest versions.

Below I use colors to highlight the relative QPS values with yellow for regressions and blue for improvements. There are large regressions from new CPU overheads.

the load step (l.i0) is almost 2X faster for 5.6.51 vs 8.4.7 (relative QPS is 0.59)
the create index step (l.x) is more than 2X faster for 8.4.7 vs 5.6.51
the first write-only steps (l.i1) has similar throughput for 5.6.51 and 8.4.7
the second write-only step (l.i2) is 14% slower in 8.4.7 vs 8.4.7
the range-query steps (qr*) are ~30% slower in 8.4.7 vs 5.6.51
the point-query steps (qp*) are 38% slower in 8.4.7 vs 5.6.51

dbms	l.i0	l.x	l.i1	l.i2	qr100	qp100	qr500	qp500	qr1000	qp1000
5.6.51	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
5.7.44	0.91	1.53	1.16	1.09	0.83	0.83	0.83	0.84	0.83	0.83
8.0.44	0.60	2.42	1.05	0.87	0.69	0.62	0.70	0.62	0.70	0.62
8.4.7	0.59	2.54	1.04	0.86	0.68	0.61	0.68	0.61	0.67	0.60
9.4.0	0.59	2.57	1.03	0.86	0.69	0.62	0.69	0.62	0.70	0.61
9.5.0	0.59	2.61	1.05	0.85	0.69	0.62	0.69	0.62	0.69	0.62

Results: IO-bound

Performance summaries are here for all versions and latest versions. I focus on the latest versions.

Below I use colors to highlight the relative QPS values with yellow for regressions and blue for improvements. There are large regressions from new CPU overheads.

the load step (l.i0) is almost 2X faster for 5.6.51 vs 8.4.7 (relative QPS is 0.60)
the create index step (l.x) is more than 2X faster for 8.4.7 vs 5.6.51
the first write-only steps (l.i1) is 1.54X faster for 8.4.7 vs 5.6.51
the second write-only step (l.i2) is 1.82X faster for 8.4.7 vs 5.6.51
the range-query steps (qr*) are ~20% slower in 8.4.7 vs 5.6.51
the point-query steps (qp*) are 13% slower, 3% slower and 17% faster in 8.4.7 vs 5.6.51

dbms	l.i0	l.x	l.i1	l.i2	qr100	qp100	qr500	qp500	qr1000	qp1000
5.6.51	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
5.7.44	0.91	1.42	1.52	1.78	0.84	0.92	0.87	0.97	0.93	1.17
8.0.44	0.62	2.58	1.56	1.81	0.76	0.88	0.79	0.99	0.85	1.18
8.4.7	0.60	2.65	1.54	1.82	0.74	0.87	0.77	0.98	0.82	1.17
9.4.0	0.61	2.68	1.52	1.76	0.75	0.86	0.80	0.97	0.85	1.16
9.5.0	0.60	2.75	1.53	1.73	0.75	0.87	0.79	0.97	0.84	1.17

The insert benchmark on a small server : Postgres 12.22 through 18.1

This has results for Postgres versions 12.22 through 18.1 with the Insert Benchmark on a small server.

Postgres continues to be boring in a good way. It is hard to find performance regressions.

tl;dr for a cached workload

performance has been stable from Postgres 12 through 18

tl;dr for an IO-bound workload

performance has mostly been stable
create index has been ~10% faster since Postgres 15
throughput for the write-only steps has been ~10% less since Postgres 15
throughput for the point-query steps (qp*) has been ~20% better since Postgres 13

Builds, configuration and hardware

I compiled Postgres from source using -O2 -fno-omit-frame-pointer for versions 12.22, 13.22, 13.23, 14.19, 14.20, 15.14, 15.15, 16.10, 16.11, 17.6, 17.7, 18.0 and 18.1.

For versions prior to 18, the config file is named conf.diff.cx10a_c8r32 and they are as similar as possible and here for versions 12, 13, 14, 15, 16 and 17.

For Postgres 18 I used 3 variations, which are here:

conf.diff.cx10b_c8r32

uses io_method='sync' to match Postgres 17 behavior

conf.diff.cx10c_c8r32

uses io_method='worker' and io_workers=16 to do async IO via a thread pool. I eventually learned that 16 is too large.

conf.diff.cx10d_c8r32

uses io_method='io_uring' to do async IO via io_uring

The Benchmark

The benchmark is explained here and is run with 1 client and 1 table. I repeated it with two workloads:

cached - the values for X, Y, Z are 30M, 40M, 10M
IO-bound - the values for X, Y, Z are 800M, 4M, 1M

The point query (qp100, qp500, qp1000) and range query (qr100, qr500, qr1000) steps are run for 1800 seconds each.

The benchmark steps are:

l.i0

insert X rows per table in PK order. The table has a PK index but no secondary indexes. There is one connection per client.

create 3 secondary indexes per table. There is one connection per client.

l.i1

use 2 connections/client. One inserts Y rows per table and the other does deletes at the same rate as the inserts. Each transaction modifies 50 rows (big transactions). This step is run for a fixed number of inserts, so the run time varies depending on the insert rate.

l.i2

like l.i1 but each transaction modifies 5 rows (small transactions) and Z rows are inserted and deleted per table.
Wait for S seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of S is a function of the table size.

qr100

use 3 connections/client. One does range queries and performance is reported for this. The second does does 100 inserts/s and the third does 100 deletes/s. The second and third are less busy than the first. The range queries use covering secondary indexes. If the target insert rate is not sustained then that is considered to be an SLA failure. If the target insert rate is sustained then the step does the same number of inserts for all systems tested. This step is frequently not IO-bound for the IO-bound workload.

qp100

like qr100 except uses point queries on the PK index

qr500

like qr100 but the insert and delete rates are increased from 100/s to 500/s

qp500

like qp100 but the insert and delete rates are increased from 100/s to 500/s

qr1000

like qr100 but the insert and delete rates are increased from 100/s to 1000/s

qp1000

like qp100 but the insert and delete rates are increased from 100/s to 1000/s

Results: overview

The performance reports are here for:

Cached

IO-bound

Below I use relative QPS to explain how performance changes. It is: (QPS for $me / QPS for $base) where $me is the result for some version $base is the result from Postgres 12.22.

When relative QPS is > 1.0 then performance improved over time. When it is < 1.0 then there are regressions. The Q in relative QPS measures:

insert/s for l.i0, l.i1, l.i2
indexed rows/s for l.x
range queries/s for qr100, qr500, qr1000
point queries/s for qp100, qp500, qp1000

This statement doesn't apply to this blog post, but I keep it here for copy/paste into future posts. Below I use colors to highlight the relative QPS values with red for <= 0.95, green for >= 1.05 and grey for values between 0.95 and 1.05.

Results: cached

The performance summaries are here for all versions and latest versions.

I focus on the latest versions. Throughput for 18.1 is within 2% of 12.22, with the exception of the l.i2 benchmark step. This is great news because it means that Postgres has avoided introducing new CPU overhead as they improve the DBMS. There is some noise from the l.i2 benchmark step and that doesn't surprise me because it is likely variance from two issues -- vacuum and get_actual_variable_range.

Results: IO-bound

The performance summaries are here for all versions and latest versions.

I focus on the latest versions.

throughput for the load step (l.i0) is 1% less in 18.1 vs 12.22
throughput for the index step (l.x) is 13% better in 18.1 vs 12.22
throughput for the write-only steps (l.i1, l.i2) is 11% and 12% less in 18.1 vs 12.22
throughput for the range-query steps (qr*) is 2%, 3% and 3% less in 18.1 vs 12.22
throughput for the point-query steps (qp*) is 22%, 23% and 23% better in 18.1 vs 12.22

The improvements for the index step arrived in Postgres 15.

The regressions for the write-only steps arrived in Postgres 15 and are likely from two issues -- vacuum and get_actual_variable_range.

The improvements for the point-query steps arrived in Postgres 13.

Monday, December 8, 2025

RocksDB performance over time on a small Arm server

This post has results for RocksDB on an Arm server. I previously shared results for RocksDB performance using gcc and clang. Here I share results using clang with LTO.

RocksDB is boring, there are few performance regressions.

tl;dr

for cached workloads throughput with RocksDB 10.8 is as good or better than with 6.29
for not-cached workloads throughput with RocksDB 10.8 is similar to 6.29 except for the overwrite test where it is 7% less, probably from correctness checks added in 7.x and 8.x.

Software

I used RocksDB versions 6.29, 7.0, 7.10, 8.0, 8.4, 8.8, 8.11, 9.0, 9.4, 9.8, 9.11 and 10.0 through 10.8.

I compiled each version clang version 18.3.1 with link-time optimization enabled (LTO). The build command line was:

flags=( DISABLE_WARNING_AS_ERROR=1 DEBUG_LEVEL=0 V=1 VERBOSE=1 )

# for clang+LTO
AR=llvm-ar-18 RANLIB=llvm-ranlib-18 CC=clang CXX=clang++ \
make USE_LTO=1 "${flags[@]}" static_lib db_bench

Hardware

I used a small Arm server from the Google cloud running Ubuntu 22.04. The server type was c4a-standard-8-lssd with 8 cores and 32G of RAM. Storage was 2 local SSDs with RAID 0 and ext-4.

Benchmark

Overviews on how I use db_bench are here and here.

The benchmark was run with 1 thread and used the LRU block cache.

Tests were run for three workloads:

byrx - database cached by RocksDB
iobuf - database is larger than RAM and RocksDB used buffered IO
iodir - database is larger than RAM and RocksDB used O_DIRECT

The benchmark steps that I focus on are:

fillseq

load RocksDB in key order with 1 thread

revrangeww, fwdrangeww

do reverse or forward range queries with a rate-limited writer. Report performance for the range queries

readww

do point queries with a rate-limited writer. Report performance for the point queries.

overwrite

overwrite (via Put) random keys

Relative QPS

Many of the tables below (inlined and via URL) show the relative QPS which is:
(QPS for my version / QPS for RocksDB 6.29)

The base version varies and is listed below. When the relative QPS is > 1.0 then my version is faster than RocksDB 6.29. When it is < 1.0 then there might be a performance regression or there might just be noise.

The spreadsheet with numbers and charts is here. Performance summaries are here.

Results: byrx

This has results for by byrx workload where the database is cached by RocksDB.

RocksDB 10.x is faster than 6.29 for all tests.

Results: iobuf

This has results for by iobuf workload where the database is larger than RAM and RocksDB used buffered IO.

Performance in RocksDB 10.x is about the same as 6.29 except for overwrite. I think the performance decreases in overwrite that arrived in versions 7.x and 8.x are from new correctness checks and throughput in 10.8 is 7% less than in 6.29. The big drop for fillseq in 10.6.2 was from bug 13996.

Results: iodir

This has results for by iodir workload where the database is larger than RAM and RocksDB used O_DIRECT.

Monday, December 1, 2025

Using db_bench to measure RocksDB performance with gcc and clang

This has results for db_bench, a benchmark for RocksDB, when compiling it with gcc and clang. On one of my servers I saw a regression on one of the tests (fillseq) when compiling with gcc. The result on that server didn't match what I measured on two other servers. So I repeated tests after compiling with clang to see if I could reproduce it.

tl;dr

a common outcome is

~10% more QPS with clang+LTO than with gcc
~5% more QPS with clang than with gcc

the performance gap between clang and gcc is larger in RocksDB 10.x than in earlier versions

Variance

I always worry about variance when I search for performance bugs. Variance can be misinterpreted as a performance regression and I strive to avoid that because I don't want to file bogus performance bugs.

Possible sources of variance are:

the compiler toolchain

a bad code layout might hurt performance by increasing cache and TLB misses

RocksDB

the overhead from compaction is intermittent and the LSM tree layout can help or hurt CPU overhead during reads

hardware

sources include noisy neighbors on public cloud servers, insufficient CPU cooling and CPU frequency management that is too clever

benchmark client

the way in which I run tests can create more or less variance and more information on that is here and here

Software

I used RocksDB versions 6.29.5, 7.10.2, 8.0, 8.4, 8.8, 8.11, 9.0, 9.4, 9.8, 9.11 and 10.0 through 10.8.

I compiled each version three times:

gcc using version 13.3.0
clang - using version 18.3.1
clang+LTO - using version 18.3.1, where LTO is link-time optimization

The build command lines are below

flags=( DISABLE_WARNING_AS_ERROR=1 DEBUG_LEVEL=0 V=1 VERBOSE=1 )

# for gcc
make "${flags[@]}" static_lib db_bench

# for clang
AR=llvm-ar-18 RANLIB=llvm-ranlib-18 CC=clang CXX=clang++ \
make "${flags[@]}" static_lib db_bench

# for clang+LTO
AR=llvm-ar-18 RANLIB=llvm-ranlib-18 CC=clang CXX=clang++ \
make USE_LTO=1 "${flags[@]}" static_lib db_bench

On the small servers I used the LRU block cache. On the large server I used hyper clock when possible:

lru_cache was used for versions 7.6 and earlier
hyper_clock_cache was used for versions 7.7 through 8.5
auto_hyper_clock_cache was used for versions 8.5+

Hardware

I used two small servers and one large server, all run Ubuntu 22.04:

pn-53

Ryzen 7 (AMD) CPU with 8 cores and 32G of RAM. It is v5 in the blog post
benchmarks are run with 1 client (thread)

an ARM server from the Google cloud -- c4a-standard-8-lssd with 8 cores and 32G of RAM, 2 local SSDs using RAID 0 and ext-4
benchmarks are run with 1 client (thread)

hetzner

an ax162s from Hetzner with an AMD EPYC 9454P 48-Core Processor with SMT disabled, 128G of RAM, 2 SSDs with RAID 1 (3.8T each) using ext4
benchmarks are run with 36 clients (threads)

Benchmark

Overviews on how I use db_bench are here and here.

Tests were run for a workload with the database cached by RocksDB that I call byrx in my scripts.

The benchmark steps that I focus on are:

fillseq

load RocksDB in key order with 1 thread

revrangeww, fwdrangeww

do reverse or forward range queries with a rate-limited writer. Report performance for the range queries

readww

do point queries with a rate-limited writer. Report performance for the point queries.

overwrite

overwrite (via Put) random keys

Relative QPS

Many of the tables below (inlined and via URL) show the relative QPS which is:
(QPS for my version / QPS for RocksDB 6.29 compiled with gcc)

The base version varies and is listed below. When the relative QPS is > 1.0 then my version is faster than the base version. When it is < 1.0 then there might be a performance regression or there might just be noise.

The spreadsheet with numbers and charts is here.

Results: fillseq

Results for the pn53 server

clang+LTO provides ~15% more QPS than gcc in RocksDB 10.8

clang provides ~11% more QPS than gcc in RocksDB 10.8

Results for the Arm server

I am fascinated by how stable the QPS is here for clang and clang+LTO
clang+LTO and clang provide ~3% more QPS than gcc in RocksDB 10.8

Results for the Hetzner server

I don't show results for 6.29 or 7.x to improve readability
the performance for RocksDB 10.8.3 with gcc is what motivated me to repeat tests with clang
clang+LTO and clang provide ~20% more QPS than gcc in RocksDB 10.8