Revisiting perf regressions in Postgres, a larger server and sysbench

The previous results I shared for Postgres and sysbench on 30-core server are not to be trusted. I used huge pages and a too-big value for vm.nr_hugepages. This caused some odd behavior. So I reduced the Postgres buffer pool to 150G. vm.nr_hugepages to 85000 and repeated the benchmarks.

Refer to the previous post for more details. The summary is that I ran in-memory sysbench for many versions of Postgres on a c2-standard-60 server. 

The benchmarks tries to answer two questions:

• 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 ~5% more throughput.
• For perf changes from 11.19 to 15.2
• Point queries get about 3% more QPS in 15.2
• Range queries get about 8% more QPS in 15.2
• Writes get about 4% less QPS in 15.2
• The range query and write microbenchmarks have more variance. See the Results for all versions section for more details.

Benchmark

A description of how I run sysbench is here. The sysbench microbenchmarks were run for 20 clients, 600 seconds per microbenchmark using 4 tables with 50M rows per table. The test database was <= 64G and fits in memory.

The workload was configured to fit in the 150G database buffer pool. The config file, conf.diff.cx7a_gcp_c2s60, is here for Postgres 11, 12, 13, 14, 15.

The previous post explains the builds that I used where each build uses different compiler optimizations. Here I used all of the builds for Postgres. This has results for Postgres 11.19, 12.14, 13.10, 14.7, 15.1 and 15.2. For 15.1 I used all builds (def, o2_nofp, o3, o3_native, o3_native_lto). For other versions I only used the o3_native_lto build to save time.

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.redo 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.

The microbenchmarks for range queries and writes have more variance than for point queries based on the relative throughput per microbenchmark. 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:

• range queries
• range-covered-pk_range=100 - relative throughput is 1.26
• range-covered-si_range=100 - relative throughput is 1.27
• read-only.pre_range=10 - relative throughput is 1.14
• read-only_range=10 - relative throughput is 1.17
• scan_range=100 - relative throughput for is 1.12
• writes
• read-write_range=100 - relative throughput is 0.88
• update-linst_range=100 - relative throughput is 1.24
• update-one_range=100 - relative throughput is 0.80
• write-only_range=100 - relative throughput is 0.89

Summary statistics:

pg1119	12.14	13.10	14.7	15.1	15.2
Point: avg	1.01	1.01	1.02	1.03	1.03
Point: median	1.00	1.01	1.03	1.04	1.04
Point: min	0.98	0.97	0.96	0.98	0.99
Point: max	1.05	1.05	1.07	1.10	1.11
Point: stddev	0.017	0.017	0.031	0.033	0.033
Range: avg	1.03	1.04	1.05	1.08	1.08
Range: median	1.00	1.02	1.02	1.03	1.03
Range: min	0.95	0.90	0.99	1.00	1.00
Range: max	1.20	1.25	1.24	1.28	1.27
Range: stddev	0.075	0.094	0.083	0.094	0.093
Write: avg	0.92	0.92	0.97	0.97	0.96
Write: median	0.92	0.91	0.95	0.96	0.95
Write: min	0.70	0.86	0.79	0.80	0.77
Write: max	1.07	1.00	1.24	1.24	1.22
Write: stddev	0.101	0.045	0.124	0.121	0.119

Results for Postgres 15.1

The result spreadsheet is here. See the pg151.redo 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 the def build that uses -O2.

Summary statistics:

pg151_def	o2_nofp	o3	o3_native	o3_native_lto
Point: avg	1.00	1.01	1.02	1.05
Point: median	1.00	1.01	1.02	1.05
Point: min	0.98	1.00	1.01	1.03
Point: max	1.01	1.04	1.04	1.06
Point: stddev	0.008	0.010	0.007	0.008
Range: avg	1.00	1.02	1.03	1.07
Range: median	0.99	1.02	1.03	1.07
Range: min	0.99	1.00	1.00	1.02
Range: max	1.02	1.03	1.05	1.11
Range: stddev	0.010	0.010	0.015	0.030
Write: avg	1.00	1.02	1.01	1.03
Write: median	1.00	1.01	1.01	1.02
Write: min	0.96	0.98	0.99	1.00
Write: max	1.03	1.20	1.02	1.08
Write: stddev	0.021	0.064	0.011	0.021