Hi guys,
I meet a problem when I use pt-online-schema-change to change the table structure.
RDS: I use AWS RDS to run my mysql server which version is mysql 5.6, and the server has tow replications.
Run command: pt-online-schema-change --no-check-unique-key-change --print --charset=utf8 --progress=percentage,30 --host=10.101.4.5 --user=root --ask-pass --alter “add unique key company_config_index_unique(ent_id,ent_key,source,sub_source,profile_id)” D=production,P=3306,t=enterprise_config --execute
What happened: Actually the alter process can be executed successfully and correctly, but the table I want to alter has only 2 million records, and the process will take nearly 24 hours. So you can see, the command execute process is so slow.
What I expect: In my test environment, pt-online-schema-change executes very fast, and it only takes 1 minute. And I want the same performance in my production environment instead of 24 hours.
Output:
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
No slaves found. See --recursion-method if host ip-172-25-1-31 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `production`.`enterprise_config`...
Creating new table...
CREATE TABLE `production`.`_enterprise_config_new` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique id',
`ent_id` int(11) NOT NULL COMMENT 'ent_id',
`ent_key` varchar(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'key',
`value` text NOT NULL COMMENT 'config',
`profile_id` int(11) NOT NULL DEFAULT '0' COMMENT 'key',
`created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'key',
`updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT 'key',
`deleted_at` datetime(6) DEFAULT NULL COMMENT 'key',
`source` varchar(64) NOT NULL DEFAULT '' COMMENT 'CHANNEL: web|sdk...',
`sub_source` varchar(64) NOT NULL DEFAULT '' COMMENT 'SUB CHANNEL',
PRIMARY KEY (`id`),
UNIQUE KEY `company_config_index_unique` (`ent_id`,`ent_key`,`profile_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2541903 DEFAULT CHARSET=utf8mb4
Created new table production._enterprise_config_new OK.
Altering new table...
ALTER TABLE `production`.`_enterprise_config_new` drop key company_config_index_unique,add unique key company_config_index_unique(ent_id,ent_key,source,sub_source,profile_id)
Altered `production`.`_enterprise_config_new` OK.
2020-09-16T13:50:14 Creating triggers...
2020-09-16T13:50:14 Created triggers OK.
2020-09-16T13:50:14 Copying approximately 1985356 rows...
INSERT LOW_PRIORITY IGNORE INTO `production`.`_enterprise_config_new` (`id`, `ent_id`, `ent_key`, `value`, `profile_id`, `created_at`, `updated_at`, `deleted_at`, `source`, `sub_source`) SELECT `id`, `ent_id`, `ent_key`, `value`, `profile_id`, `created_at`, `updated_at`, `deleted_at`, `source`, `sub_source` FROM `production`.`enterprise_config` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 20356 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `production`.`enterprise_config` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Copying `production`.`enterprise_config`: 30% 14:01:40 remain
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Copying `production`.`enterprise_config`: 60% 07:06:06 remain
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.63,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.0.105,p=...,u=production_dbroot
Cannot connect to A=utf8,h=172.25.1.132,p=...,u=production_dbroot
Description:
As you see above, the process will pause between every Cannot connect to A=utf8,h=
and the time will last about 30 minutes.