This post has results from sysbench run at higher concurrency for Postgres versions 12 through 18 on a server with 24 cores and 2 sockets. My previous post had results for sysbench run with low concurrency. The goal is to search for regressions from new CPU overhead and mutex contention.
tl;dr, from Postgres 17.6 to 18.0
- For most microbenchmarks Postgres 18.0 is between 1% and 3% slower than 17.6
- The root cause might be new CPU overhead. It will take more time to gain confidence in results like this. On other servers with sysbench run at low concurrency I only see regressions for some of the range-query microbenchmarks. Here I see them for point-query and writes.
tl;dr, from Postgres 12.22 through 18.0
- For point queries Postgres 18.0 is usually about 5% faster than 12.22
- For range queries Postgres 18.0 is usually as fast as 12.22
- For writes Postgres 18.0 is much faster than 12.22
Builds, configuration and hardware
I compiled Postgres from source for versions 12.22, 13.22, 14.19, 15.14, 16.10, 17.6, and 18.0.
The server is a SuperMicro SuperWorkstation 7049A-T with 2 sockets, 12 cores/socket, 64G RAM. The CPU is Intel Xeon Silver 4214R CPU @ 2.40GHz. It runs Ubuntu 24.04. Storage is a 1TB m.2 NVMe device with ext-4 and discard enabled.
Prior to 18.0, the configuration file was named conf.diff.cx10a_c24r64 and is here for 12.22, 13.22, 14.19, 15.14, 16.10 and 17.6.
For 18.0 I tried 3 configuration files:
- conf.diff.cx10b_c8r32 (x10b) - uses io_method=sync
- conf.diff.cx10c_c8r32 (x10c) - uses io_method=worker
- conf.diff.cx10d_c8r32 (x10d) - uses io_method=io_uring
Benchmark
I used sysbench and my usage is explained here. To save time I only run 32 of the 42 microbenchmarks
and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres.
The read-heavy microbenchmarks 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.
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 base version)
When the relative QPS is > 1 then some version is faster than base version. When it is < 1 then there might be a regression. Values from iostat and vmstat divided by QPS are also provided here. These can help to explain why something is faster or slower because it shows how much HW is used per request.
I present results for:
col-1 col-2 col-3 writes
I present results for:
- versions 12 through 18 using 12.22 as the base version
- versions 17.6 and 18.0 using 17.6 as the base version
Results: Postgres 17.6 and 18.0
Results per microbenchmark from vmstat and iostat are here.
For point queries, 18.0 often gets between 1% and 3% less QPS than 17.6 and the root cause might be new CPU overhead. See the cpu/o column (CPU per query) in the vmstat metrics here for the random-points microbenchmarks.
For range queries, 18.0 often gets between 1% and 3% less QPS than 17.6 and the root cause might be new CPU overhead. See the cpu/o column (CPU per query) in the vmstat metrics here for the read-only_range=X microbenchmarks.
For writes queries, 18.0 often gets between 1% and 2% less QPS than 17.6 and the root cause might be new CPU overhead. I ignore the write-heavy microbenchmarks that also do queries as the regressions for them might be from the queries. See the cpu/o column (CPU per query) in the vmstat metrics here for the update-index microbenchmark.
Relative to: 17.6
col-1 : 18.0 with the x10b config
col-2 : 18.0 with the x10c config
col-3 : 18.0 with the x10d config
col-1 col-2 col-3 point queries
1.00 0.99 1.00 hot-points_range=100
0.99 0.98 1.00 point-query_range=100
0.98 0.99 0.99 points-covered-pk_range=100
0.99 0.99 0.98 points-covered-si_range=100
0.97 0.99 0.98 points-notcovered-pk_range=100
0.98 0.99 0.97 points-notcovered-si_range=100
0.98 0.99 0.98 random-points_range=1000
0.97 0.99 0.98 random-points_range=100
0.99 0.99 0.98 random-points_range=10
col-1 col-2 col-3 range queries without aggregation
0.98 0.98 0.99 range-covered-pk_range=100
0.98 0.98 0.98 range-covered-si_range=100
0.98 0.99 0.98 range-notcovered-pk_range=100
1.00 1.02 0.99 range-notcovered-si_range=100
1.01 1.01 1.01 scan_range=100
col-1 col-2 col-3 range queries with aggregation
0.99 1.00 0.98 read-only-count_range=1000
0.98 0.98 0.98 read-only-distinct_range=1000
0.97 0.97 0.96 read-only-order_range=1000
0.97 0.98 0.97 read-only_range=10000
0.98 0.99 0.98 read-only_range=100
0.99 0.99 0.99 read-only_range=10
0.98 0.99 0.99 read-only-simple_range=1000
0.98 1.00 0.98 read-only-sum_range=1000
col-1 col-2 col-3 writes
0.99 0.99 0.99 delete_range=100
0.99 0.99 0.99 insert_range=100
0.98 0.98 0.98 read-write_range=100
0.99 1.00 0.99 read-write_range=10
0.99 0.98 0.97 update-index_range=100
0.99 0.99 1.00 update-inlist_range=100
1.00 0.97 0.99 update-nonindex_range=100
0.97 1.00 0.98 update-one_range=100
1.00 0.99 1.01 update-zipf_range=100
0.98 0.98 0.97 write-only_range=10000
Results: Postgres 12 to 18
For the Postgres 18.0 results in col-6, the result is in green when relative QPS is >= 1.05 and in yellow when relative QPS is <= 0.98. Yellow indicates a possible regression.
Results per microbenchmark from vmstat and iostat are here.
Relative to: 12.22
col-1 : 13.22
col-2 : 14.19
col-3 : 15.14
col-4 : 16.10
col-5 : 17.6
col-6 : 18.0 with the x10b config
col-1 col-2 col-3 col-4 col-5 col-6 point queries
0.98 0.96 0.99 0.98 2.13 2.13 hot-points_range=100
1.00 1.02 1.01 1.02 1.03 1.01 point-query_range=100
0.99 1.05 1.05 1.08 1.07 1.05 points-covered-pk_range=100
0.99 1.08 1.05 1.07 1.07 1.05 points-covered-si_range=100
0.99 1.04 1.05 1.06 1.07 1.05 points-notcovered-pk_range=100
0.99 1.05 1.04 1.05 1.06 1.04 points-notcovered-si_range=100
0.98 1.03 1.04 1.06 1.06 1.04 random-points_range=1000
0.98 1.04 1.05 1.07 1.07 1.05 random-points_range=100
0.99 1.02 1.03 1.05 1.05 1.04 random-points_range=10
col-1 col-2 col-3 col-4 col-5 col-6 range queries without aggregation
1.02 1.04 1.03 1.04 1.03 1.01 range-covered-pk_range=100
1.05 1.07 1.06 1.06 1.06 1.05 range-covered-si_range=100
0.99 1.00 1.00 1.00 1.01 0.98 range-notcovered-pk_range=100
0.97 0.99 1.00 1.01 1.01 1.01 range-notcovered-si_range=100
0.86 1.06 1.08 1.17 1.18 1.20 scan_range=100
col-1 col-2 col-3 col-4 col-5 col-6 range queries with aggregation
0.98 0.97 0.97 1.00 0.98 0.97 read-only-count_range=1000
0.99 0.99 1.02 1.02 1.01 0.99 read-only-distinct_range=1000
1.00 0.99 1.02 1.05 1.05 1.02 read-only-order_range=1000
0.99 0.99 1.04 1.07 1.09 1.06 read-only_range=10000
0.99 1.00 1.00 1.01 1.02 0.99 read-only_range=100
1.00 1.00 1.00 1.01 1.01 1.00 read-only_range=10
0.99 0.99 1.00 1.01 1.01 0.99 read-only-simple_range=1000
0.98 0.99 0.99 1.00 1.00 0.98 read-only-sum_range=1000
col-1 col-2 col-3 col-4 col-5 col-6 writes
0.98 1.09 1.09 1.04 1.29 1.27 delete_range=100
0.99 1.03 1.02 1.03 1.08 1.07 insert_range=100
1.00 1.03 1.04 1.05 1.07 1.05 read-write_range=100
1.01 1.09 1.09 1.09 1.15 1.14 read-write_range=10
1.00 1.04 1.03 0.86 1.44 1.42 update-index_range=100
1.01 1.11 1.11 1.12 1.13 1.12 update-inlist_range=100
0.99 1.04 1.06 1.05 1.25 1.25 update-nonindex_range=100
1.05 0.92 0.90 0.84 1.18 1.15 update-one_range=100
0.98 1.04 1.03 1.01 1.26 1.26 update-zipf_range=100
1.02 1.05 1.10 1.09 1.21 1.18 write-only_range=10000
No comments:
Post a Comment