[MDEV-29296] Undo Size Problems with MariaDB 10.6 Created: 2022-08-11  Updated: 2023-07-09  Resolved: 2023-07-09

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Maximiliano Dumon Assignee: Marko Mäkelä
Resolution: Incomplete Votes: 1
Labels: None

Attachments: JPEG File innodb-purge-activity-10.6.jpeg     JPEG File transaction-history-10.6.jpeg     JPEG File undo-space-usage-10.6.jpeg    
Issue Links:
Relates
relates to MDEV-29401 InnoDB history list length increased ... Closed

 Description   

We are doing tests with MariaDB 10.6 testing to deploy it on our production enviroment.

But, in our testing we have some wrong expirence with the undo purge process.

We did the test with sysbench tool:

sysbench \
 
--db-driver=mysql \
 
--mysql-user=sbtest_user \
 
--mysql_password=user_test \
 
--mysql-db=sbtest \
 
--mysql-host=mariadb-dba-test-01 \
 
--mysql-port=33033 \
 
--tables=32 \
 
--table-size=1000000 \
 
/usr/share/sysbench/oltp_read_write.lua prepare
 
 
 
sysbench \
 
--db-driver=mysql \
 
--mysql-user=sbtest_user \
 
--mysql_password=user_test \
 
--mysql-db=sbtest \
 
--mysql-host=mariadb-dba-test-00 \
 
--mysql-port=33033 \
 
--tables=32 \
 
--table-size=1000000 \
 
--threads=8 \
 
--time=0 \
 
--events=0 \
 
--report-interval=1 \
 
/usr/share/sysbench/oltp_read_write.lua run

After many tests an reads we found this pool of variables to set:

innodb_purge_threads = 32
innodb_purge_batch_size = 50
innodb_max_undo_log_size = 1G
innodb_purge_rseg_truncate_frequency = 64
innodb_undo_log_truncate = on

But, the problem was the same.

We didn't see the same behaivor wth MariaDB 10.5 version.

Executing this query:

select count from information_schema.innodb_metrics where name = 'trx_rseg_history_len';

The values never stop to increase.

 Comments   
Comment by Valerii Kravchuk [ 2022-08-27 ]

See also MDEV-29401 for a sysbench test comparing 10.6.9 to 10.5.x and showing increased history list length for the same load.

Comment by Marko Mäkelä [ 2023-06-07 ]

Would the purge throttling work-around that was implemented in MDEV-29401 help here? Or would MDEV-29593 help prevent the growth of undo tablespaces? How does MariaDB Server 10.6.14 work for you?

Before MDEV-31382, innodb_undo_log_truncate=ON would actually be ignored much of the time unless you are running with innodb_purge_rseg_truncate_frequency=1. That parameter is somewhat misleading; the value is more like a reciprocal of probability of truncating undo logs. With the default value, the probability would be 1/128. Maybe not a problem if you have a constant write workload, but likely one if the workload includes bursts of writes followed by long periods of no write activity.

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