Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1327

LP #1542291: pt-osc rebuild constraint can be faster since 5.6

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by DavidDucos last update 17-08-2016 17:54:24

      Since 5.6, it is possible to use inplace algorithm for alter tables and this can be combined with FOREIGN_KEY_CHECKS=OFF.

      This means that could be possible to rebuild the constraint in child tables pretty fast because it does not rebuild the table when you create the constraint.

      I attached a patch for version 2.2.16 just for testing but I think that we need to change the logic because we need to determine mysql version.

      This is my test case:

      CREATE TABLE `parent` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      CREATE TABLE `child` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `fk_child` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `child_ibfk_1` (`fk_child`),
      CONSTRAINT `parent_ibfk_1` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      insert into parent values (); – Execute it several times

      insert into child (fk_child) select * from parent;

      insert into child (fk_child) select fk_child from child; – Execute it several times

      • This is for test that it takes times:
        alter table child add CONSTRAINT `parent_ibfk_2` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`);

      alter table child drop FOREIGN KEY `parent_ibfk_2`;

      • This is for test that it is really fast:
        set foreign_key_checks=off;

      alter table child add CONSTRAINT `parent_ibfk_2` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`);

      alter table child drop FOREIGN KEY `parent_ibfk_2`;

      set foreign_key_checks=on;

      • This is the error when you try to do it inplace with foreign_key_checks=ON :
        alter table child add CONSTRAINT `parent_ibfk_2` FOREIGN KEY (`fk_child`) REFERENCES `parent` (`id`), algorithm= inplace;
        ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY.

      I used this command to see if the inodes changed at os level:
      ! ls -li /var/lib/mysql/test/

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                2 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: