Axel Schwenke added a comment - 2019-03-12 12:54 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

Varun Gupta (Inactive) added a comment - 2019-03-12 17:26 - edited %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

Sergei Petrunia added a comment - 2019-04-04 14:29 Details about the benchmark are here: https://github.com/hgxl64/mariadb-benchmarks/tree/master/regression/t_collate_distinct_range_utf8_general

Sergei Petrunia added a comment - 2019-04-04 15:02 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.

Sergei Petrunia added a comment - 2019-04-04 15:07 t_1K-reads-innodb-single has the same picture

Sergei Petrunia added a comment - 2019-04-04 15:10

Sergei Petrunia added a comment - 2019-04-04 15:15

Sergei Petrunia added a comment - 2019-04-17 11:43 The ball is on Varun's side now: https://lists.launchpad.net/maria-developers/msg11781.html

Sergei Petrunia added a comment - 2019-06-14 18:08 See also MDEV-18172

Julien Fritsch added a comment - 2023-12-05 09:26 Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.