[#MDEV-31234] InnoDB does not free UNDO after the fix of MDEV-30671, thus shared tablespace (ibdata1) may grow indefinitely for no good reason

Yuliyas-Air:~ Valerii$ cd dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ rm -rf data/

Yuliyas-Air:maria10.6 Valerii$ scripts/mysql_install_db --no-defaults

Installing MariaDB/MySQL system tables in './data' ...

2023-05-10 16:14:22 0 [Warning] InnoDB: innodb_open_files 300 should not be greater than the open_files_limit 256

OK

To start mariadbd at boot time you have to copy

support-files/mariadb.service to the right place for your system

Two all-privilege accounts were created.

One is root@localhost, it has no password, but you need to

be system 'root' user to connect. Use, for example, sudo mysql

The second is Valerii@localhost, it has no password either, but

you need to be the system 'Valerii' user to connect.

After connecting you can set the password, if you would need to be

able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:

cd '.' ; ./bin/mariadb-safe --datadir='./data'

You can test the MariaDB daemon with mysql-test-run.pl

cd './mysql-test' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:

https://mariadb.org/get-involved/

Yuliyas-Air:maria10.6 Valerii$ bin/mysqld_safe --no-defaults --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=128M &

[1] 76002

Yuliyas-Air:maria10.6 Valerii$ 230510 16:16:09 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.err'.

230510 16:16:09 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.6/data

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'create database sbtest';

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  12582912 10 May 16:14 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ cd ~/git/sysbench

Yuliyas-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 --report_interval=10 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest4'...Creating table 'sbtest3'...Creating table 'sbtest2'...

Creating table 'sbtest1'...

Inserting 100000 records into 'sbtest1'

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest4'

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest3'...

Yuliyas-Air:sysbench Valerii$ ls -l data/ibdata1

ls: data/ibdata1: No such file or directory

Yuliyas-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 --report_interval=10 --time=100 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: 1116.06 qps: 22325.01 (r/w/o: 15628.28/4464.22/2232.51) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 1145.58 qps: 22911.35 (r/w/o: 16037.86/4582.33/2291.17) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 1102.88 qps: 22057.62 (r/w/o: 15440.36/4411.50/2205.75) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 1041.18 qps: 20826.21 (r/w/o: 14578.43/4165.42/2082.36) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 867.21 qps: 17340.72 (r/w/o: 12138.15/3468.14/1734.42) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 1019.78 qps: 20394.57 (r/w/o: 14275.87/4079.13/2039.57) lat (ms,95%): 6.21 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 1159.62 qps: 23195.84 (r/w/o: 16238.01/4638.59/2319.24) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 1124.21 qps: 22484.66 (r/w/o: 15739.21/4497.03/2248.42) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 1117.99 qps: 22359.21 (r/w/o: 15651.57/4471.66/2235.98) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1519084

        write:                           434024

        other:                           217012

        total:                           2170120

    transactions:                        108506 (1085.01 per sec.)

    queries:                             2170120 (21700.25 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      1085.0124

    time elapsed:                        100.0044s

    total number of events:              108506

Latency (ms):

         min:                                    1.77

         avg:                                    3.68

         max:                                  103.45

         95th percentile:                        5.57

         sum:                               399636.30

Threads fairness:

    events (avg/stddev):           27126.5000/11.08

    execution time (avg/stddev):   99.9091/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  79691776 10 May 16:20 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 14009 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     101	Index page

    3050	Undo log page

      38	Inode page

       0	Insert buffer free list page

    1504	Freshly allocated page

       1	Insert buffer bitmap

     168	System page

       1	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 3050

Undo page state: 0 active, 0 cached, 3050 to_purge, 0 prepared, 0 other

...

Yuliyas-Air:maria10.5 Valerii$ rm -rf data/

Yuliyas-Air:maria10.5 Valerii$

Yuliyas-Air:maria10.5 Valerii$ scripts/mysql_install_db --no-defaults

Installing MariaDB/MySQL system tables in './data' ...

2023-05-10 16:22:15 0 [Warning] InnoDB: innodb_open_files 300 should not be greater than the open_files_limit 256

OK

To start mariadbd at boot time you have to copy

support-files/mariadb.service to the right place for your system

Two all-privilege accounts were created.

One is root@localhost, it has no password, but you need to

be system 'root' user to connect. Use, for example, sudo mysql

The second is Valerii@localhost, it has no password either, but

you need to be the system 'Valerii' user to connect.

After connecting you can set the password, if you would need to be

able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:

cd '.' ; ./bin/mariadb-safe --datadir='./data'

You can test the MariaDB daemon with mysql-test-run.pl

cd './mysql-test' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:

https://mariadb.org/get-involved/

Yuliyas-Air:maria10.5 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  12582912 10 May 16:22 data/ibdata1

Yuliyas-Air:maria10.5 Valerii$ bin/mysqld_safe --no-defaults --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=128M &

[1] 76269

Yuliyas-Air:maria10.5 Valerii$ 230510 16:22:51 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.5/data/Yuliyas-Air.err'.

230510 16:22:51 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.5/data

Yuliyas-Air:maria10.5 Valerii$ bin/mysql -e 'create database sbtest';

Yuliyas-Air:maria10.5 Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ cd ~/git/sysbench

Yuliyas-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 --report_interval=10 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest1'...Creating table 'sbtest4'...Creating table 'sbtest2'...

Creating table 'sbtest3'...

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest1'...

Yuliyas-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 --report_interval=10 --time=100 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: 930.13 qps: 18608.20 (r/w/o: 13026.62/3720.92/1860.66) lat (ms,95%): 5.67 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 980.93 qps: 19618.58 (r/w/o: 13733.01/3923.72/1961.86) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 939.24 qps: 18786.84 (r/w/o: 13150.19/3758.17/1878.48) lat (ms,95%): 5.88 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 902.83 qps: 18054.89 (r/w/o: 12638.68/3610.54/1805.67) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 896.09 qps: 17920.59 (r/w/o: 12544.62/3583.78/1792.19) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 974.70 qps: 19494.90 (r/w/o: 13646.93/3898.58/1949.39) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 955.04 qps: 19098.98 (r/w/o: 13368.72/3820.18/1910.09) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 973.13 qps: 19461.73 (r/w/o: 13622.94/3892.53/1946.26) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 975.06 qps: 19502.48 (r/w/o: 13652.10/3900.26/1950.13) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 914.84 qps: 18297.92 (r/w/o: 12808.48/3659.86/1829.58) lat (ms,95%): 6.21 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1321978

        write:                           377708

        other:                           188854

        total:                           1888540

    transactions:                        94427  (944.21 per sec.)

    queries:                             1888540 (18884.22 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      944.2111

    time elapsed:                        100.0062s

    total number of events:              94427

Latency (ms):

         min:                                    1.94

         avg:                                    4.23

         max:                                   98.29

         95th percentile:                        5.88

         sum:                               399536.68

Threads fairness:

    events (avg/stddev):           23606.7500/79.99

    execution time (avg/stddev):   99.8842/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ cd ../maria10.5

Yuliyas-Air:maria10.5 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  79691776 10 May 16:25 data/ibdata1

Yuliyas-Air:maria10.5 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     141	Index page

     383	Undo log page

       6	Inode page

       0	Insert buffer free list page

    4201	Freshly allocated page

       1	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 383

Undo page state: 5 active, 378 cached, 0 to_purge, 0 prepared, 0 other

...

Yuliyas-Air:maria10.6 Valerii$ bin/mysqld_safe --no-defaults --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=128M &

[1] 76809

Yuliyas-Air:maria10.6 Valerii$ 230510 16:41:42 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.err'.

230510 16:41:42 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.6/data

Yuliyas-Air:maria10.6 Valerii$ ./src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --report_interval=10 --time=100 cleanup

-bash: ./src/sysbench: No such file or directory

Yuliyas-Air:maria10.6 Valerii$ cd ~/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 cleanup

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest1'...Creating table 'sbtest2'...Creating table 'sbtest3'...

Creating table 'sbtest4'...

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 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: 1089.98 qps: 21805.61 (r/w/o: 15265.03/4360.22/2180.36) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 1159.46 qps: 23188.84 (r/w/o: 16232.40/4637.53/2318.91) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 1146.60 qps: 22929.39 (r/w/o: 16049.79/4586.40/2293.20) lat (ms,95%): 5.00 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 1164.29 qps: 23290.07 (r/w/o: 16303.01/4658.47/2328.59) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 1114.27 qps: 22281.81 (r/w/o: 15597.58/4455.78/2228.44) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 1143.25 qps: 22867.82 (r/w/o: 16007.31/4573.90/2286.60) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 1177.78 qps: 23553.43 (r/w/o: 16487.64/4710.23/2355.56) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 1119.55 qps: 22391.45 (r/w/o: 15674.17/4478.19/2239.10) lat (ms,95%): 5.00 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 1133.05 qps: 22657.43 (r/w/o: 15859.15/4532.19/2266.09) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 1134.62 qps: 22697.08 (r/w/o: 15889.03/4538.80/2269.25) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1593704

        write:                           455344

        other:                           227672

        total:                           2276720

    transactions:                        113836 (1138.30 per sec.)

    queries:                             2276720 (22765.98 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      1138.2991

    time elapsed:                        100.0054s

    total number of events:              113836

Latency (ms):

         min:                                    1.78

         avg:                                    3.51

         max:                                  189.23

         95th percentile:                        4.91

         sum:                               399628.71

Threads fairness:

    events (avg/stddev):           28459.0000/51.09

    execution time (avg/stddev):   99.9072/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  146800640 10 May 16:44 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     132	Index page

    7414	Undo log page

      91	Inode page

       0	Insert buffer free list page

    1185	Freshly allocated page

       3	Insert buffer bitmap

     130	System page

       1	Transaction system page

       4	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 7414

Undo page state: 0 active, 0 cached, 7378 to_purge, 0 prepared, 36 other

...

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     132	Index page

    7414	Undo log page

      91	Inode page

       0	Insert buffer free list page

    1185	Freshly allocated page

       3	Insert buffer bitmap

     130	System page

       1	Transaction system page

       4	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 7414

Undo page state: 0 active, 0 cached, 7378 to_purge, 0 prepared, 36 other

...

Yuliyas-Air:maria10.5 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  79691776 10 May 16:51 data/ibdata1

Yuliyas-Air:maria10.5 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     141	Index page

     519	Undo log page

       7	Inode page

       0	Insert buffer free list page

    4064	Freshly allocated page

       1	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 519

Undo page state: 5 active, 493 cached, 9 to_purge, 0 prepared, 12 other

...

Yuliyas-Air:server Valerii$ git log -1

commit afe44ef2126d74c11407266cf7d0225ac0392e61 (HEAD -> 10.6, origin/bb-10.6-bumpversion, origin/10.6)

Author: Daniel Bartholomew <db@dbart.us>

Date:   Wed May 10 08:45:08 2023 -0400

    bump the VERSION

Yuliyas-Air:~ Valerii$ cd dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ rm -rf data/

Yuliyas-Air:maria10.6 Valerii$ scripts/mysql_install_db --no-defaults

Installing MariaDB/MySQL system tables in './data' ...

2023-05-10 18:25:00 0 [Warning] InnoDB: innodb_open_files 300 should not be greater than the open_files_limit 256

OK

To start mariadbd at boot time you have to copy

support-files/mariadb.service to the right place for your system

Two all-privilege accounts were created.

One is root@localhost, it has no password, but you need to

be system 'root' user to connect. Use, for example, sudo mysql

The second is Valerii@localhost, it has no password either, but

you need to be the system 'Valerii' user to connect.

After connecting you can set the password, if you would need to be

able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:

cd '.' ; ./bin/mariadb-safe --datadir='./data'

You can test the MariaDB daemon with mysql-test-run.pl

cd './mysql-test' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:

https://mariadb.org/get-involved/

Yuliyas-Air:maria10.6 Valerii$

Yuliyas-Air:maria10.6 Valerii$ bin/mysqld_safe --no-defaults --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=128M &

[1] 3387

Yuliyas-Air:maria10.6 Valerii$ 230510 18:25:23 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.err'.

230510 18:25:23 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.6/data

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'select version()'

+-----------------+

| version()       |

+-----------------+

| 10.6.14-MariaDB |

+-----------------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'create database sbtest;'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "History%"'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 6     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ~/git/sysbench/src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

FATAL: Cannot find benchmark 'oltp_read_write': no such built-in test, file or module

Yuliyas-Air:maria10.6 Valerii$ cd ~/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest1'...Creating table 'sbtest3'...Creating table 'sbtest2'...Creating table 'sbtest4'...

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest1'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest4'

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 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: 1130.75 qps: 22619.94 (r/w/o: 15834.83/4523.21/2261.90) lat (ms,95%): 5.00 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 1058.67 qps: 21175.70 (r/w/o: 14822.75/4235.60/2117.35) lat (ms,95%): 5.67 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 1129.88 qps: 22595.45 (r/w/o: 15816.96/4518.73/2259.76) lat (ms,95%): 4.41 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 1032.51 qps: 20648.74 (r/w/o: 14454.07/4129.75/2064.92) lat (ms,95%): 5.67 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 1024.65 qps: 20495.53 (r/w/o: 14347.52/4098.61/2049.40) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 988.26 qps: 19764.99 (r/w/o: 13835.43/3953.04/1976.52) lat (ms,95%): 6.43 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 1045.45 qps: 20908.07 (r/w/o: 14635.38/4181.79/2090.90) lat (ms,95%): 5.77 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 1171.56 qps: 23432.09 (r/w/o: 16402.71/4686.26/2343.13) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 939.60 qps: 18791.84 (r/w/o: 13154.26/3758.39/1879.19) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1500254

        write:                           428644

        other:                           214322

        total:                           2143220

    transactions:                        107161 (1071.58 per sec.)

    queries:                             2143220 (21431.70 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      1071.5848

    time elapsed:                        100.0023s

    total number of events:              107161

Latency (ms):

         min:                                    1.81

         avg:                                    3.73

         max:                                  108.74

         95th percentile:                        5.28

         sum:                               399637.95

Threads fairness:

    events (avg/stddev):           26790.2500/49.49

    execution time (avg/stddev):   99.9095/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  79691776 10 May 18:29 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     136	Index page

    3029	Undo log page

      34	Inode page

       0	Insert buffer free list page

    1532	Freshly allocated page

       1	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 3029

Undo page state: 3 active, 128 cached, 2602 to_purge, 0 prepared, 296 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

23		21		21		73		15038

24		39		39		73		15038

25		12		12		73		15038

26		33		33		73		15038

27		8		8		831		10814

28		3		3		789		10257

29		5		5		960		12480

30		4		4		900		11706

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

23		0	0	0	0	0	0	0	0	0	0	21	0

24		0	0	0	0	0	0	0	0	0	0	39	0

25		0	0	0	0	0	0	0	0	0	0	12	0

26		0	0	0	0	0	0	0	0	0	0	33	0

27		0	0	0	0	0	4	1	0	0	0	3	0

28		0	0	0	0	0	2	0	0	0	0	1	0

29		0	0	0	0	0	2	0	0	0	0	3	0

30		0	0	0	0	0	2	0	0	0	0	2	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 14841 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     136	Index page

    3029	Undo log page

      34	Inode page

       0	Insert buffer free list page

    1532	Freshly allocated page

       1	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 3029

Undo page state: 3 active, 128 cached, 2602 to_purge, 0 prepared, 296 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

23		21		21		73		15038

24		39		39		73		15038

25		12		12		73		15038

26		33		33		73		15038

27		8		8		831		10814

28		3		3		789		10257

29		5		5		960		12480

30		4		4		900		11706

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

23		0	0	0	0	0	0	0	0	0	0	21	0

24		0	0	0	0	0	0	0	0	0	0	39	0

25		0	0	0	0	0	0	0	0	0	0	12	0

26		0	0	0	0	0	0	0	0	0	0	33	0

27		0	0	0	0	0	4	1	0	0	0	3	0

28		0	0	0	0	0	2	0	0	0	0	1	0

29		0	0	0	0	0	2	0	0	0	0	3	0

30		0	0	0	0	0	2	0	0	0	0	2	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ cd -

/Users/Valerii/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 cleanup

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest2'...Creating table 'sbtest1'...Creating table 'sbtest4'...Creating table 'sbtest3'...

Inserting 100000 records into 'sbtest1'

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest4'

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 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: 1122.63 qps: 22457.48 (r/w/o: 15721.31/4490.52/2245.66) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 1046.27 qps: 20924.06 (r/w/o: 14646.42/4185.09/2092.55) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 1058.16 qps: 21161.99 (r/w/o: 14813.03/4232.64/2116.32) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 1039.24 qps: 20789.53 (r/w/o: 14552.88/4158.17/2078.48) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 976.35 qps: 19524.75 (r/w/o: 13667.54/3904.51/1952.71) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 999.26 qps: 19985.57 (r/w/o: 13990.12/3996.93/1998.52) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 756.65 qps: 15133.11 (r/w/o: 10593.40/3026.40/1513.30) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 1003.70 qps: 20071.70 (r/w/o: 14049.47/4014.82/2007.41) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 853.51 qps: 17070.48 (r/w/o: 11949.42/3414.14/1706.92) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 926.72 qps: 18534.87 (r/w/o: 12974.23/3707.29/1853.35) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1369662

        write:                           391332

        other:                           195666

        total:                           1956660

    transactions:                        97833  (978.28 per sec.)

    queries:                             1956660 (19565.69 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      978.2843

    time elapsed:                        100.0047s

    total number of events:              97833

Latency (ms):

         min:                                    1.97

         avg:                                    4.09

         max:                                  165.87

         95th percentile:                        7.04

         sum:                               399666.97

Threads fairness:

    events (avg/stddev):           24458.2500/69.78

    execution time (avg/stddev):   99.9167/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 9514  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 9514  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 9514  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  146800640 10 May 18:32 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     132	Index page

    6127	Undo log page

      73	Inode page

       0	Insert buffer free list page

    2490	Freshly allocated page

       3	Insert buffer bitmap

     131	System page

       1	Transaction system page

       3	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 6127

Undo page state: 7 active, 121 cached, 5687 to_purge, 0 prepared, 312 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

31		23		23		73		15038

32		34		34		73		15038

33		15		15		73		15038

34		21		21		73		15038

35		1		1		620		8060

36		7		7		698		9083

37		11		11		789		10257

38		1		1		1191		15483

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

31		0	0	0	0	0	0	0	0	0	0	23	0

32		0	0	0	0	0	0	0	0	0	0	34	0

33		0	0	0	0	0	0	0	0	0	0	15	0

34		0	0	0	0	0	0	0	0	0	0	21	0

35		0	0	0	0	0	1	0	0	0	0	0	0

36		0	0	0	0	0	5	1	0	0	0	1	0

37		0	0	0	0	0	7	0	1	0	0	3	0

38		0	0	0	0	0	0	0	0	0	0	1	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 9514  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     132	Index page

    6127	Undo log page

      73	Inode page

       0	Insert buffer free list page

    2490	Freshly allocated page

       3	Insert buffer bitmap

     131	System page

       1	Transaction system page

       3	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 6127

Undo page state: 7 active, 121 cached, 5687 to_purge, 0 prepared, 312 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

31		23		23		73		15038

32		34		34		73		15038

33		15		15		73		15038

34		21		21		73		15038

35		1		1		620		8060

36		7		7		698		9083

37		11		11		789		10257

38		1		1		1191		15483

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

31		0	0	0	0	0	0	0	0	0	0	23	0

32		0	0	0	0	0	0	0	0	0	0	34	0

33		0	0	0	0	0	0	0	0	0	0	15	0

34		0	0	0	0	0	0	0	0	0	0	21	0

35		0	0	0	0	0	1	0	0	0	0	0	0

36		0	0	0	0	0	5	1	0	0	0	1	0

37		0	0	0	0	0	7	0	1	0	0	3	0

38		0	0	0	0	0	0	0	0	0	0	1	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     132	Index page

    6127	Undo log page

      73	Inode page

       0	Insert buffer free list page

    2490	Freshly allocated page

       3	Insert buffer bitmap

     131	System page

       1	Transaction system page

       3	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 6127

Undo page state: 7 active, 121 cached, 5687 to_purge, 0 prepared, 312 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

31		23		23		73		15038

32		34		34		73		15038

33		15		15		73		15038

34		21		21		73		15038

35		1		1		620		8060

36		7		7		698		9083

37		11		11		789		10257

38		1		1		1191		15483

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

31		0	0	0	0	0	0	0	0	0	0	23	0

32		0	0	0	0	0	0	0	0	0	0	34	0

33		0	0	0	0	0	0	0	0	0	0	15	0

34		0	0	0	0	0	0	0	0	0	0	21	0

35		0	0	0	0	0	1	0	0	0	0	0	0

36		0	0	0	0	0	5	1	0	0	0	1	0

37		0	0	0	0	0	7	0	1	0	0	3	0

38		0	0	0	0	0	0	0	0	0	0	1	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_max_purge_lag%"'

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| innodb_max_purge_lag       | 0          |

| innodb_max_purge_lag_delay | 0          |

| innodb_max_purge_lag_wait  | 4294967295 |

+----------------------------+------------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag = 500000'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag_delay = 5000'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_max_purge_lag%"'

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| innodb_max_purge_lag       | 500000     |

| innodb_max_purge_lag_delay | 5000       |

| innodb_max_purge_lag_wait  | 4294967295 |

+----------------------------+------------+

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     132	Index page

    6127	Undo log page

      73	Inode page

       0	Insert buffer free list page

    2490	Freshly allocated page

       3	Insert buffer bitmap

     131	System page

       1	Transaction system page

       3	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 6127

Undo page state: 7 active, 121 cached, 5687 to_purge, 0 prepared, 312 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

31		23		23		73		15038

32		34		34		73		15038

33		15		15		73		15038

34		21		21		73		15038

35		1		1		620		8060

36		7		7		698		9083

37		11		11		789		10257

38		1		1		1191		15483

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

31		0	0	0	0	0	0	0	0	0	0	23	0

32		0	0	0	0	0	0	0	0	0	0	34	0

33		0	0	0	0	0	0	0	0	0	0	15	0

34		0	0	0	0	0	0	0	0	0	0	21	0

35		0	0	0	0	0	1	0	0	0	0	0	0

36		0	0	0	0	0	5	1	0	0	0	1	0

37		0	0	0	0	0	7	0	1	0	0	3	0

38		0	0	0	0	0	0	0	0	0	0	1	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ cd -

/Users/Valerii/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 cleanup

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest4'...Creating table 'sbtest1'...Creating table 'sbtest2'...Creating table 'sbtest3'...

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 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: 963.28 qps: 19271.53 (r/w/o: 13491.44/3853.13/1926.96) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 1032.93 qps: 20657.49 (r/w/o: 14459.78/4131.84/2065.87) lat (ms,95%): 5.88 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 804.67 qps: 16090.49 (r/w/o: 11262.67/3218.58/1609.24) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 793.27 qps: 15868.29 (r/w/o: 11108.58/3173.08/1586.64) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 771.14 qps: 15419.91 (r/w/o: 10793.20/3084.54/1542.17) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 892.86 qps: 17861.77 (r/w/o: 12504.02/3571.93/1785.82) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 890.88 qps: 17817.21 (r/w/o: 12472.25/3563.20/1781.75) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 926.31 qps: 18524.86 (r/w/o: 12967.08/3705.15/1852.63) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 799.88 qps: 15995.53 (r/w/o: 11196.37/3199.41/1599.75) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 888.00 qps: 17757.17 (r/w/o: 12429.58/3551.99/1775.60) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1226918

        write:                           350548

        other:                           175274

        total:                           1752740

    transactions:                        87637  (876.32 per sec.)

    queries:                             1752740 (17526.50 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      876.3249

    time elapsed:                        100.0051s

    total number of events:              87637

Latency (ms):

         min:                                    1.76

         avg:                                    4.56

         max:                                  167.92

         95th percentile:                        8.28

         sum:                               399658.03

Threads fairness:

    events (avg/stddev):           21909.2500/29.35

    execution time (avg/stddev):   99.9145/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  213909504 10 May 18:38 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     138	Index page

   10016	Undo log page

     114	Inode page

       0	Insert buffer free list page

    2655	Freshly allocated page

       1	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 10016

Undo page state: 0 active, 129 cached, 9405 to_purge, 0 prepared, 482 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

39		19		19		73		15038

40		18		18		73		15038

41		14		14		73		15038

42		24		24		73		15038

43		12		12		947		12320

44		12		12		951		12365

45		11		11		821		10675

46		17		17		782		10176

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

39		0	0	0	0	0	0	0	0	0	0	19	0

40		0	0	0	0	0	0	0	0	0	0	18	0

41		0	0	0	0	0	0	0	0	0	0	14	0

42		0	0	0	0	0	0	0	0	0	0	24	0

43		0	0	0	0	0	4	1	0	0	0	7	0

44		0	0	0	0	0	4	1	0	0	0	7	0

45		0	0	0	0	0	6	1	0	0	0	4	0

46		0	0	0	0	0	11	1	0	0	0	5	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_max_purge_lag%"'

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| innodb_max_purge_lag       | 500000     |

| innodb_max_purge_lag_delay | 5000       |

| innodb_max_purge_lag_wait  | 4294967295 |

+----------------------------+------------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 4752  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     138	Index page

   10016	Undo log page

     114	Inode page

       0	Insert buffer free list page

    2655	Freshly allocated page

       1	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 10016

Undo page state: 0 active, 129 cached, 9405 to_purge, 0 prepared, 482 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

39		19		19		73		15038

40		18		18		73		15038

41		14		14		73		15038

42		24		24		73		15038

43		12		12		947		12320

44		12		12		951		12365

45		11		11		821		10675

46		17		17		782		10176

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

39		0	0	0	0	0	0	0	0	0	0	19	0

40		0	0	0	0	0	0	0	0	0	0	18	0

41		0	0	0	0	0	0	0	0	0	0	14	0

42		0	0	0	0	0	0	0	0	0	0	24	0

43		0	0	0	0	0	4	1	0	0	0	7	0

44		0	0	0	0	0	4	1	0	0	0	7	0

45		0	0	0	0	0	6	1	0	0	0	4	0

46		0	0	0	0	0	11	1	0	0	0	5	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$

Yuliyas-Air:maria10.6 Valerii$ rm -rf data/*

Yuliyas-Air:maria10.6 Valerii$ scripts/mysql_install_db --no-defaults --innodb_undo_tablespaces=3

Installing MariaDB/MySQL system tables in './data' ...

2023-05-10 18:52:18 0 [Warning] InnoDB: innodb_open_files 300 should not be greater than the open_files_limit 256

OK

To start mariadbd at boot time you have to copy

support-files/mariadb.service to the right place for your system

Two all-privilege accounts were created.

One is root@localhost, it has no password, but you need to

be system 'root' user to connect. Use, for example, sudo mysql

The second is Valerii@localhost, it has no password either, but

you need to be the system 'Valerii' user to connect.

After connecting you can set the password, if you would need to be

able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:

cd '.' ; ./bin/mariadb-safe --datadir='./data'

You can test the MariaDB daemon with mysql-test-run.pl

cd './mysql-test' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:

https://mariadb.org/get-involved/

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ib*

-rw-r-----  1 Valerii  staff        811 10 May 18:52 data/ib_buffer_pool

-rw-rw----  1 Valerii  staff  100663296 10 May 18:52 data/ib_logfile0

-rw-rw----  1 Valerii  staff   12582912 10 May 18:52 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  10485760 10 May 18:52 data/undo001

-rw-rw----  1 Valerii  staff  10485760 10 May 18:52 data/undo002

-rw-rw----  1 Valerii  staff  10485760 10 May 18:52 data/undo003

Yuliyas-Air:maria10.6 Valerii$ 230510 18:56:11 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.err'.

230510 18:56:11 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.6/data

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_undo%"'

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_undo_directory    | ./    |

| innodb_undo_log_truncate | ON    |

| innodb_undo_tablespaces  | 3     |

+--------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag = 500000'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag_delay = 5000'

Yuliyas-Air:maria10.6 Valerii$ cd ~/git/sysbench

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'create database sbtest'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_undo%"'

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_undo_directory    | ./    |

| innodb_undo_log_truncate | ON    |

| innodb_undo_tablespaces  | 3     |

+--------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_max_purge_lag%"'

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| innodb_max_purge_lag       | 500000     |

| innodb_max_purge_lag_delay | 5000       |

| innodb_max_purge_lag_wait  | 4294967295 |

+----------------------------+------------+

Yuliyas-Air:maria10.6 Valerii$ cd ~/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest2'...Creating table 'sbtest4'...Creating table 'sbtest1'...

Creating table 'sbtest3'...

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest2'...

Yuliyas-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 --report_interval=10 --time=100 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: 1117.48 qps: 22354.71 (r/w/o: 15649.46/4469.90/2235.35) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 936.98 qps: 18738.88 (r/w/o: 13116.61/3748.32/1873.96) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 819.80 qps: 16395.62 (r/w/o: 11477.24/3278.78/1639.59) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 1009.97 qps: 20196.07 (r/w/o: 14136.23/4039.89/2019.95) lat (ms,95%): 6.21 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 927.21 qps: 18547.92 (r/w/o: 12984.39/3709.13/1854.41) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 778.95 qps: 15577.53 (r/w/o: 10904.15/3115.49/1557.89) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 645.73 qps: 12917.02 (r/w/o: 9042.44/2583.12/1291.46) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 848.32 qps: 16966.18 (r/w/o: 11876.44/3393.10/1696.65) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 992.91 qps: 19855.23 (r/w/o: 13897.76/3971.65/1985.82) lat (ms,95%): 6.43 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 3 tps: 973.68 qps: 19474.76 (r/w/o: 13632.39/3895.21/1947.16) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1267266

        write:                           362076

        other:                           181038

        total:                           1810380

    transactions:                        90519  (904.90 per sec.)

    queries:                             1810380 (18097.95 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      904.8974

    time elapsed:                        100.0323s

    total number of events:              90519

Latency (ms):

         min:                                    1.78

         avg:                                    4.42

         max:                                  162.14

         95th percentile:                        8.13

         sum:                               399692.62

Threads fairness:

    events (avg/stddev):           22629.7500/61.71

    execution time (avg/stddev):   99.9232/0.01

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ib*

-rw-r-----  1 Valerii  staff        819 10 May 18:55 data/ib_buffer_pool

-rw-rw----  1 Valerii  staff  100663296 10 May 18:59 data/ib_logfile0

-rw-rw----  1 Valerii  staff   12582912 10 May 18:59 data/ibdata1

-rw-rw----  1 Valerii  staff   12582912 10 May 18:58 data/ibtmp1

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  13631488 10 May 18:58 data/undo001

-rw-rw----  1 Valerii  staff  15728640 10 May 18:59 data/undo002

-rw-rw----  1 Valerii  staff  13631488 10 May 18:59 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  13631488 10 May 18:58 data/undo001

-rw-rw----  1 Valerii  staff  15728640 10 May 18:59 data/undo002

-rw-rw----  1 Valerii  staff  13631488 10 May 18:59 data/undo003

Yuliyas-Air:maria10.6 Valerii$ cd -

/Users/Valerii/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 cleanup

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest4'...Creating table 'sbtest2'...Creating table 'sbtest1'...Creating table 'sbtest3'...

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest3'...

Yuliyas-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 --report_interval=10 --time=100 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: 686.71 qps: 13738.81 (r/w/o: 9618.05/2746.94/1373.82) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 681.92 qps: 13636.07 (r/w/o: 9544.63/2727.59/1363.85) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 895.23 qps: 17905.21 (r/w/o: 12533.86/3580.90/1790.45) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 928.12 qps: 18564.48 (r/w/o: 12995.74/3712.50/1856.25) lat (ms,95%): 7.30 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 958.65 qps: 19171.62 (r/w/o: 13419.35/3834.98/1917.29) lat (ms,95%): 7.30 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 938.44 qps: 18770.96 (r/w/o: 13139.84/3754.25/1876.88) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 993.94 qps: 19876.75 (r/w/o: 13913.93/3974.95/1987.88) lat (ms,95%): 6.67 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 984.14 qps: 19683.09 (r/w/o: 13778.35/3936.46/1968.28) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 979.44 qps: 19589.27 (r/w/o: 13712.61/3917.77/1958.89) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 888.01 qps: 17759.24 (r/w/o: 12430.87/3552.55/1775.82) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1250900

        write:                           357400

        other:                           178700

        total:                           1787000

    transactions:                        89350  (893.45 per sec.)

    queries:                             1787000 (17868.93 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      893.4466

    time elapsed:                        100.0060s

    total number of events:              89350

Latency (ms):

         min:                                    2.30

         avg:                                    4.47

         max:                                  324.42

         95th percentile:                        7.98

         sum:                               399661.57

Threads fairness:

    events (avg/stddev):           22337.5000/44.33

    execution time (avg/stddev):   99.9154/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ib*

-rw-r-----  1 Valerii  staff        819 10 May 18:55 data/ib_buffer_pool

-rw-rw----  1 Valerii  staff  100663296 10 May 19:02 data/ib_logfile0

-rw-rw----  1 Valerii  staff   12582912 10 May 19:02 data/ibdata1

-rw-rw----  1 Valerii  staff   12582912 10 May 18:58 data/ibtmp1

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  13631488 10 May 18:58 data/undo001

-rw-rw----  1 Valerii  staff  27262976 10 May 19:02 data/undo002

-rw-rw----  1 Valerii  staff  15728640 10 May 19:02 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  13631488 10 May 18:58 data/undo001

-rw-rw----  1 Valerii  staff  27262976 10 May 19:02 data/undo002

-rw-rw----  1 Valerii  staff  15728640 10 May 19:02 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo002

File::data/undo002

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

    1223	Undo log page

      15	Inode page

       0	Insert buffer free list page

     382	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 1223

Undo page state: 3 active, 77 cached, 1137 to_purge, 0 prepared, 6 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo001

File::data/undo001

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     402	Undo log page

       6	Inode page

       0	Insert buffer free list page

     379	Freshly allocated page

       1	Insert buffer bitmap

      43	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 402

Undo page state: 0 active, 29 cached, 369 to_purge, 0 prepared, 4 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo002

File::data/undo002

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

    1223	Undo log page

      15	Inode page

       0	Insert buffer free list page

     382	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 1223

Undo page state: 3 active, 77 cached, 1137 to_purge, 0 prepared, 6 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo003

File::data/undo003

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     545	Undo log page

       7	Inode page

       0	Insert buffer free list page

     364	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 545

Undo page state: 0 active, 42 cached, 499 to_purge, 0 prepared, 4 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$

innochecksum ibdata1

Undo page type: 5474513

Undo page state: 1 active, 78 cached, 5472837 to_purge, 0 prepared, 1597 other

mariadb-10.6.12
11 Index page
337 Undo log page
13 Inode page
0 Insert buffer free list page
28818 Freshly allocated page
2 Insert buffer bitmap
255 System page
1 Transaction system page
2 File Space Header
1 Extent descriptor page
0 BLOB page
0 Compressed BLOB page
0 Page compressed page
0 Page compressed encrypted page
0 Other type of page

mariadb-10.6.12
11 Index page
25557 Undo log page
307 Inode page
0 Insert buffer free list page
3430 Freshly allocated page
2 Insert buffer bitmap
130 System page
1 Transaction system page
1 File Space Header
1 Extent descriptor page
0 BLOB page
0 Compressed BLOB page
0 Page compressed page
0 Page compressed encrypted page
0 Other type of page

mariadb-10.6.12
11 Index page
431 Undo log page
303 Inode page
0 Insert buffer free list page
28559 Freshly allocated page
2 Insert buffer bitmap
130 System page
1 Transaction system page
1 File Space Header
2 Extent descriptor page
0 BLOB page
0 Compressed BLOB page
0 Page compressed page
0 Page compressed encrypted page
0 Other type of page

od -Ax -w32 -t x1 sbtest-rr/ibdata1 |grep '[048c]000\( ..\)\{24\} 00 02'

4b4000 00 00 00 00 00 00 01 2d ff ff ff ff ff ff ff ff 00 00 00 00 00 00 7f dc 00 02 00 00 00 00 00 00

4cc000 00 00 00 00 00 00 01 33 ff ff ff ff ff ff ff ff 00 00 00 00 00 00 87 56 00 02 00 00 00 00 00 00

4d0000 00 00 00 00 00 00 01 34 ff ff ff ff ff ff ff ff 00 00 00 00 00 00 8e 8c 00 02 00 00 00 00 00 00

4d4000 00 00 00 00 00 00 01 35 ff ff ff ff ff ff ff ff 00 00 00 00 00 00 98 71 00 02 00 00 00 00 00 00

4d8000 00 00 00 00 00 00 01 36 ff ff ff ff ff ff ff ff 00 00 00 00 00 00 9d aa 00 02 00 00 00 00 00 00

4dc000 00 00 00 00 00 00 01 37 ff ff ff ff ff ff ff ff 00 00 00 00 00 00 a0 fa 00 02 00 00 00 00 00 00

od -Ax -t x1 -j 0x158 -N 8 sbtest-rr/ibdata1

000158 aa ae ea ff ff ff 00 00

@@ -485,7 +476,14 @@ trx_purge_truncate_rseg_history(trx_rseg_t& rseg,

       rseg.needs_purge > (purge_sys.head.trx_no

                           ? purge_sys.head.trx_no

                           : purge_sys.tail.trx_no))

-    /* We cannot free the entire undo page. */;

+    /* We cannot free the entire undo page. */

+    { if (srv_shutdown_state != SRV_SHUTDOWN_NONE)

+        ib::warn() << "cannot free " << rseg_hdr->page.id()

+                   << ":"

+                   << mach_read_from_2(b->page.frame + hdr_addr.boffset + TRX_UNDO_NEXT_LOG)

+                   << ","

+                   << mach_read_from_2(TRX_UNDO_SEG_HDR + TRX_UNDO_STATE +

+                                       b->page.frame); }

   else

     const uint32_t seg_size=

diff --git a/storage/innobase/trx/trx0undo.cc b/storage/innobase/trx/trx0undo.cc

index 20434d9fb9c..c78ac0afd4a 100644

--- a/storage/innobase/trx/trx0undo.cc

+++ b/storage/innobase/trx/trx0undo.cc

@@ -1481,13 +1481,7 @@ trx_undo_set_state_at_finish(

   /* This function is invoked during transaction commit, which is not

   allowed to fail. If we get a corrupted undo header, we will crash here. */

   ut_a(block);

-  const uint16_t state = undo->size == 1 &&

-    TRX_UNDO_PAGE_REUSE_LIMIT >

-    mach_read_from_2(TRX_UNDO_PAGE_HDR + TRX_UNDO_PAGE_FREE +

-                     block->page.frame)

-    ? TRX_UNDO_CACHED

-    : TRX_UNDO_TO_PURGE;

+  const uint16_t state= TRX_UNDO_TO_PURGE;

   undo->state= state;

   mtr->write<2>(*block, TRX_UNDO_SEG_HDR + TRX_UNDO_STATE + block->page.frame,

                 state);

innodb.recovery_shutdown 'innodb'        w41 [ fail ]  Found warnings/errors in server log file!

        Test ended at 2023-05-12 16:40:20

line

2023-05-12 16:40:18 0 [Warning] InnoDB: cannot free [page id: space=0, page number=47]:0,4

./mtr --rr --parallel=auto innodb.recovery_shutdown{,,,,,,,,,,,,}

(rr) p rseg

$1 = (trx_rseg_t &) @0x55b576f21680: {space = 0x55b578cd7940, latch = {

    writer = {lock = std::atomic<unsigned int> = { 2147483649 }},

    readers = std::atomic<unsigned int> = { 2147483648 }}, page_no = 47,

  history_size = 1, needs_purge = 22, ref = std::atomic<unsigned int> = { 2 },

  curr_size = 3, undo_list = {count = 1, start = 0x55b578d20010,

    end = 0x55b578d20010, node = &trx_undo_t::undo_list}, undo_cached = {

    count = 0, start = 0x0, end = 0x0, node = &trx_undo_t::undo_list},

  last_page_no = 4294967295, last_commit_and_offset = 24206847997116424}

(rr) p purge_sys.head.trx_no

$2 = 86

(rr) p purge_sys.tail.trx_no

$3 = 86

(rr) watch -l rseg.ref._M_i

Hardware watchpoint 2: -location rseg.ref._M_i

(rr) reverse-continue

Continuing.

[Switching to Thread 1452979.1452979]

Thread 1 hit Breakpoint 1, sql_print_warning (

    format=format@entry=0x55b576074f30 "Found %d prepared XA transactions")

    at /mariadb/10.6/sql/log.cc:9269

9269	{

(rr)

Continuing.

Thread 1 hit Hardware watchpoint 2: -location rseg.ref._M_i

Old value = 2

New value = 0

0x000055b576c9d719 in std::__atomic_base<unsigned int>::fetch_add (

    __m=std::memory_order_seq_cst, __i=2, this=<optimized out>)

    at /usr/include/c++/12/bits/atomic_base.h:616

616	      fetch_add(__int_type __i,

(rr) bt

#0  0x000055b576c9d719 in std::__atomic_base<unsigned int>::fetch_add (

    __m=std::memory_order_seq_cst, __i=2, this=<optimized out>)

    at /usr/include/c++/12/bits/atomic_base.h:616

#1  trx_rseg_t::acquire (this=<optimized out>)

    at /mariadb/10.6/storage/innobase/include/trx0rseg.h:136

#2  trx_resurrect (rows_to_undo=<synthetic pointer>,

    start_time_micro=29597343636, start_time=1683899014, rseg=<optimized out>,

    undo=0x55b578d20010) at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:679

#3  trx_lists_init_at_db_start ()

    at /mariadb/10.6/storage/innobase/trx/trx0trx.cc:750

#4  0x000055b576c843f1 in srv_start (create_new_db=<optimized out>)

    at /mariadb/10.6/storage/innobase/srv/srv0start.cc:1482

  if (head.trx_no >= purge_sys.low_limit_no())

    /* This is sometimes necessary. TODO: find out why. */

    head.trx_no= purge_sys.low_limit_no();

    head.undo_no= 0;

@@ -482,9 +473,7 @@ trx_purge_truncate_rseg_history(trx_rseg_t& rseg,

   if (mach_read_from_2(b->page.frame + hdr_addr.boffset + TRX_UNDO_NEXT_LOG) ||

       rseg.is_referenced() ||

-      rseg.needs_purge > (purge_sys.head.trx_no

-                          ? purge_sys.head.trx_no

-                          : purge_sys.tail.trx_no))

+      rseg.needs_purge > purge_sys.low_limit_no())

     /* We cannot free the entire undo page. */;

   else

  if (mach_read_from_2(b->page.frame + hdr_addr.boffset + TRX_UNDO_NEXT_LOG) ||

      rseg.is_referenced() ||

      rseg.needs_purge > (purge_sys.head.trx_no

                          ? purge_sys.head.trx_no

                          : purge_sys.tail.trx_no))

    /* We cannot free the entire undo log segment. */;

diff --git a/storage/innobase/trx/trx0purge.cc b/storage/innobase/trx/trx0purge.cc

index b04e71ba871..687d1af5ba4 100644

--- a/storage/innobase/trx/trx0purge.cc

+++ b/storage/innobase/trx/trx0purge.cc

@@ -480,11 +480,7 @@ trx_purge_truncate_rseg_history(trx_rseg_t& rseg,

   rseg_hdr->fix();

-  if (mach_read_from_2(b->page.frame + hdr_addr.boffset + TRX_UNDO_NEXT_LOG) ||

-      rseg.is_referenced() ||

-      rseg.needs_purge > (purge_sys.head.trx_no

-                          ? purge_sys.head.trx_no

-                          : purge_sys.tail.trx_no))

+  if (mach_read_from_2(b->page.frame + hdr_addr.boffset + TRX_UNDO_NEXT_LOG))

     /* We cannot free the entire undo page. */;

   else

@@ -613,8 +609,9 @@ TRANSACTIONAL_TARGET static void trx_purge_truncate_history()

       ut_ad(rseg.is_persistent());

       rseg.latch.wr_lock(SRW_LOCK_CALL);

-      if (dberr_t e= trx_purge_truncate_rseg_history(rseg, head))

-        err= e;

+      if (!rseg.is_referenced() && rseg.needs_purge <= head.trx_no)

+        if (dberr_t e= trx_purge_truncate_rseg_history(rseg, head))

+          err= e;

       rseg.latch.wr_unlock();

diff --git a/storage/innobase/trx/trx0undo.cc b/storage/innobase/trx/trx0undo.cc

index 20434d9fb9c..85ae27c9865 100644

--- a/storage/innobase/trx/trx0undo.cc

+++ b/storage/innobase/trx/trx0undo.cc

@@ -1481,12 +1481,7 @@ trx_undo_set_state_at_finish(

   /* This function is invoked during transaction commit, which is not

   allowed to fail. If we get a corrupted undo header, we will crash here. */

   ut_a(block);

-  const uint16_t state = undo->size == 1 &&

-    TRX_UNDO_PAGE_REUSE_LIMIT >

-    mach_read_from_2(TRX_UNDO_PAGE_HDR + TRX_UNDO_PAGE_FREE +

-                     block->page.frame)

-    ? TRX_UNDO_CACHED

-    : TRX_UNDO_TO_PURGE;

+  const uint16_t state = TRX_UNDO_TO_PURGE;

   undo->state= state;

   mtr->write<2>(*block, TRX_UNDO_SEG_HDR + TRX_UNDO_STATE + block->page.frame,

Yuliyas-Air:server Valerii$ git log -1

commit a3e5b5c4db47cd29dc090d27db1d7f915cf774f6 (HEAD -> 10.6, origin/10.6)

Merge: f522b0f2303 c9eff1a144b

Author: Marko Mäkelä <marko.makela@mariadb.com>

Date:   Mon May 15 09:02:32 2023 +0300

    Merge 10.5 into 10.6

Yuliyas-Air:server Valerii$ git diff

...

diff --git a/storage/innobase/trx/trx0purge.cc b/storage/innobase/trx/trx0purge.cc

index b04e71ba871..687d1af5ba4 100644

--- a/storage/innobase/trx/trx0purge.cc

+++ b/storage/innobase/trx/trx0purge.cc

@@ -480,11 +480,7 @@ trx_purge_truncate_rseg_history(trx_rseg_t& rseg,

   rseg_hdr->fix();

-  if (mach_read_from_2(b->page.frame + hdr_addr.boffset + TRX_UNDO_NEXT_LOG) ||

-      rseg.is_referenced() ||

-      rseg.needs_purge > (purge_sys.head.trx_no

-                          ? purge_sys.head.trx_no

-                          : purge_sys.tail.trx_no))

+  if (mach_read_from_2(b->page.frame + hdr_addr.boffset + TRX_UNDO_NEXT_LOG))

     /* We cannot free the entire undo page. */;

   else

@@ -613,8 +609,9 @@ TRANSACTIONAL_TARGET static void trx_purge_truncate_history()

       ut_ad(rseg.is_persistent());

       rseg.latch.wr_lock(SRW_LOCK_CALL);

-      if (dberr_t e= trx_purge_truncate_rseg_history(rseg, head))

-        err= e;

+      if (!rseg.is_referenced() && rseg.needs_purge <= head.trx_no)

+        if (dberr_t e= trx_purge_truncate_rseg_history(rseg, head))

+          err= e;

       rseg.latch.wr_unlock();

diff --git a/storage/innobase/trx/trx0undo.cc b/storage/innobase/trx/trx0undo.cc

index 20434d9fb9c..85ae27c9865 100644

--- a/storage/innobase/trx/trx0undo.cc

+++ b/storage/innobase/trx/trx0undo.cc

@@ -1481,12 +1481,7 @@ trx_undo_set_state_at_finish(

   /* This function is invoked during transaction commit, which is not

   allowed to fail. If we get a corrupted undo header, we will crash here. */

   ut_a(block);

-  const uint16_t state = undo->size == 1 &&

-    TRX_UNDO_PAGE_REUSE_LIMIT >

-    mach_read_from_2(TRX_UNDO_PAGE_HDR + TRX_UNDO_PAGE_FREE +

-                     block->page.frame)

-    ? TRX_UNDO_CACHED

-    : TRX_UNDO_TO_PURGE;

+  const uint16_t state = TRX_UNDO_TO_PURGE;

   undo->state= state;

   mtr->write<2>(*block, TRX_UNDO_SEG_HDR + TRX_UNDO_STATE + block->page.frame,

Yuliyas-Air:maria10.6 Valerii$ bin/mysqld_safe --no-defaults --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=128M --innodb_undo_log_truncate=1 --innodb_undo_tablespaces=3 &

[1] 54377

Yuliyas-Air:maria10.6 Valerii$ 230517 16:43:34 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.err'.

230517 16:43:34 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.6/data

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  10485760 17 May 16:41 data/undo001

-rw-rw----  1 Valerii  staff  10485760 17 May 16:41 data/undo002

-rw-rw----  1 Valerii  staff  10485760 17 May 16:41 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag = 500000'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag_delay = 5000'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'create database sbtest'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_undo%"'

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_undo_directory    | ./    |

| innodb_undo_log_truncate | ON    |

| innodb_undo_tablespaces  | 3     |

+--------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_max_purge_lag%"'

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| innodb_max_purge_lag       | 500000     |

| innodb_max_purge_lag_delay | 5000       |

| innodb_max_purge_lag_wait  | 4294967295 |

+----------------------------+------------+

Yuliyas-Air:maria10.6 Valerii$ cd ~/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest4'...Creating table 'sbtest1'...Creating table 'sbtest2'...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 'sbtest4'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest3'...

Yuliyas-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 --report_interval=10 --time=100 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: 721.48 qps: 14434.61 (r/w/o: 10105.36/2885.90/1443.35) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 675.94 qps: 13520.01 (r/w/o: 9463.99/2704.14/1351.87) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 779.73 qps: 15594.28 (r/w/o: 10916.28/3118.54/1559.47) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 700.25 qps: 14004.93 (r/w/o: 9803.05/2801.39/1400.49) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 695.70 qps: 13913.56 (r/w/o: 9739.77/2782.39/1391.40) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 854.39 qps: 17088.28 (r/w/o: 11961.92/3417.58/1708.79) lat (ms,95%): 10.84 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 778.34 qps: 15566.79 (r/w/o: 10896.62/3113.48/1556.69) lat (ms,95%): 9.06 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 694.39 qps: 13886.06 (r/w/o: 9719.84/2777.45/1388.78) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 792.50 qps: 15852.40 (r/w/o: 11096.73/3170.68/1584.99) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 789.84 qps: 15796.18 (r/w/o: 11057.81/3158.68/1579.69) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1047662

        write:                           299332

        other:                           149666

        total:                           1496660

    transactions:                        74833  (747.57 per sec.)

    queries:                             1496660 (14951.43 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      747.5714

    time elapsed:                        100.1015s

    total number of events:              74833

Latency (ms):

         min:                                    1.82

         avg:                                    5.35

         max:                                  722.42

         95th percentile:                       13.22

         sum:                               400115.68

Threads fairness:

    events (avg/stddev):           18708.2500/97.29

    execution time (avg/stddev):   100.0289/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  13631488 17 May 16:48 data/undo001

-rw-rw----  1 Valerii  staff  12582912 17 May 16:48 data/undo002

-rw-rw----  1 Valerii  staff  12582912 17 May 16:48 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo001

File::data/undo001

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     587	Undo log page

       8	Inode page

       0	Insert buffer free list page

     192	Freshly allocated page

       1	Insert buffer bitmap

      43	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 587

Undo page state: 0 active, 0 cached, 587 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo002

File::data/undo002

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     588	Undo log page

       8	Inode page

       0	Insert buffer free list page

     128	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 588

Undo page state: 0 active, 0 cached, 588 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo003

File::data/undo003

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     588	Undo log page

       8	Inode page

       0	Insert buffer free list page

     128	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 588

Undo page state: 0 active, 0 cached, 588 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_fast_shutdown = 0'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'shutdown'

Yuliyas-Air:maria10.6 Valerii$ tail data/Yuliyas-Air.err

2023-05-17 16:50:05 0 [Note] InnoDB: Truncated .//undo003

2023-05-17 16:50:05 0 [Note] InnoDB: Starting shutdown...

2023-05-17 16:50:06 0 [Note] InnoDB: Dumping buffer pool(s) to /Users/Valerii/dbs/maria10.6/data/ib_buffer_pool

2023-05-17 16:50:06 0 [Note] InnoDB: Restricted to 2007 pages due to innodb_buf_pool_dump_pct=25

2023-05-17 16:50:06 0 [Note] InnoDB: Buffer pool(s) dump completed at 230517 16:50:06

2023-05-17 16:50:06 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"

2023-05-17 16:50:06 0 [Note] InnoDB: Shutdown completed; log sequence number 342784548; transaction id 150044

2023-05-17 16:50:06 0 [Note] /Users/Valerii/dbs/maria10.6/bin/mariadbd: Shutdown complete

230517 16:50:06 mysqld_safe mysqld from pid file /Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.pid ended

[1]+  Done                    bin/mysqld_safe --no-defaults --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=128M --innodb_undo_log_truncate=1 --innodb_undo_tablespaces=3

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  10485760 17 May 16:50 data/undo001

-rw-rw----  1 Valerii  staff  10485760 17 May 16:50 data/undo002

-rw-rw----  1 Valerii  staff  10485760 17 May 16:50 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo001

File::data/undo001

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     587	Undo log page

       8	Inode page

       0	Insert buffer free list page

       0	Freshly allocated page

       1	Insert buffer bitmap

      43	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 587

Undo page state: 0 active, 0 cached, 587 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo002

File::data/undo002

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     588	Undo log page

       8	Inode page

       0	Insert buffer free list page

       0	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 588

Undo page state: 0 active, 0 cached, 588 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo003

File::data/undo003

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

     588	Undo log page

       8	Inode page

       0	Insert buffer free list page

       0	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 588

Undo page state: 0 active, 0 cached, 588 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global variables like "innodb_%scrub%"'

+------------------------------------------+-------+

| Variable_name                            | Value |

+------------------------------------------+-------+

| innodb_immediate_scrub_data_uncompressed | ON    |

+------------------------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  10485760 17 May 17:20 data/undo001

-rw-rw----  1 Valerii  staff  10485760 17 May 17:20 data/undo002

-rw-rw----  1 Valerii  staff  10485760 17 May 17:20 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo001

File::data/undo001

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

      28	Undo log page

       8	Inode page

       0	Insert buffer free list page

     559	Freshly allocated page

       1	Insert buffer bitmap

      43	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 28

Undo page state: 0 active, 0 cached, 28 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo002

File::data/undo002

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

      14	Undo log page

       8	Inode page

       0	Insert buffer free list page

     574	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 14

Undo page state: 0 active, 0 cached, 14 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo003

File::data/undo003

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

      28	Undo log page

       8	Inode page

       0	Insert buffer free list page

     560	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 28

Undo page state: 0 active, 0 cached, 28 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ ./src/sysbench oltp_read_write --threads=4 --tables=4 --table-size=100000 --db-driver=mysql --mysql-socket=/tmp/mysql.sock --mysql-user=Valerii --report_interval=10 --time=100 cleanup

-bash: ./src/sysbench: No such file or directory

Yuliyas-Air:maria10.6 Valerii$ cd -

/Users/Valerii/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 cleanup

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest3'...Creating table 'sbtest1'...Creating table 'sbtest2'...Creating table 'sbtest4'...

Inserting 100000 records into 'sbtest3'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest1'

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest1'...

Yuliyas-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 --report_interval=10 --time=100 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: 759.17 qps: 15188.63 (r/w/o: 10632.83/3037.07/1518.73) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 722.91 qps: 14456.94 (r/w/o: 10119.90/2891.23/1445.81) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 531.19 qps: 10625.17 (r/w/o: 7437.71/2125.07/1062.39) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 749.33 qps: 14984.62 (r/w/o: 10488.97/2997.00/1498.65) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 767.51 qps: 15352.44 (r/w/o: 10746.87/3070.55/1535.02) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 553.50 qps: 11069.28 (r/w/o: 7748.69/2213.60/1107.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 772.26 qps: 15443.88 (r/w/o: 10810.42/3088.94/1544.52) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 770.30 qps: 15408.07 (r/w/o: 10786.15/3081.31/1540.61) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 591.62 qps: 11830.55 (r/w/o: 8280.82/2366.49/1183.24) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 1 tps: 754.00 qps: 15076.29 (r/w/o: 10552.76/3015.92/1507.61) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            976108

        write:                           278888

        other:                           139444

        total:                           1394440

    transactions:                        69722  (697.18 per sec.)

    queries:                             1394440 (13943.52 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      697.1761

    time elapsed:                        100.0063s

    total number of events:              69722

Latency (ms):

         min:                                    1.89

         avg:                                    5.73

         max:                                  486.36

         95th percentile:                       13.70

         sum:                               399718.65

Threads fairness:

    events (avg/stddev):           17430.5000/111.47

    execution time (avg/stddev):   99.9297/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  71303168 17 May 17:23 data/undo001

-rw-rw----  1 Valerii  staff  71303168 17 May 17:23 data/undo002

-rw-rw----  1 Valerii  staff  71303168 17 May 17:23 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 11351 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 11351 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 11351 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 11351 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 11351 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 11351 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/undo*

-rw-rw----  1 Valerii  staff  10485760 17 May 17:24 data/undo001

-rw-rw----  1 Valerii  staff  10485760 17 May 17:24 data/undo002

-rw-rw----  1 Valerii  staff  10485760 17 May 17:24 data/undo003

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo001

File::data/undo001

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

      13	Undo log page

       8	Inode page

       0	Insert buffer free list page

     574	Freshly allocated page

       1	Insert buffer bitmap

      43	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 13

Undo page state: 0 active, 0 cached, 13 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo002

File::data/undo002

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

      14	Undo log page

       8	Inode page

       0	Insert buffer free list page

     574	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 14

Undo page state: 0 active, 0 cached, 14 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/undo003

File::data/undo003

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

       0	Index page

      14	Undo log page

       8	Inode page

       0	Insert buffer free list page

     574	Freshly allocated page

       1	Insert buffer bitmap

      42	System page

       0	Transaction system page

       1	File Space Header

       0	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 14

Undo page state: 0 active, 0 cached, 14 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

index_id	page_data_bytes_histgram(empty,...,oversized)

Yuliyas-Air:maria10.6 Valerii$

Yuliyas-Air:maria10.6 Valerii$ bin/mysqld_safe --no-defaults --innodb_flush_log_at_trx_commit=0 --innodb_buffer_pool_size=128M --innodb-immediate_scrub_data_uncompressed=1  &

[1] 56551

Yuliyas-Air:maria10.6 Valerii$ 230517 18:04:43 mysqld_safe Logging to '/Users/Valerii/dbs/maria10.6/data/Yuliyas-Air.err'.

230517 18:04:43 mysqld_safe Starting mariadbd daemon with databases from /Users/Valerii/dbs/maria10.6/data

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  12582912 17 May 18:03 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag_delay = 5000'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'set global innodb_max_purge_lag = 500000'

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 6     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'create database sbtest'

Yuliyas-Air:maria10.6 Valerii$ cd -

/Users/Valerii/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 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 'sbtest3'

Inserting 100000 records into 'sbtest1'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest4'

Creating a secondary index on 'sbtest3'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest2'...

Yuliyas-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 --report_interval=10 --time=100 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: 784.13 qps: 15689.24 (r/w/o: 10983.45/3137.13/1568.66) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 732.46 qps: 14647.55 (r/w/o: 10253.37/2929.25/1464.92) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 711.87 qps: 14238.53 (r/w/o: 9966.93/2847.87/1423.73) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 773.05 qps: 15460.59 (r/w/o: 10822.69/3091.80/1546.10) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 752.70 qps: 15054.81 (r/w/o: 10538.04/3011.38/1505.39) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 750.67 qps: 15013.17 (r/w/o: 10509.36/3002.47/1501.34) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 811.32 qps: 16225.32 (r/w/o: 11357.42/3245.26/1622.63) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 699.69 qps: 13994.94 (r/w/o: 9796.62/2798.95/1399.37) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 759.91 qps: 15196.86 (r/w/o: 10637.41/3039.63/1519.82) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 744.33 qps: 14886.09 (r/w/o: 10420.71/2976.72/1488.66) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            1052898

        write:                           300828

        other:                           150414

        total:                           1504140

    transactions:                        75207  (752.03 per sec.)

    queries:                             1504140 (15040.50 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      752.0252

    time elapsed:                        100.0060s

    total number of events:              75207

Latency (ms):

         min:                                    1.75

         avg:                                    5.32

         max:                                  727.88

         95th percentile:                       11.65

         sum:                               399733.43

Threads fairness:

    events (avg/stddev):           18801.7500/87.07

    execution time (avg/stddev):   99.9334/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 71950 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  1220542464 17 May 18:08 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     139	Index page

   71388	Undo log page

     842	Inode page

       0	Insert buffer free list page

    1986	Freshly allocated page

       5	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       4	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 71388

Undo page state: 5 active, 0 cached, 71383 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

23		19		19		73		15038

24		35		35		73		15038

25		42		42		73		15038

26		32		32		73		15038

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

23		0	0	0	0	0	0	0	0	0	0	19	0

24		0	0	0	0	0	0	0	0	0	0	35	0

25		0	0	0	0	0	0	0	0	0	0	42	0

26		0	0	0	0	0	0	0	0	0	0	32	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 55669 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 3038  |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  1220542464 17 May 18:09 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

      11	Index page

     423	Undo log page

     973	Inode page

       0	Insert buffer free list page

   72947	Freshly allocated page

       5	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       5	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 423

Undo page state: 0 active, 0 cached, 423 to_purge, 0 prepared, 0 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ cd -

/Users/Valerii/git/sysbench

Yuliyas-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 --report_interval=10 --time=100 cleanup

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Yuliyas-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 --report_interval=10 --time=100 prepare

sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest4'...Creating table 'sbtest2'...Creating table 'sbtest1'...Creating table 'sbtest3'...

Inserting 100000 records into 'sbtest4'

Inserting 100000 records into 'sbtest1'

Inserting 100000 records into 'sbtest2'

Inserting 100000 records into 'sbtest3'

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest3'...

Yuliyas-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 --report_interval=10 --time=100 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: 819.13 qps: 16387.17 (r/w/o: 11471.87/3276.63/1638.67) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 4 tps: 775.49 qps: 15508.42 (r/w/o: 10855.58/3101.86/1550.98) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 4 tps: 681.57 qps: 13633.81 (r/w/o: 9544.18/2726.48/1363.14) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 4 tps: 470.61 qps: 9412.66 (r/w/o: 6589.22/1882.23/941.22) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 4 tps: 443.27 qps: 8861.61 (r/w/o: 6201.72/1773.46/886.43) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 4 tps: 635.23 qps: 12705.13 (r/w/o: 8894.07/2540.51/1270.55) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 4 tps: 810.99 qps: 16223.54 (r/w/o: 11356.99/3244.57/1621.98) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 4 tps: 704.00 qps: 14079.39 (r/w/o: 9855.59/2815.80/1408.00) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 4 tps: 780.59 qps: 15610.61 (r/w/o: 10927.47/3121.96/1561.18) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 4 tps: 818.28 qps: 16366.57 (r/w/o: 11456.87/3273.13/1636.57) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            971572

        write:                           277592

        other:                           138796

        total:                           1387960

    transactions:                        69398  (693.95 per sec.)

    queries:                             1387960 (13878.95 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

Throughput:

    events/s (eps):                      693.9477

    time elapsed:                        100.0046s

    total number of events:              69398

Latency (ms):

         min:                                    1.83

         avg:                                    5.76

         max:                                  738.14

         95th percentile:                       13.22

         sum:                               399746.31

Threads fairness:

    events (avg/stddev):           17349.5000/103.55

    execution time (avg/stddev):   99.9366/0.00

Yuliyas-Air:sysbench Valerii$ cd -

/Users/Valerii/dbs/maria10.6

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  1220542464 17 May 18:11 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 67410 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 67410 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 67410 |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

     139	Index page

   65213	Undo log page

     820	Inode page

       0	Insert buffer free list page

    8183	Freshly allocated page

       5	Insert buffer bitmap

     130	System page

       1	Transaction system page

       1	File Space Header

       4	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 65213

Undo page state: 0 active, 0 cached, 64943 to_purge, 0 prepared, 270 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

31		29		29		73		15038

32		36		36		73		15038

33		21		21		73		15038

34		34		34		73		15038

35		3		3		1182		15370

37		3		2		836		11035

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

31		0	0	0	0	0	0	0	0	0	0	29	0

32		0	0	0	0	0	0	0	0	0	0	36	0

33		0	0	0	0	0	0	0	0	0	0	21	0

34		0	0	0	0	0	0	0	0	0	0	34	0

35		0	0	0	0	0	0	0	0	0	0	3	0

37		0	0	1	0	0	0	0	0	0	0	2	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$ bin/mysql -e 'show global status like "innodb_%history%"'

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Innodb_history_list_length | 0     |

+----------------------------+-------+

Yuliyas-Air:maria10.6 Valerii$ ls -l data/ibdata1

-rw-rw----  1 Valerii  staff  1220542464 17 May 18:12 data/ibdata1

Yuliyas-Air:maria10.6 Valerii$ bin/innochecksum -S data/ibdata1

File::data/ibdata1

================PAGE TYPE SUMMARY==============

#PAGE_COUNT	PAGE_TYPE

===============================================

      11	Index page

     789	Undo log page

     935	Inode page

       0	Insert buffer free list page

   72607	Freshly allocated page

       5	Insert buffer bitmap

     138	System page

       1	Transaction system page

       2	File Space Header

       8	Extent descriptor page

       0	BLOB page

       0	Compressed BLOB page

       0	Page compressed page

       0	Page compressed encrypted page

       0	Other type of page

===============================================

Additional information:

Undo page type: 789

Undo page state: 0 active, 0 cached, 787 to_purge, 0 prepared, 2 other

index_id	#pages		#leaf_pages	#recs_per_page	#bytes_per_page

1		1		1		11		859

2		1		1		50		3173

3		1		1		20		1442

4		1		1		29		1246

5		1		1		11		364

11		1		1		0		0

12		1		1		0		0

13		1		1		0		0

14		1		1		0		0

15		1		1		0		0

-4294967296		1		1		0		0

index_id	page_data_bytes_histgram(empty,...,oversized)

1		0	1	0	0	0	0	0	0	0	0	0	0

2		0	0	1	0	0	0	0	0	0	0	0	0

3		0	1	0	0	0	0	0	0	0	0	0	0

4		0	1	0	0	0	0	0	0	0	0	0	0

5		0	1	0	0	0	0	0	0	0	0	0	0

11		1	0	0	0	0	0	0	0	0	0	0	0

12		1	0	0	0	0	0	0	0	0	0	0	0

13		1	0	0	0	0	0	0	0	0	0	0	0

14		1	0	0	0	0	0	0	0	0	0	0	0

15		1	0	0	0	0	0	0	0	0	0	0	0

-4294967296		1	0	0	0	0	0	0	0	0	0	0	0

Yuliyas-Air:maria10.6 Valerii$

CREATE TABLE undo_test (

    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    process_date DATE NOT NULL,

    flag1 TINYINT(1) UNSIGNED NOT NULL,

    flag2 TINYINT(1) UNSIGNED NOT NULL,

    label varchar(20) NOT NULL,

    PRIMARY KEY (id),

    KEY procdate (process_date),

    KEY flag1 (flag1),

    KEY flag2 (flag2)

) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

        UPDATE undo_test

            SET flag1 = IF ( flag1 = 0, 1, 0 ),

                flag2 = IF ( flag2 = 0, 1, 0 )

SELECT id FROM undo_test WHERE label LIKE 'a%'

SELECT id FROM undo_test WHERE label LIKE 'b%'

DELETE FROM velocitynorep.undo_test WHERE process_date = '2023-05-17' LIMIT 10000;

START TRANSACTION;

SELECT label, flag1, flag2 FROM undo_test WHERE flag1 = 1 AND flag2 = 1;

SELECT label, flag1, flag2 FROM undo_test WHERE flag1 = 0 AND flag2 = 0;

SELECT label, flag1, flag2 FROM undo_test WHERE flag1 = 0 AND flag2 = 1;

SELECT label, flag1, flag2 FROM undo_test WHERE flag1 = 1 AND flag2 = 0;

COMMIT;

innodb_max_undo_log_size=500M

innodb_undo_tablespaces=4

innodb_undo_log_truncate=ON

SELECT FILE_SIZE FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME = "innodb_undo001";

SELECT FILE_SIZE FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME = "innodb_undo002";

SELECT FILE_SIZE FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME = "innodb_undo003";

SELECT FILE_SIZE FROM information_schema.INNODB_SYS_TABLESPACES WHERE NAME = "innodb_undo004";

SELECT count FROM information_schema.INNODB_METRICS WHERE name = 'trx_rseg_history_len';

SET GLOBAL innodb_purge_rseg_truncate_frequency = 1;

SET GLOBAL innodb_max_purge_lag_wait=0;

origin/bb-10.6-MDEV-31234 1e1e0e98080faf23a2a1ff78bae1df60340f1183 2023-05-17T17:44:35+03:00

build type: RelWithDebInfo

behaved well in RQG testing. The bad effects observed occur when testing other trees too.

diff --git a/storage/innobase/trx/trx0purge.cc b/storage/innobase/trx/trx0purge.cc

index 6541ede447e..cd070a77859 100644

--- a/storage/innobase/trx/trx0purge.cc

+++ b/storage/innobase/trx/trx0purge.cc

@@ -664,9 +664,6 @@ TRANSACTIONAL_TARGET static void trx_purge_truncate_history()

       ut_ad(rseg.curr_size > cached);

-      if (rseg.curr_size > cached + 1)

-        goto not_free;

       rseg.latch.rd_unlock();

diff --git a/storage/innobase/trx/trx0purge.cc b/storage/innobase/trx/trx0purge.cc

index e8d81ff49cd..583a603e69e 100644

--- a/storage/innobase/trx/trx0purge.cc

+++ b/storage/innobase/trx/trx0purge.cc

@@ -664,7 +664,8 @@ TRANSACTIONAL_TARGET static void trx_purge_truncate_history()

       ut_ad(rseg.curr_size > cached);

-      if (rseg.curr_size > cached + 1)

+      if (rseg.curr_size > cached + 1 &&

+          (rseg.history_size || srv_fast_shutdown || srv_undo_sources))

         goto not_free;

       rseg.latch.rd_unlock();

--error ER_DUP_ENTRY

INSERT INTO mysql.innodb_table_stats values('','',default,0,0,0),('','',default,0,0,0);

SET GLOBAL innodb_undo_log_truncate=ON, innodb_fast_shutdown=0;

SHUTDOWN;

--error ER_DUP_ENTRY

INSERT INTO mysql.innodb_table_stats values('','',default,0,0,0),('','',default,0,0,0);

SET GLOBAL innodb_undo_log_truncate=ON, innodb_fast_shutdown=0;

SHUTDOWN;

MariaDB [(none)]> SHOW PROCESSLIST;

+---------+-------------+---------------------------+---------+--------------+------+---------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+

| Id      | User        | Host                      | db      | Command      | Time | State                                                                     | Info                                                                                                 | Progress |

+---------+-------------+---------------------------+---------+--------------+------+---------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+

| 1254146 | pmm         | localhost:62740           | NULL    | Query        |  124 | Filling schema table                                                      | SHOW GLOBAL STATUS                                                                                   |    0.000 |

| 1254148 | pmm         | localhost:62752           | NULL    | Query        |  121 | starting                                                                  | SHOW ENGINE INNODB STATUS                                                                            |    0.000 |

| 1254149 | pmm         | localhost:62768           | NULL    | Query        |  119 | Filling schema table                                                      | SHOW GLOBAL STATUS                                                                                   |    0.000 |

| 1254152 | pmm         | localhost:14546           | NULL    | Query        |  114 | Filling schema table                                                      | SHOW GLOBAL STATUS                                                                                   |    0.000 |

| 1254153 | pmm         | localhost:14550           | NULL    | Query        |  111 | starting                                                                  | SHOW ENGINE INNODB STATUS                                                                            |    0.000 |

| 1254247 | root        | localhost                 | NULL    | Query        |    0 | starting                                                                  | SHOW PROCESSLIST                                                                                     |    0.000 |

+---------+-------------+---------------------------+---------+--------------+------+---------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+

-rw-r-----. 1 mysql mysql  42G Jul 19 05:12 undo006

-rw-r-----. 1 mysql mysql  26G Jul 19 09:57 undo002

-rw-r-----. 1 mysql mysql  21G Jul 19 09:57 undo003

-rw-r-----. 1 mysql mysql  13G Jul 19 09:57 undo004

-rw-r-----. 1 mysql mysql 1.8G Jul 19 09:57 undo005

-rw-r-----. 1 mysql mysql  34G Jul 19 09:57 undo001

[MDEV-31234] InnoDB does not free UNDO after the fix of MDEV-30671, thus shared tablespace (ibdata1) may grow indefinitely for no good reason Created: 2023-05-10 Updated: 2023-09-14 Resolved: 2023-05-22
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.11.1, 10.5.20, 10.6.13, 10.8.7, 10.9.6, 10.10.3
Fix Version/s:	11.1.1, 11.0.2, 10.5.21, 10.6.14, 10.9.7, 10.10.5, 10.11.4

[MDEV-31234] InnoDB does not free UNDO after the fix of MDEV-30671, thus shared tablespace (ibdata1) may grow indefinitely for no good reason Created: 2023-05-10 Updated: 2023-09-14 Resolved: 2023-05-22