Pt-online-schema-change principle Analysis

When you modify a schema by using the Pt-online-schema-change tool, a new table with the same structure as the original data table is created, and the data from the original table is progressively copied to the new table.

For example, a ZS table with Id,name data columns, when adding a new column named UID to the table, use the following command:

Root@localhost: Mysql3316.sock -: -: - [Test]>ShowCreate TableZs\g*************************** 1. Row***************************       Table: ZsCreate Table:CREATE TABLE' ZS ' (' ID 'int( One) not NULLauto_increment, ' name 'varchar(255)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=2 DEFAULTCHARSET=UTF81Rowinch Set(0.00sec) Root@localhost: Mysql3316.sock -: -:Ten [Test]>SELECT *  fromZs;+----+-----------+|Id|Name|+----+-----------+|  1 |Zhangshuo|+----+-----------+1Rowinch Set(0.00Sec

[[email protected] data]# pt-online-schema---  alter "add UID int" D=test,t=zs--no-drop-old-table--no-drop-new-table--chunk-size=500--chunk-size-limit= --DEFAULTS-FILE=/USR/LOCAL/MYSQL/MY3316.CNF--host=localhost--port=3316--charset=utf8--user=root--ask-pass-- progress=time,30--max-load= "threads_running=100"--critical-load= "threads_running=1000"--chunk-index=PRIMARY-- Execute

1.pt-online-schema-change processing the above command, the new table is created based on the structure of the original ZS data table, (_zs_new), and the columns to be added to the new table:

 -Query SHOW VARIABLES like 'wsrep_on'                    -Query/*!40101 SET @OLD_SQL_MODE: = @ @SQL_MODE, @ @SQL_MODE: = ", @OLD_QUOTE: = @ @SQL_QUOTE_SHOW_CREATE, @ @SQL_QUOTE_SHOW_CRE ATE: = 1*/                    -Query Use' Test ' -Query SHOWCREATE TABLE' test '. ' Zs ' -Query/*!40101 SET @ @SQL_MODE: = @OLD_SQL_MODE, @ @SQL_QUOTE_SHOW_CREATE: = @OLD_QUOTE*/                    -QueryCREATE TABLE' test '. ' _zs_new ' (' ID ')int( One) not NULLauto_increment, ' name 'varchar(255)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=2 DEFAULTCHARSET=UTF8 -QueryALTER TABLE' Test '. ' _zs_new 'AddUidint                    -Query/*!40101 SET @OLD_SQL_MODE: = @ @SQL_MODE, @ @SQL_MODE: = ", @OLD_QUOTE: = @ @SQL_QUOTE_SHOW_CREATE, @ @SQL_QUOTE_SHOW_CRE ATE: = 1*/                    -Query Use' Test ' -Query SHOWCREATE TABLE' test '. ' _zs_new ' -Query/*!40101 SET @ @SQL_MODE: = @OLD_SQL_MODE, @ @SQL_QUOTE_SHOW_CREATE: = @OLD_QUOTE*/

2. Then create the after trigger to pass the INSERT, update, and delete operation contents of the original data table (ZS) to the _zs_new data table:

 -QueryCREATE TRIGGER' Pt_osc_test_zs_del ' afterDELETE  on' Test '. ' Zs ' forEach ROWDELETEIGNORE from' Test '. ' _zs_new 'WHERE' Test '. ' _zs_new '. ' ID '<=>Old . ' id ' -QueryCREATE TRIGGER' Pt_osc_test_zs_upd ' afterUPDATE  on' Test '. ' Zs ' forEach ROWREPLACE  into' Test '. ' _zs_new ' (' id ', ' name ')VALUES(new ' id ', new ' name ') -QueryCREATE TRIGGER' Pt_osc_test_zs_ins ' afterINSERT  on' Test '. ' Zs ' forEach ROWREPLACE  into' Test '. ' _zs_new ' (' id ', ' name ')VALUES(New ' id ', new ' name ')

3.pt-online-schema-change reads records from the original data table, reads the number of bars determined by the chunk-size option, and copies the read records to the _zs_new data table.

 -Query EXPLAINSELECT *  from' Test '. ' Zs 'WHERE 1=1                    -Query EXPLAINSELECT' id ', ' name ' from' Test '. ' Zs ' LOCKinchSHARE MODE/*explain pt-online-schema-change 13294 copy table*/                    -QueryINSERTLow_priority IGNORE into' Test '. ' _zs_new ' (' id ', ' name ')SELECT' id ', ' name ' from' Test '. ' Zs ' LOCKinchSHARE MODE/*pt-online-schema-change 13294 Copy table*/

4. DML performed by the user on the original ZS data table is automatically reflected to the _zs_new data table via a trigger. Pt-online-schema-change after all records have been copied, use Rename to rename the Zs table to _zs_old and modify the _zs_new table name to ZS. The rename command handles renaming of multiple data tables as a thing, so the user's query does not fail during the renaming process.

 -Query SHOW GLOBAL STATUS like 'threads_running'                    -Query ANALYZETABLE' Test '. ' _zs_new '/*Pt-online-schema-change*/                    -Query RENAMETABLE' Test '. ' Zs ' to' Test '. ' _zs_old ', ' Test '. ' _zs_new ' to' test '. ' Zs ' -QueryDROP TRIGGER IF EXISTS' test '. ' Pt_osc_test_zs_del ' -QueryDROP TRIGGER IF EXISTS' test '. ' Pt_osc_test_zs_upd ' -QueryDROP TRIGGER IF EXISTS' test '. ' Pt_osc_test_zs_ins ' -Query SHOW TABLES from' Test ' like '\_zs\_new'

5. Use of Pt-online-schema-change should be noted

(1) Repeat trigger: If an after trigger is already present in the original data table, Pt-online-schema-change cannot be used.

(2) Deadlock: Assuming that the ID column of the ZS data table has a unique index, Pt-online-schema-change uses the primary key, splits the record in memory, and then copies the records in the selected range to the _zs_new data table. In this process, the primary key of the _zs_new data table is locked, and the ID column unique index is locked for repeated checking. User-Modified content (INSERT, delete, update) is also applied to the _ZS_NEW data table via triggers. However, a query that arrives through a trigger locks the unique index created in the ID column and then attempts to lock the primary key again, creating a deadlock with the memory-block copy job of the Pt-online-schema-change process.

Pt-online-schema-change principle Analysis