Postgres 17beta3 vs sysbench on a small server: looking good

This has benchmark results for Postgres 17beta3 using sysbench and a small server. By small, medium or large server I mean < 10 cores for small, 10 to 19 cores for medium, 20+ cores for large.

A recent result for Postgres 17beta3 on a medium server is here.

    tl;dr - 17beta3 looks great

    • There are no regressions
    • Throughput on write microbenchmarks is often ~5% to ~10% better than 16.x
    • Throughput on hot-points (read-only) is more than 2X faster in 17beta than 16.x
    Builds, configuration and hardware

    I compiled Postgres versions 10.23, 11.22, 12.20, 13.16, 14.13, 15.7, 15.8, 16.0, 16.1, 16.2, 16.3, 16.4, 17beta1, 17beta2 and 17beta3 from source.

    The server is named v3 or PN53 here and has 8 AMD cores with SMT disabled, 16 G of RAM and uses Ubuntu 22.04 and XFS with 1 m.2 device. I need to switch to ext-4 soon to match what I use elsewhere.

    The configuration files have the name conf.diff.cx10a_c8r32 and are in the pg* subdirectories 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 the database cached by Postgres with 1 table and 50M rows. There is 1 client thread (connection), read-heavy microbenchmarks run for 300 seconds and write-heavy run for 600 seconds.

    The command lines for my helper script was:
    bash r.sh 1 50000000 300 600 md0 1 1 1

    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. 

    Values from iostat and vmstat divided by QPS are here. This can help to explain why something is faster or slower because it shows how much HW is used per request.

    The numbers in the spreadsheets are the relative QPS as below. When the relative QPS is > 1 then $version is faster than Postgres 10.23.
    (QPS for $version) / (QPS for Postgres 10.23)

    Notes on the charts

    • the y-axis starts at 0.9 to make it easier to see differences
    • for the first graph, the y-axis truncates one outlier (hot-points) from Postgres 17beta where the relative QPS is greater than 2.0 for Postgres 17beta. That is a great improvement
    Point queries, part 1
    • The y-axis ends at 1.2 and truncates the (great) outliers for hot-points where it is ~2.2X faster in Postgres 17beta
    Point queries, part 2
    Range queries, part 1
    • While the scan benchmark has some variance that I don't yet understand the trend is positive starting in Postgres 13.x and continuing through 17beta3
    Range queries, part 2
    Writes
    • Many of these are ~5% to ~10% faster in Postgres 17beta3 vs 16.x

    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