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?
- 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)
See my previous post for more details.
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.
- 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-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.
- 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.