Not the answer you need?
Register and ask your own question!

pt-online-schema-change execute very slowly

triThirtytriThirty Current User Role Participant
edited September 17 in General Questions

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.

Answers

  • jriverajrivera Percona Support Engineer Percona Staff Role

    Hi,

    If you do not want the tool to find replicas try running it with --recursion-method=none.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.