This is yet another great result for Postgres 18.0 vs sysbench. This time I used a 32-core server. Results for a 24-core server are here. The goal for this benchmark is to check for regressions from new CPU overhead and mutex contention.
I repeated the benchmark twice because I had some uncertainty about platform variance (HW and SW) on the first run.
tl;dr, from Postgres 17.6 to 18.0
- There might be regressions from 17.6 to 18.0 but they are small (usually <= 3%)
tl;dr, from Postgres 12.22 through 18.0
- the hot-points test is almost 2X faster starting in 17.6
- scan is ~1.2X faster starting in 14.19
- all write tests are much faster staring in 17.6
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 Dell Precision 7865 Tower Workstation with 1 socket, 128G RAM and an AMD Ryzen Threadripper PRO 5975WX with 32-Cores. The OS is Ubuntu 24.04 and storage is a 2TB m.2 SSD with ext-4 and discard enabled.
Prior to 18.0, the configuration file was named conf.diff.cx10a_c32r128 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_c32r128 (x10b) - uses io_method=sync
- conf.diff.cx10c_c32r128 (x10c) - uses io_method=worker
- conf.diff.cx10d_c32r128 (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 24 clients and 8 tables with 10M rows per table. The purpose is to search for regressions from new CPU overhead and mutex contention.
I ran the benchmark twice. In the first run, there was several weeks between getting results for the older Postgres releases and Postgres 18.0 so I am less certain about variance from the hardware and softare. One concern is changes in daily temperature because I don't have a climate-controlled server room. Another concern is changes from updating my OS install.
In the second run, all results were collected within 7 days and I am less concerned about variance there.
In the second run, all results were collected within 7 days and I am less concerned about variance there.
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:
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
Some comments:
- 18.0 looks better relative to 17.6 in the second run and I explain my uncertainty about the first run above
- But I am skeptical about the great result for 18.0 on the full scan test (scan_range=100) in the second run. That might be variance induced by vacuum.
- There might be regressions from 17.6 to 18.0 but they are small (usually <= 3%)
- The small regression in read-only_range=10 might be from new optimizer overhead, because it doesn't reproduce when the length of the range query is increased -- see read-only_range=100 and read-only_range=10000.
Relative to: 17.6
col-1 : 18.0 with the x10b config that uses io_method=sync
col-2 : 18.0 with the x10c config that uses io_method=worker
col-3 : 18.0 with the x10d config that uses io_method=io_uring
col-1 col-2 col-3 point queries, first run
0.97 0.99 0.94 hot-points_range=100
0.97 0.98 0.96 point-query_range=100
1.00 0.99 0.99 points-covered-pk_range=100
0.99 1.00 1.00 points-covered-si_range=100
0.98 0.99 0.98 points-notcovered-pk_range=100
0.99 0.99 0.99 points-notcovered-si_range=100
1.00 1.00 0.99 random-points_range=1000
0.98 0.98 0.98 random-points_range=100
0.99 0.98 0.99 random-points_range=10
col-1 col-2 col-3 point queries, second run
0.98 1.00 0.99 hot-points_range=100
1.00 1.00 0.99 point-query_range=100
1.01 1.01 1.01 points-covered-pk_range=100
1.00 1.01 1.00 points-covered-si_range=100
1.00 0.98 1.00 points-notcovered-pk_range=100
1.00 1.00 1.01 points-notcovered-si_range=100
1.00 1.01 1.01 random-points_range=1000
1.00 0.99 1.01 random-points_range=100
0.99 0.99 1.00 random-points_range=10
col-1 col-2 col-3 range queries without aggregation, first run
0.97 0.98 0.95 range-covered-pk_range=100
0.97 0.97 0.94 range-covered-si_range=100
0.98 0.98 0.97 range-notcovered-pk_range=100
0.99 0.99 0.98 range-notcovered-si_range=100
0.97 0.99 0.96 scan_range=100
col-1 col-2 col-3 range queries without aggregation, second run
0.99 0.99 0.98 range-covered-pk_range=100
0.99 0.99 0.99 range-covered-si_range=100
0.98 0.99 0.98 range-notcovered-pk_range=100
0.99 1.00 1.00 range-notcovered-si_range=100
1.24 1.24 1.22 scan_range=100
col-1 col-2 col-3 range queries with aggregation, first run
0.99 1.00 1.00 read-only-count_range=1000
1.01 1.01 1.01 read-only-distinct_range=1000
1.01 1.01 1.00 read-only-order_range=1000
1.04 1.04 1.04 read-only_range=10000
0.99 0.99 0.98 read-only_range=100
0.97 0.98 0.97 read-only_range=10
0.99 0.98 0.98 read-only-simple_range=1000
0.99 0.99 0.99 read-only-sum_range=1000
col-1 col-2 col-3 range queries with aggregation, second run
0.99 1.00 1.00 read-only-count_range=1000
1.01 1.01 1.00 read-only-distinct_range=1000
0.99 0.99 1.00 read-only-order_range=1000
1.02 1.03 1.03 read-only_range=10000
0.99 0.99 0.99 read-only_range=100
0.99 0.99 0.98 read-only_range=10
0.99 1.00 1.01 read-only-simple_range=1000
1.00 1.00 1.00 read-only-sum_range=1000
col-1 col-2 col-3 writes, first run
0.99 0.98 0.96 delete_range=100
0.99 0.96 0.98 insert_range=100
1.00 0.99 0.98 read-write_range=100
0.99 0.98 0.98 read-write_range=10
1.00 0.99 1.00 update-index_range=100
1.03 0.95 1.01 update-inlist_range=100
0.99 0.99 1.00 update-nonindex_range=100
1.00 1.00 1.01 update-one_range=100
0.98 0.99 1.00 update-zipf_range=100
0.97 0.97 0.99 write-only_range=10000
col-1 col-2 col-3 writes, second run
0.97 0.97 0.98 delete_range=100
0.99 0.99 1.00 insert_range=100
0.99 0.99 0.98 read-write_range=100
0.98 0.98 0.98 read-write_range=10
0.97 0.98 0.97 update-index_range=100
0.98 0.99 1.04 update-inlist_range=100
0.98 0.99 0.98 update-nonindex_range=100
0.99 0.99 0.98 update-one_range=100
0.98 0.99 0.98 update-zipf_range=100
0.99 0.97 0.95 write-only_range=10000
Results: Postgres 12 to 18
The data below with a larger font is here.
Some comments:
- the hot-points test is almost 2X faster starting in 17.6
- scan is ~1.2X faster starting in 14.19
- all write tests are much faster staring in 17.6
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-7 : 18.0 with the x10c config
col-8 : 18.0 with the x10d config
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 point queries, first run
1.02 1.00 1.01 1.00 1.94 1.87 1.91 1.82 hot-points_range=100
1.01 1.02 1.02 1.00 1.02 0.99 1.00 0.98 point-query_range=100
1.02 1.02 1.01 1.03 1.01 1.01 1.00 1.00 points-covered-pk_range=100
1.01 1.04 1.03 1.05 1.03 1.02 1.03 1.03 points-covered-si_range=100
1.01 1.01 1.01 1.02 1.02 1.00 1.00 1.00 points-notcovered-pk_range=100
1.00 1.03 1.02 1.03 1.02 1.01 1.01 1.02 points-notcovered-si_range=100
1.01 1.02 1.02 1.03 1.00 1.00 1.00 0.99 random-points_range=1000
1.01 1.02 1.02 1.02 1.02 1.00 1.00 1.00 random-points_range=100
1.02 1.03 1.02 1.02 1.01 1.00 1.00 1.00 random-points_range=10
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 point queries, second run
1.00 0.98 0.99 1.00 1.94 1.90 1.93 1.92 hot-points_range=100
1.00 1.01 1.02 1.03 1.03 1.02 1.02 1.02 point-query_range=100
1.02 1.01 1.00 1.04 0.99 1.00 1.00 0.99 points-covered-pk_range=100
1.01 1.04 1.03 1.07 1.03 1.03 1.05 1.04 points-covered-si_range=100
1.01 1.02 1.03 1.04 1.01 1.00 0.99 1.01 points-notcovered-pk_range=100
1.02 1.05 1.05 1.05 1.03 1.03 1.03 1.04 points-notcovered-si_range=100
1.01 1.02 1.03 1.03 0.99 0.99 1.00 1.00 random-points_range=1000
1.02 1.02 1.03 1.04 1.01 1.01 1.00 1.01 random-points_range=100
1.02 1.02 1.02 1.03 1.02 1.01 1.01 1.02 random-points_range=10
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 range queries without aggregation, first run
1.00 1.02 1.02 1.01 1.00 0.97 0.98 0.95 range-covered-pk_range=100
1.00 1.02 1.02 1.01 1.00 0.97 0.97 0.94 range-covered-si_range=100
1.01 1.00 1.00 1.00 0.99 0.97 0.97 0.97 range-notcovered-pk_range=100
0.99 1.00 1.00 0.99 1.01 1.00 1.00 0.99 range-notcovered-si_range=100
0.98 1.24 1.11 1.13 1.16 1.12 1.14 1.11 scan_range=100
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 range queries without aggregation, second run
1.01 1.02 1.02 1.02 1.01 1.00 1.00 0.99 range-covered-pk_range=100
1.01 1.03 1.02 1.02 1.01 1.00 1.01 1.00 range-covered-si_range=100
1.00 0.99 1.00 1.00 0.99 0.97 0.98 0.98 range-notcovered-pk_range=100
1.00 1.00 1.00 0.98 1.01 1.00 1.01 1.01 range-notcovered-si_range=100
1.00 1.27 1.15 1.15 0.97 1.20 1.20 1.18 scan_range=100
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 range queries with aggregation, first run
1.02 1.00 1.00 1.01 0.97 0.96 0.97 0.97 read-only-count_range=1000
1.00 1.00 1.02 1.02 0.98 0.99 0.99 0.99 read-only-distinct_range=1000
1.01 1.00 1.03 1.03 1.00 1.01 1.01 1.01 read-only-order_range=1000
1.00 0.98 1.00 1.06 0.95 0.99 0.99 0.99 read-only_range=10000
1.00 1.00 1.00 1.00 1.00 0.98 0.98 0.98 read-only_range=100
1.00 1.01 1.01 1.00 1.01 0.98 0.99 0.98 read-only_range=10
1.01 1.00 1.02 1.01 1.00 0.99 0.98 0.98 read-only-simple_range=1000
1.00 1.00 1.01 1.00 0.99 0.98 0.98 0.98 read-only-sum_range=1000
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 range queries with aggregation, second run
1.03 1.02 1.02 1.03 0.97 0.97 0.97 0.98 read-only-count_range=1000
1.00 0.99 1.02 1.02 0.98 0.99 0.99 0.99 read-only-distinct_range=1000
1.00 0.99 1.02 1.04 1.02 1.01 1.01 1.02 read-only-order_range=1000
1.01 1.03 1.03 1.06 0.97 0.99 0.99 0.99 read-only_range=10000
0.99 1.00 1.00 1.01 1.00 0.99 0.99 0.99 read-only_range=100
0.99 1.00 1.00 1.00 1.01 0.99 1.00 0.99 read-only_range=10
1.00 0.99 1.01 1.00 0.99 0.98 0.98 0.99 read-only-simple_range=1000
1.00 1.00 1.01 1.01 0.99 0.98 0.98 0.98 read-only-sum_range=1000
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 writes, first run
1.00 1.08 1.08 1.05 1.25 1.24 1.23 1.20 delete_range=100
1.01 1.05 1.04 1.03 1.07 1.06 1.02 1.05 insert_range=100
1.00 1.06 1.07 1.07 1.10 1.09 1.08 1.07 read-write_range=100
1.00 1.07 1.08 1.07 1.13 1.13 1.11 1.11 read-write_range=10
0.99 1.04 1.04 0.90 1.43 1.43 1.41 1.43 update-index_range=100
1.00 1.09 1.08 1.08 1.11 1.15 1.06 1.12 update-inlist_range=100
1.00 1.05 1.05 1.04 1.35 1.34 1.34 1.35 update-nonindex_range=100
1.02 0.95 0.96 0.93 1.19 1.19 1.19 1.20 update-one_range=100
1.00 1.05 1.08 1.07 1.23 1.21 1.22 1.23 update-zipf_range=100
1.01 1.06 1.05 1.01 1.25 1.22 1.20 1.24 write-only_range=10000
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 writes, second run
1.00 1.06 1.07 1.07 1.26 1.23 1.23 1.24 delete_range=100
1.03 1.07 1.05 1.05 1.09 1.07 1.08 1.09 insert_range=100
1.01 1.07 1.08 1.07 1.11 1.10 1.10 1.09 read-write_range=100
0.99 1.04 1.06 1.07 1.13 1.11 1.11 1.12 read-write_range=10
0.99 1.02 1.04 0.87 1.44 1.40 1.41 1.40 update-index_range=100
1.00 1.11 1.12 1.09 1.17 1.14 1.16 1.22 update-inlist_range=100
1.01 1.04 1.06 1.03 1.36 1.33 1.35 1.34 update-nonindex_range=100
1.01 0.95 0.98 0.94 1.22 1.21 1.21 1.20 update-one_range=100
0.99 1.05 1.07 1.07 1.24 1.21 1.22 1.21 update-zipf_range=100
1.02 1.06 1.06 1.02 1.27 1.25 1.23 1.21 write-only_range=10000
No comments:
Post a Comment