MySQL - online schema change with percona toolkit

To Nha Notes | April 15, 2021, 12:21 a.m.

The command pt-online-schema-change is one of useful command of Percona Toolkit which could help DBA can easily alter MySQL tables without downtime. The main idea of this command is it internally copy origin table to new one which include the schema change, and capture changes such as insert/update/delete from original table to new tables via trigger. After copying table complete, it will swap to use new table and delete origin one.

Installation

Usage: pt-online-schema-change [OPTIONS] DSN

Example:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

Below are highlight useful arguments:

--chunk-size (type: size; default: 1000): the number of rows to select for each chunk copied. Allowable suffixes are k, M, G.

--chunk-time (type: float; default: 0.5): adjust the chunk size dynamically so each data-copy query takes this long to execute. The tool tracks the copy rate (rows per second) and adjusts the chunk size after each data-copy query. If this option is set to zero, the chunk size doesn’t auto-adjust. Another way to do the same thing is to specify a value for --chunk-size explicitly, instead of leaving it at the default.

--max-load (type: Array; default: Threads_running=25): examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than their thresholds.

--critical-load (type: Array; default: Threads_running=50): Examine SHOW GLOBAL STATUS after every chunk, and abort if the load is too high.

--dry-run: create and alter the new table, but do not create triggers, copy data, or replace the original table.

--execute: Indicate that you have read the documentation and want to alter the table. You must specify this option to alter the table. If you do not, then the tool will only perform some safety checks and exit.

--print: print SQL statements to STDOUT. Specifying this option allows you to see most of the statements that the tool executes. You can use this option with --dry-run

--progress (type: array; default: time,30), print progress reports to STDERR while copying rows. The value is a comma-separated list with two parts. The first part can be percentage, time, or iterations; the second part specifies how often an update should be printed, in percentage, seconds, or number of iterations.

--set-vars (type: Array): Set the MySQL variables in this comma-separated list of variable=value pairs. By default, the tool sets:

wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60

For example, specifying --set-vars wait_timeout=500 overrides the default value of 10000

--sleep (type: float; default: 0): how long to sleep (in seconds) after copying each chunk. This option is useful when throttling by --max-lag and --max-load are not possible. A small, sub-second value should be used, like 0.1, else the tool could take a very long time to copy large tables.

--[no]swap-tables (default: yes): Swap the original table and the new, altered table. This step completes the online schema change process by making the table with the new schema take the place of the original table. The original table becomes the “old table,” and the tool drops it unless you disable --[no]drop-old-table.

--pause-file (type: string): execution will be paused while the file specified by this param exists.

alter-foreign-keys-method: there are two values:

• rebuild_constraints: this is the preferred approach for the reason it maintains the consistency of the schema and its relations.

      Rename T1 –> T1_old
      Rename _T1_new –> T1
      ALTER on child table to adjust the foreign key so that it points to T1 rather T1_old.
      Drop the table T1_old
      Drop triggers from the new T1 table.

• auto: If this value is used, it leaves the decision up to the tool itself to choose from the two (drop_swap/rebuild_constraints) options available. If the number of rows in the child table is small, it uses rebuild_constraints; otherwise, it goes with the drop_swap approach. For this reason, this option should always be chosen carefully as it can end up with unexpected results when choosing drop_swap.

Reference here for other arguments: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html