As a full-time MySQL DBA consultant one of the biggest pain I face in my day-to-day operations is performing DDL in an high traffic / transaction intensive MySQL infrastructure without impacting business continuity, reliability and availability, This is because MySQL blocks the entire table for both reads and writes while modifying the table. The pain is more intense if you are doing it on huge tables, this might take hours to modify the table affecting application reliability but how can we do this on an 24*7 web property ? MySQL 5.6 introduced ONLINE DDL for InnoDB tables, This makes ALTER TABLE no longer a blocker for data modification. Percona developed PT-ONLINE-SCHEMA-CHANGE (version 3.0.5 while writing this post) alter tables without locking them during ALTER TABLE operations . I have explained below how PT-ONLINE-SCHEMA-CHANGE and ONLINE DDL works.
Table of Contents
How PT-ONLINE-SCHEMA-CHANGE works ?
PT-ONLINE-SCHEMA-CHANGE tool is developed by Percona to alter tables without locking them during ALTER TABLE operation. This tool creates a new empty table like the original table with the necessary structure changes, copy the data from the original table in multiple smaller chunks to the new table, drop the original table and finally rename the new table to the original name. I have explained below PT-ONLINE-SCHEMA-CHANGE with simple example
Consider table city (I have copied below table creation script) , You want to index column “city” for some performance reason. Now what will be impact if you create index on this table while your transaction is still on ? MySQL blocks your transaction causing performance outage (may not be too long) so how can we create index with no impact on application performance and reliability ? The example below explains the step-by-step process of what I am referring to:
mysql> show create table city\G; *************************** 1. row *************************** Table: city Create Table: CREATE TABLE `city` ( `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (`country_id`), CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=901 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
Now I am using PT-ONLINE-SCHEMA-CHANGE to create index on column “city” of table “city”:
[root@localhost ~]# pt-online-schema-change D=sakila,t=city,h=localhost,u=root --execute --alter="ADD INDEX city_idx (city)" --alter-foreign-keys-method="auto" --ask-pass Enter MySQL password: No slaves found. See --recursion-method if host localhost.localdomain has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 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 Child tables: `sakila`.`address` (approx. 603 rows) Will automatically choose the method to update foreign keys. Altering `sakila`.`city`... Creating new table... Created new table sakila._city_new OK. Altering new table... Altered `sakila`.`_city_new` OK. 2017-11-29T23:38:36 Creating triggers... 2017-11-29T23:38:36 Created triggers OK. 2017-11-29T23:38:36 Copying approximately 601 rows... 2017-11-29T23:38:36 Copied rows OK. 2017-11-29T23:38:36 Max rows for the rebuild_constraints method: 79360 Determining the method to update foreign keys... 2017-11-29T23:38:36 `sakila`.`address`: 603 rows; can use rebuild_constraints 2017-11-29T23:38:36 Analyzing new table... 2017-11-29T23:38:36 Swapping tables... 2017-11-29T23:38:36 Swapped original and new tables OK. 2017-11-29T23:38:36 Rebuilding foreign key constraints... 2017-11-29T23:38:36 Rebuilt foreign key constraints OK. 2017-11-29T23:38:36 Dropping old table... 2017-11-29T23:38:36 Dropped old table `sakila`.`_city_old` OK. 2017-11-29T23:38:36 Dropping triggers... 2017-11-29T23:38:36 Dropped triggers OK. Successfully altered `sakila`.`city`.
PT-ONLINE-SCHEMA-CHANGE Limitations
The following are limitations while using PT-ONLINE-SCHEMA-CHANGE :
- The PRIMARY KEY or UNIQUE INDEX needs to be present in the table, This is because the tool creates a DELETE trigger to keep new table updated while the process is running. The exception is when a PRIMARY KEY or UNIQUE INDEX is being created from existing columns as part of the ALTER clause; In such cases it will be using these column(s) for the DELETE trigger
- The RENAME clause cannot be used to rename the table
- The columns cannot be rename by dropping and re-adding with new name. This tool will not copy the original column’s data to the new column
- The tool will fail if you add a column without a default value and make it NOT NULL
- We need to specify _constraint_name to DROP FOREIGN KEY constraint_name, i.e –alter “DROP FOREIGN KEY _city_id_fk”
- The tool does not use LOCK IN SHARE MODE with MySQL 5.0 because it can cause a slave error which breaks replication
ONLINE DDL
MySQL 5.6 introduced online DDL for InnoDB tables, which means ALTER TABLE statements will no longer block access to database modification while running. Typically if you want to make changes to your production servers please prefer during off hours, Especially when the server utilization is at its lowest for both CPU and disk. The Online DDL syntax is almost similar to the normal DDL statement with two additional parameters : ALGORITHM and LOCK
ALGORITHM:
- INPLACE: The table change will happen “in-place” (without rebuilding the entire table), Often copying data to a temporary table is not required
- COPY: This is actually traditional method of copying data to a temporary table, rebuilding the table and reconstructing the secondary indexes
LOCK:
NONE: Read and write operations are allowed during the database altering process
SHARED: Only read operations are allowed during the database altering process
EXCLUSIVE: Neither SELECT nor DML are allowed, The entire table will be locked for both reading and writing
ONLINE DDL Example
mysql> alter table city -> add index city_idx(city), algorithm=inplace, lock=none; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
