Monday, October 14, 2024

I recently published results with a summary of HW performance counters for sysbench with MySQL on four CPU types. The performance reports were published over several weeks and to make it easier to see the impact of performance regressions over time, this blog post has the relative QPS for all of the microbenchmarks. Having all of the numbers in one place makes it easier for me to spot things to investigate, like the drop in throughput on the AMD Threadripper for the update-index microbenchmark.

The blog posts with details on the HW perf counters are:

How I use sysbench is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. The database is cached by MyRocks and InnoDB.

For each microbenchmark I continue to use relative QPS (rQPS) which is the following, the and base version is named in each of the seconds that follow:
     (QPS for my version / QPS for base version)

tl;dr

  • MySQL has large perf regressions from 5.6 to 8.0 for low-concurrency workloads
  • MySQL doesn't have large perf regressions from 5.6 to 8.0 for high-concurrency workloads, but performance in 8.0 would be a lot better if the CPU regressions were fixed. With 8.0 we benefit from many improvements that reduce mutex contention but a lot of the gains are cancelled out by CPU regressions.

Ryzen 7 4700u

Tests were run with 1 thread, 1 tables and 30M rows. The numbers are the relative QPS which is explained above and the base version is MyRocks 5.6.35 at git sha f89641gf with RocksDB 6.19 for MyRocks and MySQL 5.6.51 for InnoDB.

MyRocks

Relative to: x.fbmy5635_rel_o2nofp_210407_f896415f_6190.za1_bee.pk1
col-1 : x.fbmy5635_rel_o2nofp_231016_4f3a57a1_870.za1_bee.pk1
col-2 : x.fbmy8028_rel_o2nofp_220829_a35c8dfe_752.za1_bee.pk1
col-3 : x.fbmy8028_rel_o2nofp_231202_4edf1eec_870.za1_bee.pk1
col-4 : x.fbmy8032_rel_o2nofp_231204_e3a854e8_870.za1_bee.pk1
col-5 : x.fbmy8032_rel_o2nofp_240529_49b37dfe_921.za1_bee.pk1

col-1 col-2 col-3 col-4 col-5
0.90 0.72 0.72 0.80 0.84 hot-points_range=100
0.95 0.77 0.74 0.73 0.73 point-query.pre_range=100
0.96 0.75 0.74 0.72 0.73 point-query_range=100
0.99 0.81 0.82 0.88 0.88 points-covered-pk.pre_range=100
0.98 0.78 0.83 0.89 0.89 points-covered-pk_range=100
1.28 0.99 1.10 1.20 1.17 points-covered-si.pre_range=100
1.33 0.92 1.24 1.63 1.60 points-covered-si_range=100
0.97 0.81 0.81 0.87 0.87 points-notcovered-pk.pre_range=100
0.96 0.78 0.83 0.88 0.89 points-notcovered-pk_range=100
1.13 0.98 1.05 1.10 1.09 points-notcovered-si.pre_range=100
1.18 0.90 1.13 1.35 1.34 points-notcovered-si_range=100
0.95 0.44 0.44 0.87 0.86 random-points.pre_range=1000
0.97 0.81 0.82 0.88 0.88 random-points.pre_range=100
0.96 0.87 0.87 0.87 0.87 random-points.pre_range=10
0.97 0.45 0.47 0.90 0.90 random-points_range=1000
0.96 0.78 0.83 0.89 0.89 random-points_range=100
0.97 0.83 0.87 0.86 0.87 random-points_range=10
0.93 0.77 0.77 0.75 0.75 range-covered-pk.pre_range=100
0.99 0.76 0.80 0.78 0.78 range-covered-pk_range=100
0.95 0.78 0.76 0.75 0.75 range-covered-si.pre_range=100
0.94 0.77 0.78 0.81 0.78 range-covered-si_range=100
0.84 0.74 0.73 0.72 0.70 range-notcovered-pk.pre_range=100
0.93 0.74 0.76 0.77 0.75 range-notcovered-pk_range=100
0.95 0.90 0.91 0.91 0.90 range-notcovered-si.pre_range=100
0.95 0.85 0.92 0.93 0.93 range-notcovered-si_range=100
0.87 0.86 0.84 0.84 0.83 read-only.pre_range=10000
0.93 0.82 0.81 0.79 0.78 read-only.pre_range=100
0.97 0.79 0.81 0.78 0.77 read-only.pre_range=10
0.90 0.87 0.85 0.85 0.85 read-only_range=10000
0.94 0.78 0.82 0.80 0.79 read-only_range=100
0.97 0.77 0.80 0.80 0.77 read-only_range=10
0.96 0.73 0.73 0.77 0.73 scan_range=100
0.98 0.81 0.80 0.76 0.76 delete_range=100
0.89 0.63 0.62 0.58 0.56 insert_range=100
0.94 0.83 0.83 0.81 0.80 read-write_range=100
0.96 0.81 0.81 0.78 0.78 read-write_range=10
0.99 0.77 0.79 0.77 0.75 update-index_range=100
1.10 0.92 0.94 0.91 0.90 update-inlist_range=100
0.98 0.78 0.79 0.75 0.74 update-nonindex_range=100
0.92 0.76 0.76 0.72 0.70 update-one_range=100
0.98 0.78 0.79 0.75 0.74 update-zipf_range=100
1.04 0.88 0.90 0.84 0.84 write-only_range=10000

InnoDB

Relative to: x.my5651_rel_o2nofp.z11a_bee.pk1
col-1 : x.my5710_rel_o2nofp.z11a_bee.pk1
col-2 : x.my5744_rel_o2nofp.z11a_bee.pk1
col-3 : x.my8011_rel_o2nofp.z11a_bee.pk1
col-4 : x.my8028_rel_o2nofp.z11a_bee.pk1
col-5 : x.my8039_rel_o2nofp.z11a_bee.pk1

col-1 col-2 col-3 col-4 col-5
0.79 0.85 0.76 0.58 0.63 hot-points_range=100
0.87 0.86 0.78 0.71 0.66 point-query.pre_range=100
0.87 0.85 0.78 0.71 0.66 point-query_range=100
0.82 0.86 0.80 0.65 0.68 points-covered-pk.pre_range=100
0.82 0.85 0.80 0.66 0.68 points-covered-pk_range=100
0.86 0.89 0.85 0.72 0.76 points-covered-si.pre_range=100
0.87 0.89 0.86 0.73 0.76 points-covered-si_range=100
0.82 0.85 0.79 0.66 0.68 points-notcovered-pk.pre_range=100
0.82 0.86 0.79 0.66 0.68 points-notcovered-pk_range=100
0.83 0.86 0.81 0.73 0.72 points-notcovered-si.pre_range=100
0.83 0.86 0.81 0.74 0.73 points-notcovered-si_range=100
0.92 0.97 0.91 0.34 0.78 random-points.pre_range=1000
0.82 0.85 0.79 0.65 0.68 random-points.pre_range=100
0.79 0.78 0.72 0.64 0.61 random-points.pre_range=10
0.92 0.98 0.91 0.34 0.79 random-points_range=1000
0.82 0.85 0.79 0.66 0.68 random-points_range=100
0.79 0.78 0.72 0.64 0.61 random-points_range=10
0.85 0.80 0.82 0.70 0.65 range-covered-pk.pre_range=100
0.85 0.80 0.82 0.70 0.65 range-covered-pk_range=100
0.84 0.80 0.82 0.70 0.66 range-covered-si.pre_range=100
0.83 0.80 0.82 0.70 0.66 range-covered-si_range=100
0.82 0.81 0.74 0.69 0.65 range-notcovered-pk.pre_range=100
0.82 0.80 0.74 0.69 0.66 range-notcovered-pk_range=100
0.81 0.83 0.77 0.75 0.69 range-notcovered-si.pre_range=100
0.81 0.83 0.77 0.75 0.69 range-notcovered-si_range=100
1.24 1.21 1.13 1.03 0.98 read-only.pre_range=10000
0.97 0.95 0.90 0.81 0.74 read-only.pre_range=100
0.85 0.84 0.78 0.70 0.64 read-only.pre_range=10
1.25 1.22 1.14 1.03 0.98 read-only_range=10000
0.97 0.97 0.90 0.81 0.75 read-only_range=100
0.86 0.85 0.79 0.70 0.65 read-only_range=10
1.03 0.93 1.02 0.92 0.75 scan_range=100
0.79 0.75 0.68 0.56 0.52 delete_range=100
0.79 0.75 0.64 0.52 0.46 insert_range=100
0.97 0.97 0.87 0.79 0.73 read-write_range=100
0.87 0.87 0.77 0.69 0.63 read-write_range=10
1.16 1.28 1.34 1.17 1.01 update-index_range=100
0.80 0.96 0.81 0.71 0.63 update-inlist_range=100
0.82 0.81 0.70 0.60 0.54 update-nonindex_range=100
0.80 0.78 0.67 0.58 0.53 update-one_range=100
0.82 0.81 0.70 0.59 0.54 update-zipf_range=100
1.00 1.00 0.88 0.75 0.66 write-only_range=10000

Ryzen 7 7735HS

Tests were run with 1 thread, 1 tables and 50M rows. The numbers are the relative QPS which is explained above and the base version is MyRocks 5.6.35 at git sha f89641gf with RocksDB 6.19 for MyRocks and MySQL 5.6.51 for InnoDB.

MyRocks

Relative to: x.fbmy5635_rel_o2nofp_210407_f896415f_6190.za1_c8r32.pk1
col-1 : x.fbmy5635_rel_o2nofp_231016_4f3a57a1_870.za1_c8r32.pk1
col-2 : x.fbmy8028_rel_o2nofp_220829_a35c8dfe_752.za1_c8r32.pk1
col-3 : x.fbmy8028_rel_o2nofp_231202_4edf1eec_870.za1_c8r32.pk1
col-4 : x.fbmy8032_rel_o2nofp_231204_e3a854e8_870.za1_c8r32.pk1
col-5 : x.fbmy8032_rel_o2nofp_240529_49b37dfe_921.za1_c8r32.pk1

col-1 col-2 col-3 col-4 col-5
1.06 0.76 0.71 0.89 0.89 hot-points_range=100
0.94 0.83 0.82 0.78 0.78 point-query.pre_range=100
0.95 0.83 0.83 0.80 0.79 point-query_range=100
0.96 0.85 0.72 0.88 0.88 points-covered-pk.pre_range=100
1.04 0.92 0.73 0.94 0.94 points-covered-pk_range=100
1.83 1.56 1.44 1.71 1.68 points-covered-si.pre_range=100
3.43 2.60 1.74 2.42 2.19 points-covered-si_range=100
0.95 0.87 0.69 0.88 0.88 points-notcovered-pk.pre_range=100
1.02 0.93 0.73 0.92 0.93 points-notcovered-pk_range=100
1.23 1.35 1.11 1.18 1.17 points-notcovered-si.pre_range=100
2.07 2.03 1.50 1.77 1.73 points-notcovered-si_range=100
0.95 0.45 0.41 0.89 0.88 random-points.pre_range=1000
0.94 0.87 0.69 0.88 0.87 random-points.pre_range=100
0.95 0.97 0.92 0.91 0.91 random-points.pre_range=10
1.03 0.52 0.49 0.96 0.96 random-points_range=1000
1.01 0.93 0.73 0.90 0.91 random-points_range=100
0.99 1.01 0.95 0.94 0.93 random-points_range=10
0.95 0.88 0.87 0.86 0.83 range-covered-pk.pre_range=100
1.02 0.92 0.93 0.92 0.90 range-covered-pk_range=100
0.96 0.87 0.86 0.85 0.80 range-covered-si.pre_range=100
1.08 0.96 0.91 0.94 0.89 range-covered-si_range=100
0.89 0.87 0.84 0.84 0.82 range-notcovered-pk.pre_range=100
0.98 0.92 0.92 0.92 0.89 range-notcovered-pk_range=100
0.94 1.05 0.91 0.91 0.89 range-notcovered-si.pre_range=100
1.06 1.16 0.99 1.00 0.99 range-notcovered-si_range=100
0.90 0.91 0.88 0.90 0.87 read-only.pre_range=10000
0.93 0.91 0.89 0.88 0.86 read-only.pre_range=100
0.95 0.89 0.88 0.86 0.85 read-only.pre_range=10
0.93 0.89 0.90 0.92 0.89 read-only_range=10000
0.98 0.94 0.93 0.92 0.91 read-only_range=100
1.00 0.92 0.93 0.90 0.89 read-only_range=10
0.98 0.73 0.78 0.78 0.75 scan_range=100
0.94 1.02 0.83 0.80 0.80 delete_range=100
0.94 0.69 0.68 0.64 0.64 insert_range=100
0.96 0.93 0.90 0.88 0.87 read-write_range=100
0.96 0.92 0.88 0.86 0.85 read-write_range=10
0.96 0.86 0.84 0.81 0.81 update-index_range=100
0.95 0.86 0.80 0.79 0.79 update-inlist_range=100
0.95 0.85 0.82 0.80 0.79 update-nonindex_range=100
0.94 0.82 0.80 0.77 0.76 update-one_range=100
0.95 0.85 0.82 0.79 0.79 update-zipf_range=100
0.96 0.87 0.85 0.81 0.81 write-only_range=10000

InnoDB

Relative to: x.my5651_rel_o2nofp.z11a_c8r32.pk1
col-1 : x.my5710_rel_o2nofp.z11a_c8r32.pk1
col-2 : x.my5744_rel_o2nofp.z11a_c8r32.pk1
col-3 : x.my8011_rel_o2nofp.z11a_c8r32.pk1
col-4 : x.my8028_rel_o2nofp.z11a_c8r32.pk1
col-5 : x.my8039_rel_o2nofp.z11a_c8r32.pk1

col-1 col-2 col-3 col-4 col-5
0.85 0.87 0.82 0.60 0.68 hot-points_range=100
0.92 0.87 0.81 0.73 0.70 point-query.pre_range=100
0.92 0.87 0.80 0.74 0.70 point-query_range=100
0.91 0.87 0.89 0.68 0.76 points-covered-pk.pre_range=100
0.91 0.87 0.90 0.68 0.76 points-covered-pk_range=100
0.97 0.91 0.94 0.75 0.85 points-covered-si.pre_range=100
0.98 0.91 0.95 0.76 0.86 points-covered-si_range=100
0.93 0.88 0.89 0.68 0.77 points-notcovered-pk.pre_range=100
0.93 0.89 0.89 0.69 0.77 points-notcovered-pk_range=100
0.96 0.91 0.93 0.67 0.84 points-notcovered-si.pre_range=100
0.95 0.90 0.91 0.66 0.84 points-notcovered-si_range=100
1.06 1.02 1.05 0.38 0.92 random-points.pre_range=1000
0.94 0.89 0.89 0.69 0.78 random-points.pre_range=100
0.91 0.85 0.81 0.74 0.71 random-points.pre_range=10
1.07 1.03 1.05 0.37 0.92 random-points_range=1000
0.94 0.89 0.90 0.70 0.78 random-points_range=100
0.92 0.85 0.82 0.75 0.72 random-points_range=10
0.90 0.83 0.83 0.73 0.69 range-covered-pk.pre_range=100
0.90 0.83 0.83 0.73 0.69 range-covered-pk_range=100
0.89 0.82 0.82 0.75 0.69 range-covered-si.pre_range=100
0.88 0.83 0.81 0.75 0.69 range-covered-si_range=100
0.91 0.85 0.78 0.72 0.68 range-notcovered-pk.pre_range=100
0.91 0.84 0.78 0.72 0.68 range-notcovered-pk_range=100
0.92 0.88 0.88 0.77 0.77 range-notcovered-si.pre_range=100
0.92 0.88 0.88 0.78 0.77 range-notcovered-si_range=100
1.29 1.23 1.18 1.02 0.99 read-only.pre_range=10000
1.04 0.99 0.95 0.88 0.83 read-only.pre_range=100
0.95 0.90 0.86 0.79 0.75 read-only.pre_range=10
1.28 1.23 1.19 1.01 0.99 read-only_range=10000
1.04 0.99 0.95 0.87 0.83 read-only_range=100
0.96 0.92 0.87 0.80 0.76 read-only_range=10
0.98 0.89 0.97 0.84 0.71 scan_range=100
1.16 0.84 1.01 0.67 0.79 delete_range=100
0.89 0.82 0.74 0.63 0.58 insert_range=100
1.03 0.98 0.93 0.84 0.80 read-write_range=100
0.96 0.91 0.86 0.78 0.75 read-write_range=10
1.57 1.17 1.57 1.12 1.19 update-index_range=100
0.95 1.05 0.95 0.83 0.80 update-inlist_range=100
0.89 0.86 0.80 0.67 0.64 update-nonindex_range=100
0.90 0.86 0.81 0.68 0.64 update-one_range=100
0.90 0.87 0.81 0.68 0.64 update-zipf_range=100
1.02 0.93 0.90 0.78 0.75 write-only_range=10000

Intel Xeon Silver 4214R

Tests were run with 16 threads, 8 tables and 10M rows per table. The numbers are the relative QPS which is explained above and the base version is MyRocks 5.6.35 at git sha f89641gf with RocksDB 6.19 for MyRocks and MySQL 5.6.51 for InnoDB.

MyRocks

Relative to: x.fbmy5635_rel_o2nofp_210407_f896415f_6190.za1_c24r64.pk1
col-1 : x.fbmy5635_rel_o2nofp_231016_4f3a57a1_870.za1_c24r64.pk1
col-2 : x.fbmy8028_rel_o2nofp_220829_a35c8dfe_752.za1_c24r64.pk1
col-3 : x.fbmy8028_rel_o2nofp_231202_4edf1eec_870.za1_c24r64.pk1
col-4 : x.fbmy8032_rel_o2nofp_231204_e3a854e8_870.za1_c24r64.pk1
col-5 : x.fbmy8032_rel_o2nofp_240529_49b37dfe_921.za1_c24r64.pk1

col-1 col-2 col-3 col-4 col-5
1.02 0.91 0.96 0.89 0.94 hot-points_range=100
0.97 0.90 0.90 0.87 0.88 point-query.pre_range=100
0.99 0.91 0.91 0.88 0.89 point-query_range=100
0.95 0.84 0.85 0.90 0.89 points-covered-pk.pre_range=100
0.96 0.89 0.94 0.92 0.90 points-covered-pk_range=100
1.25 1.07 1.18 1.27 1.26 points-covered-si.pre_range=100
1.40 1.28 1.37 1.34 1.38 points-covered-si_range=100
0.94 0.84 0.85 0.89 0.89 points-notcovered-pk.pre_range=100
0.96 0.89 0.93 0.92 0.90 points-notcovered-pk_range=100
1.07 1.01 1.05 1.09 1.09 points-notcovered-si.pre_range=100
1.20 1.14 1.20 1.16 1.18 points-notcovered-si_range=100
0.94 0.52 0.53 0.92 0.92 random-points.pre_range=1000
0.94 0.84 0.84 0.90 0.88 random-points.pre_range=100
0.96 1.06 1.08 1.06 1.06 random-points.pre_range=10
0.94 0.62 0.64 0.92 0.90 random-points_range=1000
0.95 0.89 0.93 0.92 0.90 random-points_range=100
0.99 1.06 1.11 1.06 1.05 random-points_range=10
0.95 0.96 0.99 0.98 0.97 range-covered-pk.pre_range=100
1.00 1.00 1.04 1.02 1.00 range-covered-pk_range=100
0.96 0.97 1.01 1.00 0.98 range-covered-si.pre_range=100
1.01 1.02 1.05 1.04 1.02 range-covered-si_range=100
0.90 0.92 0.93 0.95 0.91 range-notcovered-pk.pre_range=100
0.94 0.95 0.97 0.98 0.94 range-notcovered-pk_range=100
0.93 0.93 0.93 0.92 0.93 range-notcovered-si.pre_range=100
0.96 0.96 1.00 0.95 0.93 range-notcovered-si_range=100
0.92 0.98 0.97 0.99 0.96 read-only.pre_range=10000
0.95 0.98 1.00 1.00 0.98 read-only.pre_range=100
0.97 0.97 1.00 0.99 0.98 read-only.pre_range=10
0.92 0.98 0.98 0.99 0.96 read-only_range=10000
0.98 1.01 1.03 1.01 1.00 read-only_range=100
1.01 1.02 1.05 1.02 1.02 read-only_range=10
0.94 0.69 0.77 0.77 0.70 scan_range=100
0.94 0.98 0.94 0.90 0.79 delete_range=100
0.95 0.84 0.83 0.81 0.79 insert_range=100
0.96 0.99 1.00 0.98 0.97 read-write_range=100
0.97 0.99 0.99 0.97 0.96 read-write_range=10
0.96 0.97 0.94 0.92 0.91 update-index_range=100
0.99 0.91 0.95 0.94 0.91 update-inlist_range=100
0.95 0.95 0.92 0.91 0.89 update-nonindex_range=100
0.94 0.99 0.96 0.93 0.92 update-one_range=100
0.95 0.96 0.93 0.91 0.90 update-zipf_range=100
0.96 1.01 0.99 0.97 0.95 write-only_range=10000

InnoDB

Relative to: x.my5651_rel_o2nofp.z11a_c24r64.pk1
col-1 : x.my5710_rel_o2nofp.z11a_c24r64.pk1
col-2 : x.my5744_rel_o2nofp.z11a_c24r64.pk1
col-3 : x.my8011_rel_o2nofp.z11a_c24r64.pk1
col-4 : x.my8028_rel_o2nofp.z11a_c24r64.pk1
col-5 : x.my8039_rel_o2nofp.z11a_c24r64.pk1

col-1 col-2 col-3 col-4 col-5
1.47 1.48 1.45 1.16 1.16 hot-points_range=100
0.99 0.97 0.93 0.87 0.84 point-query.pre_range=100
0.99 0.96 0.92 0.88 0.83 point-query_range=100
1.17 1.25 1.16 0.95 0.97 points-covered-pk.pre_range=100
1.16 1.22 1.15 0.93 0.96 points-covered-pk_range=100
0.87 0.89 0.85 0.75 0.75 points-covered-si.pre_range=100
0.88 0.89 0.87 0.75 0.77 points-covered-si_range=100
1.16 1.22 1.13 0.94 0.95 points-notcovered-pk.pre_range=100
1.14 1.19 1.12 0.92 0.93 points-notcovered-pk_range=100
0.86 0.88 0.84 0.75 0.74 points-notcovered-si.pre_range=100
0.88 0.88 0.86 0.76 0.74 points-notcovered-si_range=100
0.97 1.29 1.18 0.57 1.02 random-points.pre_range=1000
1.15 1.21 1.10 0.92 0.93 random-points.pre_range=100
0.97 0.97 0.90 0.82 0.77 random-points.pre_range=10
1.16 1.27 1.22 0.57 1.03 random-points_range=1000
1.14 1.19 1.12 0.92 0.94 random-points_range=100
0.97 0.96 0.89 0.82 0.77 random-points_range=10
0.91 0.82 0.87 0.77 0.73 range-covered-pk.pre_range=100
0.91 0.82 0.87 0.77 0.73 range-covered-pk_range=100
0.91 0.82 0.89 0.80 0.75 range-covered-si.pre_range=100
0.91 0.82 0.88 0.80 0.75 range-covered-si_range=100
0.90 0.82 0.81 0.75 0.70 range-notcovered-pk.pre_range=100
0.90 0.82 0.81 0.75 0.70 range-notcovered-pk_range=100
0.87 0.85 0.84 0.76 0.71 range-notcovered-si.pre_range=100
0.88 0.84 0.83 0.76 0.70 range-notcovered-si_range=100
1.22 1.28 1.22 1.11 0.89 read-only.pre_range=10000
1.15 1.11 1.08 1.01 0.96 read-only.pre_range=100
1.04 1.03 0.98 0.93 0.89 read-only.pre_range=10
1.35 1.27 1.24 1.11 1.07 read-only_range=10000
1.15 1.12 1.08 1.02 0.96 read-only_range=100
1.04 1.02 0.97 0.93 0.88 read-only_range=10
0.93 0.81 0.98 0.88 0.69 scan_range=100
1.20 1.23 1.17 1.18 1.12 delete_range=100
1.25 1.21 1.26 1.17 1.11 insert_range=100
1.15 1.18 1.06 1.05 0.99 read-write_range=100
1.09 1.14 0.99 1.01 0.95 read-write_range=10
5.18 5.55 3.88 4.06 3.61 update-index_range=100
1.33 1.55 1.64 1.65 1.62 update-inlist_range=100
1.24 1.25 1.27 1.21 1.16 update-nonindex_range=100
1.02 1.04 1.05 1.03 0.97 update-one_range=100
1.25 1.28 1.30 1.23 1.19 update-zipf_range=100
1.32 1.35 1.17 1.21 1.09 write-only_range=10000

AMD Threadripper Pro 5975WX

Tests were run with 24 threads, 8 tables and 10M rows per table. The numbers are the relative QPS which is explained above and the base version is MyRocks 5.6.35 at git sha f89641gf with RocksDB 6.19 for MyRocks and MySQL 5.6.51 for InnoDB.

MyRocks

Relative to: x.fbmy5635_rel_o2nofp_210407_f896415f_6190.za1_c32r128.pk1
col-1 : x.fbmy5635_rel_o2nofp_231016_4f3a57a1_870.za1_c32r128.pk1
col-2 : x.fbmy8028_rel_o2nofp_220829_a35c8dfe_752.za1_c32r128.pk1
col-3 : x.fbmy8028_rel_o2nofp_231202_4edf1eec_870.za1_c32r128.pk1
col-4 : x.fbmy8032_rel_o2nofp_231204_e3a854e8_870.za1_c32r128.pk1
col-5 : x.fbmy8032_rel_o2nofp_240529_49b37dfe_921.za1_c32r128.pk1

col-1 col-2 col-3 col-4 col-5
0.80 0.83 0.76 0.92 0.88 hot-points_range=100
0.97 0.88 0.88 0.85 0.84 point-query.pre_range=100
0.97 0.87 0.87 0.85 0.85 point-query_range=100
0.97 0.88 0.86 0.97 0.96 points-covered-pk.pre_range=100
0.96 0.88 0.88 0.94 1.00 points-covered-pk_range=100
1.29 1.15 1.20 1.38 1.33 points-covered-si.pre_range=100
1.30 0.94 1.16 1.26 1.23 points-covered-si_range=100
0.96 0.88 0.85 0.96 0.94 points-notcovered-pk.pre_range=100
0.96 0.89 0.88 0.94 1.00 points-notcovered-pk_range=100
1.10 1.05 1.07 1.15 1.14 points-notcovered-si.pre_range=100
1.14 0.91 1.05 1.11 1.08 points-notcovered-si_range=100
0.94 0.52 0.53 0.93 0.91 random-points.pre_range=1000
0.96 0.88 0.86 0.96 0.94 random-points.pre_range=100
0.97 1.18 1.19 1.18 1.15 random-points.pre_range=10
0.96 0.65 0.67 0.92 1.00 random-points_range=1000
0.96 0.89 0.88 0.94 1.00 random-points_range=100
0.98 1.12 1.15 1.15 1.17 random-points_range=10
0.97 1.08 1.11 1.10 1.06 range-covered-pk.pre_range=100
0.97 1.02 1.09 1.08 1.02 range-covered-pk_range=100
0.97 1.11 1.14 1.12 1.06 range-covered-si.pre_range=100
0.97 1.05 1.11 1.09 1.05 range-covered-si_range=100
0.91 1.04 1.05 1.03 1.02 range-notcovered-pk.pre_range=100
0.92 0.99 1.05 1.02 1.00 range-notcovered-pk_range=100
0.94 0.96 0.95 0.96 0.93 range-notcovered-si.pre_range=100
0.96 0.93 0.94 0.95 1.01 range-notcovered-si_range=100
0.98 1.00 0.98 0.97 0.97 read-only.pre_range=10000
0.96 1.01 1.01 0.99 0.98 read-only.pre_range=100
0.98 1.01 1.02 0.99 0.98 read-only.pre_range=10
0.98 0.96 0.95 0.96 0.93 read-only_range=10000
0.95 0.99 1.01 1.00 0.99 read-only_range=100
0.97 0.99 1.02 1.00 1.00 read-only_range=10
0.97 0.64 0.71 0.62 0.60 scan_range=100
0.96 0.97 0.94 0.92 0.85 delete_range=100
0.96 0.83 0.80 0.78 0.77 insert_range=100
0.97 1.02 1.01 0.99 0.98 read-write_range=100
0.97 1.01 1.01 0.98 0.99 read-write_range=10
0.98 0.97 0.94 0.92 0.92 update-index_range=100
1.04 1.01 0.97 0.96 0.95 update-inlist_range=100
0.97 0.96 0.93 0.90 0.90 update-nonindex_range=100
0.92 0.98 0.94 0.92 0.92 update-one_range=100
0.97 0.96 0.93 0.90 0.90 update-zipf_range=100
0.98 1.02 1.00 0.97 0.96 write-only_range=10000

InnoDB

Relative to: x.my5651_rel_o2nofp.z11a_c32r128.pk1
col-1 : x.my5710_rel_o2nofp.z11a_c32r128.pk1
col-2 : x.my5744_rel_o2nofp.z11a_c32r128.pk1
col-3 : x.my8011_rel_o2nofp.z11a_c32r128.pk1
col-4 : x.my8028_rel_o2nofp.z11a_c32r128.pk1
col-5 : x.my8039_rel_o2nofp.z11a_c32r128.pk1

col-1 col-2 col-3 col-4 col-5
2.24 2.22 2.08 1.60 1.64 hot-points_range=100
1.03 1.00 0.93 0.88 0.82 point-query.pre_range=100
1.04 1.01 0.94 0.89 0.83 point-query_range=100
1.71 1.77 1.66 1.35 1.37 points-covered-pk.pre_range=100
1.71 1.77 1.66 1.36 1.39 points-covered-pk_range=100
0.88 0.89 0.84 0.73 0.73 points-covered-si.pre_range=100
0.88 0.89 0.84 0.73 0.74 points-covered-si_range=100
1.66 1.74 1.61 1.33 1.35 points-notcovered-pk.pre_range=100
1.66 1.74 1.62 1.34 1.37 points-notcovered-pk_range=100
0.85 0.88 0.82 0.74 0.72 points-notcovered-si.pre_range=100
0.85 0.89 0.82 0.74 0.72 points-notcovered-si_range=100
1.70 1.85 1.72 0.80 1.47 random-points.pre_range=1000
1.66 1.75 1.62 1.33 1.36 random-points.pre_range=100
1.01 1.01 0.93 0.87 0.82 random-points.pre_range=10
1.70 1.85 1.72 0.80 1.48 random-points_range=1000
1.66 1.75 1.62 1.35 1.37 random-points_range=100
1.01 1.02 0.94 0.87 0.83 random-points_range=10
0.93 0.90 0.88 0.80 0.76 range-covered-pk.pre_range=100
0.94 0.89 0.88 0.81 0.76 range-covered-pk_range=100
0.92 0.88 0.87 0.82 0.77 range-covered-si.pre_range=100
0.92 0.88 0.87 0.82 0.77 range-covered-si_range=100
0.94 0.90 0.84 0.78 0.74 range-notcovered-pk.pre_range=100
0.94 0.90 0.84 0.78 0.74 range-notcovered-pk_range=100
0.85 0.86 0.82 0.73 0.67 range-notcovered-si.pre_range=100
0.85 0.87 0.82 0.74 0.67 range-notcovered-si_range=100
0.95 0.94 0.94 1.11 1.07 read-only.pre_range=10000
1.16 1.13 1.08 1.01 0.96 read-only.pre_range=100
1.10 1.07 1.02 0.97 0.91 read-only.pre_range=10
0.95 0.94 0.97 1.14 1.10 read-only_range=10000
1.16 1.13 1.08 1.02 0.96 read-only_range=100
1.10 1.08 1.02 0.97 0.92 read-only_range=10
0.98 0.90 1.03 0.92 0.75 scan_range=100
1.41 1.51 1.44 1.52 1.42 delete_range=100
1.51 1.52 1.59 1.50 1.41 insert_range=100
1.13 1.06 1.06 1.02 0.96 read-write_range=100
1.21 1.12 1.14 1.10 1.04 read-write_range=10
3.12 3.62 1.44 3.21 1.77 update-index_range=100
1.53 1.82 1.99 2.00 2.02 update-inlist_range=100
1.25 1.31 1.33 1.30 1.23 update-nonindex_range=100
0.98 1.01 1.02 1.04 0.98 update-one_range=100
1.20 1.26 1.28 1.26 1.19 update-zipf_range=100
1.38 1.44 1.29 1.33 1.24 write-only_range=10000
at No comments:
Labels: , , , ,

Sunday, October 13, 2024

Managing CPU frequency for AMD on Ubuntu 22.04

I need stable performance from the servers I use for benchmarks. I also need servers that don't run too hot because too-hot servers cause HW to fail and introduce variance when CPU speeds are throttled. This post is about my latest adventures managing CPU speed. My previous post is here.

At a high level my solution is:

  • disable turbo boost
  • (optional) cap the max frequency the CPU can use
Background reading

The AMD pstate scaling drivers (amd-pstate, amd-pstate-epp) make life interesting for some of us. The common advice today is to stay with acpi-cpufreq for servers. I agree and assume it is best to wait for things to settle, for the scaling drivers to be feature complete, for docs to catch up and for new AMD CPUs that support all of the new features to arrive in your servers. It is very confusing today -- the non-expert user experience isn't great and there is too much advice on the interweb that is wrong and/or out of date.

The amd-pstate-epp scaling driver used in active mode doesn't appear to have a way to disable or enable turbo boost. It also isn't obvious it has a way to limit the max CPU frequency. So expect your server to run fast and hot, then throttle the CPU, then repeat forever. That might be fine for a laptop but isn't good for my use case.

There is much to learn, but sometimes I prefer to focus on my problems (database storage engines) and not have to spend too much time on topics like this:
  • an overview on AMD pstate drivers from AMD (see here)
  • an overview of CPU frequency scaling from Arch Linux (see here)
  • good configuration advice on Reddit (see here)
  • benchmark results from Phoronix (see here)
  • kernel docs on frequency governors (see here)
  • a good user experience post (see here)
  • an overview from RedHat (see here)
The solution

The concrete steps are:
  • disable turbo boost
  • use the acpi-cpufreq scaling driver
  • use the performance frequency governor
  • (optionally) cap the max CPU frequency (this only works on some of my servers)
Run this to disable turbo boost. Note that the boost file exists when using the acpi-cpufreq scaling driver. If using amd-pstate-epp then the file isn't there with active mode and is there with guided mode.

    echo '0' | sudo tee /sys/devices/system/cpu/cpufreq/boost

To get the acpi-cpufreq scaling driver, edit /etc/default/grub to add one of these lines (nosmt disables AMD SMT), then run sudo update-grub and then reboot.

    GRUB_CMDLINE_LINUX_DEFAULT="nosmt amd_pstate=disable"
    GRUB_CMDLINE_LINUX_DEFAULT="amd_pstate=disable"

Run this to use the performance frequency governor

    sudo cpupower frequency-set --governor performance

And then run this to confirm you enabled the performance governor:

    cpupower -c all frequency-info | grep gov ; cpupower frequency-info

While disabling turbo boost goes a long way to avoiding a too-hot CPU, sometimes you might want to reduce the CPU frequencies even more, and somtimes that is possible via the cpupower command. However, this doesn't have an impact on all of my servers. Fortunately, summer has passed and I don't have to worry as much about overheating for a few months. Here is one example of a user having a problem similar to mine (the max value is ignored).

    sudo cpupower frequency-set -u 2.40GHz

Setting up the adventure

This adventure began when I had to replace m.2 devices on several of my small servers because they were at or near endurance limits. I use Ubuntu 22.04 and then decided to update the installs which brought new kernel versions. And while doing that I updated all of my servers to use an HWE kernel which is now 6.8.something with Ubuntu 22.04.5.

Ubuntu 22.04 Server uses the schedutil frequency governor by default when using the acpi-cpufreq scaling driver. I noticed a few months back that schedutil that caused odd performance results for MyRocks on some of my servers and switching to the performance governor gave me up to ~2X more QPS (see here). So I decided to switch all of my small servers to use the performance governor.

Servers

I have two types of small servers. Both use Ubutu 22.04 and everything has the 6.8.0-45-generic HWE enabled kernel today.

 The older type has an AMD Ryzen 4700u CPU with 8 cores, 16G of RAM, no support for AMD SMT and no support for CPPC. I did not check to see if CPPC support was disabled in the BIOS. And by no support for CPPC I mean that these directories do not exist:

    /sys/devices/system/cpu/cpu*/acpi_cppc

However, on the same server this shows that CPPC is supported: lscpu | grep cppc

The newer small server has 8 cores with AMD SMT disabled, 32G RAM and an AMD Ryzen 7 CPU -- either 7840HS or 7735HS.  These have support for CPPC.

Results

I used this script to determine the behavior of scaling driver (acpi-cpufreq, amd-pstate-epp in active mode, amd-pstate-epp in guided mode), frequency governor (schedutil, performance, powersave) and energy performance preference (the EPP in amd-pstate-epp).

I decided to not share the results, perhaps I am grouchy after spending a few too many hours on this.

Update 1 - cpupower idle-set

One of my smart friends suggested I might need to use cpupower idle-set -D1 to avoid some sources of variance. While I would rather more on storage engines and less on tuning frequency management, I suppose I need to look at this.

I ran cpupower idle-info on the CPUs in my home servers. The CPUs are and per-state latencies are listed below. One interesting thing is that gap between C1 and C2 is huge for the 4700u CPU (1 to 350) but much smaller on all of the other (and newer) CPUs.

  • AMD Ryzen 7 4700u (laptop class, the oldest & slowest of the bunch)
    • Latency for (Poll, C1, C2, C3) = (0, 1, 350, 400)
  • AMD Ryzen 7 7735HS (laptop class)
    • Latency for (Poll, C1, C2, C3) = (0, 1, 18, 350)
  • AMD Ryzen 7 7840HS (laptop class)
    • Latency for (Poll, C1, C2, C3) = (0, 1, 18, 350)
  • Intel Xeon Silver 4214R
    • Latency for (Poll, C1, C1E, C6) = (0, 2, 10, 133)
  • AMD Ryzen Threadripper PRO 5975WX
    • Latency for (Poll, C1, C2) = (0, 1, 18)
I get the following output from cpupower idle-info

AMD Ryzen 7 4700u

CPUidle driver: acpi_idle
CPUidle governor: menu
analyzing CPU 6:

Number of idle states: 4
Available idle states: POLL C1 C2 C3
POLL:
Flags/Description: CPUIDLE CORE POLL IDLE
Latency: 0
Usage: 18496
Duration: 924722
C1:
Flags/Description: ACPI FFH MWAIT 0x0
Latency: 1
Usage: 89774
Duration: 31663862
C2:
Flags/Description: ACPI IOPORT 0x414
Latency: 350
Usage: 25768
Duration: 24190992
C3:
Flags/Description: ACPI IOPORT 0x415
Latency: 400
Usage: 405313
Duration: 85575022637

AMD Ryzen 7 7735HS

CPUidle driver: acpi_idle
CPUidle governor: menu
analyzing CPU 4:

Number of idle states: 4
Available idle states: POLL C1 C2 C3
POLL:
Flags/Description: CPUIDLE CORE POLL IDLE
Latency: 0
Usage: 5236511
Duration: 33151236
C1:
Flags/Description: ACPI FFH MWAIT 0x0
Latency: 1
Usage: 68356802
Duration: 1954121741
C2:
Flags/Description: ACPI IOPORT 0x414
Latency: 18
Usage: 25603592
Duration: 1986129966
C3:
Flags/Description: ACPI IOPORT 0x415
Latency: 350
Usage: 14551352
Duration: 77114754509

AMD Ryzen 7 7840HS

CPUidle driver: acpi_idle
CPUidle governor: menu
analyzing CPU 5:

Number of idle states: 4
Available idle states: POLL C1 C2 C3
POLL:
Flags/Description: CPUIDLE CORE POLL IDLE
Latency: 0
Usage: 2754712
Duration: 91604147
C1:
Flags/Description: ACPI FFH MWAIT 0x0
Latency: 1
Usage: 101206334
Duration: 5540660093
C2:
Flags/Description: ACPI IOPORT 0x414
Latency: 18
Usage: 17879457
Duration: 1736766115
C3:
Flags/Description: ACPI IOPORT 0x415
Latency: 350
Usage: 3887844
Duration: 73780112353

Intel Xeon Silver 4214R

CPUidle driver: intel_idle
CPUidle governor: menu
analyzing CPU 13:

Number of idle states: 4
Available idle states: POLL C1 C1E C6
POLL:
Flags/Description: CPUIDLE CORE POLL IDLE
Latency: 0
Usage: 1780886844
Duration: 8636888464
C1:
Flags/Description: MWAIT 0x00
Latency: 2
Usage: 16431990427
Duration: 337814322622
C1E:
Flags/Description: MWAIT 0x01
Latency: 10
Usage: 5084275890
Duration: 309067690957
C6:
Flags/Description: MWAIT 0x20
Latency: 133
Usage: 1368088856
Duration: 3588025308542

AMD Ryzen Threadripper PRO 5975WX

CPUidle driver: acpi_idle
CPUidle governor: menu
analyzing CPU 27:

Number of idle states: 3
Available idle states: POLL C1 C2
POLL:
Flags/Description: CPUIDLE CORE POLL IDLE
Latency: 0
Usage: 410444172
Duration: 1861530465
C1:
Flags/Description: ACPI FFH MWAIT 0x0
Latency: 1
Usage: 14490956799
Duration: 422501068392
C2:
Flags/Description: ACPI IOPORT 0x814
Latency: 18
Usage: 3995240933
Duration: 3752298780474

at No comments:
Subscribe to: Posts (Atom)

I recently published results with a summary of HW performance counters for sysbench with MySQL on four CPU types. The performance reports we...