Postgres 16.0, sysbench and a small server

I recently shared results for Postgres 16rc1 vs sysbench on a small server. Now I have results for Postgres 16.0 and the news continues to be good.

tl;dr

  • insert QPS might have dropped by ~5%, otherwise there are no regressions for writes
  • for everything else QPS in Postgres 16.0 is within ~2% of 15.2, which is a great result

Builds

I compiled Postgres 15.2, 15.3, 15.4, 16 beta1, 16 beta2, 16 beta3, 16 rc1 and 16.0 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 used a1 config.

Results

A spreadsheet with the results is here. It has two sheets: one with absolute QPS for each version tested, the other with the relative QPS per version. The relative QPS is: (QPS for me) / (QPS for 15.2). With relative QPS it is easy to quantify improvements and regressions.

There are ~42 tests and each can be called a benchmark step or microbenchmark. I will call them benchmark steps. These are put into one of 5 groups based on the workload:

  • point query, part 1 - point queries that don't use the random-points benchmark step
  • point query, part 2 - point query variants that use the random-points benchmark step
  • range query, part 1 - range queries without aggregation
  • range query, part 2 - read-only variants of the original sysbench workload that does range queries with aggregation
  • writes - benchmark steps that do insert, update and delete

The y-axis starts at 0.8 rather than 0 to make it easier to see the changes.

  • point queries, part 1 - there are no regression. There is too much variance for the second and third benchmark steps from the left -- point-query.pre_range=100 and point-query_range=100. I have to explain the cause.
  • point queries, part 2 - there are no regressions
  • range queries, part 1 - QPS might have dropped by ~2% or there might be noise
  • range queries, part 2 - there are no regressions
  • writes - insert QPS might have dropped by ~5%, otherwise there are no regressions

Comments

Post a Comment

Popular posts from this blog

Fixing bug 109595 makes MySQL almost 4X faster on the Insert Benchmark

Image
MySQL 8.0.35 includes a fix for bug 109595  and with that fix the QPS is almost 4X larger on the read+write benchmark steps compared to MySQL 8.0.34. Thank you to MySQL for fixing this quickly. I reported the bug in January of 2023. I have been aware of the performance problem for years, but didn't spend time debugging it until this year. I assume this problem was limited to InnoDB because I did not see problems with MyRocks. The chart below shows relative QPS which is (QPS for MySQL 8.0.35) / (QPS for MySQL 8.0.34). When the value is larger than 1.0 then MySQL 8.0.35 is faster than 8.0.34. And below the relative QPS is almost 4.0 on the read+write benchmark steps: q100, q500, q1000. The Insert Benchmark is explained here .
Read more

Postgres versions 11, 12, 13, 14, 15, and 16 vs sysbench with a medium server

Image
This provides additional results for Postgres versions 11 through 16 vs Sysbench on a medium server. My previous post is here . The goal is to document how performance changes over time with a focus on CPU overheads. tl;dr Postgres avoids performance regressions from old to new versions. This is excellent. Postgres 16.0 is mostly faster than 11.21 on read-only benchmark steps Postgres 16.0 and 11.21 have similar throughput on read-write and write-only benchmark steps Builds I compiled Postgres 11.21, 12.16, 13.12, 14.9, 15.2, 15.3, 15.4 and 16.0. 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 a c2-standard-30 server from GCP with 15 cores, hyperthreads disabled, 120G of RAM, Ubuntu 22.04 and 1.5TB of NVMe SSD with XFS (SW RAID 0 over 4 local devices). The benchmark is run with 8 clients and 4 table with 2
Read more

Postgres vs MySQL: the impact of CPU overhead on performance

Image
This post documents how MySQL and Postgres performance has changed over time using sysbench. It compares QPS for Postgres 11.21 vs MySQL 5.6.21 and then Postgres 16.0 vs MySQL 8.0.34. The focus here is on CPU overhead and for that a workload that is low-concurrency, CPU-bound and cached is sufficient. However a full comparison would also include results for workloads with more concurrency and workloads that are IO-bound. The benchmark was explained in a series of posts - see  part 1 ,  part 2 ,  part 3 ,  part 4  and part 5  and the results here are from my small server. Usually I avoid direct comparisons between Postgres and MySQL but I am doing this because I care about the future of MySQL and the CPU regressions in it over time have become a problem. tl;dr Postgres 11.21 and MySQL 5.6.21 have similar QPS on most sysbench tests Postgres 16.0 gets much more QPS than MySQL 8.0.34 on most sysbench tests The problem for MySQL is new CPU overhead Results The spreadsheet with all of the nu
Read more