Table modifications are a common task for database administrators. In this blog, I’ll explain how to alter tables online in a controlled manner that does not disrupt application users or cause application downtime.
One of the tools in Percona Toolkit is pt-online-schema-change, a utility that alters the structure of a table without interfering with the reads or writes. The tool creates an empty copy of the table to alter and modify as desired before copying the rows from the original table into the new one.
When the copying is finished, it removes the original table and replaces it with the new one. Any changes made to data in the original tables during the copy process will be reflected in the new table as the tool creates triggers on the original table to update the corresponding rows in the new table.
How to test the pt-online-schema-change command?
Before running the actual alter using the tool, perform a dry run to ensure the pt-online-schema-change command is functional. The –dry-run option creates and modifies the new table without adding triggers, copying data, or replacing the existing table.
The basic command for modifying a table is as follows, which may need to be tweaked as needed using the variables like –critical-load threads_running –max-load Threads_running –chunk-size –max-lag, –max-flow-ctl (Percona XtraDB Cluster) and so on running in a production environment.
I’m using “ENGINE=InnoDB” for the first test case, which rebuilds the table; this is useful for removing fragmented spaces from the table.
Dry-run test:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | $ pt-online-schema-change --dry-run --alter "ENGINE=InnoDB" h=172.31.92.72,D=mytestdb,t=authors; 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 Starting a dry run. `mytestdb`.`authors` will not be altered. Specify --execute instead of --dry-run to alter the table. Creating new table... Created new table mytestdb._authors_new OK. Altering new table... Altered `mytestdb`.`_authors_new` OK. Not creating triggers because this is a dry run. Not copying rows because this is a dry run. Not swapping tables because this is a dry run. Not dropping old table because this is a dry run. Not dropping triggers because this is a dry run. 2022-12-09T05:44:23 Dropping new table... 2022-12-09T05:44:23 Dropped new table OK. Dry run complete. `mytestdb`.`authors` was not altered. $ |
How to run the ALTER TABLE?
It is recommended that you read the documentation before performing the task.
To run the alter, replace the –dry-run option with –execute.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | $ pt-online-schema-change --execute --alter "ENGINE=InnoDB" h=172.31.92.72,D=mytestdb,t=authors; Found 1 slaves: ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket Will check slave lag on: ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket .. 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 `mytestdb`.`authors`... Creating new table... Created new table mytestdb._authors_new OK. Altering new table... Altered `mytestdb`.`_authors_new` OK. 2022-12-09T05:57:10 Creating triggers... 2022-12-09T05:57:10 Created triggers OK. 2022-12-09T05:57:10 Copying approximately 10023 rows... 2022-12-09T05:57:10 Copied rows OK. 2022-12-09T05:57:10 Analyzing new table... 2022-12-09T05:57:10 Swapping tables... 2022-12-09T05:57:10 Swapped original and new tables OK. 2022-12-09T05:57:10 Dropping old table... 2022-12-09T05:57:10 Dropped old table `mytestdb`.`_authors_old` OK. 2022-12-09T05:57:10 Dropping triggers... 2022-12-09T05:57:10 Dropped triggers OK. Successfully altered `mytestdb`.`authors`. $ |
Can we pause the pt-online-schema-change execution? Yes!
The –pause-file=/tmp/pt-osc.pause option helps you to pause the execution. While the file specified by this parameter is present, execution will be paused and resumed when it is removed.
Note: I shortened the pt-osc log to make the result more readable.
1 2 3 4 5 6 7 8 9 | $ pt-online-schema-change --pause-file=/tmp/pt-osc.pause --execute --alter "ENGINE=InnoDB" h=172.31.92.72,D=mytestdb,t=authors; …. 2022-12-10T15:42:01 Copying approximately 10023 rows... Sleeping 60 seconds because /tmp/pt-osc.pause exists Sleeping 60 seconds because /tmp/pt-osc.pause exists Copying `mytestdb`.`authors`: 73% 00:44 remain 2022-12-10T15:44:04 Copied rows OK. ... Successfully altered `mytestdb`.`authors`. |
Can we review the data and tables before swapping them? Yes!
The —no-swap-tables —no-drop-old-table —no-drop-new-table —no-drop-triggers options allow us to do the alter in a controlled manner.
That is, we will let tools handle the majority of the tasks, such as creating the new table, altering, copying the records, and the remaining table swapping and trigger dropping will be done manually.
Caution: The —no-swap-tables option does not work if the table has foreign keys with child tables associated.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | $ pt-online-schema-change --no-swap-tables --no-drop-triggers --no-drop-old-table --no-drop-new-table --execute --alter "CHARACTER SET = utf8mb4, COLLATE = utf8mb4_general_ci, MODIFY email varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,MODIFY name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL" h=172.31.92.72,D=mytestdb,t=authors2; Found 1 slaves: ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket Will check slave lag on: ip-172-31-90-216.ec2.internal -> ip-172-31-90-216.ec2.internal:socket 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 `mytestdb`.`authors2`... Creating new table... Created new table mytestdb._authors2_new OK. Altering new table... Altered `mytestdb`.`_authors2_new` OK. 2022-12-09T09:16:28 Creating triggers... 2022-12-09T09:16:28 Created triggers OK. 2022-12-09T09:16:28 Copying approximately 10067 rows... 2022-12-09T09:16:29 Copied rows OK. Not dropping old table because --no-drop-triggers was specified. Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute: DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_del` DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_upd` DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_ins` Not dropping the new table `mytestdb`.`_authors2_new` because --no-drop-new-table was specified. To drop the new table, execute: DROP TABLE IF EXISTS `mytestdb`.`_authors2_new`; Successfully altered `mytestdb`.`authors2`. $ |
pt-online-schema-change has done the job and we now have two tables and three triggers. So we can safely review the table structure and data in the _authors2_new table, and once we’re sure everything is in order, we can swap and drop the triggers.
1 2 3 4 5 6 7 8 | mysql> show tables like '%authors2%'; +---------------------------------+ | Tables_in_mytestdb (%authors2%) | +---------------------------------+ | _authors2_new | | authors2 | +---------------------------------+ 2 rows in set (0.00 sec) |
Find the TRIGGERS:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SELECT TRIGGER_NAME,EVENT_MANIPULATION FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='mytestdb' and EVENT_OBJECT_TABLE like '%authors%' G *************************** 1. row *************************** TRIGGER_NAME: pt_osc_mytestdb_authors2_del EVENT_MANIPULATION: DELETE *************************** 2. row *************************** TRIGGER_NAME: pt_osc_mytestdb_authors2_upd EVENT_MANIPULATION: UPDATE *************************** 3. row *************************** TRIGGER_NAME: pt_osc_mytestdb_authors2_ins EVENT_MANIPULATION: INSERT 3 rows in set (0.00 sec) |
Run the following SQL to swap the tables and remove the triggers.
1 2 3 | RENAME TABLE mytestdb.authors2 TO mytestdb._authors2_old, mytestdb._authors2_new TO mytestdb.authors2; |
Lastly, remove the triggers and the old table:
1 2 3 4 | DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_del`; DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_upd`; DROP TRIGGER IF EXISTS `mytestdb`.`pt_osc_mytestdb_authors2_ins`; DROP TABLE IF EXISTS mytestdb._authors2_old; |
Wrap up
pt-online-schema-change is a part of the Percona Toolkit for altering tables online, and we can customize it with various options available based on our needs. MySQL’s online DDL with the direct alter is an option, particularly for dropping indexes and changing metadata, among other things. Where online DDL is not a choice, we can use the pt-online-schema-change.
Caution: It is not recommended to run the tool directly in the replica instance as the PT-OSC operations will not produce a consistent table on the replicas. The statements coming via binlog will not be processed by triggers, so whatever new data is coming in via replication, will be missing in the new table.
