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

Implement DISABLE KEYS/ENABLE KEYS in Innodb

Details

    Description

      DISABLE KEYS : ignoring secondary indexes, until they are reenabled with ENABLE KEYS.

      Also ignoring foreign key constraints, I'd guess (although, there is another setting for that)

      This is a tool that MyISAM/Aria use for the data load, into the empty table.
      The idea is to insert a lot of data (one can assume primary key order for this specific case),
      then build all indexes indexes on ENABLE.

      This should make restoring of mariadb-dump, and mariadb-import automatically faster.

      There is alternative for the data loading tools, to parse the table DDL as given by SHOW CREATE TABLE (MDEV-34740), but ENABLE/DISABLE appears to be much more convenient

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-5171 Add support for --innodb-optimize-keys to mysqldump.

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-11658 Simpler, faster IMPORT of InnoDB tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34740 mariadb-import: delay creation of secondary indexes until after data load

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-33188 Enhance mariadb-dump and mariadb-import capabilities similar to MyDumper

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled

          Activity

            marko Marko Mäkelä added a comment -

            Implementing ALTER TABLE…DISABLE KEYS would require a change to the InnoDB data dictionary format, or maybe better to the data file format (MDEV-11658), to incidate that some indexes need to be rebuilt before they can be used.

            Implementing ALTER TABLE…ENABLE KEYS would be somewhat tricky. I assume that it currently is a blocking operation, because neither MyISAM nor Aria support ALTER TABLE…ALGORITHM=INPLACE, LOCK=NONE. It might be best if the in-datafile index metadata records that I have envisioned in MDEV-11658 would be covered by undo log records. In that way, killing or backing up a server during such an operation should be manageable.

            marko Marko Mäkelä added a comment - Implementing ALTER TABLE…DISABLE KEYS would require a change to the InnoDB data dictionary format, or maybe better to the data file format ( MDEV-11658 ), to incidate that some indexes need to be rebuilt before they can be used. Implementing ALTER TABLE…ENABLE KEYS would be somewhat tricky. I assume that it currently is a blocking operation, because neither MyISAM nor Aria support ALTER TABLE…ALGORITHM=INPLACE, LOCK=NONE . It might be best if the in-datafile index metadata records that I have envisioned in MDEV-11658 would be covered by undo log records. In that way, killing or backing up a server during such an operation should be manageable.

            People

              Unassigned Unassigned
              wlad Vladislav Vaintroub
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.