Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1382

LP #1615520: pt-online-schema-change did not finished on 100% progress.

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by Hyuk Lee last update 12-02-2017 04:17:57

      pt-online-schema-change did not finished on 100% progress.
      In fact, we changed the column 'not null' to 'nullable' with different data size( around 50GB and 100GB ).
      This issue only happened on around 100GB tables, not on 50GB ones.
      While working, we sometimes changed innodb_io_capacity 10000 to 1000.

      Also, after swapping the original and new tables 2GB data were disappeared.
      ( 104 GB -> 102 GB )

      Consider the following schema:

      CREATE TABLE `mytable` (
      `USR_ID` varchar(10) COLLATE utf8mb4_bin NOT NULL,
      `REG_ID` varchar(128) COLLATE utf8mb4_bin NOT NULL,
      ...
      `EMAIL` varchar(100) COLLATE utf8mb4_bin NOT NULL,
      ...
      PRIMARY KEY (`USR_ID`,`REG_ID`),
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

      1. Complete commandline used to run the tool
      ./pt-online-schema-change D=test,t=mytable --sleep=0.5 --chunk-size=500 --nocheck-replication-filters --critical-load="Threads_running=1000" --max-load="Innodb_buffer_pool_pages_dirty=655360" --set-vars="innodb_lock_wait_timeout=100" --recursion-method=processlist --max-lag=1 --socket=/***/mysql.sock --host=...* --port=**** --user=**** --password=**** --charset=utf8mb4 --execute --alter "MODIFY EMAIL VARCHAR(100) DEFAULT NULL"

      2. Tool version
      percona-toolkit-2.2.15

      3. MySQL version of all servers involved
      MySQL 5.6.25 Enterprise Server

      4. Output from the tool including STDERR
      Found 6 slaves:
      ip-*-*
      ip-*-*
      ip-*-*
      ip-*-*
      ip-*-*
      ip-*-*
      Will check slave lag on:
      ip-*-*
      ip-*-*
      ip-*-*
      ip-*-*
      ip-*-*
      ip-*-*
      Operation, tries, wait:
      copy_rows, 10, 0.25
      create_triggers, 10, 1
      drop_triggers, 10, 1
      swap_tables, 10, 1
      update_foreign_keys, 10, 1
      Altering `test`.`mytable`...
      Creating new table...
      Created new table test._mytable_new OK.
      Waiting forever for new table `test`.`_mytable_new` to replicate to ip-*-*...
      Altering new table...
      Altered `test`.`_mytable_new` OK.
      2016-08-17T08:55:03 Creating triggers...
      2016-08-17T08:55:03 Created triggers OK.
      2016-08-17T08:55:03 Copying approximately 125366439 rows...
      Copying `test`.`mytable`: 0% 2+08:27:46 remain
      ...
      Copying `test`.`mytable`: 90% 12:44:20 remain
      2016-08-22T05:32:06 Copied rows OK.
      2016-08-22T05:32:06 Swapping tables...
      2016-08-22T05:32:07 Swapped original and new tables OK.
      2016-08-22T05:32:07 Dropping old table...
      2016-08-22T05:32:13 Dropped old table `test`.`_mytable_old` OK.
      2016-08-22T05:32:13 Dropping triggers...
      2016-08-22T05:32:13 Dropped triggers OK.
      Successfully altered `test`.`mytable`.

      5. Input files (log/dump/config files, etc.)

      • none

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            lpjirasync lpjirasync (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist