maria-discuss team mailing list archive

[Marko Mäkelä <marko.makela@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp



-- 
Marko Mäkelä, Lead Developer InnoDB
MariaDB plc