This has performance results for Postgres 18 beta3, beta2, beta1, 17.5 and 17.4 using the sysbench benchmark and a large server. The working set is cached and the benchmark is run with high concurrency (40 connections). The goal is to search for CPU and mutex regressions. This work was done by Small Datum LLC and not sponsored
tl;dr
- There might be small regressions (~2%) for several range queries that don't do aggregation. This is similar to what I reported for 18 beta3 on a small server, but here it only occurs for 3 of the 4 microbenchmarks and on the small server it occurs on all 4. I am still uncertain about whether this really is a regression.
Builds, configuration and hardware
I compiled Postgres versions 17.4, 17.5, 18 beta1, 18 beta2 and 18 beta3 from source.
The server is an ax162-s from Hetzner with an AMD EPYC 9454P processor, 48 cores, AMD SMT disabled and 128G RAM. The OS is Ubuntu 22.04. Storage is 2 NVMe devices with SW RAID 1 and
ext4. More details on it are here.
The config file for Postgres 17.4 and 17.5 is x10a_c32r128.
The config files for Postgres 18 are:
- x10b_c32r128 is functionally the same as x10a_c32r128 but adds io_method=sync
- x10d_c32r128 starts with x10a_c2r128 and adds 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 tests are run using 8 tables with 10M rows per table. The read-heavy microbenchmarks run for 600 seconds and the write-heavy for 900 seconds.
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 Postgres 17.5)
When the relative QPS is > 1 then some version is faster than PG 17.5. 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.
Relative to: pg174_o2nofp.x10a_c32r128
col-1 : pg175_o2nofp.x10a_c32r128
col-2 : pg18beta1_o2nofp.x10b_c32r128
col-3 : pg18beta1_o2nofp.x10d_c32r128
col-4 : pg18beta2_o2nofp.x10d_c32r128
col-5 : pg18beta3_o2nofp.x10d_c32r128
col-1 col-2 col-3 col-4 col-5
0.98 0.99 0.99 1.00 0.99 hot-points_range=100
1.01 1.01 1.00 1.01 1.01 point-query_range=100
1.00 1.00 0.99 1.00 1.00 points-covered-pk
1.00 1.01 1.00 1.02 1.00 points-covered-si
1.00 1.01 1.00 1.00 1.00 points-notcovered-pk
1.00 1.00 1.01 1.02 1.00 points-notcovered-si
1.00 1.00 1.00 1.00 1.00 random-points_range=1000
1.00 1.01 1.00 1.00 1.00 random-points_range=100
1.00 1.00 1.00 1.00 1.00 random-points_range=10
1.00 0.97 0.96 0.98 0.97 range-covered-pk
1.00 0.97 0.97 0.98 0.97 range-covered-si
0.99 0.99 0.99 0.99 0.98 range-notcovered-pk
1.00 1.01 1.01 1.00 1.01 range-notcovered-si
1.00 1.02 1.03 1.03 1.02 read-only-count
1.00 1.00 1.00 1.01 1.01 read-only-distinct
1.00 1.00 1.00 1.00 1.00 read-only-order
1.01 1.01 1.02 1.02 1.01 read-only_range=10000
1.00 0.99 0.99 0.99 1.00 read-only_range=100
1.01 0.99 0.99 1.00 0.99 read-only_range=10
1.00 1.01 1.01 1.01 1.01 read-only-simple
1.00 1.02 1.03 1.03 1.02 read-only-sum
1.00 1.13 1.14 1.02 0.91 scan_range=100
1.00 1.13 1.13 1.02 0.90 scan.warm_range=100
1.00 0.99 0.99 0.99 0.99 delete_range=100
0.99 1.00 1.02 0.99 1.00 insert_range=100
1.01 1.00 1.00 1.00 0.99 read-write_range=100
1.00 0.98 1.00 1.01 0.99 read-write_range=10
0.99 0.99 1.02 0.98 0.96 update-index
1.00 1.01 1.00 1.00 1.01 update-inlist
0.98 0.98 0.99 0.98 0.97 update-nonindex
0.95 0.95 0.94 0.93 0.95 update-one_range=100
0.97 0.98 0.98 0.97 0.95 update-zipf_range=100
0.98 0.99 0.99 0.98 0.98 write-only_range=10000
No comments:
Post a Comment