This post has results for Postgres 15 and 16 versus sysbench on a small server. I am searching for performance regressions. All tests use 1 database connection as my focus here is on CPU regressions. I will repeat this on larger hardware with more concurrency to search for problems with mutexes and RW-locks.
tl;dr
- I did not find any significant CPU regressions. Throughput in PG 16rc1 is similar to 15.2. There might be small regressions (1% or 2%) but I won't claim my current setup is able to detect them.
- From past work I know that Postgres has been great at avoiding regressions over time.
Builds
I compiled Postgres 15.2, 15.3, 15.4, 16 beta1, 16 beta2, 16 beta3 and 16 rc1 from source. The builds are named o3_native_lto which is shorthand for using: -O3 -march=native -mtune=native -flto.
Benchmark
I used sysbench and my usage is explained here. Postgres was configured to cache all tables.
This benchmark used the Beelink server explained here that has 8 cores, 16G RAM and 1TB of NVMe SSD with XFS and Ubuntu 22.04.
The benchmark is run with 1 client and 1 table with 20M rows. The read-only tests ran for 600 seconds each and the other tests ran for 1200 seconds each. The command line for my wrapper scripts is:
bash r.sh 1 20000000 600 1200 nvme0n1 1 1 1
The benchmark was repeated using the a1 and a2 configs. They differ in the value for wal_compression: a1 uses lz4 and a2 uses off.
Results
The results below use relative throughput which is: (QPS for $version / QPS for version 15.2). A value larger than 1.0 means that $version gets more QPS than 15.2. I will wave my hands and claim a result greater than 1.05 or less than 0.95 is significant. There are few such numbers in the results below.
Results with the a1 config
Relative to: pg152_o3_native_lto.x9a1_bee
col-1 : pg153_o3_native_lto.x9a1_bee
col-2 : pg154_o3_native_lto.x9a1_bee
col-3 : pg16beta1_o3_native_lto.x9a1_bee
col-4 : pg16beta2_o3_native_lto.x9a1_bee
col-5 : pg16beta3_o3_native_lto.x9a1_bee
col-6 : pg16rc1_o3_native_lto.x9a1_bee
col-1 : pg153_o3_native_lto.x9a1_bee
col-2 : pg154_o3_native_lto.x9a1_bee
col-3 : pg16beta1_o3_native_lto.x9a1_bee
col-4 : pg16beta2_o3_native_lto.x9a1_bee
col-5 : pg16beta3_o3_native_lto.x9a1_bee
col-6 : pg16rc1_o3_native_lto.x9a1_bee
col-1 col-2 col-3 col-4 col-5 col-6
0.99 1.04 1.00 1.00 1.03 1.02 hot-points_range=100
0.95 0.85 0.95 0.98 0.86 1.00 point-query.pre_range=100
1.17 1.10 1.01 1.10 1.00 1.09 point-query_range=100
0.99 1.00 1.00 1.01 1.01 1.02 points-covered-pk.pre_range=100
1.00 1.00 1.00 1.01 1.01 1.01 points-covered-pk_range=100
0.99 1.00 1.00 1.01 1.00 1.01 points-covered-si.pre_range=100
1.00 1.00 1.00 1.02 1.01 1.02 points-covered-si_range=100
0.99 1.00 1.00 1.01 1.00 1.01 points-notcovered-pk.pre_range=100
0.99 1.00 1.01 1.01 1.01 1.01 points-notcovered-pk_range=100
0.99 0.99 1.00 1.01 1.00 1.01 points-notcovered-si.pre_range=100
0.99 1.00 1.01 1.01 1.01 1.01 points-notcovered-si_range=100
0.99 1.00 1.01 0.99 1.01 1.01 random-points.pre_range=1000
1.00 1.00 0.99 0.99 1.01 1.01 random-points.pre_range=100
0.99 1.00 1.01 1.00 0.99 1.00 random-points.pre_range=10
0.99 1.00 1.01 1.00 1.01 1.00 random-points_range=1000
0.99 1.00 1.00 1.00 1.00 1.00 random-points_range=100
0.99 1.00 1.01 1.00 0.99 1.00 random-points_range=10
1.00 1.00 0.98 0.98 0.95 0.99 range-covered-pk.pre_range=100
1.02 1.01 0.99 1.00 0.97 0.99 range-covered-pk_range=100
1.01 1.00 0.99 1.00 0.97 0.98 range-covered-si.pre_range=100
1.01 1.00 0.99 1.00 0.96 0.98 range-covered-si_range=100
1.01 1.00 0.98 0.99 0.97 0.99 range-notcovered-pk.pre_range=100
1.01 1.00 0.99 1.00 0.99 1.00 range-notcovered-pk_range=100
1.00 0.99 0.98 0.98 0.97 0.98 range-notcovered-si.pre_range=100
1.00 0.99 0.99 0.99 0.98 0.99 range-notcovered-si_range=100
1.00 1.07 1.56 1.09 1.07 1.05 read-only.pre_range=10000
1.00 1.02 1.02 1.03 1.02 1.01 read-only.pre_range=100
1.04 1.00 1.01 1.06 0.98 1.05 read-only.pre_range=10
1.01 1.06 1.43 1.08 1.07 1.06 read-only_range=10000
1.00 1.02 1.02 1.03 1.02 1.01 read-only_range=100
0.97 0.99 0.99 0.96 0.97 0.95 read-only_range=10
0.98 0.97 0.95 0.96 0.97 0.97 scan_range=100
0.95 0.95 1.00 0.95 0.97 0.99 delete_range=100
1.02 1.01 1.00 1.00 0.97 1.01 insert_range=100
1.00 1.01 1.01 1.02 1.02 1.01 read-write_range=100
0.98 0.99 0.99 1.00 0.98 0.97 read-write_range=10
0.99 0.99 0.98 1.00 0.98 0.99 update-index_range=100
0.99 0.99 1.00 1.00 1.00 1.00 update-inlist_range=100
0.99 0.99 0.99 1.01 1.00 0.99 update-nonindex_range=100
1.00 1.00 0.97 1.00 0.99 0.98 update-one_range=100
0.98 0.99 0.99 1.00 0.98 0.99 update-zipf_range=100
1.01 1.03 1.00 1.03 0.97 0.97 write-only_range=10000
Results with the a2 config
Relative to: pg152_o3_native_lto.x9a2_bee
col-1 : pg153_o3_native_lto.x9a2_bee
col-2 : pg154_o3_native_lto.x9a2_bee
col-3 : pg16beta1_o3_native_lto.x9a2_bee
col-4 : pg16beta2_o3_native_lto.x9a2_bee
col-5 : pg16beta3_o3_native_lto.x9a2_bee
col-6 : pg16rc1_o3_native_lto.x9a2_bee
col-1 : pg153_o3_native_lto.x9a2_bee
col-2 : pg154_o3_native_lto.x9a2_bee
col-3 : pg16beta1_o3_native_lto.x9a2_bee
col-4 : pg16beta2_o3_native_lto.x9a2_bee
col-5 : pg16beta3_o3_native_lto.x9a2_bee
col-6 : pg16rc1_o3_native_lto.x9a2_bee
col-1 col-2 col-3 col-4 col-5 col-6
0.97 0.96 0.96 0.96 1.00 0.96 hot-points_range=100
1.06 1.04 0.94 1.18 1.05 1.08 point-query.pre_range=100
1.00 1.13 1.05 1.13 1.04 1.07 point-query_range=100
1.00 0.99 1.01 1.01 1.01 1.01 points-covered-pk.pre_range=100
1.00 1.00 1.01 1.00 1.00 1.01 points-covered-pk_range=100
1.00 1.00 1.00 1.00 1.01 1.01 points-covered-si.pre_range=100
1.00 0.99 1.01 1.00 1.00 1.01 points-covered-si_range=100
1.00 1.00 1.00 1.00 1.00 1.00 points-notcovered-pk.pre_range=100
0.99 0.99 1.00 1.00 1.00 1.00 points-notcovered-pk_range=100
0.98 0.99 1.00 1.01 1.00 1.00 points-notcovered-si.pre_range=100
0.98 0.99 1.00 1.00 1.00 1.00 points-notcovered-si_range=100
1.01 0.99 1.01 1.01 1.01 1.01 random-points.pre_range=1000
1.00 1.00 0.99 0.99 1.01 1.01 random-points.pre_range=100
0.98 0.99 1.00 1.00 0.99 1.00 random-points.pre_range=10
1.00 1.01 1.01 1.01 1.01 1.01 random-points_range=1000
1.00 1.00 1.00 1.00 1.01 1.00 random-points_range=100
0.98 1.00 1.01 1.00 0.99 1.00 random-points_range=10
0.99 1.00 0.99 0.97 0.97 0.99 range-covered-pk.pre_range=100
0.99 1.00 0.98 0.98 0.97 0.98 range-covered-pk_range=100
1.00 1.00 0.99 0.99 0.98 0.99 range-covered-si.pre_range=100
1.01 1.01 1.00 1.00 0.98 0.99 range-covered-si_range=100
0.99 1.00 0.98 0.98 0.96 0.98 range-notcovered-pk.pre_range=100
0.99 0.98 0.98 0.99 0.98 0.98 range-notcovered-pk_range=100
0.99 0.99 0.98 0.99 0.98 0.98 range-notcovered-si.pre_range=100
0.99 0.99 0.99 0.99 0.98 0.99 range-notcovered-si_range=100
1.01 1.06 1.59 1.08 1.08 1.06 read-only.pre_range=10000
1.00 1.02 1.02 1.03 1.03 1.02 read-only.pre_range=100
1.00 1.05 1.01 0.99 1.01 1.08 read-only.pre_range=10
1.01 1.04 1.44 1.08 1.07 1.05 read-only_range=10000
1.01 1.01 1.02 1.04 1.02 1.02 read-only_range=100
0.97 1.00 0.98 0.96 0.97 1.03 read-only_range=10
0.96 0.96 0.96 0.97 0.96 0.97 scan_range=100
1.01 1.04 1.00 1.03 1.04 1.05 delete_range=100
1.05 1.02 1.01 1.00 1.00 1.00 insert_range=100
1.01 1.02 1.02 1.03 1.02 1.02 read-write_range=100
1.00 1.01 0.99 1.00 1.02 1.00 read-write_range=10
1.00 1.00 0.99 1.01 1.01 1.00 update-index_range=100
1.00 1.00 1.00 1.00 1.00 1.00 update-inlist_range=100
1.00 1.00 0.99 1.01 1.01 0.98 update-nonindex_range=100
1.00 0.99 1.01 1.02 1.00 1.00 update-one_range=100
0.99 1.00 1.00 1.01 1.01 0.99 update-zipf_range=100
1.00 1.06 1.05 1.02 1.04 1.08 write-only_range=10000
No comments:
Post a Comment