[MDEV-29401] InnoDB history list length increased in 10.6 compared to 10.5 for the same load - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.6.9
Fix Version/s: 11.1.1, 10.11.3, 11.0.2, 10.6.13, 10.8.8, 10.9.6, 10.10.4
Component/s: Storage Engine - InnoDB
Labels:
None

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

write-only2.pdf
87 kB
2023-04-27 19:39
tpcc2.pdf
220 kB
2023-04-28 09:04
pt_large.test
1 kB
2023-04-02 10:20

Issue Links

blocks

MDEV-16260 Scale the purge effort according to the workload

Open

is part of

MDEV-30628 10.6 performance regression with sustained high-connection write-only OLTP workload (55-80% degradation)

Closed

relates to

MDEV-29593 Purge misses a chance to free not-yet-reused undo pages

Closed

MDEV-30100 Assertion `purge_sys.tail.trx_no <= purge_sys.rseg->last_trx_no()'

Closed

MDEV-31169 innodb_max_purge_lag is documented as being in milliseconds, not microseconds

Closed

MDEV-29296 Undo Size Problems with MariaDB 10.6

Closed

MDEV-32050 UNDO logs still growing for write-intensive workloads

Closed

(2 relates to)

Activity

People

Assignee:: Marko Mäkelä

Reporter:: Valerii Kravchuk

Votes:: 4 Vote for this issue

Watchers:: 17 Start watching this issue

Dates

Created:: 2022-08-27 13:28

Updated:: 2024-07-07 19:52

Resolved:: 2023-04-27 14:41

Git Integration

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