Performance regression on sysbench update-index test

This problems arrives in 10.5 and remains in 13.0.

So far I see it with sysbench, a cached database and 16 threads on a 2-socket, 24-core server. You should be able to reproduce it using this script and focus on the result for update-index.

Using relative QPS to characterize this, which is: (QPS for my version / QPS for 10.2.30), I get 72280 updates/s with 10.4.34 and 51658 updates/s with 13.0.0. The relative QPS per version shows that the problem arrived in 10.5 and throughput has improved a bit since then but is still smaller than what it was in 10.4.

A few interesting points:

• this occurs on my 2-socket, 24-core server
• this doesn't occur on my 32-core, 1-socket server. But on the 1-socket server where I ran the test for 24 threads I see that CPU increased by ~5% and the context switch rate by 1.5X in 13.0 vs 10.4 on the update-index test and that problem started with 10.5.
• this doesn't occur on my 48-core server that I rent from Hetzner (ax162-s). I think that is a 1-socket server. There I see throughput drop in 10.5 and 10.6 but recover in 10.11 and remain stable to 13.0. And the CPU and context switch rate increases are similar to what I see on the 32-core server. Here they go bad in 10.5 and 10.6 but recover in 10.11.
• of the 10 write-heavy tests I run via sysbench, this only occurs on update-index

Relative QPS by version

1.06    10.4.34

0.59    10.5.29

0.69    10.6.25

0.77    10.11.16

0.73    11.4.10

0.74    11.8.6

0.74    12.3.1

0.75    13.0.0

Using vmstat and iostat average values normalized (divided) by updates/s, in 10.5 there are bad changes that still exist in modern MariaDB (13.0):

• a large increase in the context switch rate (cs/o)
• a large increase in CPU/update (cpu/o)
• a large increase in KB written to storage per update (wKB/o). I can't explain this one yet. I have innodb_change_buffering=none for all versions.

--- absolute

cpu/o           cs/o    r/o     rKB/o   wKB/o   o/s     dbms

0.000925        6.507   0       0       5.076   68477   10.2.30

0.001014        6.362   0       0       5.971   64417   10.2.44

0.000875        6.256   0       0       4.584   73707   10.3.39

0.000906        6.595   0       0       4.591   72280   10.4.34

0.001503        13.784  0       0       16.328  40135   10.5.29

0.001320        13.287  0       0       11.765  47468   10.6.25

0.001228        10.895  0       0       9.636   52828   10.11.16

0.001283        10.109  0       0       10.855  49836   11.4.10

0.001245        10.014  0       0       10.712  50827   11.8.6

0.001244        9.961   0       0       10.703  50798   12.3.1

0.001193        10.042  0       0       10.489  51658   13.0.0

--- relative to first result (10.2.30)

1.10            0.98    1       1       1.18    0.94    10.2.44

0.95            0.96    1       1       0.90    1.08    10.3.39

0.98            1.01    1       1       0.90    1.06    10.4.34

1.62            2.12    1       1       3.22    0.59    10.5.29

1.43            2.04    1       1       2.32    0.69    10.6.25

1.33            1.67    1       1       1.90    0.77    10.11.16

1.39            1.55    1       1       2.14    0.73    11.4.10

1.35            1.54    1       1       2.11    0.74    11.8.6

1.34            1.53    1       1       2.11    0.74    12.3.1

1.29            1.54    1       1       2.07    0.75    13.0.0