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

            Activity

              People

              • Assignee:
                KennethDyer Kenneth Dyer
                Reporter:
                nunop Nuno
              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated: