[MDEV-18822] investigate performance regressions in 10.4 Created: 2019-03-05  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Server
Fix Version/s: None

Type: Task Priority: Major
Reporter: Axel Schwenke Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 1
Labels: None

Attachments: File mdev18822-plots.ods     PNG File screenshot-1.png     PNG File t_1K-reads-innodb-multi-relative.png     PNG File t_1K-reads-innodb-single-relative.png     PNG File t_1K-reads-memory-multi.png    
Issue Links:
Relates
relates to MDEV-6111 optimizer trace Closed
relates to MDEV-18172 Benchmarking 10.4 for optimizer trace Closed

 Description   

The regression test suite has found regressions for many tests when moving from MariaDB 10.4.2 to 10.4.3. Those must be investigated.

 Comments   
Comment by Axel Schwenke [ 2019-03-12 ]

bisecting the commits between tags "mariadb-10.4.2" and "mariadb-.10.4.3" yields one commit that affects InnoDB performance. The good/bad selector test was doing point-selects on multiple tables with 32 threads. 

commit be8709eb7bdf2a68a1c04fd8ab368113f5f39b63
 
Author: Varun Gupta <varun.gupta@mariadb.com>
 
Date:   Wed Feb 13 11:22:16 2019 +0530
 
 
 
    MDEV-6111 Optimizer Trace
 
...

The verification run of the test suite was however less conclusive. There are 4 builds in comparison: 10.4.2 (release), 10.4-good (predecessor of be8709e), 10.4-bad (be8709e) and 10.4.3 (release).

Some tests seem to verify that this commit is a problem:

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.2          19087   138583  244603  369566  370204  371588  368619
 
mariadb-10.4-good       20280   144202  244855  370095  367917  373643  366621
 
mariadb-10.4-bad        20117   138618  240292  364251  363217  368866  363653
 
mariadb-10.4.3          19814   135953  233957  357642  357150  360942  354243
 
 
 
--------------------------------------------------------------------------------
 
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.2          19222   136760  239838  364005  365489  367748  363192
 
mariadb-10.4-good       20270   142050  241461  367423  367075  370143  364642
 
mariadb-10.4-bad        19461   139380  238528  360254  359980  362181  358685
 
mariadb-10.4.3          19303   136266  233723  354490  356811  358907  353368
 
 
 
--------------------------------------------------------------------------------
 
Test 't_1K-reads-memory-multi' - sysbench OLTP readonly
 
1000 point selects per iteration, no range queries
 
20 tables, 1 mio rows total, engine MEMORY
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.2          20856   147196  252492  384321  388061  390269  386006
 
mariadb-10.4-good       21387   147181  253542  381279  386880  389252  383184
 
mariadb-10.4-bad        21134   141405  247557  376253  378868  378773  377099
 
mariadb-10.4.3          20378   139601  245345  373224  376145  379660  372967
 
 
 
--------------------------------------------------------------------------------
 
Test 't_collate_distinct_range_utf8_general' - sysbench OLTP readonly
 
selecting distinct rows from short range, collation utf8_general_ci
 
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.2          8196.2  56026   96395   136538  123254  123829  120007
 
mariadb-10.4-good       8024.5  56021   94431   132833  121781  122930  124543
 
mariadb-10.4-bad        7947.1  51105   88813   129070  120479  117463  117190
 
mariadb-10.4.3          7855.2  48038   87255   119983  108900  111793  111483
 
 
 
--------------------------------------------------------------------------------
 
Test 't_oltp-innodb-multi' - sysbench OLTP read/write
 
32 InnoDB tables with 10 mio rows total
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.2          4255.7  26925   53098   105244  154373  172803  171739
 
mariadb-10.4-good       4312.1  26837   53637   105524  154327  173257  171776
 
mariadb-10.4-bad        4288.2  26878   53306   104754  153527  170237  169750
 
mariadb-10.4.3          4167.9  26538   52403   102738  148246  165164  164049
 
 
 
--------------------------------------------------------------------------------
 
Test 't_oltp-innodb-single' - sysbench OLTP read/write
 
single InnoDB table with 10 mio rows
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.2          4291.2  26850   53291   105838  152935  167109  161561
 
mariadb-10.4-good       4352.3  27003   53267   105351  151978  166635  161547
 
mariadb-10.4-bad        4196.0  26978   52729   104603  150586  164803  158916
 
mariadb-10.4.3          4143.5  26521   51977   101958  145384  158258  152602
 
 
 
--------------------------------------------------------------------------------
 
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.2          3718.8  18717   35884   66686   113109  158418  171464
 
mariadb-10.4-good       3671.9  18583   35592   66464   112443  157740  170386
 
mariadb-10.4-bad        3610.6  18371   35386   65973   110617  155432  166706
 
mariadb-10.4.3          3558.8  18232   34662   65173   109179  148210  158562

But then other tests say different:

--------------------------------------------------------------------------------
 
Test 't_1K-reads-aria-fixed-multi' - sysbench OLTP readonly
 
1000 point selects per iteration, no range queries
 
32 tables, 1 mio rows total, engine ARIA ROW_FORMAT=FIXED
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.2          18872   127344  206310  163030  157240  160838  173114
 
mariadb-10.4-good       19807   128265  198593  151602  147699  151709  162497
 
mariadb-10.4-bad        18774   126306  200998  157049  151760  155105  165707
 
mariadb-10.4.3          18016   125105  199643  168037  161160  164176  175989
 
 
 
--------------------------------------------------------------------------------
 
Test 't_1K-reads-memory-single' - sysbench OLTP readonly
 
1000 point selects per iteration, no range queries
 
1 table, 1 mio rows, engine MEMORY
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.2          20215   144732  251528  323131  334004  330010  324367
 
mariadb-10.4-good       21338   147709  250600  324771  335285  327276  322960
 
mariadb-10.4-bad        20611   144795  244826  320317  330380  327601  322011
 
mariadb-10.4.3          20363   141520  240510  316989  330020  325516  322784
 
 
 
--------------------------------------------------------------------------------
 
Test 't_1K-reads-myisam-multi' - sysbench OLTP readonly
 
1000 point selects per iteration, no range queries
 
20 tables, 1 mio rows total, engine MyISAM
 
numbers are queries per second
 
 
 
#thread count           1       8       16      32      64      128     256
 
mariadb-10.4.2          18519   123677  165937  122176  120789  121718  126186
 
mariadb-10.4-good       19613   121910  142280  112617  112010  114133  120508
 
mariadb-10.4-bad        18883   118639  142528  114179  113419  115295  122319
 
mariadb-10.4.3          18477   120729  145219  111912  110926  112689  118662
 
 
 
--------------------------------------------------------------------------------
 
Test 't_collate_distinct_range_utf8mb4_general' - sysbench OLTP readonly
 
selecting distinct rows from short range, collation utf8mb4_general_ci
 
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.2          8124.7  52400   91058   127938  117670  118268  117673
 
mariadb-10.4-good       8110.4  51505   89347   124735  119085  116247  116966
 
mariadb-10.4-bad        7972.6  49436   90320   126901  117248  114634  120384
 
mariadb-10.4.3          7669.1  46140   80430   117227  109178  114016  111417
 
 
 
--------------------------------------------------------------------------------
 
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.2          4581.7  30531   60788   110068  160286  188658  202495
 
mariadb-10.4-good       4483.1  30287   60335   109998  156156  186888  201680
 
mariadb-10.4-bad        4445.5  30276   59278   109298  155975  185987  200604
 
mariadb-10.4.3          4330.8  29346   58088   104348  151151  176713  187286

Comment by Varun Gupta (Inactive) [ 2019-03-12 ]

%change in performance
comparing 10.4.2-good vs 10.4.2-bad
comparing 10.4.2-good vs 10.4.3

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)							
10.4.2-good vs bad	0.8037475345	3.872345737	1.863551898	1.579054027	1.277462036	1.278493107	0.8095553719
 
10.4.2-good vs 10.4.3	2.297830375	5.720447705	4.450797411	3.364811738	2.926475265	3.399234028	3.376238677
 
							
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)							
10.4.2-good vs bad	3.991119882	1.879619852	1.214688915	1.951157113	1.932847511	2.151060536	1.63365712
 
10.4.2-good vs 10.4.3	4.770596941	4.071805702	3.204658309	3.519921181	2.796158823	3.035583545	3.091799628
 
							
Test 't_1K-reads-memory-multi' - sysbench OLTP readonly							
1000 point selects per iteration, no range queries							
20 tables, 1 mio rows total, engine MEMORY							
10.4.2-good vs bad	1.182961612	3.924419592	2.360555648	1.318194813	2.070926385	2.692086361	1.588009938
 
10.4.2-good vs 10.4.3	4.717819236	5.150121279	3.232994928	2.112626187	2.7747622	2.464213414	2.666343062
 
							
Test 't_collate_distinct_range_utf8_general' - sysbench OLTP readonly							
selecting distinct rows from short range, collation utf8_general_ci							
1 table, 1 mio rows, engine InnoDB/XtraDB (builtin)							
10.4.2-good vs bad	0.9645460776	8.775280698	5.949317491	2.832880384	1.069132295	4.4472464	5.903984969
 
10.4.2-good vs 10.4.3	2.109788772	14.25001339	7.599199415	9.673800938	10.57718363	9.05962743	10.48633805
 
							
Test 't_oltp-innodb-multi' - sysbench OLTP read/write							
32 InnoDB tables with 10 mio rows total							
10.4.2-good vs bad	0.5542543076	-0.1527741551	0.6171113224	0.7296918237	0.5183798039	1.743075316	1.179442996
 
10.4.2-good vs 10.4.3	3.344078291	1.114133472	2.30065067	2.640157689	3.940334485	4.67109554	4.498300112
 
							
Test 't_oltp-innodb-single' - sysbench OLTP read/write							
single InnoDB table with 10 mio rows							
10.4.2-good vs bad	3.59120465	0.09258230567	1.010006195	0.7100074987	0.9159220413	1.099408888	1.628628201
 
10.4.2-good vs 10.4.3	4.79746341	1.784986853	2.421762067	3.220662357	4.338785877	5.027155159	5.537088278
 
							
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)							
10.4.2-good vs bad	1.669435442	1.140827638	0.5787817487	0.7387457872	1.623933904	1.463167237	2.159801862
 
10.4.2-good vs 10.4.3	3.080149242	1.888823118	2.61294673	1.942404911	2.902804087	6.041587422	6.939537286

Comment by Sergei Petrunia [ 2019-04-04 ]

Details about the benchmark are here:
https://github.com/hgxl64/mariadb-benchmarks/tree/master/regression/t_collate_distinct_range_utf8_general

Comment by Sergei Petrunia [ 2019-04-04 ]

Let's plot:

  • something odd happens at n_threads=1. MariaDB-10.4.2 is the worst there, all other versions beat it. Let's ignore this for now.
  • I would say that MariaDB-10.4-good is on par with MariaDB-10.4.2 - it is sometimes a bit slower, sometimes a bit faster.
  • MariaDB-10.4-bad has consistently lower performance than MariaDB-10.4-good
  • Note that MariaDB-10.4.3 is also consistently worse than MariaDB-10.4-bad. There seems to be another source of slowdown.
Comment by Sergei Petrunia [ 2019-04-04 ]

t_1K-reads-innodb-single has the same picture

Comment by Sergei Petrunia [ 2019-04-04 ]

Comment by Sergei Petrunia [ 2019-04-04 ]

Comment by Sergei Petrunia [ 2019-04-17 ]

The ball is on Varun's side now: https://lists.launchpad.net/maria-developers/msg11781.html

Comment by Sergei Petrunia [ 2019-06-14 ]

See also MDEV-18172

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Generated at Thu Feb 08 08:46:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.