Revisiting perf regressions in Postgres, a larger server and sysbench, part 2

My previous post has results for sysbench from Postgres when the database fits in the buffer pool. In this post I have results for a configuration where the workload fits in the OS page cache but not the database buffer pool.

The benchmarks tries to answer two questions. The context is a server with fast IO (reading from the OS page cache).

• How does perf change from Postgres version 11 to version 15?
  
• What is the impact from compiler optimizations?

tl;dr

• The o3_native_lto build has the best performance as it did in the previous post. It provides ~4% more throughput.
• For perf changes from 11.19 to 15.2
• Point queries get about 5% more QPS in 15.2
• Range queries get about 3% more QPS in 15.2
• Writes get about 9% more QPS in 15.2
• There are outliers in all of the microbenchmark groups (point query, range query & writes)

Benchmark

See my previous post for more details.

The workload was configured to fit in the OS page cache but not in the 4G Postgres buffer pool. The config file, conf.diff.cx7_gcp_c2s60_4g, is here for Postgres 11, 12, 13, 14 and 15.

I use sysbench to run 42 microbenchmarks and each microbenchmark is put in one of three groups based on the dominant operation: point query, range query, writes.

Results for all versions

The result spreadsheet is here. See the pgall.4g tab. The chart doesn't show the full name for each benchmark. Consult the spreadsheet.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case is Postgres 11.19.

There are outliers for all of the microbenchmark groups. The bullet points list the relative throughput for version 15.2 versus 11.19. The microbenchmark names on the graphs below are cut off so refer to the spreadsheet:

• point
• points-covered-pk_range=100 - relative throughput is 1.56 and the big change is from version 13.7 to version 14.7. This does point queries on a covering PK index.
• range
• range-notcovered-pk_range=100 - relative throughput is 1.31 and the big change is from version 11.19 to version 12.14. This does range scans on a non-covering PK index.
• read-only_range=100 - relative throughput is 1.21 and the big change is from version 11.19 to version 12.14. This does range queries that scan 100 rows with aggregation.
• read-only_range=10 - relative throughput is 1.38 and the big change is from verson 11.19 to 12.14. Queries are like read-only_range=10 except they only scan 10 rows.
• writes
• update-index_range=100 - relative throughput is 1.20 and the improvements arrive in several versions (12, 14, 15). This does updates that require index maintenance.
• update-inlist_range=100 - relative throughput is 1.33 and the big improvement arrives is from version 11.19 to version 12.14.
• update-nonindex_range=100 - relative throughput is 1.17 and the improvements arrive in versions 12.14 and 14.7.

Summary statistics:

o3_native_lto	12.14	13.10	14.7	15.1	15.2
Point: avg	1.01	1.03	1.08	1.08	1.09
Point: median	1.01	1.03	1.05	1.05	1.05
Point: min	0.96	0.97	0.90	0.92	0.96
Point: max	1.03	1.09	1.53	1.52	1.56
Point: stddev	0.016	0.025	0.128	0.123	0.129
Range: avg	1.06	1.07	1.08	1.09	1.08
Range: median	1.01	1.02	1.03	1.03	1.03
Range: min	0.97	0.93	0.94	0.98	0.94
Range: max	1.35	1.38	1.37	1.40	1.38
Range: stddev	0.127	0.140	0.133	0.128	0.126
Write: avg	1.06	1.05	1.08	1.11	1.11
Write: median	1.02	1.03	1.10	1.10	1.09
Write: min	0.96	0.96	0.80	1.00	1.00
Write: max	1.43	1.28	1.29	1.33	1.33
Write: stddev	0.141	0.099	0.132	0.104	0.103

Results for Postgres 15.1

The result spreadsheet is here. See the pg151.r4g tab. The chart doesn't show the full name for each benchmark. Consult the spreadsheet.

The graphs use relative throughput which is throughput for me / throughput for base case. When the relative throughput is > 1 then my results are better than the base case. When it is 1.10 then my results are ~10% better than the base case. The base case is Postgres 11.19.

Summary statistics:

pg151_def	o2_nofp	o3	o3_native	o3_native_lto
Point: avg	0.99	1.00	1.02	1.04
Point: median	0.99	1.00	1.02	1.04
Point: min	0.97	0.99	0.98	1.02
Point: max	1.00	1.01	1.03	1.06
Point: stddev	0.007	0.005	0.013	0.009
Range: avg	0.98	1.00	1.03	1.06
Range: median	0.98	1.00	1.03	1.05
Range: min	0.96	1.00	1.01	1.03
Range: max	1.01	1.02	1.05	1.10
Range: stddev	0.014	0.006	0.012	0.026
Write: avg	1.00	1.01	1.01	1.03
Write: median	1.00	1.00	1.01	1.03
Write: min	0.98	0.99	1.00	1.01
Write: max	1.04	1.05	1.07	1.08
Write: stddev	0.018	0.019	0.020	0.020