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

Shrink temporary tablespaces without restart

Details

    Description

      The documentation at https://mariadb.com/kb/en/innodb-temporary-tablespaces/ says:

      Unlike normal tablespaces, temporary tablespaces are deleted when you stop MariaDB. To shrink temporary tablespaces to their minimum sizes, restart the server.

      This means temporary tablespaces will keep growing until MariaDB is restarted. Having to restart MariaDB periodically is not optimal. It would be welcome if MariaDB shrinks temporary tablespaces on disk without restarting.

      To achieve this, we would introduce new syntax:

      SET GLOBAL innodb_truncate_temporary_tablespace_now=1;

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33101 Server crashes when starting the server with innodb-force-recovery=6 and enabling the innodb_truncate_temporary_tablespace_now variable.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34256 InnoDB throws out of bound write due to temporary tablespace truncation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is blocked by

          Task - A task that needs to be done. MDEV-14795 InnoDB system tablespace cannot be shrunk

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32028 InnoDB scrubbing doesn't write zero while freeing the extent

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32145 Disable read-ahead for temporary tablespace

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32151 innodb_immediate_scrub_data_uncompressed=ON may fail to scrub temporary tablespace pages

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26782 InnoDB temporary tablespace: reclaiming of free space does not work

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-12227 Defer writes to the InnoDB temporary tablespace

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13013 InnoDB unnecessarily extends data files

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-14795 InnoDB system tablespace cannot be shrunk

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15528 Avoid writing freed InnoDB pages

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32163 Crash recovery fails after DROP TABLE in system tablespace

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            Before we can fix this, I think that MDEV-13013 needs to be addressed. If any space is available in the data file, we should try to allocate that space, instead of extending the file.

            When it comes to shrinking InnoDB data files, the groundwork is laid out in MDEV-15528. Theoretically, we could implement shrinking of any InnoDB data files. Currently we only do that for innodb_undo_log_truncate=ON when rebuilding an entire undo tablespace.

            For the temporary tablespace, we do not even have to think about recovery or logging. Possibly we could extend the idea of MDEV-12227 and implement a new file format in which pages in the temporary tablespace are assigned a file offset when the pages are actually spilled from the buffer pool to the file system.

            marko Marko Mäkelä added a comment - Before we can fix this, I think that MDEV-13013 needs to be addressed. If any space is available in the data file, we should try to allocate that space, instead of extending the file. When it comes to shrinking InnoDB data files, the groundwork is laid out in MDEV-15528 . Theoretically, we could implement shrinking of any InnoDB data files. Currently we only do that for innodb_undo_log_truncate=ON when rebuilding an entire undo tablespace. For the temporary tablespace, we do not even have to think about recovery or logging. Possibly we could extend the idea of MDEV-12227 and implement a new file format in which pages in the temporary tablespace are assigned a file offset when the pages are actually spilled from the buffer pool to the file system.
            wedwards William Edwards added a comment - - edited

            Thank you for your substantive reply, Marko. I think that this issue, together with MDEV-12459, shows that temporary tablespaces need some work...

            wedwards William Edwards added a comment - - edited Thank you for your substantive reply, Marko. I think that this issue, together with MDEV-12459 , shows that temporary tablespaces need some work...
            marko Marko Mäkelä added a comment -

            I think that this will be easier to implement, once MDEV-14795 has been implemented.

            marko Marko Mäkelä added a comment - I think that this will be easier to implement, once MDEV-14795 has been implemented.
            marko Marko Mäkelä added a comment - - edited

            Now that tests by mleich seem to be reasonably stable, I reviewed the current development version. The logic looks mostly OK. The only problem that I found is a lock order inversion (or latching order violation) when we are evicting page allocation bitmap pages that would be in the to-be-freed page range. We must never wait for a page latch while holding buf_pool.mutex. If a "trylock" fails, we need a fallback path that will involve releasing and reacquiring buf_pool.mutex. See buf_page_create_low() for an example.

            marko Marko Mäkelä added a comment - - edited Now that tests by mleich seem to be reasonably stable, I reviewed the current development version. The logic looks mostly OK. The only problem that I found is a lock order inversion (or latching order violation) when we are evicting page allocation bitmap pages that would be in the to-be-freed page range. We must never wait for a page latch while holding buf_pool.mutex . If a "trylock" fails, we need a fallback path that will involve releasing and reacquiring buf_pool.mutex . See buf_page_create_low() for an example.
            marko Marko Mäkelä added a comment -

            This rebase is OK to push to the preview release. Thank you for separating the bug fixes into separate commits. I did not notice any correctness problems, but it would be nice to address my minor review comments.

            marko Marko Mäkelä added a comment - This rebase is OK to push to the preview release. Thank you for separating the bug fixes into separate commits. I did not notice any correctness problems, but it would be nice to address my minor review comments.
            mleich Matthias Leich added a comment - - edited 

            origin/preview-11.3-preview, origin/bb-11.3-mdev-32254, preview-11.3-preview 76e20f00772148fa928c6c6e42401f38ca89abf0 2023-09-27T13:08:34+10:00

            performed quite good in RQG testing (with innodb_truncate_temporary_tablespace_now etc.). No new InnoDB related problems related to the modifications of MDEV-28699.

            mleich Matthias Leich added a comment - - edited origin/preview-11.3-preview, origin/bb-11.3-mdev-32254, preview-11.3-preview 76e20f00772148fa928c6c6e42401f38ca89abf0 2023-09-27T13:08:34+10:00 performed quite good in RQG testing (with innodb_truncate_temporary_tablespace_now etc.). No new InnoDB related problems related to the modifications of MDEV-28699 .
            marko Marko Mäkelä added a comment - - edited

            This could not be pushed to 11.3 before MDEV-32028, MDEV-32145, and MDEV-32151 had been merged.

            marko Marko Mäkelä added a comment - - edited This could not be pushed to 11.3 before MDEV-32028 , MDEV-32145 , and MDEV-32151 had been merged.

            People

              thiru Thirunarayanan Balathandayuthapani
              wedwards William Edwards
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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