[MDEV-29986] Set innodb_undo_tablespaces=3 by default Created: 2022-11-09  Updated: 2024-01-12  Resolved: 2023-01-13

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 11.0.1

Type: Task Priority: Blocker
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Blocks
is blocked by MDEV-19229 Allow innodb_undo_tablespaces to be c... Closed
is blocked by MDEV-29999 innodb_undo_log_truncate=ON is not cr... Closed
is blocked by MDEV-30119 INFORMATION_SCHEMA.INNODB_TABLESPACES... Closed
is blocked by MDEV-30122 mariabackup.skip_innodb crashes when ... Closed
is blocked by MDEV-30144 Incremental prepare fails when innodb... Closed
is blocked by MDEV-30157 Galera SST doesn't properly handle un... Closed
is blocked by MDEV-30158 InnoDB fails to start ther server 10.... Closed
is blocked by MDEV-30180 Server hang with innodb_undo_log_trun... Closed
Duplicate
is duplicated by MDEV-31760 innodb_undo_tablespaces would be bett... Closed
Problem/Incident
causes MDEV-32974 Member fails to join due to old seqno... Open
Relates
relates to MDEV-14795 InnoDB system tablespace cannot be sh... Closed
relates to MDEV-30179 mariabackup --backup fails with FATAL... Closed
relates to MDEV-30479 OPT_PAGE_CHECKSUM mismatch during rec... Closed
relates to MDEV-21952 ibdata1 file size growing in MariaDB Closed
relates to MDEV-32141 More fully document innodb_undo_table... Open

 Description   

Now that MDEV-19229 allows the parameter innodb_undo_tablespaces to be increased from its current default value 0, we should enable multiple undo tablespaces by default. Likewise, we should set innodb_undo_log_truncate=ON by default, so that the space occupied by possible bursts of undo log records will be eventually reclaimed.

Edit: Based on some preliminary performance test results presented by axel, innodb_undo_log_truncate=ON seems to reduce throughput. That setting can be changed by SET GLOBAL while the server is running. The main thing here is to run the server with multiple tablespaces by default, so that it will be possible to truncate the undo tablespaces while the server is running.

Space that was occupied by undo logs in the system tablespace will not be reclaimed until MDEV-14795 has been implemented.

 Comments   
Comment by Marko Mäkelä [ 2022-11-24 ]

It turns out that one test hangs when using multiple undo tablespaces:

./mtr --mysqld=--innodb-undo-tablespaces=3 encryption.encrypt_and_grep

After I forcibly killed the server processes, I got the following output:

10.6 6d40274f65b8d145fbf496e9b1b1d46f258de227

 
CURRENT_TEST: encryption.encrypt_and_grep
 
mysqltest: In included file "./include/wait_condition.inc": 
included from /mariadb/10.6/mysql-test/suite/encryption/t/encrypt_and_grep.test at line 65:
 
At line 54: query 'let $success= `$wait_condition`' failed with wrong errno <Unknown> (2013): 'Lost connection to server during query', instead of  (0)...
 
…
 
2022-11-24 13:42:23 4 [Note] InnoDB: Deleting the meta-data file './test/t1.cfg'
 
2022-11-24 13:42:23 4 [Note] InnoDB: Deleting the meta-data file './test/t2.cfg'
 
2022-11-24 13:42:23 4 [Note] InnoDB: Deleting the meta-data file './test/t3.cfg'
 
2022-11-24 13:42:23 4 [Note] InnoDB: Resuming purge
 
----------SERVER LOG END-------------

We need to analyze this deeper to understand if this is to be expected (and the test merely needs to be adjusted), or a genuine code bug.

Comment by Marko Mäkelä [ 2022-11-24 ]

Apart from encryption, there were some issues with backup as well.
thiru, can you please check my draft commit and file&fix the bugs separately, in the earliest applicable version?

Comment by Marko Mäkelä [ 2022-11-28 ]

I applied thiru’s fixes. We have one more problem, which will need to be filed and fixed in 10.6, once we can reproduce it (under RQG, I suppose):

CURRENT_TEST: parts.partition_alter4_innodb
 
--- /mariadb/10.10/mysql-test/suite/parts/r/partition_alter4_innodb.result	2022-10-13 10:57:18.766594249 +0300
 
+++ /mariadb/10.10/mysql-test/suite/parts/r/partition_alter4_innodb.reject	2022-11-28 15:17:30.451478711 +0200
 
@@ -69868,6 +69868,9 @@
 
 test.t1	analyze	status	OK
 
 CHECK    TABLE t1 EXTENDED;
 
 Table	Op	Msg_type	Msg_text
 
+test.t1	check	Warning	InnoDB: Unpurged clustered index record in table `test`.`t1` /* Partition `part_2` */: COMPACT RECORD(info_bits=32, 8 fields): {[6]    OH(0x000000004F48),[6]      (0x00000000D185),[7],     =(0x2C000001CA0D3D),[4]    (0x80000008),[4]    (0x80000008),[20]8                   (0x3820202020202020202020202020202020202020),[20]8                   (0x3820202020202020202020202020202020202020),[7]===8===(0x3D3D3D383D3D3D)}
 
+test.t1	check	Warning	InnoDB: Unpurged clustered index record in table `test`.`t1` /* Partition `part_3` */: COMPACT RECORD(info_bits=32, 8 fields): {[6]    OV(0x000000004F56),[6]      (0x00000000D185),[7],     K(0x2C000001CA0E4B),[4]    (0x8000000E),[4]    (0x8000000E),[20]14                  (0x3134202020202020202020202020202020202020),[20]14                  (0x3134202020202020202020202020202020202020),[8]===14===(0x3D3D3D31343D3D3D)}
 
+test.t1	check	Warning	InnoDB: Unpurged clustered index record in table `test`.`t1` /* Partition `part_4` */: COMPACT RECORD(info_bits=32, 8 fields): {[6]    O^(0x000000004F5E),[6]      (0x00000000D185),[7],     Y(0x2C000001CA0F59),[4]    (0x8000000F),[4]    (0x8000000F),[20]15                  (0x3135202020202020202020202020202020202020),[20]15                  (0x3135202020202020202020202020202020202020),[8]===15===(0x3D3D3D31353D3D3D)}
 
 test.t1	check	status	OK
 
 CHECKSUM TABLE t1 EXTENDED;
 
 Table	Checksum

Comment by Marko Mäkelä [ 2022-11-30 ]

thiru, thank you for fixing MDEV-30119.

Comment by Marko Mäkelä [ 2022-12-01 ]

A few issues were caught by the regression test suite:

  1. Upgrade tests fail (MDEV-30158): 

    Nov 30 12:36:51 debian-buster-amd64 mariadbd[3973]: 2022-11-30 12:36:51 0 [ERROR] InnoDB: Expected to open innodb_undo_tablespaces=3 but was able to find only 0

  2. A number of incremental backup tests fail on Microsoft Windows (MDEV-30114).
  3. Galera snapshot transfer is incompatible with innodb_undo_tablespaces>0 (MDEV-30157): 

    Failing test(s): galera.galera_sst_encrypted galera.galera_sst_rsync galera.galera_sst_rsync2 galera.galera_sst_rsync_data_dir galera.galera_sst_rsync_recv_auto

Comment by Marko Mäkelä [ 2022-12-12 ]

When innodb_undo_log_truncate=ON, the size of the undo tablespace files is controlled by the parameter innodb_max_undo_log_size, which was added in MySQL 5.7.5 and MariaDB 10.2.2. Its default value was changed from 1G to 10M in MariaDB 10.2.6. It is a soft limit.

Comment by Matthias Leich [ 2022-12-13 ] 

origin/bb-10.11-new-innodb-defaults c434f870a346fb11a6b5932dbdf860ed7e2d2f74 2022-12-12T10:05:22+02:00
 
which contains MDEV-29986, MDEV-19506, MDEV-29694, MDEV-30136, MDEV-29983
 
performed well in RQG testing. No new problems

Generated at Thu Feb 08 10:12:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.