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/

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 1 hour
                1h

                  Smart Checklist