Buy Percona ServicesBuy Now!

pt-online-schema-change locking replication - troubleshoot

  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-online-schema-change locking replication - troubleshoot

    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