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

Use InnoDB fast index creation to rebuild secondary indexes during OPTIMIZE TABLE

Details

    Description

      This is related to the following upstream feature request:

      https://bugs.mysql.com/bug.php?id=57583

      I want rebuild all secondary indexes on a table without specifying DDL for each index. It is much easier to run "alter table foo engine=innodb" for each table than to drop/add each index. However when I run "alter table foo engine=innodb" it does not appear to use fast index creation based on the space used by the table and reported in SHOW TABLE STATUS for the index.

      Attachments

        Issue Links

          Activity

            aadant, I was confused by the mention of secondary indexes in the description. A table rebuild (rebuilding the clustered index) can happen online, but it will obviously consume more space than merely dropping and creating the secondary indexes, because a new copy of all data will be made. Actually, if the desire is to save disk space, then rebuilding the secondary indexes could have an undesired effect of causing the data files to contain lots of freed pages.

            If we ignore the request about secondary indexes, this sounds like a duplicate of MDEV-10453.

            marko Marko Mäkelä added a comment - aadant , I was confused by the mention of secondary indexes in the description. A table rebuild (rebuilding the clustered index) can happen online, but it will obviously consume more space than merely dropping and creating the secondary indexes, because a new copy of all data will be made. Actually, if the desire is to save disk space, then rebuilding the secondary indexes could have an undesired effect of causing the data files to contain lots of freed pages. If we ignore the request about secondary indexes, this sounds like a duplicate of MDEV-10453 .

            An implementation of

            ALTER TABLE foo REBUILD INDEXES;
            

            should be possible without any InnoDB code changes. Basically, the SQL layer would fill in the information for invoking handler::check_if_supported_inplace_alter() and friends, asking for each and every secondary index to be dropped and added. If the storage engine cannot support that, then it would fall back to ALGORITHM=COPY, as usual.

            marko Marko Mäkelä added a comment - An implementation of ALTER TABLE foo REBUILD INDEXES; should be possible without any InnoDB code changes. Basically, the SQL layer would fill in the information for invoking handler::check_if_supported_inplace_alter() and friends, asking for each and every secondary index to be dropped and added. If the storage engine cannot support that, then it would fall back to ALGORITHM=COPY , as usual.

            In discussion we decided that it should be ALTER TABLE foo REBUILD INDEXES;

            SQL layer implementation is clear - drop and recreate.
            Engine can optimize the process when see list for drop and creation of indexes

            sanja Oleksandr Byelkin added a comment - In discussion we decided that it should be ALTER TABLE foo REBUILD INDEXES; SQL layer implementation is clear - drop and recreate. Engine can optimize the process when see list for drop and creation of indexes

            1. Why would one want to rebuild indexes?
            2. falling back to ALGORITHM=COPY is an overkill, there is generally no need to copy the data just to rebuild indexes

            serg Sergei Golubchik added a comment - 1. Why would one want to rebuild indexes? 2. falling back to ALGORITHM=COPY is an overkill, there is generally no need to copy the data just to rebuild indexes
            1. Secondary indexes in InnoDB may contain garbage records due to some earlier bug, such as MDEV-14799. That garbage would be invisible to SQL, but wasting of CPU and storage.
            2. InnoDB never supported native creation of multiple FULLTEXT INDEX in one statement. If an InnoDB table contained multiple FULLTEXT INDEX, the ALTER TABLE foo REBUILD INDEXES would fall back to ALGORITHM=COPY.

            An intermediate fallback step could be to invoke DISABLE KEYS and ENABLE KEYS for engines that support it, if the ENABLE KEYS involves rebuilding the indexes.

            marko Marko Mäkelä added a comment - Secondary indexes in InnoDB may contain garbage records due to some earlier bug, such as MDEV-14799 . That garbage would be invisible to SQL, but wasting of CPU and storage. InnoDB never supported native creation of multiple FULLTEXT INDEX in one statement. If an InnoDB table contained multiple FULLTEXT INDEX , the ALTER TABLE foo REBUILD INDEXES would fall back to ALGORITHM=COPY . An intermediate fallback step could be to invoke DISABLE KEYS and ENABLE KEYS for engines that support it, if the ENABLE KEYS involves rebuilding the indexes.

            People

              sanja Oleksandr Byelkin
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              10 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.