Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30000

make mariadb-backup to force an innodb checkpoint

Details

    Description

      Since MDEV-23855 InnoDB can do checkpoints much more rarely. This improves performance under write-heavy load, but makes mariadb-backup create huge incremental backups (since the last checkpoint).

      One way to solve it would be to let mariadb-backup to force an InnoDB checkpoint before a backup. There are many ways of doing it, innodb.page_cleaner test shows one of them, marko knows more. Perhaps it should be optional, but, likely, enabled by default.

      Alternatively, InnoDB can force a checkpoint automatically when entering a certain backup stage.

      Attachments

        Issue Links

          Activity

            In InnoDB (any MariaDB version), a log checkpoint can simply be initiated by calling the function log_make_checkpoint(). It could be simplest to call that function when executing the appropriate BACKUP STAGE statement.

            marko Marko Mäkelä added a comment - In InnoDB (any MariaDB version), a log checkpoint can simply be initiated by calling the function log_make_checkpoint() . It could be simplest to call that function when executing the appropriate BACKUP STAGE statement.

            serg, in debug builds, one could invoke

            SET GLOBAL innodb_log_checkpoint_now=ON;
            

            to force a log checkpoint. If we made that parameter available in all builds, then this bug could easily be fixed by making mariadb-backup --backup issue that statement under some (which?) conditions.

            marko Marko Mäkelä added a comment - serg , in debug builds, one could invoke SET GLOBAL innodb_log_checkpoint_now= ON ; to force a log checkpoint. If we made that parameter available in all builds, then this bug could easily be fixed by making mariadb-backup --backup issue that statement under some (which?) conditions.

            Starting with MariaDB Server 10.11, instead or in addition to forcing a checkpoint, one may need to execute the following on the server before starting a backup:

            SET GLOBAL innodb_log_file_buffering=ON;
            

            Furthermore, as noted in MDEV-34062, mariadb-backup --backup may need to be started with a significantly larger innodb_log_buffer_size than the default 2 MiB.

            marko Marko Mäkelä added a comment - Starting with MariaDB Server 10.11, instead or in addition to forcing a checkpoint, one may need to execute the following on the server before starting a backup: SET GLOBAL innodb_log_file_buffering= ON ; Furthermore, as noted in MDEV-34062 , mariadb-backup --backup may need to be started with a significantly larger innodb_log_buffer_size than the default 2 MiB.
            brevilo Oliver Bock added a comment - - edited

            I'm running 10.11.6 with innodb_log_file_buffering = ON and innodb_log_buffer_size = 64 GB (for the server that's backed up) but that doesn't help at all. Depending on the checkpoint age (and thus the redo log size) this still means that the backup waits up to 8 (!) hours for log copy thread to read lsn XYZ and that the resulting compressed backup size can vary by up to 12 GB.

            As a workaround I now set innodb_max_dirty_pages_pct_lwm = 10 (MySQL 8's default) which leads to consistent backup time and size, but that of course somewhat defeats the purpose of a large log buffer.

            Since I'm backing up a replica, I'd also like to see a checkpoint being written as part of mariadb-backup --backup before the replica thread gets stopped.

            Thanks

            brevilo Oliver Bock added a comment - - edited I'm running 10.11.6 with innodb_log_file_buffering = ON and innodb_log_buffer_size = 64 GB (for the server that's backed up) but that doesn't help at all. Depending on the checkpoint age (and thus the redo log size) this still means that the backup waits up to 8 (!) hours for log copy thread to read lsn XYZ and that the resulting compressed backup size can vary by up to 12 GB. As a workaround I now set innodb_max_dirty_pages_pct_lwm = 10 (MySQL 8's default) which leads to consistent backup time and size, but that of course somewhat defeats the purpose of a large log buffer. Since I'm backing up a replica, I'd also like to see a checkpoint being written as part of mariadb-backup --backup before the replica thread gets stopped. Thanks

            I think that

            SET GLOBAL innodb_log_checkpoint_now=ON;
            

            would be the best approach. It clearly says what's to be done, doesn't make any system-wide changes that need to be restored (so, no problems if the client suddenly disconnects). Let's do that?

            serg Sergei Golubchik added a comment - I think that SET GLOBAL innodb_log_checkpoint_now= ON ; would be the best approach. It clearly says what's to be done, doesn't make any system-wide changes that need to be restored (so, no problems if the client suddenly disconnects). Let's do that?

            People

              marko Marko Mäkelä
              serg Sergei Golubchik
              Votes:
              9 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.