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

Document use cases and locking behavior for OPTIMIZE TABLE with innodb_defragment=1

Details

    Description

      Using MariaDB 10.3, I've just tested the Defragmentation for the first time.

      I added to my.cnf:
      innodb_defragment = 1
      .

      And then, after restart, I tried "OPTIMIZE TABLE xxxx" on a frequently used table (lots of INSERTs per second), to see how it goes.

      I see that pretty much every INSERT query was getting stuck waiting for locks to be released.
      .

      This doesn't seem to be an improvement over the original OPTIMIZE which creates a new temp table and then swaps, and consequently the table file will be much shorter.

      While the new OPTIMIZE using defragmentation locks the table and the file won't get shorter.
      .
      .

      Is it intentional that OPTIMIZE using defragmentation locks the table?

      MDEV-9155 is marked as Fixed/Closed since April 2017, so I assumed this would not lock anymore.
      .

      Thanks!

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-5834 Merge Kakao Defragmentation implementation to MariaDB 10.1

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11336 Enable defragmentation on 10.2 when tests pass

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-30544 Deprecate innodb_defragment and related parameters

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-30545 Remove innodb_defragment and related parameters

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-30635 OPTIMIZE TABLE documentation is not mentioning ANALYZE

          • Major - Major loss of function.
          • Closed

          Activity

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

            jplindst, can you please comment on this? You reviewed and applied the original contribution MDEV-5834. How is the page latching supposed to work? How often are index locks acquired and released?

            marko Marko Mäkelä added a comment - jplindst , can you please comment on this? You reviewed and applied the original contribution MDEV-5834 . How is the page latching supposed to work? How often are index locks acquired and released?
            pomyk Patryk Pomykalski added a comment -

            For now you can at least document that it takes the table data lock and is basically useless and instead use ALTER TABLE ENGINE=InnoDB, ALGORITHM=INPLACE.

            pomyk Patryk Pomykalski added a comment - For now you can at least document that it takes the table data lock and is basically useless and instead use ALTER TABLE ENGINE=InnoDB, ALGORITHM=INPLACE.
            GeoffMontee Geoff Montee (Inactive) added a comment -

            The following documentation pages may need to be updated:

            GeoffMontee Geoff Montee (Inactive) added a comment - The following documentation pages may need to be updated: https://mariadb.com/kb/en/library/defragmenting-innodb-tablespaces/ https://mariadb.com/kb/en/library/optimize-table/
            julien.fritsch Julien Fritsch added a comment -

            jacob.moorman from our discussion, I decided to reassign Kenneth's ticket to you?

            julien.fritsch Julien Fritsch added a comment - jacob.moorman from our discussion, I decided to reassign Kenneth's ticket to you?
            marko Marko Mäkelä added a comment -

            The defragmentation will acquire an exclusive index latch potentially for extended periods of time. That, as we know from MDEV-14637, is a performance disaster even if it was done for shorter periods of time. It is true that the normal online table rebuild that is done by OPTIMIZE TABLE when innodb_defragment=1 is not set may cause more writes. However, it should minimize the impact to concurrent DML operations.

            marko Marko Mäkelä added a comment - The defragmentation will acquire an exclusive index latch potentially for extended periods of time. That, as we know from MDEV-14637 , is a performance disaster even if it was done for shorter periods of time. It is true that the normal online table rebuild that is done by OPTIMIZE TABLE when innodb_defragment=1 is not set may cause more writes. However, it should minimize the impact to concurrent DML operations.

            People

              marko Marko Mäkelä
              nunop Nuno
              Votes:
              2 Vote for this issue
              Watchers:
              11 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.