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

...