This has benchmark results for Postgres 17beta2 using sysbench and a medium server. By small, medium or large server I mean < 10 cores for small, 10 to 19 cores for medium, 20+ cores for large.
Recent results for Postgres 17beta1 are here:
- Insert Benchmark, IO-bound, large server
- Sysbench, cached, large server
- Sysbench, cached, small server
tl;dr
- 17beta2 looks good
- Write microbenchmarks are much faster in 17beta1 and 17beta2 vs 16.3
- Read microbenchmarks have similar performance between 16.3, 17beta1 and 17beta2
Builds, configuration and hardware
I compiled Postgres versions 16.3, 17beta1 and 17beta2 from source.
The server is a c2d-highcpu-32 instance type on GCP (c2d high-CPU) with 32 vCPU, 64G RAM and SMT disabled so there are 16 cores. It uses Ubuntu 22.04 and storage is ext4 (data=writeback) using SW RAID 0 over 2 locally attached NVMe devices.
The configuration file is here.
Benchmark
I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with two workloads:
- cached - database is cached by Postgres, 8 tables and 10M rows/table
- IO-bound - database is larger than memory, 8 tables and 200M rows/table
In both cases, there are 12 client threads, read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.
The command lines for my helper scripts were:
# cached -> 10M rows/table
bash r.sh 8 10000000 300 600 md0 1 1 12# IO-bound -> 200M rows/table
bash r.sh 8 200000000 300 600 md0 1 1 12
Results
For the results below I split the 42 microbenchmarks into 5 groups -- 2 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. The spreadsheet with all data is here. For each microbenchmark group there is a table with summary statistics.
The numbers in the spreadsheets are the relative QPS for Postgres 17beta1 and 17beta2 which are:
(QPS for 17beta1) / (QPS for 16.3)(QPS for 17beta2) / (QPS for 16.3)
When the relative QPS is > 1 then 17beta1 or 17beta2 are faster.
I use summary statistics per microbenchmark group rather than charts to save time. The numbers are the relative QPS. I focus on the median value per microbenchmark group
Cached
- Many of the write microbenchmarks are significantly faster in 17beta1 and 17beta2
- The min relative QPS in the range-1 group is 0.97 for 17beta1 and 0.93 for 17beta2 and that comes from the scan microbenchmark. I thought this was a regression but it might be variance from some of my test methods, Postgres and my test HW. I spent much time trying to reproduce this but mostly see variance.
IO-bound
- The hot-points result here shows a big improvement as it does. The hot-points workload fits in the Postgres buffer pool so the result here matches the result above.
- The min relative QPS in the range-1 group is 0.95 and that comes from the scan microbenchmark. See the comments above.
No comments:
Post a Comment