Small Datum

Monday, September 18, 2023

Perf regressions in MySQL from 5.6.21 to 8.0.34 using the Insert Benchmark and a small server

This post has results from the Insert Benchmark for some MySQL 5.6 releases, all 5.7 releases and all 8.0 releases. I previously shared results like this using sysbench.

Disclaimer - these results are from a low concurrency workload (1 to 3 concurrent connections). Results on a bigger server with more concurrency won't look as bad.

tl;dr

From MySQL 5.6.21 to 8.0.34

Throughput for 4 of 6 benchmark steps drops by ~50%
Throughput for one of the benchmark steps drops by ~20%
Throughput for the remaining benchmark step increases by ~20%
The problem is new CPU overhead

With MySQL 5.6

It mostly avoids perf regressions from 5.6.21 to 5.6.51 as throughput drops by ~5%

With MySQL 5.7

There is a large perf regression from the last 5.6 release to the first 5.7 release
It mostly avoids regressions from 5.7.10 to 5.7.43 as throughput drops by ~5%

With MySQL 8.0

There is a large perf regression from the last 5.7 release to the first 8.0 release
There are perf regressions from 8.0.13 to 8.0.34 as throughput drops by up to 20%

In summary, the largest regressions occur across major versions. In theory that is good news because when regressions are not spread across many releases they are easier to fix. But I am not sure that theory holds when the adjust releases span major releases. The largest regressions occur between these adjacent releases:

From 5.6.51 to 5.7.10
From 5.7.43 to 8.0.13

Builds

It isn't easy to build older code on newer systems, compilers, etc. Notes on that are here for 5.6, for 5.6 and 5.7, for 5.7 and for 8.0. A note on using cmake is here. The rel builds were used -- everything was compiled using CMAKE_BUILD_TYPE=Release.

Tests were done for:

5.6 - 5.6.21, 5.6.31, 5.6.41, 5.6.51
5.7 - all point releases from 5.7.10 to 5.7.43
8.0 - all point releases from 8.0.13 to 8.0.34

I used the cy10a_bee config and it is here for 5.6, 5.7 and 8.0. For 8.0 releases older than 8.0.19 I changed innodb_idle_flush_pct=1 to loose_innodb_idle_flush_pct=1.

Benchmark

The Insert Benchmark was run in one setup - the database was cached by InnoDB.

The 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. The benchmark is a sequence of steps.

l.i0

insert 20 million rows per table

create 3 secondary indexes. I usually ignore performance from this step.

l.i1

insert and delete another 50 million rows per table with secondary index maintenance. The number of rows/table at the end of the benchmark step matches the number at the start with inserts done to the table head and the deletes done from the tail.

q100, q500, q1000

do queries as fast as possible with 100, 500 and 1000 inserts/s/client and the same rate for deletes/s done in the background. Run for 1200 seconds.

Results

The Summary sections linked below have tables for absolute and relative throughput. Absolute throughput is just the QPS per version. The relative throughput is (QPS for my version / QPS for base version). The base version is 5.6.21 for the 5.6-only report, 5.7.10 for the 5.7-only report, 8.0.13 for the 8.0-only report and 5.6.21 for the report that compares 5.6, 5.7 and 8.0.

Reports are here for

Some MySQL 5.6 releases

Average throughput drops by ~5% from 5.6.21 to 5.6.51 for all benchmark steps. See the relative throughput table in the Summary.
CPU overhead doesn't change for write-heavy benchmark steps (l.i0, l.x, l.i1) and increases by ~5% for read-write steps (q100, q500, q1000). See the cpupq column (CPU/operation) in the Metrics tables.

All MySQL 5.7 releases

Average throughput drops by 5% to 7% for all benchmark steps. See the relative throughput table in the Summary.
CPU overhead doesn't change much for write-heavy benchmark steps (l.i0, l.x, l.i1) and increases by ~6% for read-write steps (q100, q500, q1000). See the cpupq column (CPU/operation) in the Metrics tables.

All MySQL 8.0 releases

Average throughput drops by 17% to 20% for some benchmark steps (l.i0, q100, q500, q1000) and increases by 1% for others (l.x, l.i1). See the relative throughput table in the Summary.
It is hard to explain the results for the write-heavy benchmark steps in terms of the iostat and vmstat results in the Metrics tables. But for CPU overhead increases by 17% to 19% for the read-heavy benchmark steps. See the cpupq column (CPU/operation) in the Metrics tables.

Comparing MySQL 5.6, 5.7 and 8.0

Average throughput drops almost in half for some benchmark steps (l.i0, q100, q500, q1000), drops by ~17% for l.i1 and increases by 21% for l.x. See the relative throughput table in the Summary.
CPU overhead increases explain the performance regressions and for the read-write benchmark steps the CPU/operation overhead grows by 1.9X. See the cpupq column (CPU/operation) in the Metrics tables.

Postgres 16.0 vs the Insert Benchmark on a medium server

This has results for Postgres 16.0 and rc1 vs the Insert Benchmark on a medium server. Previous posts have results for this benchmark with Postgres 16.0 on a small server and with Postgres 16 beta on a medium server.

tl;dr

Postgres 16.0 looks good
All versions of Postgres (old and new) have too much variance on this benchmark with the IO-bound setups. See rant here. I like Postgres but not so much for write-heavy workloads.
All versions of Postgres were unable to sustain the target insert rate (8000/s) for the IO-bound setup during q1000.
All versions of Postgres suffer from fairness during l.i1 -- some clients finish much sooner than others and in the IO-bound setups the slowest clients take ~3X longer than the fastest ones.

Builds

I compiled Postgres 15.2, 15.3, 15.4, 16 beta1, 16 beta2, 16 beta3, 16rc1 and 16.0 from source. The builds are named o3_native_lto which is shorthand for using: -O3 -march=native -mtune=native -flto.

Benchmark

The insert benchmark was run in two setups.

cached by Postgres - all tables are cached by Postgres
IO-bound - the database is larger than memory

The benchmark used a c2-standard-30 server from GCP with Ubuntu 22.04, 15 cores, hyperthreads disabled, 120G of RAM and 1.5T of storage from RAID 0 over 4 local NVMe devices with XFS.

The benchmark is run with 8 clients and 8 tables. The benchmark is a sequence of steps.

l.i0

insert X million rows per table where X is 20 for cached and 500 for IO-bound

create 3 secondary indexes. I usually ignore performance from this step.

l.i1

insert and delete another X million rows per table with secondary index maintenance where X is 300 for Cached and 60 for IO-bound. The number of rows/table at the end of the benchmark step matches the number at the start with inserts done to the table head and the deletes done from the tail.

q100, q500, q1000

do queries as fast as possible with 100, 500 and 1000 inserts/s/client and the same rate for deletes/s done in the background. Run for 7200 seconds.

Configurations

I used the a27 and a28 configurations. From previous work, a27 was good for Cached by Postgres and a28 was good for IO-bound. The diff between them is:

wal_compression is lz4 for a27 and off for a28
autovacuum_vacuum_cost_limit is 2000 for a27 and 4000 for a28
max_wal_size is 70G for a27 and 32GB for a28

Results

Reports are here for Cached by Postgres (a27 config, a28 config) and IO-bound (a27 config, a28 config).

The results for average throughput are interesting and confusing. The tables linked below use absolute and relative throughput where relative throughput is (QPS for my version / QPS for base version) and the base version is Postgres 15.2.

Cached, a27 config (see here)

For l.i1

The average throughput gradually declines by ~7% from 15.2 to 16.0. I don't know whether that is a regression or just noise (see here).
From HW metrics the CPU overhead (cpupq is CPU/operation) does not change.
From the charts with per-second insert rates there is much variance for all versions (see for 15.2 and 16.0) and the insert rate frequently changes from ~10k/s to ~40k/s.
All versions suffer from fairness. From the 8 benchmark clients the average time is ~10,000 seconds but the slowest client takes ~1000 more seconds than the fastest client.

For q100, q500 and q1000 the average throughput is the same for all versions.

Cached, a28 config (see here)

For l.i1

The average throughput is the same from 15.2 to 16.0.
From the charts with per-second insert rates there is much variance for all versions (see for 15.2 and 16.0) and the insert rate frequently changes from ~10k/s to ~40k/s.
All versions suffer from fairness. From the 8 benchmark clients the average time is ~10,000 seconds but the slowest client takes ~1000 more seconds than the fastest client.

For q100, q500 and q1000 the average throughput is the same for all versions

IO-bound, a27 config (see here)

For l.i1

I am wary of interpreting results because there is too much variance.
The per-second insert rate charts are similar for 15.2 and 16.0 -- there are spikes.
All versions suffer from fairness. From the 8 benchmark clients the times to finish the l.i1 benchmark step in seconds is: 17866, 19057, 34004, 35698, 36535, 38123, 44844, 45365. The slowest client takes almost 3X longer than the fastest.

For q100 and q500 results are probably similar across versions but there is noise.
For q1000 none of the versions were able to sustain the target insert rate (1000/s/client for a total of 8000/s). See the third table in the Summary section.

IO-bound, a28 config (see here)

For l.i1

I am wary of interpreting results because there is too much variance.
The per-second insert rate charts are similar for 15.2 and 16.0 - there are spikes.
All versions suffer from fairness. From the 8 benchmark clients the times to finish the l.i1 benchmark step in seconds is: 18387, 25221, 25764, 26830, 29587, 30214, 35562, 37566. The slowest client takes almost 2X longer than the fastest.

For q100 and q500 results are probably similar across versions but there is noise.
For q1000 none of the versions were able to sustain the target insert rate (1000/s/client for a total of 8000/s). See the third table in the Summary section.

Postgres 16.0 vs the Insert Benchmark on a small server

This has results for Postgres 16.0 and rc1 vs the Insert Benchmark on a small server. My previous post had results for beta1, beta2 and beta3.

tl;dr

Postgres 16.0 looks good
All versions of Postgres (old and new) have too much variance on this benchmark with the IO-bound setups. See rant below.

Rant

There is too much variance with Postgres vs the Insert Benchmark in IO-bound setups. This isn't a new issue, it occurs for all Postgres versions I have tested -- from version 10 or 11 to 16. I spent months testing different configuration options to see if I could tune this problem away. While better tuning helped it does not fix the problem. I think OtterTune said it best and while each new Postgres version has incremental improvements to vacuum I don't think it will ever be fixed incrementally.

I hope that something significant is done because while Postgres is a great DBMS I would be reluctant to use it for a write-intensive workload and it risks falling far behind the closed-source vendor forks.

So I focus more on the Cached by Postgres setup and less on the IO-bound setup. The Cached by Postgres setup is great when searching for CPU regressions and I do not see any in Postgres 16.0.

Builds

Benchmark

The Insert Benchmark was run in two setups.

cached by Postgres - all tables are cached by Postgres
IO-bound - the database is larger than memory

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. The benchmark is a sequence of steps.

l.i0

insert X million rows per table where X is 20 for cached and 800 for IO-bound

create 3 secondary indexes. I usually ignore performance from this step.

l.i1

insert and delete another X million rows per table with secondary index maintenance where X is 300 for Cached and 30 for IO-bound. The number of rows/table at the end of the benchmark step matches the number at the start with inserts done to the table head and the deletes done from the tail.

q100, q500, q1000

do queries as fast as possible with 100, 500 and 1000 inserts/s/client and the same rate for deletes/s done in the background. Run for 7200 seconds.

Configurations

I used the a1 and a2 configurations. The difference between them is that a1 has wal_compression set to lz4 while a2 has it set to off.

Results

Reports are here for Cached (a1 config, a2 config) and IO-bound (a1 config, a2 config).

Cached, a1 config (see here)

From the summary tables with relative throughput I see that 16.0 does better on the l.i1 benchmark step and then worse on the q100 and q500 steps that follow -- relative throughput values are 1.06, 0.91 and 0.94. Perhaps 16.0 is able to process writes faster during l.i1 but then that means the benchmark steps that follow (q100, q500) inherit more MVCC GC debt. I will revisit this.
From the HW metrics the CPU overhead (cpupq is CPU/operation) with version16.0 is less in l.i1 but more in q100 and q500 vs earlier versions. This explains the throughput differences. I assume the increases in q100 and q500 are from doing more MVCC GC (because l.i1 was faster and given the cpupq in q1000 returns to normal, perhaps GC debt was repaid by then).
During l.i1 all versions tested have much variance. The per-second insert rates regularly jump from 10k/s and 34k/s.
The max insert response time graphs have an interesting pattern for q100 and q500 which is less frequent in q1000. I assume that q100 and q500 have more MVCC GC debt to repay after l.i1 but that debt is gone by q1000.
For q100 the per-second QPS charts have less noise in 16.0 than in 15.2. This is somewhat true in q500 (see 16.0 and 15.2) but by q1000 they are similar (see 16.0 and 15.2).

Cached, a2 config (see here)

The comment above for the a1 config might apply here give that the relative throughput is 1.05 for l.i1 and 0.97 for q100. See the summary tables.
From the HW metrics results are similar to the a1 config except the MVCC GC debt was repaid by the end of q100.
During l.i1 all versions tested have much variance. The per-second insert rates regularly jump from 10k/s and 34k/s.

IO-bound, a1 config (see here)

I am reluctant to characterize these because there is too much variance -- especially during l.i1 (which suffers from vacuum-induced variance) and q100 (follows l.i1, inherits MVCC GC debt).
There is much variance for insert rates measured at 1-second intervals. Results for 15.2 show a gradual decline to ~500/s, then a sudden jump to 4000/s followed by another decline to ~500/s. The gradual declines are expected, the sudden jumps are not. This benchmark step ran for ~20,000s and I'd rather not run it for days to hope that the variance fades. Results for 16.0 also have sudden jumps but with 16.0 the rates decline to ~400/s which is worse than what 15.2 does.

IO-bound, a2 config (see here)

I am reluctant to characterize these because there is too much variance -- especially during l.i1 (which suffers from vacuum-induced variance) and q100 (follows l.i1, inherits MVCC GC debt).
With the l.i1 benchmark step the gradual fade, sudden jump pattern is similar to the results above for the a1 config (see 16.0 and 15.2).

Tuesday, September 12, 2023

Perf regressions in MySQL from 5.6.21 to 8.0.34 using sysbench and a small server

This has results for sysbench vs upstream MySQL on a small server. I have results for every release in 5.7 and 8.0 and some releases in 5.6. The goal is to document where things get faster or slower over time for a low-concurrency and CPU-bound workload. The focus is on CPU regressions.

My results here aren't universal.

Things won't look the same with an IO-bound workload. If nothing else that will make many of the CPU regressions less significant.
Things won't look the same with a workload that has more concurrency. While MySQL tends to get slower over time from more CPU overhead it also gets faster over time on concurrent workloads from improvements to synchronization code. Results from a few months ago on a larger server are here and the regressions are much smaller.
Things won't look the same with a workload that has complex queries. Most of the queries used by sysbench are simple and short running. This amplifies the impact of perf regressions in parse, semantic analysis and query optimization.

tl;dr

Upstream MySQL would benefit from changepoint detection.
MySQL 8.0 is the worst for perf regressions, while 5.7 and 5.6 are better at avoiding them. In theory this is good news because newer regressions are easier to fix than older ones.
For most of the benchmark steps MySQL 8.0.34 gets between 20% and 50% less QPS than 5.6.21
There were large regressions for point query benchmark steps in 8.0.2x, Fortunately bug 102037 has been fixed in 8.0.3x (I found that bug via past usage of sysbench).

What about Postgres?

On the same setup the regressions from Postgres 11 to Postgres 16 are small while the regressions here are big.
On a medium server they are also small.

Builds

Tests were done for:

5.6 - 5.6.21, 5.6.31, 5.6.41, 5.6.51
5.7 - all point releases from 5.7.10 to 5.7.43
8.0 - all point releases from 8.0.13 to 8.0.34

I used the cy10a_bee config and it is here for 5.6, 5.7 and 8.0. For 8.0 releases older than 8.0.19 I changed innodb_idle_flush_pct=1 to loose_innodb_idle_flush_pct=1.

Benchmarks

I used sysbench and my usage is explained here. Tests were run on a small server I have at home (see here). The test tables are cached by InnoDB.

Results

For the results below I split the benchmark steps into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query benchmark steps part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. Unfortunately, I included the full scan benchmark step (scan_range=100) in part 2 but it doesn't do aggregation. I struggled with the presentation because there are a large number of results per chart in the sections that test all releases from 5.7 and 8.0. The spreadsheet with all data and charts is here and is easier to read.

All of the charts have relative throughput on the y-axis where that is (QPS for $me) / (QPS for $base), $me is a version (for example 5.7.24) and $base is the base version. The base version is the oldest version being compared and the goal is to see whether QPS is better or worse over time.

The legend on the RHS of each chart truncates the names I use for the benchmark steps and I don't know how to fix that other than sharing the link to the Google Sheet I used.

From 5.6.21 to 8.0.34

This section uses 5.6.21 as the base version and then compares that with 5.6.51, 5.7.10, 5.7.28, 5.7.43, 8.0.13, 8.0.23 and 8.0.34 to show how performance has changed from oldest tested (5.6.21) to newest tested (8.0.34).

For point queries, 8.0.3x gets 20% to 35% less QPS vs 5.6.21. There were larger regressions in 8.0.2x but most were fixed in 8.0.3x.
For range queries that don't do aggregation (part 1), 8.0.3x gets 30% to 40% less QPS vs 5.6.21. For range queries that do aggregation, 8.0.3x gets 0% to 35% less QPS vs 5.6.21. The difference depends on the length of the range scan (shorter scan == larger regression). Also, full scan (scan_range=100) has a significant regression.
For most writes, 8.0.3x gets 25% to 50% less QPS vs 5.6.21

It isn't easy to see but some of the lines are on top of each other.

MySQL 8.0: all point releases

This section uses 8.0.13 as the base version and compares every other 8.0 release with it.

For point queries, 8.0.34 gets 5% to 12% less QPS than 8.0.13. For some benchmark steps large regressions in 8.0.2x were fixed in 8.0.3x - that might have been bug 102037, found by my past usage of sysbench and fixed in 8.0.31. For the point-query* benchmark steps there are slow but steady regressions from 8.0.2x to 8.0.3x.
For range queries, 8.0.34 gets 5% to 20% less QPS than 8.0.13. There are slow but steady regressions from 8.0.2x to 8.0.3x and the impact is becoming significant. Full scan (scan_range=100) has the largest regression.
For writes, 8.0.34 does better than 8.0.13 on the update-index benchmark step but otherwise gets 15% to 25% less QPS than 8.0.13. The regressions are slow but steady.

MySQL 5.7: all point releases

This section uses 5.7.10 as the base version and compares every other 5.7 release with it.

For point queries, 5.7.43 gets 2% to 6% less QPS than 5.7.10
For range queries that don't do aggreation (part 1), 5.7.43 gets 5% to 12% less QPS than 5.7.10. For range queries that do aggregation, 5.7.43 gets about 5% less QPS than 5.7.10. Full scan is 15% slower in 5.7.43 vs 5.7.10.
For writes, 5.7.43 gets more QPS than 5.7.10 for update-index and update-inlist. Otherwise it gets between 3% and 8% less QPS.

MySQL 5.6: some point releases

This section uses 5.6.21 as the base version and compares 5.6.31, 5.6.41 and 5.6.51 with it.

For point queries, 5.6.51 and 5.6.21 have similar results.
For range queries, excluding full scan, 5.6.51 gets 2% to 4% less QPS than 5.6.21. For full scan (scan_range=100) it gets 16% less QPS.
For writes, 5.6.51 gets from 5% less to 2% more QPS than 5.6.21 except for update-list where it gets 20% more.

Monday, September 11, 2023

Checking jemalloc versions for RSS regressions with MyRocks and the Insert Benchmark

I previously shared a blog post with results for jemalloc, tcmalloc and glibc malloc vs MyRocks and the Insert Benchmark. The summary was to use jemalloc or tcmalloc with MyRocks, or anything that uses RocksDB, because they are much better than glibc malloc at reducing the peak RSS.

Today I share results for the same benchmark using jemalloc versions from 4.0.4 to 5.3.0.

tl;dr

There was a big regression WRT peak RSS in jemalloc 4.4.0 and 4.5.0
It was mostly fixed in 5.0.1 and 5.1.0
It was fixed more in 5.2.0
It was fully fixed in 5.3.0

Benchmarks

My previous post explains the benchmark and HW. New details here is that I compiled many versions of jemalloc from source -- 4.0.4, 4.1.1, 4.2.1, 4.3.1, 4.4.0, 4.5.0, 5.0.1, 5.1.0, 5.2.0, 5.3.0 -- and repeated the benchmark for each. This was done twice using different sizes for the RocksDB block cache: 40G and 60G. And then I also repeated things twice -- once using the (older) LRU block cache, once using the (newer) Hyper Clock cache.

I made no attempt to tune or configure jemalloc. Perhaps I will learn whether that was a big deal but I already have more than enough tuning to manage in the DBMS and I prefer to not tune the allocator.

Results

All numbers are here - see lines 16 to 67.

The summary is:

There was a big regression WRT peak RSS in jemalloc 4.4.0 and 4.5.0
It was mostly fixed in 5.0.1 and 5.1.0
It was fixed more in 5.2.0
It was fully fixed in 5.3.0

Wednesday, September 6, 2023

Chasing a performance regression in MyRocks vs the Insert Benchmark

I found a few performance regressions with the Insert Benchmark and MyRocks on a large server and this explain the start of my search to for the root causes. The regressions are described in an earlier blog post (see the summaries here and here).

Update - the builds I used were bad so the results here are bogus. I fixed the builds, repeated the tests and share results here. There are no regressions and the initial load of the benchmark is ~10% faster in modern MyRocks.

The mystery

Note that l.i1, q100, q500, q1000 are benchmark steps explained here.

With MyRocks from FB MySQL 5.6.35

Throughput drops by ~5% for l.i1 and ~5% for q100, q500, q1000 between fbmy5635_202203072101 and fbmy5635_202205192101. The former release is from 2022/03/07 with RocksDB 6.28.2 and the latter is from 2022/05/19 with RocksDB 7.2.2.
After fbmy5635_202205192101.cy9c5_u the throughput for the read-write benchmark steps (q100, q500, q1000) gradually declines by another ~5%
Throughput drops by ~5% for l.i1 between fbmy5635_202210112144 and fbmy5635_202302162102. The former release is from 2022/10/11 with RocksDB 7.3.1 and the latter is from 2023/02/16 with RocksDB 7.10.0

I will start with problem 3 -- the l.i1 regression between fbmy5635_202210112144 and fbmy5635_202210112144.

This is an interesting problem

I assume the problem is more likely from changes to RocksDB than from changes to MyRocks 5.6.35 because RocksDB source gets more changes.
The pure-RocksDB benchmarks might not detect these regressions, but I will revisit that.
Running the Insert Benchmark on a small and medium server does not detect this regression. I have only seen it this bad on a large server and the large server not only has more cores but has 2 sockets.

Builds

First there is the base build:

fbmy5635_rel_202210112144 - from 20221011 at git hash (c691c7160 MySQL, 8e0f4952 RocksDB), RocksDB 7.3.1

Then there are variants that use the same MyRocks source as the base but with a more recent version of RocksDB:

fbmy5635_20221011_732 - base with RocksDB 7.3.2
fbmy5635_20221011_743 - base with RocksDB 7.4.3
fbmy5635_20221011_745 - base with RocksDB 7.4.5
fbmy5635_20221011_750 - base with RocksDB 7.5.0
fbmy5635_20221011_751 - base with RocksDB 7.5.1
fbmy5635_20221011_752 - base with RocksDB 7.5.2
fbmy5635_20221011_753 - base with RocksDB 7.5.3
fbmy5635_20221011_754 - base with RocksDB 7.5.4
fbmy5635_20221011_760 - base with RocksDB 7.6.0
fbmy5635_20221011_778 - base with RocksDB 7.7.8
fbmy5635_20221011_783 - base with RocksDB 7.8.3
fbmy5635_20221011_793 - base with RocksDB 7.9.3
fbmy5635_20221011_7102 - base with RocksDB 7.10.2

Benchmark

The insert benchmark was run in one setup -- cached by RocksDB and all tables fit in the RocksDB block cache. The server has 80 HW threads, 40 cores, 256G of RAM and fast NVMe storage with XFS. The configuration files (my.cnf) are here: base and c5. The difference between them is that c5 adds rocksdb_max_subcompactions=4.

The benchmark is run with 24 clients, 24 tables and a client per table. The benchmark is a sequence of steps.

l.i0

insert X million rows across all tables without secondary indexes where X is 20 for cached and 500 for IO-bound

create 3 secondary indexes. I usually ignore performance from this step.

l.i1

insert and delete another 50 million rows per table with secondary index maintenance. The number of rows/table at the end of the benchmark step matches the number at the start with inserts done to the table head and the deletes done from the tail.

q100, q500, q1000

do queries as fast as possible with 100, 500 and 1000 inserts/s/client and the same rate for deletes/s done in the background. Run for 7200 seconds.

Results

Performance reports are here for Cached by RocksDB (base config and c5 config). The regressions are easy to see in the second table in the Summary section (for base config and c5 config).

For l.i1 the regressions occur at:

From RocksDB 7.4.5 to 7.5.0 (fbmy5635_20221011_745 to fbmy5635_20221011_750) the relative throughput drops from 1.00 to 0.93 for the base config and from 1.00 to 0.94 for the c5 config.
There is a transient drop from 7.5.0 to 7.5.4 for the base config. I ignore that for now.
From RocksDB 7.8.3 to 7.9.3 (fbmy5635_20221011_783 to fbmy5635_20221011_793) the relative throughput drops from 0.93 to 0.90 for the base config and 0.95 to 0.90 for the c5 config.

What comes next?

For the regression between 7.4.5 and 7.5.0 I will look at the diffs added to 7.5.0. There are no releases in between them. I might do a custom build that gets some of the 7.5.0 diffs. I checked the RocksDB LOG and confirmed the RocksDB 7.5.0 build is at git hash 7506c1a4ca. I will use a build from b283f041f58de in the 7.5.fb branch which has a change that is also in 7.4.5 and then do move forward to test more diffs in 7.5.fb.
For the regression between 7.8.3 and 7.9.3 I will repeat tests for 7.9.0, 7.9.1 and 7.9.2
Eventually I will look at flamegraphs. Perhaps differential flamegraphs will be useful if the code hasn't changed too much.
Struggle to remember whether I previously reported this as a perf issue and then forgot

Saturday, September 2, 2023

Postgres 16, sysbench and a small server

This post has results for Postgres 15 and 16 versus sysbench on a small server. I am searching for performance regressions. All tests use 1 database connection as my focus here is on CPU regressions. I will repeat this on larger hardware with more concurrency to search for problems with mutexes and RW-locks.

tl;dr

I did not find any significant CPU regressions. Throughput in PG 16rc1 is similar to 15.2. There might be small regressions (1% or 2%) but I won't claim my current setup is able to detect them.
From past work I know that Postgres has been great at avoiding regressions over time.

Builds

I compiled Postgres 15.2, 15.3, 15.4, 16 beta1, 16 beta2, 16 beta3 and 16 rc1 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 was repeated using the a1 and a2 configs. They differ in the value for wal_compression: a1 uses lz4 and a2 uses off.

Results

The results below use relative throughput which is: (QPS for $version / QPS for version 15.2). A value larger than 1.0 means that $version gets more QPS than 15.2. I will wave my hands and claim a result greater than 1.05 or less than 0.95 is significant. There are few such numbers in the results below.

Results with the a1 config

Relative to: pg152_o3_native_lto.x9a1_bee
col-1 : pg153_o3_native_lto.x9a1_bee
col-2 : pg154_o3_native_lto.x9a1_bee
col-3 : pg16beta1_o3_native_lto.x9a1_bee
col-4 : pg16beta2_o3_native_lto.x9a1_bee
col-5 : pg16beta3_o3_native_lto.x9a1_bee
col-6 : pg16rc1_o3_native_lto.x9a1_bee

col-1 col-2 col-3 col-4 col-5 col-6
0.99 1.04 1.00 1.00 1.03 1.02 hot-points_range=100
0.95 0.85 0.95 0.98 0.86 1.00 point-query.pre_range=100
1.17 1.10 1.01 1.10 1.00 1.09 point-query_range=100
0.99 1.00 1.00 1.01 1.01 1.02 points-covered-pk.pre_range=100
1.00 1.00 1.00 1.01 1.01 1.01 points-covered-pk_range=100
0.99 1.00 1.00 1.01 1.00 1.01 points-covered-si.pre_range=100
1.00 1.00 1.00 1.02 1.01 1.02 points-covered-si_range=100
0.99 1.00 1.00 1.01 1.00 1.01 points-notcovered-pk.pre_range=100
0.99 1.00 1.01 1.01 1.01 1.01 points-notcovered-pk_range=100
0.99 0.99 1.00 1.01 1.00 1.01 points-notcovered-si.pre_range=100
0.99 1.00 1.01 1.01 1.01 1.01 points-notcovered-si_range=100
0.99 1.00 1.01 0.99 1.01 1.01 random-points.pre_range=1000
1.00 1.00 0.99 0.99 1.01 1.01 random-points.pre_range=100
0.99 1.00 1.01 1.00 0.99 1.00 random-points.pre_range=10
0.99 1.00 1.01 1.00 1.01 1.00 random-points_range=1000
0.99 1.00 1.00 1.00 1.00 1.00 random-points_range=100
0.99 1.00 1.01 1.00 0.99 1.00 random-points_range=10
1.00 1.00 0.98 0.98 0.95 0.99 range-covered-pk.pre_range=100
1.02 1.01 0.99 1.00 0.97 0.99 range-covered-pk_range=100
1.01 1.00 0.99 1.00 0.97 0.98 range-covered-si.pre_range=100
1.01 1.00 0.99 1.00 0.96 0.98 range-covered-si_range=100
1.01 1.00 0.98 0.99 0.97 0.99 range-notcovered-pk.pre_range=100
1.01 1.00 0.99 1.00 0.99 1.00 range-notcovered-pk_range=100
1.00 0.99 0.98 0.98 0.97 0.98 range-notcovered-si.pre_range=100
1.00 0.99 0.99 0.99 0.98 0.99 range-notcovered-si_range=100
1.00 1.07 1.56 1.09 1.07 1.05 read-only.pre_range=10000
1.00 1.02 1.02 1.03 1.02 1.01 read-only.pre_range=100
1.04 1.00 1.01 1.06 0.98 1.05 read-only.pre_range=10
1.01 1.06 1.43 1.08 1.07 1.06 read-only_range=10000
1.00 1.02 1.02 1.03 1.02 1.01 read-only_range=100
0.97 0.99 0.99 0.96 0.97 0.95 read-only_range=10
0.98 0.97 0.95 0.96 0.97 0.97 scan_range=100
0.95 0.95 1.00 0.95 0.97 0.99 delete_range=100
1.02 1.01 1.00 1.00 0.97 1.01 insert_range=100
1.00 1.01 1.01 1.02 1.02 1.01 read-write_range=100
0.98 0.99 0.99 1.00 0.98 0.97 read-write_range=10
0.99 0.99 0.98 1.00 0.98 0.99 update-index_range=100
0.99 0.99 1.00 1.00 1.00 1.00 update-inlist_range=100
0.99 0.99 0.99 1.01 1.00 0.99 update-nonindex_range=100
1.00 1.00 0.97 1.00 0.99 0.98 update-one_range=100
0.98 0.99 0.99 1.00 0.98 0.99 update-zipf_range=100
1.01 1.03 1.00 1.03 0.97 0.97 write-only_range=10000

Results with the a2 config

Relative to: pg152_o3_native_lto.x9a2_bee
col-1 : pg153_o3_native_lto.x9a2_bee
col-2 : pg154_o3_native_lto.x9a2_bee
col-3 : pg16beta1_o3_native_lto.x9a2_bee
col-4 : pg16beta2_o3_native_lto.x9a2_bee
col-5 : pg16beta3_o3_native_lto.x9a2_bee
col-6 : pg16rc1_o3_native_lto.x9a2_bee

col-1 col-2 col-3 col-4 col-5 col-6
0.97 0.96 0.96 0.96 1.00 0.96 hot-points_range=100
1.06 1.04 0.94 1.18 1.05 1.08 point-query.pre_range=100
1.00 1.13 1.05 1.13 1.04 1.07 point-query_range=100
1.00 0.99 1.01 1.01 1.01 1.01 points-covered-pk.pre_range=100
1.00 1.00 1.01 1.00 1.00 1.01 points-covered-pk_range=100
1.00 1.00 1.00 1.00 1.01 1.01 points-covered-si.pre_range=100
1.00 0.99 1.01 1.00 1.00 1.01 points-covered-si_range=100
1.00 1.00 1.00 1.00 1.00 1.00 points-notcovered-pk.pre_range=100
0.99 0.99 1.00 1.00 1.00 1.00 points-notcovered-pk_range=100
0.98 0.99 1.00 1.01 1.00 1.00 points-notcovered-si.pre_range=100
0.98 0.99 1.00 1.00 1.00 1.00 points-notcovered-si_range=100
1.01 0.99 1.01 1.01 1.01 1.01 random-points.pre_range=1000
1.00 1.00 0.99 0.99 1.01 1.01 random-points.pre_range=100
0.98 0.99 1.00 1.00 0.99 1.00 random-points.pre_range=10
1.00 1.01 1.01 1.01 1.01 1.01 random-points_range=1000
1.00 1.00 1.00 1.00 1.01 1.00 random-points_range=100
0.98 1.00 1.01 1.00 0.99 1.00 random-points_range=10
0.99 1.00 0.99 0.97 0.97 0.99 range-covered-pk.pre_range=100
0.99 1.00 0.98 0.98 0.97 0.98 range-covered-pk_range=100
1.00 1.00 0.99 0.99 0.98 0.99 range-covered-si.pre_range=100
1.01 1.01 1.00 1.00 0.98 0.99 range-covered-si_range=100
0.99 1.00 0.98 0.98 0.96 0.98 range-notcovered-pk.pre_range=100
0.99 0.98 0.98 0.99 0.98 0.98 range-notcovered-pk_range=100
0.99 0.99 0.98 0.99 0.98 0.98 range-notcovered-si.pre_range=100
0.99 0.99 0.99 0.99 0.98 0.99 range-notcovered-si_range=100
1.01 1.06 1.59 1.08 1.08 1.06 read-only.pre_range=10000
1.00 1.02 1.02 1.03 1.03 1.02 read-only.pre_range=100
1.00 1.05 1.01 0.99 1.01 1.08 read-only.pre_range=10
1.01 1.04 1.44 1.08 1.07 1.05 read-only_range=10000
1.01 1.01 1.02 1.04 1.02 1.02 read-only_range=100
0.97 1.00 0.98 0.96 0.97 1.03 read-only_range=10
0.96 0.96 0.96 0.97 0.96 0.97 scan_range=100
1.01 1.04 1.00 1.03 1.04 1.05 delete_range=100
1.05 1.02 1.01 1.00 1.00 1.00 insert_range=100
1.01 1.02 1.02 1.03 1.02 1.02 read-write_range=100
1.00 1.01 0.99 1.00 1.02 1.00 read-write_range=10
1.00 1.00 0.99 1.01 1.01 1.00 update-index_range=100
1.00 1.00 1.00 1.00 1.00 1.00 update-inlist_range=100
1.00 1.00 0.99 1.01 1.01 0.98 update-nonindex_range=100
1.00 0.99 1.01 1.02 1.00 1.00 update-one_range=100
0.99 1.00 1.00 1.01 1.01 0.99 update-zipf_range=100
1.00 1.06 1.05 1.02 1.04 1.08 write-only_range=10000