pt-osc rebuild constraint can be faster since 5.6

[DavidDucos]

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/

[Frank Cizmich (frank-cizmich)]

Thanks David!

I'll check it out as soon as i'm done with current "todo".

[Matthew Kent (mkent)]

Nice work David, this is a hugely important change for us here. Without it ptosc ends up firing off a series of ALTER tables that can take 8+ minutes to run depending on the parent. These are worse still when they hit the replicas, stalling our threaded mariadb replication for the duration.

Here's a before:

2016-08-16T22:42:59 Copying approximately 5460060 rows...
<snip>
2016-08-16T22:59:45 Copied rows OK.
2016-08-16T22:59:45 Analyzing new table...
2016-08-16T22:59:45 Swapping tables...
2016-08-16T22:59:45 Swapped original and new tables OK.
2016-08-16T22:59:45 Rebuilding foreign key constraints...
<8 minutes !!>
2016-08-16T23:08:00 Rebuilt foreign key constraints OK.
Not dropping old table because --no-drop-old-table was specified.
2016-08-16T23:08:00 Dropping triggers...
2016-08-16T23:08:00 Dropped triggers OK.
Successfully altered `foo_production`.`accounts`.

real 25m2.785s

after:

2016-08-17T15:13:46 Copying approximately 5493390 rows...
<snip>
2016-08-17T15:32:23 Copied rows OK.
2016-08-17T15:32:23 Analyzing new table...
2016-08-17T15:32:23 Swapping tables...
2016-08-17T15:32:23 Swapped original and new tables OK.
2016-08-17T15:32:23 Rebuilding foreign key constraints...
<0 seconds ;)>
2016-08-17T15:32:23 Rebuilt foreign key constraints OK.
Not dropping old table because --no-drop-old-table was specified.
2016-08-17T15:32:23 Dropping triggers...
2016-08-17T15:32:23 Dropped triggers OK.
Successfully altered `foo_production`.`accounts`.

real 18m39.000s

Just stunning.

Huge +1 to merge this one.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1327