The Insert Benchmark, MySQL 5.6, 5.7, 8.0 and a small server

This has results for MySQL vs the insert benchmark on a small server. Results from a big server are here.

The goals are to look for performance changes from MySQL 5.6 to 8.0 and to determine the impact of compiler optimizations. The context here is a cached workload (reads from cache, writes to storage) with low concurrency and a small server.

tl;dr

  • The rel_native_lto build has the best performance which is usually <= 4% better than the base case except for the l.x (create index) benchmark step.
  • QPS on the read+write benchmark steps (q100.1, q500.1, q1000.1) suffer from bug 109595. Fixing b109595 would increase QPS by ...
    • ~1.25X for the cached by InnoDB setups
    • (~2X, ~6X and ~6X) for q100.1, q500.1, q1000.1) for the cached by OS setups. To be clear, I mean that QPS for q500.1 and q1000.1 is about 1/6th of what it could be if this bug were fixed.
  • For the cached by InnoDB setup with one client (connection, thread) the throughput for 8.0.32 is between 56% and 63% of 5.6.51. Most of the perf loss is from new CPU overhead.

Benchmarks

An overview of the insert benchmark is here and here. The insert benchmark was run for a cached database with both 1 and 4 clients. For 1 client the benchmark used 1 table. For 4 clients the benchmark was first run with 4 tables (client per table) and then again with 1 table (all clients shared the table). The read+write steps (q100.1, q500.1, q1000.1) were run for 1800 seconds each.

The benchmark server is a Beelink SER 4700u described here with 8 AMD cores, 16G of RAM and 1T of NVMe SSD. The OS is Ubuntu 22.04 and the filesystem is XFS.

Benchmarks were repeated for two configurations:
  • cached by InnoDB - all data fits in the InnoDB buffer pool
  • cached by OS - all data fits in the OS page cache but not the InnoDB buffer pool. The buffer pool size is 1G and the database was ~10G at test end. 
The configuration files are here for:
The benchmark is a sequence of steps:

  • l.i0 - insert 20 million rows without secondary indexes
  • l.x - create 3 secondary indexes. I usually ignore results from this step.
  • l.i1 - insert another 20 million rows with the overhead of secondary index maintenance
  • q100.1 - do queries as fast as possible with 100 inserts/s/thread done in the background
  • q500.1 - do queries as fast as possible with 500 inserts/s/thread done in the background
  • q1000.1 - do queries as fast as possible with 1000 inserts/s/thread done in the background
Builds

I compiled MySQL from source with several build variations. Details are here. All builds are with upstream MySQL and gcc.

Reports

Performance summaries generated by shell scripts are below. A short guide to these results is here. There are three types of reports:
  • version 8.0.31 - results for MySQL 8.0.31 using different compiler optimizations
  • version 8.0 - results for MySQL 8.0.22, 8.0.28, 8.0.31 and sometimes 8.0.32
  • versions 5.6 to 8.0 - results for MySQL versions 5.6.51, 5.7.40, 8.0.22, 8.0.28, 8.0.28, 8.0.31 and 8.0.32
For each type of report there are two variations:
  • 1 client, 1 table - used 1 client & 1 table
  • 4 clients, 4 tables - used 4 clients & 4 tables with a client per table
  • 4 clients, 1 table - used 4 clients & 1 table with all clients sharing one table
Reports for cached by InnoDB:
Reports for cached by OS:
  • There has been a gradual reduction in performance in each release because there has been a gradual increase in CPU overhead. The following excludes the l.x (create index) benchmark step.
    • 1 client, 1 table
      • Cached by InnoDB
        • QPS for 8.0.32 is between 56% and 63% relative to 5.6.51
      • Cached by OS
        • QPS for 8.0.32 relative to 5.6.51 is (61%, 27%, 30%, 7%, 6%) for (l.i0, l.i1, q100.1, q500.1, q1000.1). See bug 109595.
    • 4 clients, 4 tables
      • Cached by InnoDB
        • QPS for 8.0.32 is between 60% and 67% relative to 5.6.51
      • Cached by OS
        • QPS for 8.0.32 relative to 5.6.51 is (61%, 19%, 1%, 2%, 15%) for (l.i0, l.i1, q100.1, q500.1, q1000.1). See bug 109595.
    • 4 clients, 1 table
      • Cached by InnoDB
        • QPS for 8.0.32 is between 62% and 75% relative to 5.6.51
      • Cached by OS
        • QPS for 8.0.32 relative to 5.6.51 is (60%, 18%, 2%, 2%, 9%) for (l.i0, l.i1, q100.1, q500.1, q1000.1). See bug 109595.










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