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)
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
