This has performance results for Postgres 18 beta1 and several older Postgres releases using the sysbench benchmark and a small server. The difference between this post and the previous post is that the working set here is larger than memory while it was cached by Postgres in the previous post.
I almost always run sysbench with a cached workload so there might be more noise in the results here.
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.
tl;dr - with io_method='worker' in Postgres 18 beta1
- IO-bound scans are 1.53X faster
- Context switches per query are up ~10X. I don't know if this is a problem.
- But I had set io_workers to 16 which was probably too big
- IO-bound scans are 1.29X faster
- Context switches per query are up ~5X. I don't know if this is a problem.
tl;dr - with io_method='sync' in Postgres 18 beta1
- Performance is similar to Postgres 17.5 which is good
Builds, configuration and hardware
I used a small server with 8 cores, 32G of RAM and 1 NVMe device. More details are in the previous post.
For Postgres versions 14.0 through 17.5 the configuration files are in the pg* subdirectories here with the name conf.diff.cx10a_c8r32. For Postgres 18 beta1 the configuration files are here and I used 3 variations, which are here:
I do provide tables below with relative QPS. The relative QPS is the following:
- 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 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. For most of the microbenchmarks the working set is larger than memory.
The tests run with 1 client and 1 table with 500M rows. The read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.
The tests run with 1 client and 1 table with 500M rows. 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 1 500000000 300 600 $deviceName 1 1 1
Results
I don't provide graphs in this post to save time and because there are few to no regressions from Postgres 17.5 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 $some_version) / (QPS for $base_version)
And $base_version is either Postgres 17.5 or Postgres 14.0 as specified below. 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 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.5
For the results here $base_version is Postgres 17.5 and that is compared with Postgres 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.
Results for the scan microbenchmark are much better with new configs I can use with 18 beta1:
- for the cx10c_c8r32 config that uses io_method='worker' and io_workers=16
- throughput is 1.53X better than Postgres 17.5
- the rate of context switches per query is almost 10X larger with this config, see cs/o here
- the IO efficiency rates (reads/query & read KB /query) are similar to 17.5
- I assume that 16 is too large for io_workers
- for the cx10d_c8r32 config that uses io_method='io_uring'
- throughput is 1.29X better than Postgres 17.5
- the rate of context switches per query is about 5X larger with this config, see cs/o here
- the IO efficiency rates (reads/query & read KB /query) are similar to 17.5
More iostat and vmstat metrics from the scan microbenchmark
Legend:
* r/s - iostat reads /s
* rMB/s - iostat read MB /s
* r/o - iostat reads /query
* rKB/o - iostat read KB /query
* o/s - queries /s
* cs/s - context switches /s
* cpu/s - cpu utilization (vmstat us + sy)
* cs/o - context switches /query
* cpu/o - cpu microseconds /query
r/s rMB/s r/o rKB/o o/s
3717.7 434.5 1.787 213.909 2080
6845.4 651.3 2.170 211.381 3155
5015.9 556.7 1.878 213.417 2671
cs/s cpu/s cs/o cpu/o
1699 13.1 0.817 0.006282
23639 20.2 7.493 0.006395
12487 17.1 4.675 0.006417
Relative QPS per microbenchmark
Relative to: PG 17.5 with cx10a_c8r32
col-1 : PG 18 beta1 with cx10b_c8r32
col-2 : PG 18beta1 with cx10c_c8r32
col-3 : PG 18beta1 with cx10d_c8r32
col-1 col-2 col-3 --> point queries
1.00 1.00 1.01 hot-points_range=100
0.99 0.99 0.99 point-query_range=100
1.00 1.00 1.02 points-covered-pk_range=100
1.01 1.02 1.00 points-covered-si_range=100
1.00 0.99 0.99 points-notcovered-pk_range=100
0.99 0.98 0.99 points-notcovered-si_range=100
1.00 1.00 1.00 random-points_range=1000
1.00 0.99 1.00 random-points_range=100
0.99 0.95 1.02 random-points_range=10
col-1 col-2 col-3 --> range queries, part 1, no aggregation
0.97 0.97 0.99 range-covered-pk_range=100
0.98 0.98 1.00 range-covered-si_range=100
0.99 0.99 0.99 range-notcovered-pk_range=100
0.99 0.99 1.00 range-notcovered-si_range=100
1.01 1.53 1.29 scan_range=100
col-1 col-2 col-3 --> range queries, part 2, with aggregation
0.99 1.00 1.04 read-only-count_range=1000
0.99 0.99 0.99 read-only-distinct_range=1000
0.98 0.99 0.99 read-only-order_range=1000
0.99 0.98 0.98 read-only_range=10000
0.99 0.99 0.99 read-only_range=100
0.99 0.99 0.99 read-only_range=10
0.99 0.99 0.99 read-only-simple_range=1000
0.99 0.99 0.99 read-only-sum_range=1000
col-1 col-2 col-3 --> writes
0.99 1.00 1.00 delete_range=100
0.99 0.98 0.98 insert_range=100
0.99 0.99 0.99 read-write_range=100
1.00 0.99 0.99 read-write_range=10
1.00 1.00 0.99 update-index_range=100
0.99 0.97 1.00 update-inlist_range=100
1.02 1.02 0.99 update-nonindex_range=100
0.98 0.98 0.99 update-one_range=100
1.00 1.00 0.98 update-zipf_range=100
1.00 1.00 1.00 write-only_range=10000
Results: Postgres 14.0 through 18 beta1
For the results here $base_version is Postgres 14.0 and that is compared with more recent Postgres releases. The purpose for this is to see how performance changes over time. Postgres 18beta1 is between 7% slower and 13% faster than 14.0 and there are more improvements than regressions. This is yet another boring result from Postgres, but it is great to see that it continues to focus on CPU efficiency.
The hardware efficiency metrics, counters from iostat and vmstat normalized by QPS, are here.
The data below is also here which can be easier to read.
The results for 18 beta1 are similar to 17.5.
The results for 18 beta1 are similar to 17.5.
Relative to: PG 14.0 with cx10a_c8r32
col-1 : PG 14.18 with cx10a_c8r32
col-2 : PG 15.0 with cx10a_c8r32
col-3 : PG 15.13 with cx10a_c8r32
col-4 : PG 16.0 with cx10a_c8r32
col-5 : PG 16.9 with cx10a_c8r32.
col-6 : PG 17.0 with cx10a_c8r32
col-7 : PG 17.5 with cx10a_c8r32
col-8 : PG 18 beta1 with cx10b_c8r32
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 --> point queries
0.99 1.00 1.00 0.99 1.00 2.03 2.00 2.00 hot-points_range=100
0.99 0.99 0.99 0.99 0.99 0.99 1.00 0.98 point-query_range=100
0.98 1.01 1.00 1.02 1.01 0.99 1.00 1.01 points-covered-pk_range=100
1.00 0.98 0.97 1.00 1.00 0.97 0.97 0.98 points-covered-si_range=100
1.00 1.00 1.00 1.00 1.00 0.98 1.00 1.00 points-notcovered-pk_range=100
1.01 1.01 1.01 1.01 1.01 1.00 1.01 1.00 points-notcovered-si_range=100
1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 random-points_range=1000
1.00 1.00 1.00 1.00 1.00 0.96 1.00 1.00 random-points_range=100
1.00 1.00 1.00 1.00 1.00 1.00 1.00 0.99 random-points_range=10
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 --> range queries, no aggregation
0.98 0.98 0.96 0.97 0.96 0.99 0.98 0.95 range-covered-pk_range=100
0.97 0.97 0.97 0.98 0.96 0.99 0.97 0.95 range-covered-si_range=100
1.00 0.99 1.00 0.99 0.99 0.98 0.99 0.98 range-notcovered-pk_range=100
1.01 1.00 1.01 1.01 1.01 1.00 1.01 1.00 range-notcovered-si_range=100
0.94 0.87 0.85 0.94 0.96 0.97 0.96 0.97 scan_range=100
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 --> range queries, with aggregation
1.00 1.00 1.00 1.00 0.99 0.99 1.00 0.99 read-only-count_range=1000
0.99 0.99 0.99 1.01 1.00 1.00 1.01 0.99 read-only-distinct_range=1000
0.99 1.00 1.01 1.02 1.00 1.01 1.02 1.00 read-only-order_range=1000
1.00 1.02 1.01 1.03 1.03 1.03 1.05 1.03 read-only_range=10000
1.00 0.99 0.99 1.00 1.00 1.00 1.00 0.99 read-only_range=100
0.99 0.99 0.99 1.00 0.99 1.00 1.00 0.99 read-only_range=10
0.99 0.98 0.99 0.99 0.99 0.99 1.00 0.98 read-only-simple_range=1000
1.00 0.99 1.00 1.00 0.99 1.00 1.00 0.99 read-only-sum_range=1000
col-1 col-2 col-3 col-4 col-5 col-6 col-7 col-8 --> writes
0.99 0.99 0.99 0.99 0.99 0.99 1.00 1.00 delete_range=100
1.01 0.99 0.99 0.99 1.00 1.01 1.01 1.00 insert_range=100
1.02 1.05 1.03 1.04 1.01 0.97 0.97 0.97 read-write_range=100
1.03 1.03 1.04 1.04 1.03 0.94 0.94 0.94 read-write_range=10
1.03 1.03 1.03 1.04 1.02 1.05 1.04 1.04 update-index_range=100
0.99 0.99 0.99 0.99 0.99 1.05 1.05 1.04 update-inlist_range=100
1.01 1.03 1.02 1.02 1.01 1.03 1.02 1.05 update-nonindex_range=100
0.98 0.99 0.99 0.99 0.99 1.11 1.11 1.08 update-one_range=100
1.00 0.99 1.00 1.00 1.00 0.98 0.98 0.98 update-zipf_range=100
1.00 1.00 1.00 1.00 1.00 0.89 0.89 0.89 write-only_range=10000
No comments:
Post a Comment