Hi, The latest quarterly releases of MariaDB Server 10.5 and later include a fix of https://jira.mariadb.org/browse/MDEV-30671 which fixes corruption due to premature purging of history. It has turned out that this fix causes another bug where some undo log pages will never be freed (https://jira.mariadb.org/browse/MDEV-31234). I am working on a fix. Marko On Sat, May 13, 2023 at 9:37 PM <mariadb@biblestuph.com> wrote:
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
-- Marko Mäkelä, Lead Developer InnoDB MariaDB plc