I used perf stat to collect a variety of HW performance counter and will share the results over several posts. I used 4 different servers with 4 different CPUs and there will be a post per CPU. First up is a result from my smallest server that uses a AMD Ryzen 7 4700u CPU.
tl;dr
- MySQL (InnoDB and MyRocks) gets slower over time from both memory system bloat (TLB and cache activity) and code bloat (more instructions/operation). For code bloat, InnoDB in MySQL 8.0.39 uses (1.47, 1.84, 1.39) times more instructions per SQL operation for (scan, insert, point-query) microbenchmarks relative to 5.6.51.
- the regressions in MyRocks and InnoDB are similar so this appears to be independent of the storage engine (perhaps code above the storage engine, perhaps just new code in general)
Updates:
- I corrected how I display instructions in the tables below. Originally, I just used (instructions for my version / instructions for base version) which is the number of instructions per 10-second interval for some version relative to the base version. Now I use (instructions for my version / instructions for base version / relative QPS) which is the number of instructions per operation relative to the base version. With this change it is easy to spot code bloat, that occurs when the value is much greater than 1.0. When the value in the table is 2.0 that means that some version uses 2X more instructions per SQL operation which usually means code bloat, although it can also mean more mutex contention (see the results for insert with MySQL 8.0.11).
- I updated the tables below to indicate cases where regressions from perf HW counters arrive in fbmy5635_rel_o2nofp_231016_4f3a57a1_870. I did not expect this to happen in MyRocks 5.6.35. I do expect it to happen in MyRocks 8.0.
Builds
I compiled upstream MySQL from source for versions 5.6.51, 5.7.10, 5.7.44, 8.0.11, 8.0.28 and 8.0.39.
I compiled FB MySQL from source and tested the following builds:
- fbmy5635_rel_o2nofp_210407_f896415f_6190
- MyRocks 5.6.35 at git sha f896415f (as of 21/04/07) with RocksDB 6.19
- fbmy5635_rel_o2nofp_231016_4f3a57a1_870
- MyRocks 5.6.35 at git sha 4f3a57a1 (as of 23/10/16) with RocksDB 8.7.0
- fbmy8028_rel_o2nofp_220829_a35c8dfe_752
- MyRocks 8.0.28 at git sha a35c8dfe (as of 22/08/29) with RocksDB 7.5.2
- fbmy8028_rel_o2nofp_231202_4edf1eec_870
- MyRocks 8.0.28 at git sha 4edf1eec (as of 23/12/02) with RocksDB 8.7.0
- fbmy8032_rel_o2nofp_231204_e3a854e8_870
- MyRocks 8.0.32 at git sha e3a854e8 (as of 23/12/04) with RocksDB 8.7.0
- fbmy8032_rel_o2nofp_240529_49b37dfe_921
- MyRocks 8.0.32 at git sha 49b37dfe (as of 24/05/29) with RocksDB 9.2.1
The my.cnf files for InnoDB are in the subdirectories here. Given the amount of innovation in MySQL 8.0 I can't use one my.cnf file for all 8.0 versions.
Hardware
The server here is a Beelink SER4 with an AMD Ryzen 7 4700 CPU with SMT disabled, 8 cores, 16G of RAM and Ubuntu 22.04. The storage is 1 NVMe device.
The CPU used here (AMD 4700u) is described as a laptop class CPU. It might be more prone to suffer from code bloat than server CPUs. But have no fear, I will soon share results from two server class CPUs.
Benchmark
I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. The database is cached by MyRocks and InnoDB.
The benchmark is run with 1 thread, 1 table and 30M rows. Each microbenchmark runs for 330 seconds if read-only and 630 seconds otherwise. Prepared statements were enabled.
The command line for my helper scripts was:
bash r.sh 1 30000000 330 630 nvme0n1 1 1 1
Using perf
I used perf stat with code that does:
- Sleep for 30 seconds
- Run perf stat 7 times for 10 seconds at a time. Each run collects different counters - see here.
So it takes ~100 seconds per loop and given that I run the read-heavy tests for ~330 seconds and the write-heavy tests for 630 seconds I collect data from ~3 loops for read-heavy and ~6 loops for write-heavy.
I then compare results across DBMS versions for one (or a few) of the loops and generate two tables per microbenchmark -- one with absolute values, the other with values relative to the base version. The base version for InnoDB is 5.6.51 and for MyRocks is fbmy5635_rel_o2nofp_210407_f896415f_6190. By values relative I mean: (value for my version / value for base version). My focus is to compare MyRocks with MyRocks and InnoDB with InnoDB. I assume that most of the regressions for MyRocks are also in InnoDB and then try to confirm that assumption.
Results
For the results I normally split the 42 microbenchmarks into 5 groups -- 2 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. But I don't do that here because I share 1 (or 2) tables of results from perf stat per microbenchmark and doing that for 42 microbenchmarks is too much.
So I focus on four that I think are representative:
So I focus on four that I think are representative:
- scan - do full tables scans in a loop for ~300 seconds
- insert - do inserts
- point-query - fetch 1 row by exact match on the PK index
- update-index - do updates that require secondary index maintenance
(QPS for my version / QPS for base version)
And the base version is explained above in the Using perf section.
The relative QPS is here for MyRocks. The QPS for the subset of microbenchmarks on which I focus is below. The results for a recent build for FB MyRocks 8.0.32 are in col-5 and the relative QPS values range from 0.56 for insert to 0.75 for update-index. When the relative QPS for insert is 0.56 with a recent MyRocks 8.0.32 build, the insert throughput in that recent build is about half the rate I get with an old build of MyRocks 5.6.35 -- when the workload is CPU bound. I am not happy about that.
Relative to: fbmy5635_rel_o2nofp_210407_f896415f_6190
col-1 : fbmy5635_rel_o2nofp_231016_4f3a57a1_870
col-2 : fbmy8028_rel_o2nofp_220829_a35c8dfe_752
col-3 : fbmy8028_rel_o2nofp_231202_4edf1eec_870
col-4 : fbmy8032_rel_o2nofp_231204_e3a854e8_870
col-5 : fbmy8032_rel_o2nofp_240529_49b37dfe_921
col-1 col-2 col-3 col-4 col-5
0.96 0.73 0.73 0.77 0.73 scan_range=100
0.89 0.63 0.62 0.58 0.56 insert_range=100
0.96 0.75 0.74 0.72 0.73 point-query_range=100
0.99 0.77 0.79 0.77 0.75 update-index_range=100
The relative QPS is here for InnoDB. The QPS for the subset of microbenchmarks on which I focus is below. The results for MySQL 8.0.39 are in col-5 and the relative QPS values range from 0.46 for insert to 1.01 for update-index. Again, the low values (0.46 for insert, 0.66 for point-query, 0.75 for scan) mean that MySQL 8.0.39 is much slower than 5.6.51. Also note that the values in col-5 (8.0.39) are a bit smaller than the values in col-4 (8.0.28), especially for scan. I think this is bug 111538 which might get fixed in the upcoming 8.0.40 release. Even in the one case where 8.0.39 gets more throughput than 5.6.51 (update-index) there is a steady drop in relative QPS from col-2 (5.7.44) through col-5 (8.0.39).
Relative to: my5651_rel_o2nofp
col-1 : my5710_rel_o2nofp
col-2 : my5744_rel_o2nofp
col-3 : my8011_rel_o2nofp
col-4 : my8028_rel_o2nofp
col-5 : my8039_rel_o2nofp
col-1 col-2 col-3 col-4 col-5
1.03 0.93 1.02 0.92 0.75 scan_range=100
0.79 0.75 0.64 0.52 0.46 insert_range=100
0.87 0.85 0.78 0.71 0.66 point-query_range=100
1.16 1.28 1.34 1.17 1.01 update-index_range=100
Explaining regressions
While it would be great if most of the regressions were caused by a small number of diffs, that does not appear to be the case. When I look at QPS across all of the point releases I see a few things:
- larger regressions across major releases (5.6.51 to 5.7.10 and 5.7.44 to 8.0.11)
- many regressions across 8.0 releases (8.0.11 through 8.0.39)
- some regressions across 5.7 releases (5.7.10 through 5.7.44)
- not many regressions across 5.6 releases
At a high level there are two reasons for the regressions and I am working to distinguish between them when I look at results from perf record and perf stat.
- code bloat - more instructions are executed per SQL command
- memory system bloat - more cache and TLB activity per SQL command
With either tool (perf stat, perf record) I run it for a fixed number of seconds while the amount of work (QPS) completed during that interval is not fixed -- for low-concurrency workloads the QPS is larger for older MySQL and smaller for modern MySQL. I must keep this in mind when interpreting the results.
With flamegraphs from perf record a common case is that the distribution of time per function doesn't change that much even when there are large regressions and my hypothesis is that memory system bloat is the problem.
When looking at perf stat output I start with the absolute values for the counters. When they are small then a 10X difference in the value for that counter between old and modern MySQL is frequently not significant as it won't have a large impact on performance. Otherwise, I focus on the relative values for these counters which is (value for my version / value for base version). I then use the old version of MySQL or FB MyRocks as the base version and try to focus on the cases where the relative value is large (perhaps larger than 1.15, as in a 15% increase).
Just as with perf record, the values from perf stat are measured over a fixed number of seconds while the QPS completed during that interval tends to be larger for older MySQL. So I then compute another value which is: (value for my version / value for base version / relative QPS for my version). This is the relative value for a counter per query (query == SQL command) and when that is equal to 1.5 for some version then some version does 1.5X more for that counter per query (1.5X more cache misses, etc).
Just as with perf record, the values from perf stat are measured over a fixed number of seconds while the QPS completed during that interval tends to be larger for older MySQL. So I then compute another value which is: (value for my version / value for base version / relative QPS for my version). This is the relative value for a counter per query (query == SQL command) and when that is equal to 1.5 for some version then some version does 1.5X more for that counter per query (1.5X more cache misses, etc).
While I use the word significant above to mean that the value for some counter is interesting it isn't easy to estimate how much that contributes to a performance regression.
Looking at perf stat output
This spreadsheet has the interesting counters from perf stat for the scan, insert, point-query and update-index microbenchmarks. The set of tables on the left has the absolute values for the counters and on the right are the normalized values -- (value for my version / value for base version / relative QPS).
The amd-rx tab has results for MyRocks and the amd-in tab has results for InnoDB on the server I write about here (the SER4 explained above). And I will inline the tables with normalized values below.
For MyRocks the version names I use below are abbreviated from what I explained above:
- fbmy5635.a - fbmy5635_rel_o2nofp_210407_f896415f_6190
- fbmy5635.b - fbmy5635_rel_o2nofp_231016_4f3a57a1_870
- fbmy8028.a - fbmy8028_rel_o2nofp_220829_a35c8dfe_752
- fbmy8028.b - fbmy8028_rel_o2nofp_231202_4edf1eec_870
- fbmy8032.a - fbmy8032_rel_o2nofp_231204_e3a854e8_870
- fbmy8032.b - fbmy8032_rel_o2nofp_240529_49b37dfe_921
A few more things:
- I use yellow to highlight counters for which there are large increases
- I use red to highlight the values for cpu/o (relative CPU per SQL command) and relative QPS
- values for cycles, GHz, ipc, cpu/o and relative QPS are not normalized as described above. For them the value is computed as: (value for me / value for base version) and I don't divide that by the relative QPS.
The tl;dr here is that MyRocks and InnoDB have similar regressions.
Summary:
- the relative QPS is 0.73 & 0.75 for modern MyRocks & InnoDB so they get ~75% of the throughput vs older MyRocks & InnoDB.
- the CPU overhead per scan (cpu/o) is 1.39X & 1.37X larger in modern MyRocks & InnoDB vs older MyRocks & InnoDB
- in both MyRocks and InnoDB there are large increases for TLB and cache activity. My biggest concern is for L1-icache-loads-misses which is 2.77X & 3.78X larger in modern MyRocks & InnoDB.
- the large values for iTLB-loads and iTLB-load-misses are a concern, but reducing them to near zero (via using huge pages for text) increased QPS on the scan microbenchmark by only ~5% (see here)
- both MyRocks and InnoDB suffer from code bloat because the number of instructions per SQL operation increases by 1.31X & 1.47X in modern MyRocks & InnoDB
- there are a few cases where HW perf counters show a regression in fbmy5635.b and I did not expect that
MyRocks
InnoDB
Results from perf stat: insert
The tl;dr here is that MyRocks and InnoDB have similar regressions.
Summary:
- the relative QPS is 0.55 & 0.46 for modern MyRocks & InnoDB so they get ~50% of the throughput vs older MyRocks & InnoDB.
- the CPU overhead per insert (cpu/o) is 1.64X & 2.24X larger in modern MyRocks & InnoDB vs older MyRocks & InnoDB
- in both MyRocks and InnoDB there are large increases for TLB and cache activity. My biggest concern is for L1-icache-loads-misses which is 1.76X & 2.45X larger in modern MyRocks & InnoDB.
- the large values for iTLB-loads and iTLB-load-misses are a concern, but reducing them to near zero (via using huge pages for text) increased QPS on the scan microbenchmark by only ~5% (see here)
- both MyRocks and InnoDB suffer from code bloat because the number of instructions per SQL operation increases by 2.59X & 1.84X in modern MyRocks & InnoDB
- there are many cases where HW perf counters show a regression in fbmy5635.b and I did not expect that
MyRocks
InnoDB
Results from perf stat: point-query
The tl;dr here is that MyRocks and InnoDB have similar regressions.
Summary:
- the relative QPS is 0.73 & 0.66 for modern MyRocks & InnoDB so they get ~70% of the throughput vs older MyRocks & InnoDB.
- the CPU overhead per query (cpu/o) is 1.39X & 1.55X larger in modern MyRocks & InnoDB vs older MyRocks & InnoDB
- in both MyRocks and InnoDB there are large increases for TLB and cache activity. My biggest concern is for L1-icache-loads-misses which is 1.39X & 1.87X larger in modern MyRocks & InnoDB.
- the large values for iTLB-loads and iTLB-load-misses are a concern, but reducing them to near zero (via using huge pages for text) increased QPS on the scan microbenchmark by only ~5% (see here)
- both MyRocks and InnoDB suffer from code bloat because the number of instructions per SQL operation increases by 1.18X & 1.39X in modern MyRocks & InnoDB
MyRocks
InnoDB
Results from perf stat: update-index
The tl;dr here is that MyRocks and InnoDB have similar regressions.
Summary:
- the relative QPS is 0.75 & 1.01 for modern MyRocks & InnoDB so MyRocks gets slower over time while InnoDB in 8.0.39 and 5.6.51 have similar perf but InnoDB has been getting slower since 5.7.10
- the CPU overhead per operation (cpu/o) is 1.34X larger modern MyRocks vs older MyRocks but smaller in modern InnoDB vs older InnoDB
- in both MyRocks and InnoDB there are large increases for TLB and cache activity but L1-icache-loads-misses are less of an issue here than elsewhere
- the large values for iTLB-loads and iTLB-load-misses are a concern, but reducing them to near zero (via using huge pages for text) increased QPS on the scan microbenchmark by only ~5% (see here)
- MyRocks suffers from code bloat because the number of instructions per SQL operation increases by 1.45X. InnoDB does better in 8.0.39 than 5.6.51.
- there is one case where HW perf counters show a regression in fbmy5635.b and I did not expect that
MyRocks
InnoDB
%20_%20(QPS%20for%208.0.34).png)
No comments:
Post a Comment