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

Remove excessive undo logging during ALTER TABLE…ALGORITHM=COPY

    XMLWordPrintable

Details

    Description

      MySQL 5.6 introduced ALTER TABLE…ALGORITHM=INPLACE, which avoids writing undo log for individual records that are copied. No change was made for ALGORITHM=COPY.
      There was a previous work-around that would ensure that if the server was killed during ALTER TABLE, rollback would not take too much time:

      commit fd069e2bb36a3c1c1f26d65dd298b07e6d83ac8b
      Author: marko@hundin.mysql.fi <>
      Date:   Wed Nov 3 21:32:48 2004 +0200
       
          InnoDB: commit after every 10000 rows in ALTER TABLE
      

      The proper fix would be to disable the undo logging for the individual rows altogether. This would still leave an orphan #sql table (until crash-safe atomic DDL is implemented), which can be dropped by the user. That fix was implemented in MySQL 8.0, and MariaDB should backport it:

      commit b5f211744b49d620d00fdecd13d9af09ef26c15b
      Author: Thirunarayanan Balathandayuthapani <thirunarayanan.balathandayuth@oracle.com>
      Date:   Wed Dec 2 16:09:15 2015 +0530
       
          Bug #17479594   AVOID INTERMEDIATE COMMIT WHILE DOING
                          ALTER TABLE ALGORITHM=COPY
          
          Problem:
          =======
            During ALTER TABLE, we commit and restart the transaction
          for every 10,000 rows, so that the rollback after recovery would not take
          so long.
          
          Fix:
          ====
          Supress the undo logging during copy alter operation. If fts_index is
          present then insert directly into fts auxiliary table rather
          than doing at commit time.
          
          ha_innobase::num_write_row: Remove the variable.
          ha_innobase::write_row(): Remove the hack for committing every 10000 rows.
          row_lock_table_for_mysql(): Remove the extra 2 parameters.
          lock_get_src_table(), lock_is_table_exclusive(): Remove the function.
          
          Reviewed-by: Marko Mäkelä <marko.makela@oracle.com>
          Reviewed-by: Shaohua Wang <shaohua.wang@oracle.com>
          Reviewed-by: Jon Olav Hauglid <jon.hauglid@oracle.com>
          RB: 10419
      

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.