Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29401

InnoDB history list length increased in 10.6 compared to 10.5 for the same load

    XMLWordPrintable

Details

    Description

      Consider the following primitive sysbench test executed on a freshly installed server started with --no-defaults:

      ./src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --time=300 --report_interval=10 prepare
       
      ./src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --time=300 --report_interval=10 run
      

      On 10.5.18 I get:

      Yuliyas-MacBook-Air:maria10.5 Valerii$ bin/mysqld_safe --no-defaults &
      [1] 52361
      Yuliyas-MacBook-Air:maria10.5 Valerii$ 220827 15:58:06 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.5/data/Yuliyas-Air.err'.
      220827 15:58:06 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.5/data
       
      Yuliyas-MacBook-Air:maria10.5 Valerii$ bin/mysql -e 'show global variables like "innodb%purge%"';
      +--------------------------------------+------------+
      | Variable_name                        | Value      |
      +--------------------------------------+------------+
      | innodb_max_purge_lag                 | 0          |
      | innodb_max_purge_lag_delay           | 0          |
      | innodb_max_purge_lag_wait            | 4294967295 |
      | innodb_purge_batch_size              | 300        |
      | innodb_purge_rseg_truncate_frequency | 128        |
      | innodb_purge_threads                 | 4          |
      +--------------------------------------+------------+
      Yuliyas-MacBook-Air:maria10.5 Valerii$ bin/mysql -e 'create database sbtest';
      Yuliyas-MacBook-Air:maria10.5 Valerii$ bin/mysql -e 'show global status like "%history%"';
      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Innodb_history_list_length | 60    |
      +----------------------------+-------+
      

      Then after running prepare:

      Yuliyas-MacBook-Air:maria10.5 Valerii$ bin/mysql -e 'show global status like "%history%"';
      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Innodb_history_list_length | 21    |
      +----------------------------+-------+
      

      Then I run the test for 300 seconds:

      Yuliyas-MacBook-Air:sysbench Valerii$ ./src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --time=300 --report_interval=10 run
      sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
       
      Running the test with following options:
      Number of threads: 4
      Report intermediate results every 10 second(s)
      Initializing random number generator from current time
       
       
      Initializing worker threads...
       
      Threads started!
       
      [ 10s ] thds: 4 tps: 593.70 qps: 11879.11 (r/w/o: 8316.51/2374.80/1187.80) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
      [ 20s ] thds: 4 tps: 604.43 qps: 12088.03 (r/w/o: 8461.44/2417.73/1208.86) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 30s ] thds: 4 tps: 619.17 qps: 12385.00 (r/w/o: 8669.71/2476.96/1238.33) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 40s ] thds: 4 tps: 606.82 qps: 12137.18 (r/w/o: 8495.57/2427.98/1213.64) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 50s ] thds: 4 tps: 637.16 qps: 12739.69 (r/w/o: 8917.73/2547.64/1274.32) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
      [ 60s ] thds: 4 tps: 611.75 qps: 12238.95 (r/w/o: 8566.97/2448.49/1223.50) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 70s ] thds: 4 tps: 605.34 qps: 12105.49 (r/w/o: 8474.62/2420.18/1210.69) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 80s ] thds: 4 tps: 609.67 qps: 12195.05 (r/w/o: 8535.62/2439.99/1219.45) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 90s ] thds: 4 tps: 624.65 qps: 12490.97 (r/w/o: 8744.48/2497.29/1249.20) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 100s ] thds: 4 tps: 621.98 qps: 12440.71 (r/w/o: 8708.33/2488.42/1243.96) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 110s ] thds: 4 tps: 609.24 qps: 12183.33 (r/w/o: 8528.71/2436.15/1218.47) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 120s ] thds: 4 tps: 617.56 qps: 12353.37 (r/w/o: 8646.42/2471.84/1235.12) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 130s ] thds: 4 tps: 601.65 qps: 12030.75 (r/w/o: 8422.46/2404.99/1203.29) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00
      [ 140s ] thds: 4 tps: 625.19 qps: 12504.72 (r/w/o: 8753.20/2501.14/1250.37) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
      [ 150s ] thds: 4 tps: 611.64 qps: 12234.06 (r/w/o: 8563.00/2447.77/1223.29) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 160s ] thds: 4 tps: 630.46 qps: 12607.53 (r/w/o: 8826.19/2520.43/1260.91) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
      [ 170s ] thds: 4 tps: 618.25 qps: 12366.61 (r/w/o: 8655.71/2474.40/1236.50) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 180s ] thds: 4 tps: 599.52 qps: 11990.45 (r/w/o: 8393.31/2398.09/1199.04) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
      [ 190s ] thds: 4 tps: 625.74 qps: 12514.52 (r/w/o: 8760.30/2502.74/1251.47) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
      [ 200s ] thds: 4 tps: 615.72 qps: 12312.31 (r/w/o: 8619.42/2461.46/1231.43) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 210s ] thds: 4 tps: 623.64 qps: 12474.94 (r/w/o: 8731.52/2496.15/1247.27) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
      [ 220s ] thds: 4 tps: 620.10 qps: 12397.36 (r/w/o: 8678.34/2478.81/1240.21) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 230s ] thds: 4 tps: 611.32 qps: 12228.84 (r/w/o: 8560.94/2445.27/1222.63) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 240s ] thds: 4 tps: 617.59 qps: 12351.54 (r/w/o: 8645.62/2470.75/1235.17) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 250s ] thds: 4 tps: 609.73 qps: 12194.84 (r/w/o: 8536.85/2438.53/1219.46) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 260s ] thds: 4 tps: 620.58 qps: 12413.35 (r/w/o: 8688.68/2483.51/1241.15) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
      [ 270s ] thds: 4 tps: 624.39 qps: 12488.29 (r/w/o: 8741.52/2497.98/1248.79) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 280s ] thds: 4 tps: 625.15 qps: 12503.04 (r/w/o: 8752.13/2500.61/1250.30) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
      [ 290s ] thds: 4 tps: 474.41 qps: 9488.10 (r/w/o: 6641.67/1897.62/948.81) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
      [ 300s ] thds: 4 tps: 598.52 qps: 11970.42 (r/w/o: 8379.30/2394.08/1197.04) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
      SQL statistics:
          queries performed:
              read:                            2564226
              write:                           732636
              other:                           366318
              total:                           3663180
          transactions:                        183159 (610.51 per sec.)
          queries:                             3663180 (12210.26 per sec.)
          ignored errors:                      0      (0.00 per sec.)
          reconnects:                          0      (0.00 per sec.)
       
      Throughput:
          events/s (eps):                      610.5130
          time elapsed:                        300.0084s
          total number of events:              183159
       
      Latency (ms):
               min:                                    3.23
               avg:                                    6.55
               max:                                  122.19
               95th percentile:                        9.22
               sum:                              1199364.94
       
      Threads fairness:
          events (avg/stddev):           45789.7500/18.14
          execution time (avg/stddev):   299.8412/0.00
      

      and while it runs in another shell I monitor history list length for 200 seconds:

      Yuliyas-MacBook-Air:maria10.5 Valerii$ for i in `seq 1 200`; do bin/mysql -B -e "show global status like '%history%'" | grep history ; sleep 1; done
      Innodb_history_list_length	45
      Innodb_history_list_length	17
      Innodb_history_list_length	93
      Innodb_history_list_length	56
      Innodb_history_list_length	40
      Innodb_history_list_length	20
      Innodb_history_list_length	80
      Innodb_history_list_length	58
      Innodb_history_list_length	67
      Innodb_history_list_length	36
      Innodb_history_list_length	49
      Innodb_history_list_length	27
      Innodb_history_list_length	80
      Innodb_history_list_length	29
      Innodb_history_list_length	54
      Innodb_history_list_length	73
      Innodb_history_list_length	30
      Innodb_history_list_length	8
      Innodb_history_list_length	3
      Innodb_history_list_length	16
      Innodb_history_list_length	68
      Innodb_history_list_length	78
      Innodb_history_list_length	4
      Innodb_history_list_length	13
      Innodb_history_list_length	77
      Innodb_history_list_length	47
      Innodb_history_list_length	27
      Innodb_history_list_length	12
      Innodb_history_list_length	40
      Innodb_history_list_length	16
      Innodb_history_list_length	1
      Innodb_history_list_length	92
      Innodb_history_list_length	54
      Innodb_history_list_length	41
      Innodb_history_list_length	92
      Innodb_history_list_length	58
      Innodb_history_list_length	57
      Innodb_history_list_length	44
      Innodb_history_list_length	16
      Innodb_history_list_length	68
      Innodb_history_list_length	76
      Innodb_history_list_length	81
      Innodb_history_list_length	88
      Innodb_history_list_length	69
      Innodb_history_list_length	76
      Innodb_history_list_length	61
      Innodb_history_list_length	43
      Innodb_history_list_length	36
      Innodb_history_list_length	72
      Innodb_history_list_length	75
      Innodb_history_list_length	32
      Innodb_history_list_length	8
      Innodb_history_list_length	11
      Innodb_history_list_length	8
      Innodb_history_list_length	11
      Innodb_history_list_length	16
      Innodb_history_list_length	29
      Innodb_history_list_length	60
      Innodb_history_list_length	31
      Innodb_history_list_length	81
      Innodb_history_list_length	60
      Innodb_history_list_length	45
      Innodb_history_list_length	26
      Innodb_history_list_length	9
      Innodb_history_list_length	86
      Innodb_history_list_length	84
      Innodb_history_list_length	44
      Innodb_history_list_length	17
      Innodb_history_list_length	34
      Innodb_history_list_length	24
      Innodb_history_list_length	92
      Innodb_history_list_length	71
      Innodb_history_list_length	24
      Innodb_history_list_length	97
      Innodb_history_list_length	82
      Innodb_history_list_length	12
      Innodb_history_list_length	4
      Innodb_history_list_length	84
      Innodb_history_list_length	93
      Innodb_history_list_length	20
      Innodb_history_list_length	4
      Innodb_history_list_length	4
      Innodb_history_list_length	25
      Innodb_history_list_length	13
      Innodb_history_list_length	4
      Innodb_history_list_length	10
      Innodb_history_list_length	96
      Innodb_history_list_length	32
      Innodb_history_list_length	4
      Innodb_history_list_length	4
      Innodb_history_list_length	12
      Innodb_history_list_length	8
      Innodb_history_list_length	16
      Innodb_history_list_length	8
      Innodb_history_list_length	4
      Innodb_history_list_length	37
      Innodb_history_list_length	38
      Innodb_history_list_length	72
      Innodb_history_list_length	20
      Innodb_history_list_length	33
      Innodb_history_list_length	84
      Innodb_history_list_length	28
      Innodb_history_list_length	8
      Innodb_history_list_length	92
      Innodb_history_list_length	83
      Innodb_history_list_length	83
      Innodb_history_list_length	52
      Innodb_history_list_length	22
      Innodb_history_list_length	28
      Innodb_history_list_length	24
      Innodb_history_list_length	20
      Innodb_history_list_length	17
      Innodb_history_list_length	60
      Innodb_history_list_length	39
      Innodb_history_list_length	44
      Innodb_history_list_length	9
      Innodb_history_list_length	8
      Innodb_history_list_length	72
      Innodb_history_list_length	72
      Innodb_history_list_length	23
      Innodb_history_list_length	34
      Innodb_history_list_length	41
      Innodb_history_list_length	21
      Innodb_history_list_length	12
      Innodb_history_list_length	87
      Innodb_history_list_length	78
      Innodb_history_list_length	56
      Innodb_history_list_length	29
      Innodb_history_list_length	41
      Innodb_history_list_length	54
      Innodb_history_list_length	24
      Innodb_history_list_length	28
      Innodb_history_list_length	68
      Innodb_history_list_length	24
      Innodb_history_list_length	39
      Innodb_history_list_length	20
      Innodb_history_list_length	70
      Innodb_history_list_length	79
      Innodb_history_list_length	72
      Innodb_history_list_length	44
      Innodb_history_list_length	48
      Innodb_history_list_length	39
      Innodb_history_list_length	38
      Innodb_history_list_length	56
      Innodb_history_list_length	56
      Innodb_history_list_length	48
      Innodb_history_list_length	4
      Innodb_history_list_length	88
      Innodb_history_list_length	80
      Innodb_history_list_length	80
      Innodb_history_list_length	74
      Innodb_history_list_length	94
      Innodb_history_list_length	49
      Innodb_history_list_length	6
      Innodb_history_list_length	29
      Innodb_history_list_length	30
      Innodb_history_list_length	29
      Innodb_history_list_length	72
      Innodb_history_list_length	65
      Innodb_history_list_length	40
      Innodb_history_list_length	31
      Innodb_history_list_length	92
      Innodb_history_list_length	88
      Innodb_history_list_length	59
      Innodb_history_list_length	45
      Innodb_history_list_length	15
      Innodb_history_list_length	52
      Innodb_history_list_length	91
      Innodb_history_list_length	79
      Innodb_history_list_length	22
      Innodb_history_list_length	14
      Innodb_history_list_length	24
      Innodb_history_list_length	53
      Innodb_history_list_length	33
      Innodb_history_list_length	36
      Innodb_history_list_length	77
      Innodb_history_list_length	84
      Innodb_history_list_length	40
      Innodb_history_list_length	40
      Innodb_history_list_length	40
      Innodb_history_list_length	33
      Innodb_history_list_length	56
      Innodb_history_list_length	37
      Innodb_history_list_length	26
      Innodb_history_list_length	24
      Innodb_history_list_length	99
      Innodb_history_list_length	85
      Innodb_history_list_length	54
      Innodb_history_list_length	46
      Innodb_history_list_length	50
      Innodb_history_list_length	16
      Innodb_history_list_length	20
      Innodb_history_list_length	85
      Innodb_history_list_length	17
      Innodb_history_list_length	12
      Innodb_history_list_length	60
      Innodb_history_list_length	50
      Innodb_history_list_length	68
      Innodb_history_list_length	40
      Innodb_history_list_length	97
      Yuliyas-MacBook-Air:maria10.5 Valerii$ bin/mysql -e 'show global status like "%history%"';
      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Innodb_history_list_length | 68    |
      +----------------------------+-------+
      Yuliyas-MacBook-Air:maria10.5 Valerii$ 
      

      Note that it never grows even to 100 (this is on an old MacBook Air 2015 with 4 cores and SSD).

      With 10.6.9 started with --no-defaults the same test gives:

      Yuliyas-MacBook-Air:maria10.6 Valerii$ bin/mysqld_safe --no-defaults &
      [1] 51231
      Yuliyas-MacBook-Air:maria10.6 Valerii$ 220827 15:41:30 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.err'.
      220827 15:41:30 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.6/data
       
      Yuliyas-MacBook-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb%purge%"';
      +--------------------------------------+------------+
      | Variable_name                        | Value      |
      +--------------------------------------+------------+
      | innodb_max_purge_lag                 | 0          |
      | innodb_max_purge_lag_delay           | 0          |
      | innodb_max_purge_lag_wait            | 4294967295 |
      | innodb_purge_batch_size              | 300        |
      | innodb_purge_rseg_truncate_frequency | 128        |
      | innodb_purge_threads                 | 4          |
      +--------------------------------------+------------+
      Yuliyas-MacBook-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"';
      +----------------------------+-------+
      | Variable_name              | Value |
      +----------------------------+-------+
      | Innodb_history_list_length | 4     |
      +----------------------------+-------+
      Yuliyas-MacBook-Air:maria10.6 Valerii$ bin/mysql -e 'create database sbtest';
      

      and then:

      Yuliyas-MacBook-Air:sysbench Valerii$ ./src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --time=300 --report_interval=10 prepare
      sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
       
      Initializing worker threads...
       
      Creating table 'sbtest1'...Creating table 'sbtest2'...Creating table 'sbtest4'...
      Creating table 'sbtest3'...
       
       
      Inserting 100000 records into 'sbtest1'
      Inserting 100000 records into 'sbtest2'
      Inserting 100000 records into 'sbtest4'
      Inserting 100000 records into 'sbtest3'
      Creating a secondary index on 'sbtest1'...
      Creating a secondary index on 'sbtest3'...
      Creating a secondary index on 'sbtest2'...
      Creating a secondary index on 'sbtest4'...
      Yuliyas-MacBook-Air:sysbench Valerii$ ./src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --time=300 --report_interval=10 run
      sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
       
      Running the test with following options:
      Number of threads: 4
      Report intermediate results every 10 second(s)
      Initializing random number generator from current time
       
       
      Initializing worker threads...
       
      Threads started!
       
      [ 10s ] thds: 4 tps: 603.02 qps: 12066.02 (r/w/o: 8447.49/2412.08/1206.44) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 20s ] thds: 4 tps: 604.71 qps: 12095.01 (r/w/o: 8466.18/2419.42/1209.41) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 30s ] thds: 4 tps: 607.60 qps: 12150.59 (r/w/o: 8505.60/2429.80/1215.20) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 40s ] thds: 4 tps: 614.18 qps: 12285.11 (r/w/o: 8599.36/2457.40/1228.35) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 50s ] thds: 4 tps: 597.57 qps: 11950.47 (r/w/o: 8364.63/2390.79/1195.05) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
      [ 60s ] thds: 4 tps: 616.51 qps: 12332.02 (r/w/o: 8632.48/2466.42/1233.11) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 70s ] thds: 4 tps: 607.43 qps: 12146.90 (r/w/o: 8503.95/2428.10/1214.85) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 80s ] thds: 4 tps: 601.10 qps: 12020.89 (r/w/o: 8414.26/2404.42/1202.21) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 90s ] thds: 4 tps: 610.95 qps: 12219.33 (r/w/o: 8553.65/2443.79/1221.89) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 100s ] thds: 4 tps: 607.67 qps: 12155.79 (r/w/o: 8508.17/2432.28/1215.34) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 110s ] thds: 4 tps: 598.80 qps: 11968.57 (r/w/o: 8377.58/2393.59/1197.40) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 120s ] thds: 4 tps: 597.24 qps: 11950.60 (r/w/o: 8366.96/2388.96/1194.68) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
      [ 130s ] thds: 4 tps: 597.25 qps: 11946.35 (r/w/o: 8361.54/2390.31/1194.51) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
      [ 140s ] thds: 4 tps: 593.66 qps: 11867.71 (r/w/o: 8307.05/2373.34/1187.32) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
      [ 150s ] thds: 4 tps: 599.07 qps: 11985.28 (r/w/o: 8389.77/2397.48/1198.04) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
      [ 160s ] thds: 4 tps: 602.51 qps: 12046.31 (r/w/o: 8432.35/2408.84/1205.12) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 170s ] thds: 4 tps: 586.73 qps: 11738.76 (r/w/o: 8218.39/2346.91/1173.46) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00
      [ 180s ] thds: 4 tps: 611.28 qps: 12227.12 (r/w/o: 8557.86/2446.70/1222.55) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 190s ] thds: 4 tps: 599.95 qps: 11994.93 (r/w/o: 8396.42/2398.61/1199.90) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 200s ] thds: 4 tps: 605.24 qps: 12105.68 (r/w/o: 8474.61/2420.58/1210.49) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 210s ] thds: 4 tps: 610.98 qps: 12222.81 (r/w/o: 8555.36/2445.50/1221.95) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00
      [ 220s ] thds: 4 tps: 618.72 qps: 12370.35 (r/w/o: 8659.61/2473.29/1237.45) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
      [ 230s ] thds: 4 tps: 622.05 qps: 12445.03 (r/w/o: 8711.15/2489.79/1244.09) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
      [ 240s ] thds: 4 tps: 608.42 qps: 12162.67 (r/w/o: 8513.73/2432.09/1216.85) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 250s ] thds: 4 tps: 618.69 qps: 12379.66 (r/w/o: 8665.90/2476.37/1237.39) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00
      [ 260s ] thds: 4 tps: 590.16 qps: 11801.45 (r/w/o: 8262.01/2359.13/1180.32) lat (ms,95%): 10.09 err/s: 0.00 reconn/s: 0.00
      [ 270s ] thds: 4 tps: 602.18 qps: 12045.33 (r/w/o: 8430.74/2410.23/1204.36) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
      [ 280s ] thds: 4 tps: 592.40 qps: 11843.06 (r/w/o: 8290.24/2368.01/1184.81) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
      [ 290s ] thds: 4 tps: 584.11 qps: 11685.27 (r/w/o: 8180.59/2336.45/1168.23) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00
      [ 300s ] thds: 4 tps: 587.95 qps: 11758.94 (r/w/o: 8231.26/2351.79/1175.89) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00
      SQL statistics:
          queries performed:
              read:                            2533818
              write:                           723948
              other:                           361974
              total:                           3619740
          transactions:                        180987 (603.27 per sec.)
          queries:                             3619740 (12065.36 per sec.)
          ignored errors:                      0      (0.00 per sec.)
          reconnects:                          0      (0.00 per sec.)
       
      Throughput:
          events/s (eps):                      603.2679
          time elapsed:                        300.0110s
          total number of events:              180987
       
      Latency (ms):
               min:                                    2.73
               avg:                                    6.63
               max:                                   87.35
               95th percentile:                        9.22
               sum:                              1199374.08
       
      Threads fairness:
          events (avg/stddev):           45246.7500/30.85
          execution time (avg/stddev):   299.8435/0.00
      

      We get a bit smaller TPS and QPS throughput, but the same script checking history list length for 200 seconds gives the following:

      Yuliyas-MacBook-Air:maria10.6 Valerii$ for i in `seq 1 200`; do bin/mysql -B -e "show global status like '%history%'" | grep history ; sleep 1; done
      Innodb_history_list_length	1653
      Innodb_history_list_length	2303
      Innodb_history_list_length	2876
      Innodb_history_list_length	3517
      Innodb_history_list_length	4142
      Innodb_history_list_length	4786
      Innodb_history_list_length	5406
      Innodb_history_list_length	6026
      Innodb_history_list_length	6625
      Innodb_history_list_length	7278
      Innodb_history_list_length	7913
      Innodb_history_list_length	8557
      Innodb_history_list_length	9192
      Innodb_history_list_length	9772
      Innodb_history_list_length	10443
      Innodb_history_list_length	11053
      Innodb_history_list_length	11700
      Innodb_history_list_length	12313
      Innodb_history_list_length	12932
      Innodb_history_list_length	13571
      Innodb_history_list_length	14231
      Innodb_history_list_length	14859
      Innodb_history_list_length	15470
      Innodb_history_list_length	16088
      Innodb_history_list_length	16740
      Innodb_history_list_length	17355
      Innodb_history_list_length	17995
      Innodb_history_list_length	18627
      Innodb_history_list_length	19244
      Innodb_history_list_length	19879
      Innodb_history_list_length	20577
      Innodb_history_list_length	530
      Innodb_history_list_length	1154
      Innodb_history_list_length	1783
      Innodb_history_list_length	2436
      Innodb_history_list_length	3048
      Innodb_history_list_length	3727
      Innodb_history_list_length	4365
      Innodb_history_list_length	5027
      Innodb_history_list_length	5672
      Innodb_history_list_length	6254
      Innodb_history_list_length	6835
      Innodb_history_list_length	7408
      Innodb_history_list_length	8043
      Innodb_history_list_length	8687
      Innodb_history_list_length	9308
      Innodb_history_list_length	9941
      Innodb_history_list_length	10611
      Innodb_history_list_length	11258
      Innodb_history_list_length	11890
      Innodb_history_list_length	12543
      Innodb_history_list_length	13170
      Innodb_history_list_length	13800
      Innodb_history_list_length	14417
      Innodb_history_list_length	15063
      Innodb_history_list_length	15707
      Innodb_history_list_length	16372
      Innodb_history_list_length	16999
      Innodb_history_list_length	17631
      Innodb_history_list_length	18275
      Innodb_history_list_length	598
      Innodb_history_list_length	1213
      Innodb_history_list_length	1845
      Innodb_history_list_length	2470
      Innodb_history_list_length	3068
      Innodb_history_list_length	3666
      Innodb_history_list_length	4299
      Innodb_history_list_length	4954
      Innodb_history_list_length	5605
      Innodb_history_list_length	6247
      Innodb_history_list_length	6889
      Innodb_history_list_length	7479
      Innodb_history_list_length	8118
      Innodb_history_list_length	8720
      Innodb_history_list_length	9326
      Innodb_history_list_length	9969
      Innodb_history_list_length	10636
      Innodb_history_list_length	11295
      Innodb_history_list_length	11891
      Innodb_history_list_length	12534
      Innodb_history_list_length	13174
      Innodb_history_list_length	13801
      Innodb_history_list_length	14411
      Innodb_history_list_length	15044
      Innodb_history_list_length	15676
      Innodb_history_list_length	16309
      Innodb_history_list_length	16953
      Innodb_history_list_length	17620
      Innodb_history_list_length	12695
      Innodb_history_list_length	656
      Innodb_history_list_length	1282
      Innodb_history_list_length	1887
      Innodb_history_list_length	2520
      Innodb_history_list_length	3108
      Innodb_history_list_length	3756
      Innodb_history_list_length	4400
      Innodb_history_list_length	5057
      Innodb_history_list_length	5705
      Innodb_history_list_length	6338
      Innodb_history_list_length	6969
      Innodb_history_list_length	7524
      Innodb_history_list_length	8169
      Innodb_history_list_length	8766
      Innodb_history_list_length	9345
      Innodb_history_list_length	9988
      Innodb_history_list_length	10633
      Innodb_history_list_length	11265
      Innodb_history_list_length	11894
      Innodb_history_list_length	12470
      Innodb_history_list_length	13063
      Innodb_history_list_length	13657
      Innodb_history_list_length	14278
      Innodb_history_list_length	14918
      Innodb_history_list_length	15566
      Innodb_history_list_length	16213
      Innodb_history_list_length	16840
      Innodb_history_list_length	17486
      Innodb_history_list_length	18109
      Innodb_history_list_length	76
      Innodb_history_list_length	704
      Innodb_history_list_length	1294
      Innodb_history_list_length	1936
      Innodb_history_list_length	2530
      Innodb_history_list_length	3167
      Innodb_history_list_length	3800
      Innodb_history_list_length	4445
      Innodb_history_list_length	5093
      Innodb_history_list_length	5737
      Innodb_history_list_length	6346
      Innodb_history_list_length	6932
      Innodb_history_list_length	7479
      Innodb_history_list_length	8092
      Innodb_history_list_length	8705
      Innodb_history_list_length	9334
      Innodb_history_list_length	9968
      Innodb_history_list_length	10609
      Innodb_history_list_length	11220
      Innodb_history_list_length	11884
      Innodb_history_list_length	12518
      Innodb_history_list_length	13107
      Innodb_history_list_length	13710
      Innodb_history_list_length	14342
      Innodb_history_list_length	14953
      Innodb_history_list_length	15556
      Innodb_history_list_length	16178
      Innodb_history_list_length	16796
      Innodb_history_list_length	17438
      Innodb_history_list_length	18071
      Innodb_history_list_length	193
      Innodb_history_list_length	791
      Innodb_history_list_length	1434
      Innodb_history_list_length	2044
      Innodb_history_list_length	2677
      Innodb_history_list_length	3312
      Innodb_history_list_length	3952
      Innodb_history_list_length	4594
      Innodb_history_list_length	5214
      Innodb_history_list_length	5824
      Innodb_history_list_length	6430
      Innodb_history_list_length	6954
      Innodb_history_list_length	7591
      Innodb_history_list_length	8176
      Innodb_history_list_length	8846
      Innodb_history_list_length	9484
      Innodb_history_list_length	10127
      Innodb_history_list_length	10756
      Innodb_history_list_length	11404
      Innodb_history_list_length	12024
      Innodb_history_list_length	12672
      Innodb_history_list_length	13267
      Innodb_history_list_length	13904
      Innodb_history_list_length	14540
      Innodb_history_list_length	15176
      Innodb_history_list_length	15797
      Innodb_history_list_length	16407
      Innodb_history_list_length	17044
      Innodb_history_list_length	17686
      Innodb_history_list_length	18330
      Innodb_history_list_length	18939
      Innodb_history_list_length	556
      Innodb_history_list_length	1171
      Innodb_history_list_length	1837
      Innodb_history_list_length	2441
      Innodb_history_list_length	3097
      Innodb_history_list_length	3708
      Innodb_history_list_length	4378
      Innodb_history_list_length	5028
      Innodb_history_list_length	5603
      Innodb_history_list_length	6238
      Innodb_history_list_length	6857
      Innodb_history_list_length	7458
      Innodb_history_list_length	8100
      Innodb_history_list_length	8754
      Innodb_history_list_length	9377
      Innodb_history_list_length	9994
      Innodb_history_list_length	10622
      Innodb_history_list_length	11247
      Innodb_history_list_length	11910
      Innodb_history_list_length	12528
      Innodb_history_list_length	13184
      

      So, the length grow up to 19000 and is constantly much higher than with 10.5, all this with the same default values. This will definitely impact any concurrent SELECTs and in general can be considered a performance regression with default settings that should be documented if not resolved.

      Attachments

        1. write-only2.pdf
          87 kB
        2. tpcc2.pdf
          220 kB
        3. pt_large.test
          1 kB

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              valerii Valerii Kravchuk
              Votes:
              4 Vote for this issue
              Watchers:
              17 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.