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.
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:
Load:
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)
Write-heavy
- QPS ratios are 0.81, 0.84, 0.80, 0.79, 0.78 for the next 5 tests (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)
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)
Insert/delete
%20_%20(QPS%20for%208.0.34).png)
No comments:
Post a Comment