[MDEV-21872] performance regressions in early 10.5 releases Created: 2020-03-04  Updated: 2021-09-16  Resolved: 2021-09-16

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.5.0, 10.5.1
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Axel Schwenke Assignee: Axel Schwenke
Resolution: Won't Fix Votes: 0
Labels: None

Attachments: PDF File RO-Regression.pdf     PDF File RW-Regression.pdf    

 Description   

The regression test suite shows regressions in 10.5 alpha releases vs. 10.4:

Test 't_1K-reads-innodb-multi' - sysbench OLTP readonly
 
1000 point selects per iteration, no range queries
 
20 tables, 1 mio rows total, engine InnoDB/XtraDB (builtin)
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.10         18912   129671  210118  349973  346763  348179  343266
 
mariadb-10.4.11         18867   127570  209937  347577  344957  347036  342816
 
mariadb-10.4.12         19252   131414  210170  348799  346624  348585  345306
 
mariadb-10.5.0          17921   121741  198837  330531  329911  330070  327786
 
mariadb-10.5.1          17708   120755  200716  332037  328391  330528  327110
 
 
 
--------------------------------------------------------------------------------
 
Test 't_1K-reads-innodb-single' - sysbench OLTP readonly
 
1000 point selects per iteration, no range queries
 
1 table, 1 mio rows, engine InnoDB/XtraDB (builtin)
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.10         18890   127436  207246  342860  341989  343463  340287
 
mariadb-10.4.11         18382   125455  206588  343619  341569  343079  339679
 
mariadb-10.4.12         18874   126485  206194  345189  343201  344006  340161
 
mariadb-10.5.0          17508   117554  195524  327860  324396  326157  323128
 
mariadb-10.5.1          17339   120175  197468  326402  322077  324795  321518
 
 
 
--------------------------------------------------------------------------------
 
Test 't_oltp-9010-innodb-multi' - sysbench simplified OLTP read/write 
16 InnoDB tables with 1 mio rows total, 9 point selects + 1 update
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.10         3863.5  21548   36944   52079   57303   56993   53546
 
mariadb-10.4.11         3831.2  21765   36818   51720   56804   56707   52893
 
mariadb-10.4.12         3875.9  21843   36960   52003   57461   57184   53697
 
mariadb-10.5.0          3761.2  21455   36533   49127   54586   54845   51766
 
mariadb-10.5.1          3642.5  21484   36045   48191   54614   55200   51768

for write-heavy workload, 10.5.0 shows improvement over 10.4, but 10.5.1 is again slower:

Test 't_writes-binlog-multi' - sysbench OLTP write-only, with synchronous binlog
 
9 index-update, 9 non-index-update, 1 delete, 1 insert
 
32 tables, 10 mio rows total, engine InnoDB/XtraDB (builtin)
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.10         3687.8  18779   32752   57308   99236   141833  156144
 
mariadb-10.4.11         3448.7  17719   31374   56053   93724   130839  147938
 
mariadb-10.4.12         3417.4  17821   31118   55717   92955   129281  147192
 
mariadb-10.5.0          3895.3  19014   33377   59123   101065  149009  161146
 
mariadb-10.5.1          3888.1  18488   31910   53055   84087   134575  150198
 
 
 
--------------------------------------------------------------------------------
 
Test 't_writes-innodb-multi' - sysbench OLTP write-only
 
9 index-update, 9 non-index-update, 1 delete, 1 insert
 
32 tables, 10 mio rows total, engine InnoDB/XtraDB (builtin)
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.10         4633.4  29103   52705   91827   141003  176600  178643
 
mariadb-10.4.11         4501.2  27221   52259   89126   135190  168160  177875
 
mariadb-10.4.12         4290.4  27374   52429   88275   134493  167507  176984
 
mariadb-10.5.0          4921.0  30383   53866   92111   144101  177411  181754
 
mariadb-10.5.1          4762.4  29810   52022   82436   133888  178153  180709
 
 
 
--------------------------------------------------------------------------------
 
Test 't_writes-innodb-single' - sysbench OLTP write-only
 
9 index-update, 9 non-index-update, 1 delete, 1 insert
 
single table, 10 mio rows, engine InnoDB/XtraDB (builtin)
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.10         4410.2  28764   52109   88887   135151  155011  140864
 
mariadb-10.4.11         4341.6  27129   51370   86261   124251  146377  140315
 
mariadb-10.4.12         4317.7  26918   51856   86259   125353  143034  140220
 
mariadb-10.5.0          4685.8  30309   53868   92116   135041  158190  156668
 
mariadb-10.5.1          4710.2  30574   55333   89345   138064  161451  156705



 Comments   
Comment by Axel Schwenke [ 2020-06-08 ]

The regression for the read-only benchmark is caused by our decision to disable the InnoDB Adaptive Hash Index by default in 10.5. When the AHI is enabled, performance is par with 10.4. See attached file RO-Regression.pdf

Comment by Axel Schwenke [ 2020-06-08 ]

The regression for write-only workload with binlog on seems to be cancelled in 10.5.2. See attachment RW-Regression.pdf.

In the meantime 10.5.3 and pre-10.5.4 show even more improvements for write-heavy workloads. It might still be good to investigate the commits between 10.4.12 and 10.5.0 if there is something wrong. This however has low priority.

Generated at Thu Feb 08 09:10:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.