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

          Activity

            People

              ralf.gebhardt Ralf Gebhardt
              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.