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

Allow ONLINE or ALGORITHM=INPLACE with OPTIMIZE TABLE

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None

    Description

      We should allow ONLINE or ALGORITHM=INPLACE to be used with OPTIMIZE TABLE.

      MySQL Supports this in 5.7:
      "As of 5.7.4, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for both regular and partitioned InnoDB tables. The table rebuild, triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE, is now performed using online DDL (ALGORITHM=INPLACE) and only locks the table for a brief interval, which reduces downtime for concurrent DML operations."
      http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

      We may support it, but it is not mentioned in the Docs as far as I can tell.

      However, we do "allow" an online optimize, per any of the following commands:

      ALTER TABLE t ENGINE=InnoDB, ALGORITHM=INPLACE;
      ALTER ONLINE TABLE t ENGINE=InnoDB;
      ALTER ONLINE TABLE t FORCE;

      So I think it would be fairly straight-forward to add an ONLINE keyword to be used with OPTIMZE.

      Or if it is performed automatically (which I now suspect), then perhapos we just need to mention this in our manual.

      Thank you for your consideration.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-16329 Engine-independent online ALTER TABLE

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

          Task - A task that needs to be done. MDEV-12955 Use InnoDB fast index creation to rebuild secondary indexes during OPTIMIZE TABLE

          • Major - Major loss of function.
          • Open

          Activity

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

            MDEV-16329 could make this unnecessary.

            marko Marko Mäkelä added a comment - MDEV-16329 could make this unnecessary.
            ccalender Chris Calender (Inactive) added a comment -

            If that is the case, then that sounds great to me.

            ccalender Chris Calender (Inactive) added a comment - If that is the case, then that sounds great to me.
            GeoffMontee Geoff Montee (Inactive) added a comment -

            Isn't this already supported if OPTIMIZE TABLE is equivalent to ALTER TABLE ... FORCE if the innodb_defragment system variable and the innodb_optimize_fulltext_only system variable are both set to OFF?

            https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminplace/#optimize-table

            https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminplace/#alter-table-force

            i.e. this works just fine:

            MariaDB [db1]> CREATE OR REPLACE TABLE tab (
            		 
                ->    a int PRIMARY KEY,
            		 
                ->    b varchar(50),
            		 
                ->    c varchar(50)
            		 
                -> );
            		 
            Query OK, 0 rows affected (0.030 sec)
            		 
             
            		 
            MariaDB [db1]> SET SESSION alter_algorithm='INPLACE';
            		 
            Query OK, 0 rows affected (0.000 sec)
            		 
             
            		 
            MariaDB [db1]> ALTER TABLE tab FORCE, LOCK=NONE;
            		 
            Query OK, 0 rows affected (0.023 sec)
            		 
            Records: 0  Duplicates: 0  Warnings: 0

            GeoffMontee Geoff Montee (Inactive) added a comment - Isn't this already supported if OPTIMIZE TABLE is equivalent to ALTER TABLE ... FORCE if the innodb_defragment system variable and the innodb_optimize_fulltext_only system variable are both set to OFF? https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminplace/#optimize-table https://mariadb.com/kb/en/library/innodb-online-ddl-operations-with-algorithminplace/#alter-table-force i.e. this works just fine: MariaDB [db1]> CREATE OR REPLACE TABLE tab ( -> a int PRIMARY KEY, -> b varchar(50), -> c varchar(50) -> ); Query OK, 0 rows affected (0.030 sec)   MariaDB [db1]> SET SESSION alter_algorithm='INPLACE'; Query OK, 0 rows affected (0.000 sec)   MariaDB [db1]> ALTER TABLE tab FORCE, LOCK=NONE; Query OK, 0 rows affected (0.023 sec) Records: 0 Duplicates: 0 Warnings: 0

            People

              Unassigned Unassigned
              ccalender Chris Calender (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.