Tuning Postgres 15.2 and 15.3 for the Insert Benchmark on a small server

I used the Insert Benchmark to find a good configuration for Postgres 15.2 and 15.3 on a small server. Results for Postgres 15 on a medium server are here.

tl;dr

• I had to repeat many benchmarks a few times to make sure I ran things for a long enough time so that results would be stable. I am happy to have finished this so I can move on to testing Postgres 16 beta releases.

Which configurations will I use in the future for the small server?

Builds

I compiled Postgres 15.2 and 15.3 from source. The builds are named o3_native_lto and used -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

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
• l.x
• 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 200 for cached and 20 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. This step took ~15,000 seconds for cached ~9,000 seconds for IO-bound.
• 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

All of the configurations are here and the base configuration, from which the following configs are derived, is here. The additional configs are:

• a1 - adds wal_compression=lz4
• a10 - adds autovacuum_work_mem=256MB
• a12 - adds wal_buffers=64MB
• a15 - adds max_wal_size=10GB
• a16 - adds autovacuum_vacuum_scale_factor=0.05, autovacuum_vacuum_insert_scale_factor=0.05
• a24 - adds autovacuum_vacuum_cost_limit=4000
• a25 - reduces shared_buffers to 2560MB

Results

Reports are here for Cached by Postgres, IO-bound.

I analyze these in terms of:

• average throughput
• fairness between benchmark clients
• SLA for background inserts and deletes
• response time histograms

Results: average throughput

Cached by Postgres (see here)

• For l.i0 and l.x results are similar for all configs
• The database size grows by ~1.68X from the end of l.x to the end of l.i1. This is reasonable.
• For l.i1
• PG 15.3 gets ~10% more throughput than 15.2 with some configs (base, a1, a10, a12, a15, a24). From the HW perf metrics 15.3 uses less CPU/operation than 15.2 (see the cpupq column)
• PG 15.2 and 15.3 get ~2X more throughput with the a16 config vs the base config. From the HW perf metrics the a16 config uses less CPU/operation and writes less to storage per operation (see cpupq and wkbpi columns)
• PG 15.2 gets ~15% less throughput with the a25 config vs the base config
• For q100, q500 and q1000 results are similar for all configs

IO-bound

• For l.i0 and l.x results are similar for all configs
• For l.i1
• Bad configs that get at least 10% less throughput than the base config with 15.2
• With PG 15.2 - a12, a15, a25
• With PG 15.3 - a1, a12, a15 (results for a15 and a25 are odd)
• Good configs that get at least 10% more throughput than the base config with 15.2
• With PG 15.2 - a10, a16, a24
• With PG 15.3 - base, a10, a24, a25
• For q100, q500 and q1000 results are similar for all configs

What is a good config? 

• For Cached by Postgres it might combine a1, a10, a16 and a24.
• For IO-bound it might combine a10, a16 and a24.

Results: fairness between benchmark clients

Fairness is a big problem for Postgres on larger servers but not here on a small server because there is only one client. By fairness I mean do the benchmark clients finish at the same time during the l.i1 benchmark step.

Results: SLA for background inserts and deletes

From the last table in the Summary sections

• Cached by Postgres
• All versions sustain the target insert rates
• IO-bound
• All versions sustain the target insert rates for q100 and q500
• Many versions fail to sustain the target insert rate (1000/s) for q1000
• For PG 15.2 the failures are with: base, a1, a10, a16, a24
• For PG 15.3 the failures are with: base, a24, a25

Results: response time histograms

Histograms are here for Cached by Postgres. Histograms are similar for all configs.

Histograms are here for IO-bound. 

• For l.i0 the a15 config max response time is 1.157s while others are <= 100ms
• For l.i1 the histograms are similar
• For q100, q500 and q1000 the histograms are similar

Postgres 16 beta3 and the Insert Benchmark on a medium server

Earlier this year I spent time comparing Postgres 16 beta1 with Postgres 15 to check for regressions, then realized I needed to confirm the configurations I use were good and that took longer than expected. Now I return to testing the beta releases. A post about Postgres 15 on the same hardware is here.

In this post I compare Postgres 15.3, 15.4, 16 beta1, 16 beta2 and 16 beta3 using the Insert Benchmark on a medium server (15 cores, 120G RAM).

tl;dr

• I am repeating the tests to get more results
• Create index is ~8% faster in 16 beta vs 15.x
• For the write heavy benchmark steps the lack of fairness during the l.i1 (inserts+deletes) benchmark step is a big problem for all versions
• For the read+write benchmark steps pg16 beta3 struggles or fails to sustain the target insert rates in the IO-bound setups. With each release from 15.4 to 16 beta3 the results get worse.

Builds

I compiled Postgres 15.3, 15.4, 16 beta1, 16 beta2 and 16 beta3 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 (client per table). 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
• l.x
• 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 200 for cached by InnoDB and 40 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. This step took 6000+ seconds for Cached by Postgres and 25,000+ seconds for IO-bound.
• 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 3600 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, IO-bound with a27, IO-bound with a28.

I analyze these in terms of:

• average throughput
• fairness between benchmark clients
• SLA for background inserts and deletes

Results: average throughput

Cached by Postgres (see here)

• For l.i1 there is a ~5% regression from 15.3 to 16 beta3. From the HW perf metrics I see ...
• A small increase (48 -> 49) in CPU/operation (see cpupq column) and average CPU utilization (vmstat us + sy, see cpups column) is 78.3 for 15.3 vs 76.5 for 16 beta3, which might be from doing more IO or an increase in IO latency.
• But from the wkbpi (KB written to storage/operation) I see that 16 beta3 does slightly less write IO than 15.3.
• The wmbps (MB/sec written to storage) numbers are correlated with the throughput implies that either storage write latency varies or some versions are able to do writeback faster.
• Results for the other benchmark steps are similar across versions

For l.i1 I also computed average values writes from iostat and I don't see an increase in average latency (see w_await) for the cases where versions were slower. I do see a decrease in wMB/s so for some reason writeback wasn't happening as fast. The table below has the average values from the l.i1 step.

version w/s     wMB/s   wrqm/s  w_await wareq-sz  aqu-sz  %util

15.3    1175    196.9   0.0     0.935   165.3     2.329   66.6

15.4    1107    184.9   0.0     0.860   163.8     2.059   64.3

16beta1 1182    200.9   0.0     1.041   166.7     2.673   68.0

16beta2 1089    179.8   0.0     0.864   162.9     2.070   62.6

16beta3 1103    186.1   0.0     0.883   164.3     2.103   64.6

For IO-bound with config a27 (see here)

• For l.x (create index) 16 beta releases are ~8% faster than 15.x perhaps because writeback is faster. From the HW perf metrics the write rate to storage (wmbps is MB/s written to storage) is higher for 16 beta
• For l.i1 (inserts+deletes) 16 beta1 and beta2 are 11% to 15% slower than 15.3, but 16 beta3 is only 2% slower. From HW perf metrics the problem is more CPU overhead (cpupq is CPU/operation) but it is hard to tell whether that is CPU consumed by user threads or by background (vacuum). Given the lack of fairness (see section below) it is harder to reason about the root causes.
• For read+write the results for q100 and q500 are similar across versions. For q1000 versions 15.4 and 16 beta3 do much better perhaps because they failed to sustain the target insert+delete rates -- they get 7209/s and 7486/s when the target is 8000/s.

For IO-bound with config a28 (see here)

• For l.x (create index) 16 beta releases are ~8% faster than 15.x
• For l.i1 (inserts+deletes) results for all versions are similar excluding 16 beta2
• For read+write the results for q100 and q500 results for all versions are similar excluding 16 beta2 (again, what happened there?). But for q1000 only 15.3 was able to sustain the target insert rate of ~8000/s and the rate that can be sustained drops in each release following 15.3. The HW perf metrics for q1000 can be confusing. I see a large reduction in rmbps (read MB/s from storage) and wmbps (write MB/s from storage). Did storage or Postgres get slower?

It is hard to compare some of the HW perf metrics for IO-bound with config a28 because the versions are not doing work (queries, inserts, deletes) at the same rate, but I checked the iostat results to confirm that storage latency isn't the problem and it doesn't appear to be based on the values for w_await and r_await below.

For IO-bound, config a28 and the l.i1 benchmark step

version w/s     wMB/s   wrqm/s  w_await wareq-sz  aqu-sz  %util

15.3    20157   317947  0.0     0.418   22.5      10.4    79.16

15.4    16543   278533  0.0     0.337   25.4       7.9    66.11

16beta1 18233   305163  0.0     0.405   26.1       9.4    81.82

16beta2 19296   349306  0.0     0.517   30.0      11.7    83.76

16beta3 17284   310738  0.0     0.430   32.9       9.2    73.27

version r/s     rMB/s   rrqm/s  r_await rareq-sz

15.3    11439   132618  0.0     0.207   15.0

15.4     8309    93010  0.0     0.200   12.2

16beta1 10297   116141  0.0     0.208   14.6

16beta2  9646   117296  0.0     0.219   15.1

16beta3  8813   107172  0.0     0.211   15.6

For IO-bound, config a28 and the q1000 benchmark step

version w/s     wMB/s   wrqm/s  w_await wareq-sz  aqu-sz  %util

15.3    20645   280606  0.0     0.275   17.4      8.715   97.57

15.4    18735   242598  0.0     0.152   17.3      4.738   91.35

16beta1 18375   239554  0.0     0.203   17.2      5.796   83.97

16beta2 18140   232740  0.0     0.247   16.9      5.383   91.77

16beta3 15730   203518  0.0     0.315   19.2      7.035   79.99

version r/s     rMB/s   rrqm/s  r_await rareq-sz

15.3    11499   115412  0.0     0.195   9.9

15.4    10799    89337  0.0     0.186   8.3

16beta1 10589    87671  0.0     0.190   8.3

16beta2 10414    86414  0.0     0.183   8.3

16beta3  9165    76209  0.0     0.182   8.5

Results: fairness between benchmark clients

Fairness is a big problem for all versions during the l.i1 benchmark step as show by the following tables. I expect all benchmark clients to finish at about the same time as each does the same number of inserts and deletes. But with Postgres some clients run much faster than others and the problem is much worse for the IO-bound setups.

Legend:

* min_time:  minimum time in seconds for a client to finish

* max_time: maximum time in seconds for a client to finish

* diff: max_time - min_time

* pct: (diff / min_time) * 100

cached by Postgres

version diff    pct

15.3    365     5.9

15.4    536     8.5

16beta1 378     6.3

16beta2 661     10.3

16beta3 596     9.5

io-bound, a27

version diff    pct

15.3    18242   186.9

15.4    16743   138.6

16beta1 18559   142.2

16beta2 18773   127.9

16beta3 15960   127.7

io-bound, a28

version diff    pct

15.3    15468   102.9

15.4    18808   151.4

16beta1 15723   110.7

16beta2 13275   107.3

16beta3 18398   159.7

Results: SLA for background inserts and deletes

From the last table in the Summary sections

• Cached by Postgres - all versions sustain the target insert rates
• IO-bound with a27 - 15.3 and 16 beta3 don't sustain the target insert rates for q1000 and 16 beta 2 almost fails for q1000
• IO-bound with a28 - all versions after 15.3 fail to sustain the target insert rates for q1000 and the failure gets worse from 15.4 to 16 beta3

Compiling all MySQL 5.7 versions on modern Ubuntu

This explains how I compiled all versions of MySQL 5.7 on Ubuntu 22.04 with gcc 11.4.0. It wasn't too bad but it would have been easier if some compiler warnings hadn't been ignored across multiple releases. Compiling old versions of Postgres is much easier.

First, install OpenSSL 1.0 and 1.1 as both will be needed and modern Ubuntu uses OpenSSL 3. Source is here. MySQL 5.7.29 and earlier releases use OpenSSL 1.0. MySQL 5.7.30 and later releases use OpenSSL 1.1.

./config --prefix=/path/to/ssl --openssldir=/path/to/ssl shared
make; make install

Second, hide the $SRC_ROOT/rapid directory. I moved it to rapid.hide. It has code that few of us use along with code that won't compile on modern gcc.

Get the patch files from here. These fix things that won't compile on modern gcc and most represent bug fixes.

• The my5710.patch file patches 5.7.10 through 5.7.13
• The my5714.patch file patches 5.7.14 through 5.7.18
• The my5719.patch file patches 5.7.19 and 5.7.20
• The my5721.patch file patches 5.7.21 through 5.7.33
• Fortunately, no patches are needed for 5.7.33+

The CMake scripts are here for pre-5.7.30 and 5.7.30+. The only difference is that the former points to OpenSSL 1.0 and the later to OpenSSL 1.1.

Then I run this script, assuming the patch files are in $HOME.

for x in $( seq 10 13 ); do bash domkmy57.sh $x 5710 cmk.57.pre5730 ; done
for x in $( seq 14 18 ); do bash domkmy57.sh $x 5714 cmk.57.pre5730 ; done

for x in $( seq 19 20 ); do bash domkmy57.sh $x 5719 cmk.57.pre5730 ; done

for x in $( seq 21 29 ); do bash domkmy57.sh $x 5721 cmk.57.pre5730 ; done

for x in $( seq 30 33 ); do bash domkmy57.sh $x 5721 cmk.57.5730 ; done

for x in $( seq 34 43 ); do bash domkmy57.sh $x none cmk.57.5730 ; done

Tuning MyRocks to reduce MVCC GC debt

I previously shared results for MyRocks and the Insert Benchmark where some queries were too slow because there was too much MVCC GC debt and the queries encountered long runs of tombstones. I am happy to share results here that show the problem can be avoided by adding a few options to the MyRocks my.cnf file.

By too slow I mean the query could take tens of seconds when it is expected to take tens of milliseconds. The problem occurs when a table is used like a queue with inserts at one end, deletes at the other end and the slow query does select min(transactionid) from $table. The query is slow when it encounters runs of tombstones from the deletes.

The problem is avoided by setting two options in my.cnf that are disabled by default. Documentation isn't great for these options and I have yet to read source code related to this. But the results are great so I will share that for now. The options are rocksdb_compaction_sequential_deletes and rocksdb_compaction_sequential_deletes_window. If compaction encounters X tombstones within any window of length Y (where X=r_c_sequential_deletes, Y=r_c_sequential_deletes_window) then extra work is done to get rid of the tombstones. Alas, today I am fuzzy on what that extra work is.

Note that dropping of tombstones allows for false negatives. Without the options described in the previous paragraph, many tombstones that can be dropped will not be dropped until they reach the max level of the LSM tree because the check for whether a tombstone can be dropped is meant to be cheap (some CPU, no IO) -- if the tombstone's key overlaps any SSTs from the larger levels below the tombstone's SST then it is not dropped. It might be possible to spend a bit more CPU and check bloom filters when there is overlap but I am not aware of an LSM that does that today.

MyRocks also makes this less of an issue by using SingleDelete for deletes of secondary index entries as those are easy to drop before they reach the max level of the LSM tree.

tl;dr - setting these options ...

• Prevents a scenario where some queries take 1000X longer than expected
• There is some cost -- it increases CPU overheads by up to 10% and write-amplification by up to 20% for this benchmark. The cost varies depending on the value the options are set to.

Builds

I used FB MySQL 8.0.28 compiled from git hash ef5b9b101 in June 2023.

Benchmark

The insert benchmark was run in two setups.

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

The benchmark used a server with 80 cores, hyperthreads enabled, 256G of RAM and fast NVMe with XFS. 

The benchmark is run with 24 clients and 24 tables (client per table). 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
• l.x
• 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 3600 seconds.

Configurations

The configuration files are here. They all include rocksdb_perf_context_level=2 which was done for debugging and isn't intended to always be set for production.

• y9cx_u (base) - base config
• y9cx_u_sd8k (sd8k) - sets rocksdb_compaction_sequential_deletes[_window] to 8k-1, 8k
• y9cx_u_sd32k (sd32k) - sets rocksdb_compaction_sequential_deletes[_window] to 32k-1, 32k
• y9cx_u_sd128k (sd128k) - sets rocksdb_compaction_sequential_deletes[_window] to 128k-1, 128k

Results

Performance reports are here for Cached by RocksDB and IO-bound.

The tables below show the time in seconds for the select min(transactionid) query that is run once at the start of some benchmark steps. It is fast at the start of l.i1 because there is no MVCC GC debt that that point. It is slow for the base config at the start of q100, q500 and q1000 because there is much MVCC GC debt at that point. However, it isn't slow for the configs that set the sequential delete my.cnf options

Cached by RocksDB

config  l.i1    q100    q500    q1000

base    0.001   8.206   9.926   9.188

sd8k    0.001   0.196   0.003   0.149

sd32k   0.001   0.194   0.021   0.102

sd128k  0.001   0.077   0.071   0.113

IO-bound

config  l.i1    q100    q500    q1000

base    0.001   10.883  10.267  12.833

sd8k    0.001   0.132   0.016   0.247

sd32k   0.001   0.115   0.020   0.234

sd128k  0.001   0.107   0.070   0.254

Results: average throughput

For Cached by RocksDB the average throughput for all configs is similar. 

For IO-bound the query rate for q100, q500 and q1000 is about 5% less for the configs that set sequential delete options (sd8k, sd32k, sd128k) when compared to the base config.

Results: CPU and IO overhead

For Cached by RocksDB and configs that set sequential delete options (sd8k, sd32k, sd128k)

• The CPU overhead is similar to the base config. See the cpupq columns here and cpupq means CPU/insert or /query.
• There is 10% increase in write-amplification based on the wkbpi column for the l.i1 benchmark step. Note that wkbpi stands for KB written to storage per insert.
• The increases are largest for the sd8k config and small for the sd128k config.

For IO-bound and configs that set sequential delete options (sd8k, sd32k, sd128k)

• There is a 5% to 10% increase in CPU overhead based on the cpupq column here for l.i1, q100, q500 and q1000
• There is an increase in write-amplification of 20% based on the wkibpi column for the l.i1 benchmark step
• The increases are largest for the sd8k config and small for the sd128k config.

Results: compaction IO statistics

How does the LSM tree look at the end of the benchmark?
How much overhead do the sequential delete options add to compaction?

Compaction IO statistics are here for Cached by RocksDB and for IO-bound (click on Raw, lines are long).

For Cached by RocksDB

• The total CPU time doing compaction is ~96,000 seconds for the base config and between 102,000 and 106,000 seconds for the configs that set sequential delete options which is an increase of between 7% and 9.6%. The increase is largest for the sd8k config. See the CompMergeCPU(sec) column in the lines that start with Sum like this one. Most of the increase is for compaction into the max level of the LSM tree (see the lines for sd8k and for base).
• Write-amplification increases by between 7.7% and 12.1% based on the wkbpi columns in the Sum lines (see the lines for sd8k and for base).

For IO-bound

• The total CPU time doing compaction is ~178,000 seconds for the base config and between 190,436 (for sd128k) and 209,000 (for sd8k) seconds for the other configs. So the CPU overhead from compaction increases by between 7% (for sd128k) and 17.3% (for sd8k). See the Sum lines for sd8k and for base.
• Write-amplification increases by between 5.6% and 14% based on the wkbpi columns in the Sum lines (see the lines for sd8k and for base).

Debugging

This wasn't easy to debug. Fortunately I have easy access to experts. To confirm that tombstones were the problem I set the perf_context level to 2 in my.cnf with:

rocksdb_perf_context_level=2

Then I queried information_schema.rocksdb_perf_context_global before and after running the query:

mysql -B -e 'select * from information_schema.rocksdb_perf_context_global' > o.pc.x.3

mysql ib -e 'select min(transactionid) from pi1'

mysql -B -e 'select * from information_schema.rocksdb_perf_context_global' > o.pc.x.4

Then I computed the difference of the counter values from after and before with a focus on INTERNAL_DELETE_SKIPPED_COUNT and INTERNAL_KEY_SKIPPED_COUNT. An example where the counters are huge is:

36144100                INTERNAL_DELETE_SKIPPED_COUNT

36144100                INTERNAL_KEY_SKIPPED_COUNT

The script to compute the diff is:

for x in x i1 q1 q2 q3 ; do
for f in 1 3 5 ; do
  echo; echo $x - $f
  join o.pc.${x}.${f} o.pc.${x}.$(( f + 1 )) | \
  awk '{ printf "%d\t\t%s\n", $3 - $2, $1 }' | \
  sort -n -k 1,1 | \
  tail -5
done
done

InnoDB vs the Insert Benchmark on a medium server, includes Percona Server

I previously shared results for the Insert Benchmark, InnoDB and a medium server. Now I have updated results that include Percona Server and a few new configs (a20, a21, a22). The good news is that Percona Server does better than upstream on the write-intensive parts of the benchmark. The bad news is that it does fix the problem of bad performance.

Several of the benchmark steps here (l.i1, q100, q500, q1000) were run for more time than they were in the previous report.

tl;dr

• Setting innodb_empty_free_list_algorithm to backoff with Percona Server does not fix the bad performance from single-page flushing
• The slow query from too much MVCC GC debt can be avoided by making sure that innodb_page_cleaners, innodb_purge_threads and innodb_buffer_pool_instances have the same value. They won't by default.
• Limiting max purge lag avoids some queries taking 1000X longer than needed, but at a big cost in write throughput. Query rates also suffer in many cases with the configs that limit max purge lag which isn't expected. Regardless, enabling limits for max purge lag is an important topic to consider.
• InnoDB doesn't suffer much from the fairness issue
• For Percona Server vs upstream using a similar config ...
• With a cached workload Percona Server has similar write throughput and slightly worse read throughput
• With an IO-bound workload Percona Server has better write throughput and similar read throughput
• Must not forget about bug 109595 as that reduces query rates for q100, q500 and q1000

Builds

I used InnoDB from upstream MySQL 5.6.51, 5.7.40 and 8.0.33. I also used it from Percona Server 8.0.33 (8.0.33-25). All were compiled from source with CMAKE_BUILD_TYPE=Release. Link-time optimization (-flto) was used for 5.7 and 8.0.

Benchmark

The insert benchmark was run in two setups.

• cached by InnoDB - all tables are cached by InnoDB
• 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 (client per table). 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
• l.x
• 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 200 for cached by InnoDB and 50 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

All of the my.cnf files for 8.0, 5.7 and 5.6.

The base configurations are here:

• for upstream MySQL 8.0, 5.7 and 5.6. The naming pattern is my.cnf.cy10$x_gcp_c2s30 where x is a for the base config and a1 ... a22 otherwise.
• for Percona Server 8.0.33 (ps1 and ps2, see below)

The configurations tested are:

• base - see links above
• a2 - increases innodb_io_capacity[_max] to 20k/40k
• a7 - adds innodb_lru_scan_depth=2048
• a10 - adds innodb_purge_threads=8, innodb_page_cleaners=8
• a12 - reduces innodb_redo_log_capacity to 32G
• a13 - adds innodb_max_purge_lag=50k, innodb_max_purge_lag_delay=1M, innodb_purge_threads=8
• a17 - adds innodb_doublewrite=OFF (not safe for production)
• a18 - adds innodb_buffer_pool_instances=4, innodb_page_cleaners=4
• a19 - adds innodb_max_dirty_pages_pct_lwm=20, innodb_max_dirty_pages_pct=50
• a20 - like a13 but with innodb_max_purge_lag=200k
• a21 - like a13 but with innodb_max_purge_lag=500k
• a22 - combines a10 and a20
• ps1 - for Percona Server, same as base but sets innodb_empty_free_list_algorithm to legacy
• ps2 - for Percona Server, same as base but sets innodb_empty_free_list_algorithm to backoff

Most of the configurations were used for MySQL 8.0.33. Only ps1 and ps2 were used for Percona Server. Only the base config was used for 5.6.51 and 5.7.40.

Results

Reports are here for:

• Cached by InnoDB: 5.6 vs 5.7 vs 8.0 and 8.0 only
• IO-bound: 5.6 vs 5.7 vs 8.0 and 8.0 only

I analyze these in terms of:

• average throughput
• fairness between benchmark clients
• SLA for background inserts and deletes
• response time histograms
• performance at 1-second intervals
• purge lag 
• The impact of MVCC GC on some queries

Results: average throughput

The summaries show average throughput for each benchmark step using absolute and relative values. The relative values are the throughput relative to the throughput for the base config. The summaries are here for Cached by InnoDB (5.6 v 5.7 v 8.0 and 8.0 only) and IO-bound (5.6 v 5.7 v 8.0 and 8.0 only). In the summaries green/red mean good/bad for the table with absolute values and blue/gold mean better/worse for the table with relative values. I focus on the l.i1, q100, q500 and q1000 benchmark steps.

For Cached by InnoDB, 8.0 only (see here)

• For l.i1 the a10, a13, a20, a21 and a22 configs reduce the write rate by ~15%. All but the a10 config enable limits on max purge lag and the reduction is expected for them. The reduction for a10 is surprising. The a10 config sets innodb_purge_threads and innodb_page_cleaners to 8 to match the default for innodb_buffer_pool_instances. Note that the a18 config also sets purge_threads, page_cleaners and buffer_pool_instances to the same value, but the value is 4 for a18, and there isn't a reduction for a18.
• For q100 the query throughput is ~1.4X better for the configs (a13, a20, a21, a22) that limit max purge lag. For those configs there is less MVCC GC debt which means there is less overhead from the work done to reduce that debt. The throughput is also better for a10 (see the previous bullet point). 
• For q500 and q1000 the throughput for the the max purge limiting configs is similar to the others because the history list length has been reduced for all configs (the debt was repaid during q100).

For Cached by InnoDB, 5.6 vs 5.7 vs 8.0 (see here)

• For l.i0 (initial load) throughput in 5.7 is 1.88X better than 5.6 while in 8.0 it is only ~1.6X better than 5.6. From the rps (storage reads/second), rpq (storage reads/insert) and cpupq (CPU/insert) columns the difference is mostly due to 5.6 doing read IO (I don't know why) that isn't done by 5.7 and 8.0 while the regression from 5.7 to 8.0 might be from new CPU overhead -- see here.
• For l.x the create index step is fastest for 5.7 and then regressions from 5.7 to 8.0. There is more CPU and read IO overhead in 8.0 vs 5.7 -- see here for the rps, rpq and cpupq columns.
• For l.i1 (inserts & deletes with secondary indexes) the write rates for 5.7 and 8.0 are ~1.6X better than for 5.6
• For q100 the query rate drops from 5.6 to 5.7 to 8.0 as the CPU overhead (see cpupq here) increases
• For q500 and q1000 the query rates are closer and 5.6 suffers from more synchronization overhead (see cspq here) while 8.0 benefits from code improvements related to concurrency.
• Upstream InnoDB is similar to Percona Server for writes and slightly better for reads here. The difference for reads appears to be from CPU overhead (see cpupq here for q100, q500 and q1000).

For IO-bound, 8.0 only (see here)

• For l.i1 the configs that limit max purge lag (a13, a20, a21, a22) get ~25% of the write rate that other configs sustain. This is a big price to pay for better query performance. 
• For q100 most of the configs that limit purge lag (a13, a20, a22) get better query rates but a21 does not. From metrics I see the CPU overhead (cpupq) and storage read overhead (rps, rpq) are too high for a21.
• For q500 and q1000 the query rates suffer for the configs that limit max purge lag. This is odd.
• Percona Server does better than upstream InnoDB (comparing to the base config) on the l.i1 benchmark step and similar on query throughput.
• Results for the a10 config are hard to explain -- throughput for l.i1, q100, q500 and q1000 are all worse than the base config. From the metrics here I see more mutex contention (cspq) and more CPU overhead (cpupq). This isn't an issue for a18, which is similar buts sets the options to =4 rather than =8. Maybe the CPU is oversubscribed or innodb_purge_threads=8 is a problem.

For IO-bound, 5.6 vs 5.7 vs 8.0 (see here)

• For l.i0 (initial load) throughput is 1.97X better in 5.7 vs 5.6 while 8.0 is only ~1.7X better than 5.6 because 8.0 has more CPU overhead than 5.7 (see cpupq here)
• For l.x (create index) perf is ~1.8X better in 5.7 vs 5.6 while 8.0 is only ~1.5X better in upstream 8.0 and ~1.4X better in Percona Server. The difference between 5.7 and 8.0 is from more CPU and read IO overhead (see cpupq, rps and rpq here). The difference between upstream and Percona is from CPU overhead (see cpupq here).
• For l.i1 (inserts+delete with secondary indexes) 5.7 and Percona Server are ~1.1X faster than 5.6 while upstream 8.0 is slightly slower than 5.6. Percona Server uses less CPU and read IO than upstream (see cpupq, rps and rpq here).
• For q100, q500 and q1000 5.7 is ~1.2X faster than 5.6 while 8.0 (upstream and Percona Server) are ~1.3X faster than 5.6 because they use less CPU (see cpupq here).

Results: Fairness between benchmark clients

These are measured from the l.i1 benchmark step and the duration for this step varies as it runs until the configured number of inserts are done. In this case the benchmark step takes >= 10,000 seconds. The results here show that the benchmark clients don't finish at the same time but the difference isn't as large as it is for Postgres. There are spikes in throughput near the end of the benchmark steps for the stragglers because they have less competition for the DBMS at that point.

Legend:

* min_duration/max_duration - min and max runtimes from the 8 clients

* diff - max_duration - min_duration

* pct - (diff / min_duration) * 100

* config

Cached by InnoDB

diff    pct     config

123     0.6     my5651_rel.cy10a_gcp_c2s30

30      0.2     my5740_rel_lto.cy10a_gcp_c2s30

37      0.2     my8033_rel_lto.cy10a10_gcp_c2s30

31      0.2     my8033_rel_lto.cy10a12_gcp_c2s30

58      0.4     my8033_rel_lto.cy10a13_gcp_c2s30

32      0.3     my8033_rel_lto.cy10a17_gcp_c2s30

27      0.2     my8033_rel_lto.cy10a18_gcp_c2s30

55      0.3     my8033_rel_lto.cy10a20_gcp_c2s30

17      0.1     my8033_rel_lto.cy10a21_gcp_c2s30

25      0.2     my8033_rel_lto.cy10a22_gcp_c2s30

37      0.3     my8033_rel_lto.cy10a2_gcp_c2s30

24      0.2     my8033_rel_lto.cy10a7_gcp_c2s30

23      0.2     my8033_rel_lto.cy10a_gcp_c2s30

30      0.2     ps8033_rel_lto.cy10aps1_gcp_c2s30

23      0.2     ps8033_rel_lto.cy10aps2_gcp_c2s30

IO-bound

diff    pct     config

118     0.7     my5651_rel.cy10a_gcp_c2s30

95      0.7     my5740_rel_lto.cy10a_gcp_c2s30

441     2.7     my8033_rel_lto.cy10a_gcp_c2s30

201     1.4     ps8033_rel_lto.cy10aps1_gcp_c2s30

205     1.4     ps8033_rel_lto.cy10aps2_gcp_c2s30

822     4.6     my8033_rel_lto.cy10a10_gcp_c2s30

213     1.2     my8033_rel_lto.cy10a12_gcp_c2s30

295     0.5     my8033_rel_lto.cy10a13_gcp_c2s30

337     2.6     my8033_rel_lto.cy10a17_gcp_c2s30

304     1.8     my8033_rel_lto.cy10a18_gcp_c2s30

150     0.2     my8033_rel_lto.cy10a20_gcp_c2s30

317     0.4     my8033_rel_lto.cy10a21_gcp_c2s30

316     0.5     my8033_rel_lto.cy10a22_gcp_c2s30

273     1.6     my8033_rel_lto.cy10a2_gcp_c2s30

285     1.8     my8033_rel_lto.cy10a7_gcp_c2s30

Results: SLA for background inserts and deletes

The insert+delete rates are listed at the end of the Summary section in the perf reports.

• Cached by InnoDB
• 8.0 - target rates were sustained
• 5.6 vs 5.7 vs 8.0 - 5.6.51 failed for the q100 step
• IO-bound
• 8.0 - a13, a20 and a22 configs for upstream 8.0.33 failed for the q1000 step. These configs enable limits on the max purge lag to avoid too much MVCC GC debt and the limits were reached during q1000 for which the targets are 8000 inserts/s and 8000 delete/s (8x1000). The value from limits on purge lag can be seen in the Impact of MVCC GC section below where a query can take ~100 seconds without a purge lag limit vs ~30 milliseconds with the limit.
• 5.6 vs 5.7 vs 8.0 - target rates were sustained

Results: response time histograms

Response time distributions -- trying to be brief because this blog post is long.

• Cached by InnoDB
• 8.0
• not that interesting
• 5.6 vs 5.7 vs 8.0
• results for 5.7 and 8.0 are mostly better than 5.6
• IO-bound
• 8.0
• on the l.i1 step the a21 config has a bad outlier, 18.493s for a write, while Percona Server has outliers that are worse than upstream.
• 5.6 vs 5.7 vs 8.0
• on the li0 step 5.7 has the best histogram.
• on the l.i1 step the max response time is 16.256s for 5.6, 14.511s for 5.7, 0.447s for upstream 8.0 and ~2s/~1s for Percona with the ps1/ps2 configs. Percona Server has the best response time histograms.
• For q100, q500 and q1000 the histograms are similar

Results: performance at 1-second intervals

For Cached by InnoDB and 5.6 vs 5.7 vs 8.0

• For l.i1 the insert rate and and max response time charts are similar between upstream 8.0 (base config) and Percona Server (ps2 config).
• For q1000 Percona Server with the ps2 config has less variance for inserts (see here) vs upstream InnoDB (see here).

For IO-bound and 5.6 vs 5.7 vs 8.0

• For l.i0 upstream 8.0 is similar to Percona Server
• For l.i1 the insert rate charts are similar -- a slow rise over ~4000s then stable while all have a bit of response time variance
• For q100 there is more query throughput variance with Percona Server and the ps2 config vs upstream 8.0
• For q1000 there is more query throughput variance with Percona Server and the ps2 config vs upstream 8.0

Results: purge lag

These show history list length (InnoDB purge lag) at the end of each benchmark step for Cached by InnoDB and IO-bound. The a13, a20, a21 and a22 configs all enable limits on max purge lag to avoid a too-large history list length. The benefit from this is avoiding slow queries (see the next section). The cost is a lower write rate (see the average throughput section above). There are always tradeoffs and the Rum Conjecture applies. I don't know whether the costs of this tradeoff from limiting max purge lag could be smaller. Is the current code good enough? 

Results: The impact of MVCC GC on some queries

There is a query done at the start of benchmark steps to determine the min value of transactionid (the PK column in the table). Inserts are done to the head of the table while deletes are done from the tail and for InnoDB and MyRocks this query can be slow unless you change the values of a few options.

This query can be a problem for InnoDB (regardless of tuning) and for MyRocks (dependent on tuning). It isn't a problem for Postgres.

A blog post with more details is here.

The query response time in seconds for the query done at the start of the benchmark step. It gets slow starting with the steps that follow l.i1 because l.i1 creates too much MVCC GC debt. Some configs (a13, a20, a21) avoid the slow query by enabling a limit on the max purge lag. Other configs (a10, a22) avoid the slow query by making sure that the innodb_page_cleaners, innodb_purge_threads and innodb_buffer_pool_instances have the same value.

Cached by InnoDB

config                                  l.i1    q100    q500    q1000

ps8033_rel_lto.cy10aps2_gcp_c2s30       0.032   86.014  5.308   0.001

ps8033_rel_lto.cy10aps1_gcp_c2s30       0.032   96.757  5.292   0.001

my8033_rel_lto.cy10a_gcp_c2s30          0.033   95.307  1.915   0.001

my8033_rel_lto.cy10a2_gcp_c2s30         0.033   94.290  1.566   0.001

my8033_rel_lto.cy10a7_gcp_c2s30         0.033   92.804  0.002   0.001

my8033_rel_lto.cy10a10_gcp_c2s30        0.033   0.001   0.001   0.001

my8033_rel_lto.cy10a12_gcp_c2s30        0.033   94.619  1.962   0.001

my8033_rel_lto.cy10a13_gcp_c2s30        0.034   0.001   0.001   0.001

my8033_rel_lto.cy10a17_gcp_c2s30        0.032   38.458  0.083   0.001

my8033_rel_lto.cy10a18_gcp_c2s30        0.033   97.824  3.295   0.001

my8033_rel_lto.cy10a20_gcp_c2s30        0.034   0.001   0.001   0.001

my8033_rel_lto.cy10a21_gcp_c2s30        0.033   0.001   0.001   0.001

my8033_rel_lto.cy10a22_gcp_c2s30        0.034   0.001   0.001   0.001

my5740_rel_lto.cy10a_gcp_c2s30          0.000   153.6   121.0   128.6

my5651_rel.cy10a_gcp_c2s30              0.000   450.5   368.1   126.5

IO-bound

config                                  l.i1    q100    q500    q1000

ps8033_rel_lto.cy10aps2_gcp_c2s30       0.031   77.351  25.876  27.465

ps8033_rel_lto.cy10aps1_gcp_c2s30       0.031   84.890  29.894  29.342

my8033_rel_lto.cy10a_gcp_c2s30          0.036   79.598  28.934  30.864

my8033_rel_lto.cy10a7_gcp_c2s30         0.034   80.706  27.499  31.721

my8033_rel_lto.cy10a2_gcp_c2s30         0.034   67.411  28.523  32.066

my8033_rel_lto.cy10a22_gcp_c2s30        0.032   1.178   0.001   0.001

my8033_rel_lto.cy10a21_gcp_c2s30        0.032   4.447   0.001   0.002

my8033_rel_lto.cy10a20_gcp_c2s30        0.034   1.459   0.002   0.002

my8033_rel_lto.cy10a18_gcp_c2s30        0.032   85.202  31.091  32.530

my8033_rel_lto.cy10a17_gcp_c2s30        0.034   29.340  14.566  13.648

my8033_rel_lto.cy10a13_gcp_c2s30        0.032   0.095   0.001   0.002

my8033_rel_lto.cy10a12_gcp_c2s30        0.034   61.456  26.763  29.332

my8033_rel_lto.cy10a10_gcp_c2s30        0.031   63.485  27.013  22.921

my5740_rel_lto.cy10a_gcp_c2s30          0.003   101.5   39.1    42.5

my5651_rel.cy10a_gcp_c2s30              0.018   102.7   108.9   66.2

Tuning InnoDB for the Insert Benchmark on a small server

I used the Insert Benchmark to find a good my.cnf for InnoDB on a small server with MySQL 8.0, 5.7 and 5.6. Results for InnoDB on a large server are here.

tl;dr

• Throughput in 8.0.33 for a cached workload is about half of what it was in 5.6.51. The reason is new CPU overhead. The difference here with a small server and low concurrency workloads is larger than what I measure on larger servers with high concurrency.
• Single-page flushing causes bad (multi-second) stalls in 8.0 for reads and writes. This makes it hard to get good QoS from InnoDB for a write-heavy workload.
• The database size growth isn't unreasonable during the l.i1 benchmark step -- and it was unreasonable on the medium and large servers. This is probably explained by the usage of low-concurrency here (1 client) vs 8 on the medium and 24 on the large server.
• Disabling the doublewrite (not safe for production) reduces or avoids performance problems where single-page flush stalls are part of the root cause. Hopefully this code gets revisited by upstream.
• The base configuration is one of the best. While I tested up to 22 variations on it, the variations don't improve much for a small server with a low concurrency workload.
• Queries can be slowed by MVCC GC debt unless the change buffer is disabled, but that comes at a huge cost to the write rate. The low query rates here also suffer from bug 109595.

Builds

I used upstream MySQL 8.0.33, 5.7.40 and 5.6.51. All used CMAKE_BUILD_TYPE=Release and then the 5.7 and 8.0 builds also used -flto (link time optimization).  The builds are named my8033_rel_lto, my5740_rel_lto and my5651_rel.

Benchmark

The insert benchmark was run in three setups.

• cached by InnoDB - all tables are cached by InnoDB
• cached by OS - all tables are cached by the OS page cache but not by InnoDB
• IO-bound - the database is larger than memory

For cached by InnoDB and IO-bound the setups have a 10G InnoDB buffer pool and don't use buffered IO (innodb_flush_method = O_DIRECT_NO_FSYNC), with one exception -- the a4 config uses innodb_flush_method = fsync.

For cached by OS the configurations have a 1G InnoDB buffer pool and use buffered IO (innodb_flush_method = fsync). 

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 500 for IO-bound
• l.x
• 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 40 for cached by InnoDB, 5 for cached by OS and 5 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
• do queries as fast as possible with 100 inserts/s/client and the same rate for deletes/s done in the background. Run for 3600 seconds.
• q500
• do queries as fast as possible with 500 inserts/s/client and the same rate for deletes/s done in the background. Run for 3600 seconds.
• q1000
• do queries as fast as possible with 1000 inserts/s/client and the same rate for deletes/s done in the background. Run for 3600 seconds.

Configurations

All of the my.cnf files are here.

The base configurations are here:

• for cached by InnoDB and IO-bound: 5.6.51, 5.7.40, 8.0.33. The naming pattern is my.cnf.cy10$x_bee where x is a for the base config and a1 ... a22 otherwise.
• for cached by OS: 5.6.51, 5.7.40, 8.0.33. The naming pattern is my.cnf.cy10$x_bee where x is a for the base config and a1 ... a22 otherwise.

The configurations tested are:

• base - see links above
• a2 - increases innodb_io_capacity[_max] to 20k/40k
• a3 - adds innodb_flush_sync=off and innodb_io_capacity[_max]=20k/40k
• a4 - adds innodb_flush_method=fsync
• a5 - adds innodb_change_buffering=none
• a7 - adds innodb_lru_scan_depth=2048
• a10 - adds innodb_purge_threads=8, innodb_page_cleaners=8
• a12 - reduces innodb_redo_log_capacity to 32G
• a13 - adds innodb_max_purge_lag=50k, innodb_max_purge_lag_delay=1M, innodb_purge_threads=8
• a14 - adds innodb_change_buffering=inserts
• a15 - changes innodb_idle_pct to default
• a16 - reduces innodb_io_capacity to 2000 and innodb_io_capacity_max to 4000
• a17 - adds innodb_doublewrite=OFF (not safe for production)
• a18 - adds innodb_buffer_pool_instances=4, innodb_page_cleaners=4
• a19 - adds innodb_max_dirty_pages_pct_lwm=20, innodb_max_dirty_pages_pct=50
• a20 - like a13 but with innodb_max_purge_lag=200k
• a21 - like a13 but with innodb_max_purge_lag=500k
• a22 - combines a10 and a20

Most of the configurations were used for MySQL 8.0.33. Only the first three or four (a1 to a4) were tested for 5.6.51 and 5.7.40.

Performance

For each of the workloads below I analyze the performance in four parts:

• average throughput - I look at the average queries and inserts per second to see if these are much better or worse than the base config
• response time - I look at max response time and the response time distributions to see if a config makes them much better or much worse than the base config
• charts - there are charts for the per-second query, insert and delete rates. I look at the graphs to see if the rates degrade over time or have too many stalls.
• SLA - there is a target rate for the background inserts and deletes which is one of 100, 500 or 1000 per second per client. There is an SLA failure when the actual rate is too much smaller than the target.
• get_min queries - select min(transactionid) from $table is run at the start of some benchmark steps and this query should be fast because transactionid is the PK. But in some cases it is slow when there is too much MVCC GC debt.
• size - How much does the database size increase from the create index benchmark step (l.x) to the write-only step (l.i1).  Anything larger than 1.5X for a b-tree can be too much.

Reports are here:

• MySQL 5.6.51: cached by InnoDB, cached by OS, IO-bound
• MySQL 5.7.40: cached by InnoDB, cached by OS, IO-bound
• MySQL 8.0.33: cached by InnoDB, cached by OS, IO-bound
• MySQL 5.6 vs 5.7 vs 8.0: cached by InnoDB, cached by OS, IO-bound

Results: cached by InnoDB

Summary:

• average throughput
• For 5.6 the base, a1 and a2 configs are similar. The l.x and l.i1 benchmark steps are slower with the a4 config.
• For 5.7 the base, a1, a2 and a3 configs are similar. The l.i0, l.x and l.i1 benchmarks steps are slower with the a4 config.
• For 8.0 all configs are similar on the l.i0, l.i1, q100, q500 and q1000 benchmark steps. There is more variance on the l.x (create index) step where the a18 config gets the best perf.
• For 5.6 vs 5.7 vs 8.0
• For the l.i0 step the insert rate in 8.0 drops to ~58% of the 5.6 throughput
• For the l.x step creating indexes is ~1.3X faster in 8.0 vs 5.6
• For the l.i1 step the insert rate in 8.0 is ~15% less vs 5.6 but 5.7 was faster than 5.6
• For the q100, q500 and q1000 steps the query rate in 8.0 drops to ~56% of what it was in 5.6
• For all of the benchmark steps there is a big increase in CPU overhead (see cpupq here) from 5.6 to 8.0
• response time
• For 5.6, 5.7 and 8.0 the response time histograms were similar for all configs
• charts
• I am skipping the charts as this post is long enough.
• SLA
• There were no SLA failures and the target insert+delete rates were sustained.
• get_min
• All select min(transactionid) queries were fast
• size
• For 5.6 the database size grows by 1.41X which is OK
• For 5.7 the database size grows by 1.47X which is OK
• For 8.0 the database size grows by 1.43X which is OK

Results: cached by OS

Summary:

• average throughput
• For 5.6 the base config was best and the a2 config was bad
• For 5.7 the base and a1 config were the best, the a2 and a3 configs were bad
• For 8.0 the a17 config (disables doublewrite, not safe for production) and otherwise InnoDB falls over (it looks like the problem is single-page flushing)
• For 5.6 vs 5.7 vs 8.0
• For the l.i0 step the insert rate in 8.0 drops to ~60% of the 5.6 throughput
• For the l.i1 step throughput improves from 5.6 to 5.7, but in 8.0 drops to ~63% of the 5.6 throughput
• For q100, q500 and q1000 the query rate drops from 5.6 to 5.7 and the results for 8.0 are lousy
• response time
• For 5.6 all configs have ~2 second outliers for l.i1. The a2 config is worse for q100, q500 and q1000.
• For 5.7 all configs have ~1 second outliers for l.i1. The a2 and a3 configs are worse for q100, q500 and q1000.
• For 8.0 all configs except a17 have ~4 second outliers for l.i1. For q100, q500 and q1000 all configs have ~4 second outliers for queries, inserts and deletes. This looks like a problem with too-frequent single-page flush stalls. InnoDB in 8.0 does much worse than 5.7 or 5.6.
• charts
• I am skipping the charts as this post is long enough.
• SLA
• For 5.6 the a2 config did not sustain the target insert rates for q100, q500 and q1000
• For 5.7 the a2 and a3 configs did not sustain the target insert rates
• For 8.0 only the a17 config sustains the target insert rates
• get_min
• For 5.6 the query is slow (55+ seconds) for the a2 config
• For 5.7 the query is slow (22+ seconds) for all configs
• For 8.0 the query is slow (30 to 500 seconds) for all configs except a17 and the root cause is probably single-page flush stalls which are avoided by a17 because that disables doublewrite
• size
• For 5.6 the database size grows by 1.41X for the a0 and a1 configs which is OK but by 1.70X for a2 which is a problem
• For 5.7 the database size grows by 1.57X which is too much
• For 8.0 the database size grows by 1.53X to 1.70X which is too much

Results: IO-bound

Summary:

• average throughput
• For 5.6 the base, a1 and a2 configs are similar. The a4 config is bad.
• For 5.7 the base, a1, a3 and a3 configs are similar. The a4 config is bad.
• For 8.0 some configs did worse on the l.i1 benchmark step (a5, a13, a14, a18) which might explain why they did better on q100 (because there was less writeback debt). But the base config was one of the best.
• For 5.6 vs 5.7 vs 8.0
• For the l.i0 step the insert rate in 8.0 drops to ~59% of the 5.6 throughput
• For the l.x step 5.7 and 8.0 are ~1.3X faster than 5.6
• For the l.i1 step throughput in 5.7 and 8.0 is ~1.35X larger
• For q100 and q500 the query rate in 8.0 is ~6% less than 5.6 while for q1000 it is ~2% larger than 5.6
• response time
• For 5.6 config a4 does much worse than base, a1 or a2.
• For 5.7 config a4 does much worse than base, a1, a2 or a3
• For 8.0 config a5 does worse on l.i1 and for inserts + deletes on q100, q500 and q1000
• charts
• I am skipping the charts as this post is long enough.
• SLA
• For 5.6 the a4 config does not sustain the target insert rates for q100, q500 and q1000
• For 5.7 the a4 config does not sustain the target insert rates
• For 8.0 all configs sustain the target insert rates
• get_min
• For 5.6 the query is slow (~1 second) for the base, a1 and a2 config and ~105 seconds for the a4 config
• For 5.7 the the query is slow (1 to 3 seconds) for the base, a1, a2 and a3 configs and ~311 seconds for the a4 config
• For 8.0 the query is slow (~1 second) for all configs except a5 which disabled the change buffer
• size
• For 5.6 the database size grows by ~1.4X which is OK
• For 5.7 the database size grows by ~1.3X which is OK
• For 8.0 the database size grows by ~1.3X which is OK