Small Datum

Sunday, January 10, 2021

Sysbench: in-memory Postgres, Postgres is boring

While writing and rewriting perf reports for MyRocks and InnoDB I have also been running sysbench tests with Postgres. I am happy to claim that Postgres is boring as there are no CPU regressions from version 11 to 13.

I ran tests for Postgres versions 11.10, 12.4 and 13.1 on a small server using sysbench and an in-memory, low-concurrency workload. Workloads with more IO and concurrency are great, but this is the first step. A previous post on how I run these tests. There is one table with 10M rows, tests are run for 1, 2 and 3 clients and each test is run for 180 seconds.

I spent time in 2020 evaluating configuration options for Postgres and settled on the x5 config. Vacuum analyze is run twice -- after the load and again after the write-heavy phase. Along with vacuum analyze, other things are done to write back most of the dirty pages via checkpoint and sleeping (see here).

The summary:

There are no regressions
There was a significant (~20%) improvement on one test that is update-only with all updates limited to the same row

Results

The tests are in 5 groups based on the sequence in which they are run: load, read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete.

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS/TPS that a test gets for 1, 2, and 3 clients. Relative throughput is the QPS/TPS relative to the base case (Postgres 11.10). The HW efficiency report has absolute and relative results (base case is Postgres 11.10) for CPU and IO per operation.

I use ratios to explain performance. In the output files I link to below, Postgres 11.10 is the denominator and the numerator is the value for Postgres 12.4 and 13.1. In my analysis below I focus on Postgres 13.1. A QPS ratio < 1 means the new version is slower. For HW efficiency I use CPU/operation and IO/operation (read & write). For CPU and IO per operation a ratio > 1 means the new version uses more CPU or IO per query.

The results are in github for absolute throughput, relative throughput and HW efficiency. I annotate the relative throughput and HW efficiency results below. Unless called out, I am explaining the results for 13.1 relative to 11.10.

Load:

Inserts/second ratio is 1.08 (~83k/s -> ~90k/s, see here)
CPU/insert ratio is 0.94 (see here)

Read-only before write-heavy:

QPS ratios are 1.05, 1.01, 0.97, 0.96 for the first 4 tests (here to here)

These do point queries
CPU/query ratios are: 0.96, 0.99, 1.03, 1.06 (here to here)

QPS ratios are 1.02, 1.01, 0.97 for the next 3 tests (here to here)

These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/query ratios are 0.99, 0.99, 1.03 (here to here).

QPS ratios are 0.98, 0.96 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.02, 1.04 (here to here).

QPS ratios are 0.97, 0.96 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.03, 1.03 (here to here)

QPS ratios are 1.00, 0.99 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index
CPU/query ratios are 1.00, 1.01 (here to here)

QPS ratios are 1.01, 1.00 for the next 2 tests (here to here)

These are similar to the previous test but use the secondary index.
CPU/query ratios are 0.99, 1.00 (here to here)

Write-heavy

QPS ratios are 0.99, 0.96, 1.00, 1.20, 1.00 for the next 5 tests (here to here)

These are update-only. The big gain (1.20) is for a test that does all updates to the same row.
CPU/statement ratios are 1.01, 1.05, 1.01, 0.86, 1.00 (here to here)

QPS ratio is 1.00 for the next test, write-only (see here)

This has the writes from oltp_read_write.lua.
CPU/transaction ratio is 1.02 (see here)

QPS ratios are 1.00, 1.01 for the next two tests, read-write (here to here)

These are the traditional sysbench tests (oltp_read_write.lua) with ranges of size 10 & 100
CPU/transaction ratios are 0.99, 0.98 (here to here)

Read-only after write-heavy includes tests that were run before write-heavy. The DBMS engines were given a chance to catch up on write back between the write-heavy tests above and the read-only tests below.

QPS ratio is 1.03, 1.01, 1.00 for the next 3 tests, read-only (here to here)

These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/transaction ratios are 0.97, 0.99, 1.00 (here to here)

QPS ratios are 1.05, 1.01, 0.94, 0.96, 1.00 for the next 5 tests (here to here)

These do a variety of point queries.
CPU/query ratios are 0.96, 0.98, 1.06, 1.04, 1.01 (here to here)

QPS ratios are 0.97, 0.97 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.02, 1.03 (here to here)

QPS ratios are 0.98, 0.97 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 0.99, 1.02 (here to here)

QPS ratios are 1.00, 1.01 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the PK index
CPU/query ratios are 1.00, 0.99 (here to here)

QPS ratios are 1.00, 1.00 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 0.99, 1.00 (here to here)

Insert/delete

QPS ratio is 0.99 for the delete test and 1.02 for the insert test
CPU/statement ratio is 1.04 for delete and 0.97 for insert

Saturday, January 9, 2021

Sysbench: in-memory InnoDB, MySQL 5.6, 5.7 & 8.0, take 3

This is my third attempt to report in-memory sysbench results for InnoDB in MySQL 5.6, 5.7 and 8.0. In my second attempt I changed my.cnf so that both versions used the same value for eq_range_index_dive_limit. In this attempt I get InnoDB to write back dirty pages (see here) after the write-heavy tests are run (before the read-only tests are repeated). The main reason for the change was to reduce variance in MyRocks results as explained here, but I hope it helps InnoDB as well.

Will MySQL continue to get 15% less QPS in each new major version as it has for 5.6 to 5.7 to 8.0? This is true for in-memory and low-concurrency workloads, and likely true for any workload for which CPU overhead is an issue. Workloads that are IO-bound and/or limited by contention will suffer less from this and also benefit from improvements upstream has made for mutex contention.

The summary from my previous post is mostly true here:

InnoDB in 5.7 gets ~85% or better of the QPS vs 5.6
InnoDB in 8.0 gets ~70% or more of the QPS vs 5.6
For write-heavy there is a large regression from 5.7 to 8.0 that I have yet to explain
For queries with large in-lists there is a CPU regression bug in 8.0.22 -- bug 102037
Regressions from 5.6 to 8.0 for low-concurrency, in-memory workloads are worse for InnoDB than for MyRocks. I didn't expect this, but there have been some big changes to InnoDB in 8.0. I assumed that most regressions are from code above InnoDB, but that isn't true.

Results

The tests are in 5 groups based on the sequence in which they are run: load, read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete.

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS/TPS that a test gets for 1, 2, and 3 clients. Relative throughput is the QPS/TPS relative to the base case (MySQL 5.6.49). The HW efficiency report has absolute and relative results (base case is MySQL 5.6.49) for CPU and IO per operation.

I use ratios to explain performance. In this case MySQL 5.6.49 is the denominator and the numerator is the value for 5.7 or 8.0. A QPS ratio < 1 means the new version is slower. For HW efficiency I use CPU/operation and IO/operation (read & write). For CPU and IO per operation a ratio > 1 means the new version uses more CPU or IO per query.

Inserts/second ratio is 0.65 (~104k/s -> ~68k/s, see here)
CPU/insert ratio is 1.67 (see here)
The regression is new in 8.0 and CPU overhead is the problem
There were many reads from storage for 5.6, 5.7 and 8.0.18, but not 8.0.21 or 8.0.22. That IO was from creating the secondary index which is done after the table has been loaded.
Inserts/second ratio for 5.7.31 is 0.99

Read-only before write-heavy:

QPS ratios are 0.74, 0.71, 0.59, 0.24 for the first 4 tests (here to here)

These do point queries
CPU/query ratios are: 1.36, 1.43, 1.72, 4.06 (here to here)
The large regressions (0.59, 0.24) are from bug 102037 which is new in 8.0.22 and QPS ratios are for 8.0.21 are 0.72, 0.69, 0.73, 0.89
QPS ratios for 5.7.31 are 0.85, 0.87, 0.87, 1.07

QPS ratios are 0.73, 1.01, 1.25 for the next 3 tests (here to here)

These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/query ratios are 1.34, 1.03, 0.83 (here to here).
QPS ratios for 5.7.31 are 0.86, 1.12, 1.46

QPS ratios are 0.59, 0.59 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.72, 1.74 (here to here).
8.0.22 suffers from bug 102037 and QPS ratios for 8.0.21 are 0.75, 0.74.
QPS ratios for 5.7.31 are 0.86, 0.87

QPS ratios are 0.66, 0.65 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.51, 1.53 (here to here)
8.0.22 suffers from bug 102037 and QPS ratios for 8.0.21 are 0.76, 0.74.
QPS ratios for 5.7.31 are 0.88, 0.88

QPS ratios are 0.74, 0.69 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index
CPU/query ratios are 1.35, 1.44 (here to here)
QPS ratios for 5.7.31 are 0.84, 0.85

QPS ratios are 0.77, 0.59 for the next 2 tests (here to here)

These are similar to the previous test but use the secondary index.
CPU/query ratios are 1.32, 1.60 (here to here)
QPS ratios for 5.7.31 are 0.85, 0.71

Write-heavy

QPS ratios are 0.70, 1.61, 0.76, 0.75, 0.75 for the next 5 tests (here to here)

These are update-only
HW efficiency metrics can be confusing (here to here)

CPU/statement ratios for 8.0.22 are 2.75, 1.44, 2.21, 2.24. 2.23 and for 5.7.31 are 1.14, 0.77, 1.01, 1.03, 1.04. There is a big change from 5.7 to 8.0.
wKB/statement ratios have more variance

QPS ratios for 5.7.31 are 0.93, 1.80, 0.96, 0.95, 0.94

QPS ratio is 0.84 for the next test, write-only. See here.

This has the writes from oltp_read_write.lua.
CPU/transaction ratio for 8.0.22 is 2.22 and for 5.7.31 is 1.00. See here.
QPS ratios for 5.7.31 is 1.09

QPS ratios are 0.74, 0.87 for the next two tests, read-write (here to here)

These are the traditional sysbench tests (oltp_read_write.lua) with ranges of size 10 & 100
CPU/transaction ratios are 1.41, 1.24 (here to here)
QPS ratios for 5.7.31 are 0.91, 1.01

Read-only after write-heavy includes tests that were run before write-heavy. The DBMS engines were given a chance to catch up on write back and LSM compaction between the write-heavy tests above and the read-only tests below.

QPS ratio is 0.73, 1.00, 1.25 for the next 3 tests, read-only (here to here)

These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/transaction ratios are 1.37, 1.04, 0.83 (here to here)
QPS ratios for 5.7.31 are 0.88, 1.11, 1.46

QPS ratios are 0.74, 0.71, 0.58, 0.24, 0.49 for the next 5 tests (here to here)

These do a variety of point queries.
CPU/query ratios are 1.38, 1.41, 1.76, 4.05, 1.90 (here to here)
QPS ratios for 8.0.21 are 0.73, 0.70, 0.73, 0.90, 0.63. The large regressions in QPS ratios (0.58, 0.24, 0.49) are from bug 102037 which is new in 8.0.22.
QPS ratios for 5.7.31 are 0.85, 0.87, 0.87, 1.07, 0.85

QPS ratios are 0.59, 0.59 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.72, 1.74 (here to here)
QPS ratios for 8.0.21 are 0.74, 0.74 (bug 102037 hurts 8.0.22)
QPS ratios for 5.7.31 are 0.86, 0.87

QPS ratios are 0.68, 0.66 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.48, 1.55 (here to here)
QPS ratios for 8.0.21 are 0.78, 0.75 (bug 102037 hurts 8.0.22)
QPS ratios for 5.7.31 are 0.89, 0.87

QPS ratios are 0.75, 0.70 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the PK index
CPU/query ratios are 1.29, 1.42 (here to here)
QPS ratios for 5.7.31 are 0.85, 0.85

QPS ratios are 0.77, 0.59 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.31, 1.59 (here to here)
QPS ratios for 5.7.31 are 0.85, 0.72

Insert/delete

QPS ratio is 0.68 for the delete test and 0.68 for the insert test
CPU/statement ratio is 2.34 for delete and 2.92 for insert. The ratios for 5.7.31 are 1.09 and 1.22. Hopefully I can explain this in a few days.
QPS ratios for 5.7.31 are 0.86 for delete and 0.89 for insert

Friday, January 8, 2021

Sysbench: in-memory MyRocks, MySQL 5.6 & 8.0, take 3

This is my third attempt to report in-memory sysbench results for MyRocks in MySQL 5.6.35 and 8.0.17. In my second attempt I changed my.cnf so that both versions used the same value for eq_range_index_dive_limit. In this attempt I make sure the LSM tree shape is in a better shape during the read-only tests. Both attempts resolve some of the regressions between 5.6 and 8.0 reported in my first attempt.

The summary:

QPS for read-only tests in 8.0 is ~10% less than 5.6
QPS for write-only tests in 8.0 is 20% to 30% less than 5.6
QPS for the traditional sysbench test is similar between 5.6 and 8.0
Long range scans are more efficient in 8.0

There might be a take 4 if I have the time to debug the 20% to 30% regression for write-only QPS.

LSM tree shape

RocksDB has constraints on the LSM tree shape -- the memtable should use no more than X MB, L0 should have at most F files and then there is a target size (G_i) for each level (L_i). These are more than just advisory -- writes can be stalled when these constraints are exceeded and the constraints also help RocksDB figure out where next to do compaction and whether to flush the memtable.

In the x6a my.cnf that I have been using the constraints are: 256M memtable, <= 4 files in the L0, 1G in the L1 and each level after L1 is 10X larger than the previous. I also disabled compression and each L0 file should be ~256M.

In my tests either the database is fully cached or all but the max level of the LSM tree is cached. So extra data in the smaller levels leads to more CPU overhead on queries, but not more reads from storage. The read penalty is real for LSM trees, but mostly about CPU despite what you might read on the interwebs.

I run sysbench tests in a sequence and each test runs for a fixed amount of time. For tests that do writes, the write rate isn't fixed so when there is variance in the write rate then the amount of writes done by each test can vary. A side-effect of this is that the LSM tree shape can vary (number of KV pairs in the memtable, number of files in L0, amount of data in Ln (n>1). For the Read-only after write-heavy tests this can have an impact on performance (I might do a post on this soon) and in some tests where I saw regressions with 8.0 the problem was that the L0 had several files for 8.0 while it was empty for the tests with 5.6.

I have seen this problem before and filed a feature request for it. I hope that a future RocksDB, or MyRocks, can be more clever and notice when workloads shift to a read-only or read-mostly phase and make the LSM tree more efficient for reads (shrink/flush the memtable, compact all data from L0 into L1, etc). For now I updated my helper scripts to do that via the following command which is run after the load and then again after the write-heavy tests finish. To be fair, for Postgres and InnoDB at that point my helper scripts prod them to write back dirty pages:

set global rocksdb_force_flush_memtable_and_lzero_now=1

This hasn't been a problem for me with the insert benchmark for two reasons. First, tests that do writes are run for a fixed number of writes rather than a fixed duration. Second, the insert benchmark test steps are either insert-only or inserts+reads. The issue is more likely for read-only test steps.

Results

The tests are in 5 groups based on the sequence in which they are run: load, read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete. Each test is run for 180s at 1, 2 and 3 clients.

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS/TPS that a test gets for 1, 2, and 3 clients. Relative throughput is the QPS/TPS relative to the base case (MySQL 5.6.35). The HW efficiency report has absolute and relative results (base case is MySQL 5.6.35) for CPU and IO per operation.

I use ratios to explain performance. In this case MySQL 5.6.35 is the denominator and the numerator is the value for 8.0.17. A QPS ratio < 1 means the new version is slower. For HW efficiency I use CPU/operation and IO/operation (read & write). For CPU and IO per operation a ratio > 1 means the new version uses more CPU or IO per query.

The results are in github for absolute throughput, relative throughput and HW efficiency. I annotate the relative throughput and HW efficiency results below.

Load:

Inserts/second ratio is 0.83, ~75k/s -> ~62k/s (see here)
CPU/insert ratio is 1.21 and CPU overhead explains the slowdown (see here)

Read-only before write-heavy:

QPS ratios are 0. 90, 0.90, 0.90, 0.89 for the first 4 tests (here to here)

These do point queries
CPU/query ratios are: 1.13, 1.13, 1.13, 1.12 (here to here)

QPS ratios are 0.96, 0.99, 1.13 for the next 3 tests (here to here)

These do the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/query ratios are 1.08, 1.02, 0.92 (here to here). Long scans are better in 8.0 vs 5.6.

QPS ratios are 0.91, 0.89 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.12, 1.14 (here to here).

QPS ratios are 0.92, 0.93 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.08, 1.09 (here to here)

QPS ratios are 0.98, 0.86 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index
CPU/query ratios are 1.07, 1.18 (here to here)

QPS ratios are 0.95, 0.90 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.09, 1.13 (here to here)

Write-heavy

QPS ratios are 0.82, 0.87, 0.83, 0.80, 0.71 for the next 5 tests (here to here)

These are update-only
CPU/statement ratios are 1.37, 1.19, 1.28, 1.27, 1.28 (here to here)

QPS ratio is 0.95 for the next test, write-only. See here.

This has the writes from oltp_read_write.lua.
CPU/transaction ratio is 1.16. See here.

QPS ratios are 1.02, 0.99 for the next two tests, read-write (here to here)

These are the traditional sysbench tests (oltp_read_write.lua) with ranges of size 10 & 100
CPU/transaction ratios are 1.09, 1.01 (here to here)

Read-only after write-heavy includes tests that were run before write-heavy.

QPS ratio is 0.98, 0.99, 1.10 for the next 3 tests, read-only (here to here)

These have the queries (range scans) from oltp_read_write.lua with ranges of size 10, 100 and 10,000. Ratios here are similar to Read-only before write-heavy.
CPU/transaction ratios are 1.06, 1.04, 0.95 (here to here)

QPS ratios are 0.91, 1.01, 0.92, 0.92. 0.87 for the next 5 tests (here to here)

These do a variety of point queries. The first 4 were run in Read-only before write heavy, and ratios here are similar.
CPU/query ratios are 1.15, 1.03, 1.10, 1.09, 1.17 (here to here)

QPS ratios are 0.94, 0.92 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.08, 1.11 (here to here)

QPS ratios are 0.95, 0.94 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.06, 1.07 (here to here)

QPS ratios are 0.94, 0.92 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index
CPU/query ratios are 1.10, 1.05 (here to here)

QPS ratios are 0.86, 0.93 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.13, 1.09 (here to here)

Insert/delete

QPS ratio is 0.82 for the delete test and 0.73 for the insert test
CPU/statement ratio is 1.22 for delete and 1.39 for insert

Tuesday, January 5, 2021

Sysbench: in-memory MyRocks, MySQL 5.6 & 8.0, take 2

As I shared in my previous post, the default value for eq_range_index_dive_limit increased in MySQL 5.7 and that hurts several of the benchmark queries that I use. So I improved the my.cnf, repeated the tests and now get to write another perf report. This has results for in-memory sysbench with MyRocks from MySQL 5.6.35 and 8.0.17.

The summary:

Tests in Read-only before write-heavy benefit from letting post-load compaction finish and the 8.0 results benefit from reducing eq_range_index_dive_limit to 10.
Comparing 5.6.35 and 8.0.17

QPS for Read-only before write-heavy in 8.0 is ~90% to ~100% vs 5.6
QPS for Read-only after write-heavy in 8.0 is ~80% to ~90% vs 5.6. I don't know why fragmentation from writes hurts 8.0 more than 5.6
QPS for write-only in 8.0 is ~70% to ~90% vs 5.6
QPS for read-write in 8.0 is the same vs 5.6 (read-write does range queries that benefits 8.0 and everything else that benefits 5.6)

The regressions from 5.6 to 8.0 are worse for InnoDB with 8.0.22 than MyRocks with 8.0.17. But some of the MyRocks regressions will get worse when it is ported to 8.0.22 and encounters bug 102037.
There is a large regression in the test that does point queries (covered and not covered) using a secondary index for Read-only after write-heavy but not for Read-only before write-heavy. It would be useful to explain this. Search for QPS ratios are 0.52, 0.63 below
Regressions for the Read-only after write-heavy tests might be from non-determinism. I have warned about variance in benchmarks for RocksDB and MyRocks when doing read-only tests. The problem is that the LSM tree isn't guaranteed to be in the same shape when tests are repeated and some shapes are worse than others. I think that was the case here where the LSM tree for 5.6.35 has no data in L0 while the tree for 8.0.17 has much data in L0. Both have data in L1 and L2. Therefore, there is more work for 8.0.17 on queries. See here. There are options to force RocksDB to flush the memtable and L0. I have used them in the past but forgot. Looks like I need to repeat the benchmark so they are used after the write-heavy tests. The option I want is --rocksdb-force-flush-memtable-and-lzero-now. This will get the LSM tree into a more deterministic state.

my.cnf changes

The my.cnf that I use previously included eq_range_index_dive_limit=10. I forgot it was there for a reason and removed it a few weeks ago while testing my.cnf options. That hurt results in my previous tests so I brought it back. I also compared SHOW GLOBAL VARIABLES output between 5.6 and 8.0 and changed a few options to make configurations more similar.

The changes for 8.0 in the cx6a config are:

add eq_range_index_dive_limit=10 as the default is 200
add binlog_checksum=crc32

The changes for 5.6 in the cx6a config are:

eq_range_index_dive_limit=10 to remind myself that this option matters
add binlog_checksum=crc32

Other changes

I made other changes after the first round of tests:

Run for 1, 2 and 3 clients for 180s each. Previously I used 1, 2, 3 and 4 clients at 90s each but my servers have 4 cores and CPU is frequently saturated at 4 clients.
Change helper scripts to use the --random-points option for the covered point & range query tests
Give time to perform all pending writes (buffer pool writeback, LSM compaction) after the initial load and before the read-only queries

Results

The tests are in 5 groups based on the sequence in which they are run: load, read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete.

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS/TPS that a test gets for 1, 2, and 3 clients. Relative throughput is the QPS/TPS relative to the base case (MySQL 5.6.35). The HW efficiency report has absolute and relative results (base case is MySQL 5.6.35) for CPU and IO per operation.

The results are in github for absolute throughput, relative throughput and HW efficiency. I annotate the relative throughput and HW efficiency results below.

Load:

Inserts/second ratio is 0.82, ~75k/s -> ~61k/s (see here and here)
CPU/insert ratio is 1.23 and new CPU overhead explains the slowdown (see here)

Read-only before write-heavy:

QPS ratios are 0.88, 0.89, 0.90, 0.90 for the first 4 tests (here to here)

These do point queries
CPU/query ratios are: 1.16, 1.13, 1.11, 1.12 (here to here)
Writeback was done before these tests started, unlike results in my previous post.

QPS ratios are 0.96, 1.00, 1.14 for the next 3 tests (here to here)

These do the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/query ratios are 1.07, 1.01, 0.91 (here to here). Long scans are better in 8.0 vs 5.6.

QPS ratios are 0.92, 0.90 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.11, 1.10 (here to here).
The in-lists had 100 values vs 10 for the test in the previous post. The 8.0.17 results here benefit from the change to eq_range_index_dive_limit.

QPS ratios are 0.97, 0.95 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.05, 1.06 (here to here)

QPS ratios are 1.01, 0.88 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index. The range size was 100 vs 10 in the previous tests.
CPU/query ratios are 1.02, 1.15 (here to here)

QPS ratios are 1.00, 0.91 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.04, 1.12 (here to here)

Write-heavy

QPS ratios are 0.81, 0.84, 0.80, 0.79, 0.78 for the next 5 tests (here to here)

These are update-only
CPU/statement ratios are 1.35, 1.16, 1.25, 1.33, 1.29 (here to here)

QPS ratio is 0.89 for the next test, write-only. See here.

This has the writes from oltp_read_write.lua.
CPU/transaction ratio is 1.14. See here.

QPS ratios are 0.98, 0.99 for the next two tests, read-write (here to here)

These are the traditional sysbench tests (oltp_read_write.lua) with ranges of size 10 and 100
CPU/transaction ratios are 1.12, 1.05 (here to here)

Read-only after write-heavy includes tests that were run before write-heavy.

QPS ratio is 0.98, 0.95, 1.02 for the next 3 tests, read-only (here to here)

These have the queries (range scans) from oltp_read_write.lua with ranges of size 10, 100 and 10,000. Results here are worse than Read-only before write-heavy.
CPU/transaction ratios are 1.05, 1.09, 1.01 (here to here)

QPS ratios are 0.89, 0.88, 0.87, 0.86, 0.79 for the next 5 tests (here to here)

These do a variety of point queries. The first 4 were run in Read-only before write heavy, and results here are slightly worse.
CPU/query ratios are 1.15, 1.15, 1.17, 1.16, 1.27 (here to here)

QPS ratios are 0.87, 0.86 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the primary index. Results here are worse than Read-only before write-heavy.
CPU/query ratios are 1.16, 1.18 (here to here)

QPS ratios are 0.52, 0.63 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index. Results here are much worse than for Read-only before write-heavy and CPU overhead is the problem.
HW efficiency metrics are interesting (here to here). CPU/query ratios are 1.93, 1.60. IO read (rKB/o) ratios are 2.55 for the covered index test -- both 5.6 and 8.0 are doing a lot of read IO for an index that should be cached.

QPS ratios are 0.74, 0.72 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index. Results here are worse than Read-only before write-heavy.
HW efficiency metrics are interesting (here to here). CPU/query ratios are 1.27, 1.31. Both 5.6 and 8.0 do a lot of read IO (rKB/o) for the covered query for an index that should be cached.

QPS ratios are 0.86, 0.84 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index. Results here are worse than Read-only before write-heavy.
CPU/query ratios are 1.20, 1.20 (here to here)

Insert/delete

QPS ratio is 0.81 for the delete test and 0.73 for the insert test
CPU/statement ratio is 1.20 for delete and 1.37 for insert

Monday, January 4, 2021

Sysbench: in-memory InnoDB, MySQL 5.6 & 8.0, take 2

I discovered one of the reasons for QPS regressions with in-memory Sysbench and MySQL 5.7+ that I explained in a previous post. The default value for eq_range_index_dive_limit was increased from 10 in 5.6 to 200 in 5.7. This option determines the amount of work (index searches) the optimizer might do to estimate predicate selectivity and going from 10 to 200 means up to ~20X more index searches for queries with large in-lists. I have sysbench scripts with queries that have 10, 100, 1000 and 10,000 values in an in-list, so the change in the option value effects the cases with 100, 1000 and 10,000 values.

I am wary of changing the default values for options, but change is inevitable and frequently a good thing. The best way to spot this is to compare output from SHOW GLOBAL VARIABLES. Of course, reading release notes is always a good idea if you have more time.

The summary from my previous post is mostly true here:

QPS in 8.0 is frequently between 60% and 90% of what 5.6 gets because 8.0 uses more CPU
Compared with my previous post, QPS is better here for the covered & not-covered, point & range queries in Read-only before write-heavy and Read-only after write-heavy (thanks to setting eq_range_index_dive_limit=10).
There is a CPU regression new in 8.0.22 that hurts queries with large in-lists (bug 102037)

my.cnf changes

The my.cnf that I use for 5.7 and 8.0 used to have eq_range_index_dive_limit=10. I forgot it was there for a good reason and removed it a few weeks ago while testing my.cnf options. So I compared SHOW GLOBAL VARIABLES output between 5.6, 5.7 and 8.0 and changed a few options to make configurations more similar.

The changes for 8.0 in the cx6d config are:

add eq_range_index_dive_limit=10 as the default is 200
add innodb_buffer_pool_load_at_startup=OFF, innodb_buffer_pool_dump_at_shutdown=OFF. I don't want dump/restore to interfere with my workflow.
innodb_purge_threads=1. My server is small and more threads has hurt perf in the past.
innodb_log_file_size=1G. I use innodb_dedicated_server but still want to guarantee that all versions use a similar number amount of redo log space.

The changes for 5.7 in the cx6d config are:

eq_range_index_dive_limit=10 as the default is 200
innodb_flush_method=O_DIRECT_NO_FSYNC because 8.0 uses this
innodb_autoinc_lock_mode=2 because the default is 1 in 5.7 and 2 in 8.0
innodb_flush_neighbors=0 because the default is 1 in 5.7 and 0 in 8.0
innodb_buffer_pool_load_at_startup=OFF, innodb_buffer_pool_dump_at_shutdown=OFF
innodb_purge_threads=1 to avoid CPU & mutex contention on a small server
innodb_log_file_size=1G to match 8.0 as the default is much smaller

The changes for 5.6 in the cx6d config are:

eq_range_index_dive_limit=10 to remind myself that this option matters
innodb_flush_method=O_DIRECT_NO_FSYNC because 8.0 uses this
innodb_autoinc_lock_mode=2 because the default is 1 in 5.7 and 2 in 8.0
innodb_flush_neighbors=0 because the default is 1 in 5.7 and 0 in 8.0
innodb_checksum_algorithm=crc32 to match 8.0 as the default (innodb) is slower
innodb_file_format=Barracuda, innodb_file_format_max=Barracuda to match 8.0
innodb_log_file_size=1G to match 8.0 as the default is much smaller

Other changes

I made other changes after the first round of tests:

Run for 1, 2 and 3 clients for 180s each. Previously I used 1, 2, 3 and 4 clients at 90s each but my servers have 4 cores and CPU is frequently saturated at 4 clients.
Change helper scripts to use the --random-points option for the covered point & range query tests
Give time to perform all pending writes (buffer pool writeback, LSM compaction) after the initial load and before the read-only queries
Skipped tests for 8.0.17, 8.0.19 and 8.0.20 to save time. I still run for 5.6.49, 5.7.31, 8.0.18, 8.0.21 and 8.0.22

Results

The tests are in 5 groups based on the sequence in which they are run: load, read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete.

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS/TPS that a test gets for 1, 2, and 3 clients. Relative throughput is the QPS/TPS relative to the base case (MySQL 5.6.49). The HW efficiency report has absolute and relative results (base case is MySQL 5.6.49) for CPU and IO per operation.

Inserts/second ratio is 0.72 (~100k/s -> ~70k/s)
CPU/insert ratio is 1.58
The insert/second ratio for 5.7 is 1.07, so loads are faster in 5.7 vs 5.6, and the regression begins in 8.0 and the problem is new CPU overhead
There were significant reads from storage for 5.6, 5.7 and 8.0.18, but not 8.0.21 or 8.0.22. That IO was from creating the secondary index which is done after the table has been loaded, and iostat output shows that is limited to the end of the "prepare" step.

Read-only before write-heavy:

QPS ratios are 0.74, 0.72, 0.59, 0.24 for the first 4 tests (up to here)

These do point queries
CPU/query ratios are: 1.36, 1.40, 1.72, 4.02. Up to here.
The large regressions (0.59, 0.24) are from bug 102037 which is new in 8.0.22. For 8.0.21 the QPS ratios are 0.73, 0.70, 0.74, 0.90.
Writeback was done before these tests started, unlike results in my previous post.

QPS ratios are 0.74, 1.02, 1.26 for the next 3 tests (here to here)

These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/query ratios are 1.35, 1.02, 0.83 (here to here). Long scans are better in 8.0 vs 5.6, but worse in 8.0 vs 5.7.

QPS ratios are 0.59, 0.59 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the PK index
CPU/query ratios are 1.70, 1.73 (here to here).
The in-lists had 100 values vs 10 for the test in the previous post. The 8.0.22 results here suffer more from bug 102037 but benefit from the change to eq_range_index_dive_limit. The QPS ratios for 8.0.21 are 0.75, 0.74.

QPS ratios are 0.66, 0.65 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.53, 1.53 (here to here)
The in-lists had 100 values vs 10 for the test in the previous post. The QPS ratios for 8.0.21 are 0.77, 0.75. See the previous bullet point.

QPS ratios are 0.75, 0.70 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index. The range size was 100 vs 10 in the previous tests.
CPU/query ratios are 1.35, 1.42 (here to here)

QPS ratios are 0.78, 0.59 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.29, 1.59 (here to here)

Write-heavy

QPS ratios are 0.71, 2.10, 0.75, 0.75, 0.75 for the next 5 tests (here to here)

These are update-only
HW efficiency metrics can be confusing (here to here). CPU/statement ratios are 2.72, 1.22, 2.24, 2.25, 2.25 and wKB/statement ratios are 1.95, 0.91, 0.99, 1.00, 1.44.
8.0 is faster than 5.6 for update-index (QPS ratio is 2.10), but slower in the other tests.

QPS ratio is 0.77 for the next test, write-only. See here.

This has the writes from oltp_read_write.lua.
CPU/transaction ratio is 2.31. See here.

QPS ratios are 0.71, 0.86 for the next two tests, read-write (here to here)

These are the traditional sysbench tests (oltp_read_write.lua) with ranges of size 10 and 100.
CPU/transaction ratios are 1.43, 1.26 (here to here)

Read-only after write-heavy includes tests that were run before write-heavy. There is much more write IO activity with 8.0 during these tests compared to 5.6 and 5.7. I have yet to explain this. See the wKB/o columns here. I assume that something has changed with respect to furious flushing.

QPS ratio is 0.75, 1.01, 1.25 for the next 3 tests, read-only (here to here)

These have the queries (range scans) from oltp_read_write.lua with ranges of size 10, 100 and 10,000. Results here are similar to Read-only before write-heavy and 8.0 is better than 5.6 at long range scans, but worse at shorter ones.
CPU/transaction ratios are 1.34, 1.05, 0.83 (here to here)

QPS ratios are 0.75, 0.71, 0.59, 0.24, 0.49 for the next 5 tests (here to here)

These do a variety of point queries. The first 4 were run in Read-only before write heavy, and results here are similar.
CPU/query ratios are 1.36, 1.42, 1.77, 4.07, 1.91 (here to here)
The large regressions in QPS ratios (0.59, 0.24, 0.49) are from bug 102037 which is new in 8.0.22. For 8.0.21 the QPS ratios are 0.74, 0.70, 0.73, 0.90, 0.63.

QPS ratios are 0.59, 0.59 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the primary index. Results here are similar to Read-only before write-heavy.
CPU/query ratios are 1.73, 1.74 (here to here)
For the impact from bug 102037 the QPS ratios for 8.0.21 are 0.75, 0.74

QPS ratios are 0.69, 0.66 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index. Results here are similar to Read-only before write-heavy.
CPU/query ratios are 1.47, 1.54 (here to here).
For the impact from bug 102037 the QPS ratios for 8.0.21 are 0.80, 0.76

QPS ratios are 0.76, 0.71 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index. Results here are similar to Read-only before write-heavy.
CPU/query ratios are 1.34, 1.40 (here to here)
For the impact from bug 102037 the QPS ratios for 8.0.21 are

QPS ratios are 0.78, 0.59 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index. Results here are similar to Read-only before write-heavy.
CPU/query ratios are 1.32, 1.60 (here to here)

Insert/delete

QPS ratio is 0.70 for the delete test and 0.65 for the insert test
CPU/statement ratio is 2.31 for delete and 2.96 for insert

Wednesday, December 30, 2020

Sysbench: IO-bound InnoDB in MySQL 5.6, 5.7 and 8.0

This post has results for InnoDB in MySQL 5.6, 5.7 and 8.0 using an IO-bound & low-concurrency workload via sysbench. The setup is the same as I described for MyRocks and I wrote about in-memory sysbench + InnoDB in a previous post. The goal is to understand where performance changes across releases.

I will repeat a disclaimer from my previous post, these results are from microbenchmarks. They are useful but be careful to assume these results will reproduce for your workload, especially if your queries are more complex than those used by sysbench.

Summary:

The regressions here are not as bad as in the in-memory workload. In many tests below the QPS in 8.0 is better than 5.6 because the new CPU overheads are less significant for IO-bound.
I hope to repeat some of these tests to explain the odd results.
Covered point queries with a secondary index use too much CPU and IO in 8.0. Covered range queries also have regressions in 8.0. But versions of those queries that don't use a covering index don't have a problem in 8.0 for IO-bound, while they did for in-memory.
Some of the write-heavy tests are significantly faster in 8.0

Overview

I tested MySQL versions 5.6.49, 5.7.31, and 8.0.17 through 8.0.22. As much as possible, I used the same my.cnf options for all versions. The my.cnf files are in the per-version directories in github and here is my.cnf for 8.0.22. I used the my.cnf options that were good for the insert benchmark (the cx6d configuration). For 8.0 ssl is disabled and the same charset/collation is used for all versions. Fsync is not done on commit to avoid becoming an fsync latency benchmark.

The in-memory workload used 1 table with 10M rows and each test ran for 90 seconds at 1, 2, 3 and 4 threads. The IO-bound workload used 1 table with 200M rows and each test ran for 300 seconds.

Results

The tests are in 4 groups based on the sequence in which they are run: read-only run before write-heavy, write-heavy, read-only run after write-heavy and insert/delete.

I have scripts that generate 3 summaries -- absolute throughput, relative throughput and HW efficiency. Absolute throughput is the QPS/TPS that a test gets for 1, 2, 3 and 4 clients. Relative throughput is the QPS/TPS relative to the base case (MySQL 5.6.49). The HW efficiency report has absolute and relative results (base case is MySQL 5.6.49) for CPU and IO per operation.

Read-only before write-heavy:

QPS ratios are 1.02, 1.04, 1.10, 1.14 for the first 4 tests (up to here)

These do point queries
CPU/query ratios are: 0.96, 0.93, 0.79, 0.76. Up to here.
The regressions from bug 102037 for the in-memory workload are less of an issue here but the QPS ratio for random-points with range 1000 drops from 1.29 in 8.0.21 to 1.14 here.

QPS ratios are 1.14, 1.18, 1.36 for the next 3 tests (here to here)

These have the range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
CPU/query ratios are 0.89, 0.83, 0.67 (here to here). Long scans are better in 8.0 vs 5.6.

QPS ratios are 1.09, 1.07 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the primary index.
CPU/query ratios are 0.85, 0.88 (here to here).

QPS ratios are 0.50, 1.00 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.95, 0.97 while read IO/query ratios are 2.01, 1.00 (here to here). For the in-memory result the regression was ~2X for both covered and non-covered while here it only occurs for covered. The regression is in 5.7 and 8.0 but only for the covered query while it occurs for both the covered and non-covered queries with the in-memory workload.

QPS ratios are 1.12, 1.25 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index
CPU/query ratios are 0.88, 0.78 (here to here)

QPS ratios are 0.79, 1.00 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.29, 0.95 while read IO/query ratios are 1.27 and 1.00 (here to here)
The in-memory workload has regression for both covered and non-covered, while only covered has a regression here. This is similar to the result above for point-queries using covered and non-covered queries.

Write-heavy

QPS ratios are 1.07, 9.50, 2.04, 0.91, 1.19 for the next 5 tests (here to here)

These are update-only
CPU/statement ratios are 0.87, 0.25, 0.79, 1.94, 0.83. Read IO/query ratios are 1.00, 0.44, 0.95, 1.00, 0.97 (here to here).
The improvement for the 2nd and 3rd tests (update-index, update-nonindex) started in 5.7 and improved more in 8.0.

QPS ratio is 2.37 for the next test, write-only. See here.

This has the writes from oltp_read_write.lua.
CPU/transaction ratio is 0.45 and read IO/query ratio is 0.76. See here.

QPS ratios are 1.18, 0.91 for the next two tests, read-write (here to here)

These are the traditional sysbench tests (oltp_read_write.lua) with ranges of size 10 and 100
CPU/transaction ratios are 0.91, 1.16 (here to here)

Read-only after write-heavy includes tests that were run before write-heavy:

QPS ratio is 0.92, 14.12, 1.36 for the next 3 tests, read-only (here to here)

These have the queries (range scans) from oltp_read_write.lua with ranges of size 10, 100 and 10,000. Results here are similar to Read-only before write-heavy and 8.0 is better than 5.6 at long range scans.
CPU/transaction ratios are 0.79, 0.17, 0.67 (here to here)
The huge speedup (14.12 QPS ratio) for the second test is confusing. It only happens for the 1-thread test and the QPS ratio for 2, 3, and 4 threads is ~2. The problem appears to be CPU/query (5.6 does ~5X more) while IO/query is OK.

QPS ratios are 2.04, 1.71, 4.62, 1.14, 0.50 for the next 5 tests (here to here)

These do a variety of point queries. The first 4 were run in Read-only before write heavy, and results here are similar.
CPU/query ratios are 0.51, 0.70, 0.32, 0.90, 1.89 (here to here) and that explains most of the differences.
The hot-points test doesn't do IO reads as the working set is small. So it still suffers from bug 102037 which is new in 8.0.22

QPS ratios are 1.06, 1.05 for the next 2 tests (here to here)

These do point queries via in-lists that are covering and then not covering for the primary index. Results here are similar to Read-only before write-heavy.
CPU/query ratios are 0.87, 0.90 (here to here)

QPS ratios are 0.50, 1.00 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index.
CPU/query ratios are 1.93, 0.97 while read IO/query ratios are 2.01, 1.00 (here to here). Results here are similar to Read-only before write-heavy.

QPS ratios are 1.16, 1.24 for the next 2 tests (here to here)

These do range queries that are covering and then not covering for the primary index
Results here are similar to Read-only before write-heavy.
CPU/query ratios are 0.84, 0.80 (here to here)

QPS ratios are 0.78, 1.03 for the next 2 tests (here to here)

These are similar to the previous test, but use the secondary index. Results here are similar to Read-only before write-heavy.
CPU/query ratios are 1.31, 0.94 and read IO/query ratios are 1.29 and 1.00 (here to here)

Insert/delete

QPS ratio is 1.80 for the delete test and 0.83 for the insert test
CPU/statement ratio is 0.86 for delete and 2.62 for insert
Read IO/statement ratio is 0.88 for delete and 234.69 for insert. For insert, the read IO ratio was ~2 for 5.7, then grew to ~5 in 8.0.19, then to ~16 for 8.0.20, then to ~200 for 8.0.21 & 8.0.22.