[Maria-discuss] Undo logs aren't truncating

13 May 2023

      On a recently updated MariaDB 10.6.13 server (Debian Bullseye) I noticed 
that my ibdata1 file was continuously growing; it had gotten a little 
over 3G after running a couple days. I would rather keep this growth in 
check so used it as an excuse to setup undo tablespaces that could be 
truncated (I had wanted to try this for some time anyway). So: Dump the 
whole DB, delete all the InnoDB tables, stop the server, delete the 
innodb system tables, reset the appropriate undo variables (with 
innodb_max_undo_log_size set to 750M, innodb_undo_tablespaces set to 4, 
and innodb_undo_log_truncate set to ON), restart the server and reload 
the data.

I now have FOUR undo files that appear to be continuously growing; as of 
this writing they are over 1G and still increasing.

This is an active read-only replica (processing updates very frequently; 
at times continuously but not constantly as such). How do I get it to 
actually reclaim the space? Or am I misunderstanding how this is 
supposed to work?

Below are my current relevant settings (or at least what I think are 
relevant). Normally I run with innodb_purge_rseg_truncate_frequency = 
128; but for testing I have it now set to 1 (but with no noticeable 
difference).

Things I have tried (no luck on any of them):

Set innodb_purge_rseg_truncate_frequency = 1.

Set innodb_max_purge_lag_wait=0 (it returned nearly immediately).

Stop both slave threads and wait for several minutes.

Set innodb_max_purge_lag and innodb_max_purge_lag_delay (see below).

MariaDB [(none)]> show variables like '%trunc%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 1     |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show variables like '%purge%';
+--------------------------------------+------------+
| Variable_name                        | Value      |
+--------------------------------------+------------+
| aria_log_purge_type                  | immediate  |
| innodb_max_purge_lag                 | 1000       |
| innodb_max_purge_lag_delay           | 5000       |
| innodb_max_purge_lag_wait            | 4294967295 |
| innodb_purge_batch_size              | 300        |
| innodb_purge_rseg_truncate_frequency | 1          |
| innodb_purge_threads                 | 4          |
| relay_log_purge                      | ON         |
+--------------------------------------+------------+
8 rows in set (0.000 sec)

MariaDB [(none)]> show variables like '%undo%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_max_undo_log_size | 786432000 |
| innodb_undo_directory    | ./        |
| innodb_undo_log_truncate | ON        |
| innodb_undo_tablespaces  | 4         |
+--------------------------+-----------+
4 rows in set (0.000 sec)

mariadb＠Biblestuph.com

Marko Mäkelä

tags

résztvevők (2)