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

pt-online-schema-change locking replication - troubleshoot

star2star_lewisstar2star_lewis EntrantCurrent User Role Contributor
Running pr-online-schema-change on a slave to optimize a table. It is locking the table so replication is falling behind.
I have run this successfully on this server and others, but the lock is happening on the latest run.
What can I do to troubleshoot?

Server: Debian GNU/Linux 8.7 (jessie)
Mysql version: 5.5.54-38.7-log
pt-online-schema-change 3.0.12

/usr/bin/pt-online-schema-change --execute --print --alter " ENGINE=MyISAM " u=$user,p=$passwd,D=$DB,t=$TBL --no-check-replication-filters --recursion-method=none --critical-load Threads_running=100

No slaves found. See --recursion-method if host has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.

2019-07-02T16:42:38 Copying approximately 3404389414 rows...
INSERT LOW_PRIORITY IGNORE INTO `starbox_cdr`.`_cdr_new` (`id`, `locationid`, `extensionid`, `accountcode`, `src`, `dst`, `dcontext`, `clid`, `channel`, `dstchannel`, `lastapp`, `lastdata`, `start`, `answer`, `end`, `duration`, `billsec`, `disposition`, `amaflags`, `src_display`, `dst_display`, `s2saccountcode`, `accid`, `cac`, `cac_display`, `pac`, `pac_display`) SELECT `id`, `locationid`, `extensionid`, `accountcode`, `src`, `dst`, `dcontext`, `clid`, `channel`, `dstchannel`, `lastapp`, `lastdata`, `start`, `answer`, `end`, `duration`, `billsec`, `disposition`, `amaflags`, `src_display`, `dst_display`, `s2saccountcode`, `accid`, `cac`, `cac_display`, `pac`, `pac_display` FROM `starbox_cdr`.`cdr` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 23533 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `starbox_cdr`.`cdr` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

Id User Host/IP DB Time Cmd State Query
-- ----
-- ---- ---

96625366 debian-sy localhost 0 Query show full processlist
96625523 archiver localhost starbox_cd 0 Query Sending INSERT LOW_PRIORITY IG --> pt-online-schema-change
96469822 system us star2star 33 Connec Waiting INSERT IGNORE INTO sta --> replication
96625524 archiver localhost starbox_cd 35 Sleep
75161925 system us 13366097 Connec Waiting
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.