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

    XMLWordPrintable

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

            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.