This has performance results for Postgres 18 beta1 and 17.4 from the sysbench benchmark and a large server. Results like this from me are usually boring because Postgres has done a great job at avoiding performance regressions over time. This work was done by Small Datum LLC and not sponsored.
The workload here is cached by Postgres and my focus is on regressions from new CPU overhead or mutex contention.
tl;dr
- scan is faster for 18 beta1
- some range queries without aggregation are ~3% slower for 18 beta1
- some writes are 2% to 5% slower for 18 beta1
I have work in progress to reproduce and/or explain this. There might be regressions or this might just be noise that is to be expected from a complex system. But when all three of the 18 beta1 results show a problem then I suspect there is a regression. The many cases where 18 beta1 is slower than 17.4 are accompanied by an increase in CPU and context switches so I hope to explain why 18 beta1 does that.
Builds, configuration and hardware
I compiled Postgres from source using -O2 -fno-omit-frame-pointer for versions 17.4 and 18 beta1. I got the source for 18 beta1 from github using the REL_18_BETA1 tag. I started this benchmark effort a few days before the official release.
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 17.4 is conf.diff.cx10a_c32r128.
The config files for 18 beta 1 are:
- conf.diff.cx10b_c8r32
- uses io_method='sync' to match Postgres 17 behavior
- conf.diff.cx10c_c8r32
- uses io_method='worker' and io_workers=32 to do async IO via a thread pool. I eventually learned that 32 is too large but I don't think it matters much on this workload.
- conf.diff.cx10d_c8r32
- uses io_method='io_uring' to do async IO via io_uring
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 40 client and 8 table with 10M rows per table. The read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.
The tests run with 40 client and 8 table with 10M rows per table. The read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.
The command line to run all tests is: bash r.sh 8 10000000 300 600 $deviceName 1 1 40
Results
I don't provide graphs in this post to save time and because there are few to no regressions from Postgres 17.4 to 18 beta1. 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 do provide tables below with relative QPS. The relative QPS is the following:
(QPS for PG 18beta1) / (QPS for PG 17.4)
When the relative QPS is > 1 then 18 beta1 is faster than 17.4. When it is < 1 then there might be a regression. Values from iostat and vmstat divided by QPS also provided. Theses can help to explain why something is faster or slower because it shows how much HW is used per request.
Results: Postgres 18 beta1 vs 17.4
For the results I compare throughput from Postgres 17.4 with 18 beta1 using the three configurations listed above: cx10b_c8r32, cx10c_c8r32 and cx10d_c8r32.
The hardware efficiency metrics, counters from iostat and vmstat normalized by QPS, are here.
The hardware efficiency metrics, counters from iostat and vmstat normalized by QPS, are here.
For these tests the database should be cached, the database directory (size on disk) is ~24G and shared_buffers is set to 96G.
The results are interesting and require more debugging to explain, there might be regressions or this might be natural jitter in my results. However when the jitter makes all three of the 18 beta1 results worse then I don't think it is jitter.
- scan is faster in pg18 beta1 than 17.4
- CPU/query is less with 18beta1, see cpu/o here
- there might be a small regression (about 3%) in 18 beta1 for range queries without aggregation
- using range-covered-pk* as the example, CPU/query is larger in 18 beta1, see cpu/o here
- there might be small regressions for writes in 18 beta1
- for update-nonindex* the CPU, context switches and KB written to storage /query are larger in 18 beta1, see cpu/o, cs/o, and wKB/o here
- While update-nonindex* is the outlier for an increase in wKB/o, the increases in CPU/query occur in most cases for 18 beta1
Relative to: PG 17.4 with cx10a_c32r128
col-1 : PG 18b1git with cx10b_c32r128
col-2 : PG 18b1git with cx10c_c32r128
col-3 : PG 18b1git with cx10d_c32r128
col-1 col-2 col-3 --> point queries
0.99 1.02 1.00 hot-points_range=100
0.99 0.98 1.01 point-query_range=100
1.00 1.01 0.99 points-covered-pk_range=100
1.03 1.00 1.03 points-covered-si_range=100
0.99 1.00 0.99 points-notcovered-pk_range=100
1.02 1.00 1.02 points-notcovered-si_range=100
1.00 1.01 1.00 random-points_range=1000
1.00 1.01 0.99 random-points_range=100
1.00 1.02 1.00 random-points_range=10
col-1 col-2 col-3 --> range queries without aggregation
0.96 0.96 0.97 range-covered-pk_range=100
0.96 0.96 0.96 range-covered-si_range=100
0.97 0.98 0.97 range-notcovered-pk_range=100
0.99 1.00 0.99 range-notcovered-si_range=100
1.13 1.02 1.13 scan_range=100
col-1 col-2 col-3 --> range queries with aggregation
1.01 1.00 1.01 read-only-count_range=1000
1.00 1.00 1.00 read-only-distinct_range=1000
0.99 1.00 0.99 read-only-order_range=1000
1.01 1.01 1.01 read-only_range=10000
0.99 1.00 0.99 read-only_range=100
0.97 0.98 0.98 read-only_range=10
0.99 1.00 1.00 read-only-simple_range=1000
1.00 1.00 1.00 read-only-sum_range=1000
col-1 col-2 col-3 --> writes
0.97 1.00 0.99 delete_range=100
0.97 0.98 0.97 insert_range=100
0.98 0.98 0.98 read-write_range=100
0.96 0.98 0.97 read-write_range=10
1.00 0.99 0.99 update-index_range=100
1.00 1.00 1.00 update-inlist_range=100
0.94 0.96 0.94 update-nonindex_range=100
0.90 1.03 0.98 update-one_range=100
0.96 0.96 0.95 update-zipf_range=100
1.00 1.00 0.99 write-only_range=10000
No comments:
Post a Comment