Principles and Application of pt-online-schema-change, ptonlinechangeschema

Principles and Application of pt-online-schema-change, ptonlinechangeschema

If it is reprinted, please indicate the source of the blog: www.cnblogs.com/xinysu/. The copyright belongs to xiaoradish in the blog garden. Thank you for your support! PERCONA provides several tools for managing and maintaining MySQL. It is integrated into PERCONA Toolkit and provides tools such as slow query and analysis, master-slave differential comparison, master-slave differential repair, and online table structure modification, I personally think it is quite useful. This document briefly introduces the pt-online-schema-change tool. Previous blog: http://www.cnblogs.com/xinysu/p/6732646.html, explained the introduction of Online DDL 5.6, has been more mature in version 5.7, but after all this is only supported after version 5.6, the previous version does not support this feature, this article briefly describes the pt-online-schema-change tool, which is named long and sometimes abbreviated as pt-osc in the industry, when you use a version earlier than version 5.6 or DDL that does not apply to Online DDL (such as modifying the data type), you can Post-Test MeasurementWhether to use this tool to execute ddl SQL. pt-online-schema-change can be used to execute ALTER statements without blocking write operations on database tables. 1. Principles The table must have a primary key or a unique index !!Assume that the existing tbosc requires the ALTER operation. When pt-online-schema-change is used, a new empty table _ tbosc_new is created based on the tbddl table structure and index conditions, copy data from the original table tbosc to the new table _ tbosc_new. After the copy data operation is complete, replace tbddl with _ tbosc_new and delete the old table. What is the detailed process as described above? Let's take a look at these questions:

• During the ALTER operation, does the table support DML?
• If DML is supported, how does one synchronize DML to a new temporary table?
• What is the lock situation for the entire operation process?
• What is the performance impact during execution?
• What are the restrictions on this tool?

1.1 detailed Execution ProcessHow can I view the detailed execution process? When the general log is enabled for the database and pt-online-schema-change is executed, all operations on the database are displayed. The detailed execution process is as follows: 1.2 answersAccording to the execution process, you can answer the first question.

• During the ALTER operation, does the table support DML?
  • The ALTER process adopts the Copy Table To New Table method To create a New Table, and then creates Three triggers in the original Table: DELETE \ UPDATE \ INSERT trigger. Once the New Table is created, when copying data to a new table, if the original table data changes, it is updated to the new table through the trigger.
  • If DML is supported, how does one synchronize DML to a new temporary table?
  • The ALTER process adopts the Copy Table To New Table method To create a New Table, and then creates Three triggers in the original Table: DELETE \ UPDATE \ INSERT trigger. Once the New Table is created, when copying data to a new table, if the original table data changes, it is updated to the new table through the trigger.
  • When you INSERT the original table, the trigger inserts the new record into the new table based on its primary key ID;
  • When updating the original table, the trigger determines whether the new and old IDs are consistent based on the primary key ID. If they are consistent, the trigger deletes the table, and then records the new table in replace.
  • When deleting the original table, the trigger directly deletes row records based on the primary key ID.
  • If the data has not been copied to the new table when it is modified, the data will be copied after modification. Although the data is overwritten repeatedly, no error occurs. If the data has been copied, the original table is modified, in this case, the trigger synchronously modifies data, ensuring data consistency in both cases;
• What is the lock situation for the entire operation process?
  • After creating a new table, copy data to the new table according to the size of each chunk. Each SELECT operation is share mode with the S lock, but each chunk is small, so the lock time is not long.
  • After the data copy ends, a rename operation is performed. During this operation, the DML operation is not supported, but the speed is very fast and will not cause long-time table lock.
  • The tool sets the lock wait time for this DDL operation to 1 s. When an exception occurs, it will be an ALTER operation exception, rather than other business operation exceptions, in this way, the execution of other transactions can be minimized.

• What is the performance impact during execution?

• • In general, the impact on Database locks is minimized and DML operations are allowed during execution.
  • But note that,All ddl SQL statements are converted to copy table to new table.In this process, it will greatly occupy disk I/O and CPU resources, while keeping up with the influence of latency,Repeat the impact of DDL, and then select the appropriate time for execution.
  • During data copy, if the Master/Slave latency exceeds max-lag, the copy data is stopped and the Master/Slave latency is restored. The default value is 1 min. You can set the value through -- max-lag.
  • If a server load exception is detected, the operation will also be stopped. You can use -- max-load, -- critical-load to set

• What are the restrictions on this tool?
  • The table must contain a primary key or a unique index.
  • Copy and filter out tables. ALTER operation
  • During data copy, if the Master/Slave latency exceeds max-lag, the copy data is stopped and the Master/Slave latency is restored. The default value is 1 s. You can set the value through -- max-lag.
  • If a server load exception is detected, the operation will also be stopped. You can use -- max-load, -- critical-load to set
  • Set the lock wait time for the Operation to 1 s. When an exception occurs, the ALTER operation is abnormal, instead of other business operations, so that the operation of other transactions can be minimized.
  • By default, tables referenced by foreign keys do not support the ALTER operation unless you manually specify the -- alter-foreign-keys-method parameter.
  • You cannot modify the myisam table of a node on Percona XtraDB Cluster (PXC ).

2. Prepare the environmentToolkit download page: https://www.percona.com/downloads/percona-toolkit/LATEST/ (the latest version is 3.0.2) rpm: https://www.percona.com/downloads/percona-toolkit/3.0.2/binary/redhat/7/x86_64/percona-toolkit-3.0.2-1.el7.x86_64.rpm official instructions for use documentation address: https://www.percona.com/doc/percona-toolkit/LATEST/index.html environment installation dependency project: Perl, DBI, DBD: mysql installation is very simple, run rpm installation to rpm-ivh percona-toolkit-3.0.2-1.el7.x86_64.rpm after related environment dependencies are installed 3. syntax description 3.1 main options

• -- Alter
  • If you specify the ALTER statement, the normal alter table tbname [ADD | MODIFY | DROP | ALTER] COLUMN COLUMN_NAME... removes the preceding alter table, you can directly specify the subsequent content.
  • Notes
    • Rename is not supported. Please use rename table tablename TO new_tablename directly;
    • If the table contains data, creating a non-empty column with no default value will fail. If the table is not empty, you must specify the default value;
    • If the table contains data and the default value is added to a column that can be empty, the old data that is NULL will not be modified and will still be NULL. If the new data is added to the table, the default value will be set by default.
    • The execution of foreign key deletion is performed on the new table, so the foreign key value is named differently from the original table, assume that the foreign key name of the original table is fk_foo, the foreign key name of the new table is _ fk_foo, so the ALTER statement to be deleted is: drop foreign key _ fk_foo;

• -- Alter-foreign-keys-method
  • If the modified table is another table with the foreign key reference, make sure that the rename process is successful, otherwise, these sub-tables fail to reference the specified table name, and an error will be reported for operations on the sub-tables. For example, if a foreign key fk_guid is used to reference table tbb, tbb needs to perform DDL operations. Based on the principles of the pt tool, it is known that there will be a rename link, this link may cause problems such as invalid constraints or execution congestion.
  • Therefore, for the last rename step, the tool provides the following processing methods:
    • Auto
      • Automatically select rebuild_constraints or drop_swap. rebuild_constraints is preferred.
      • Rebuild_constraints
      • Before rename table, firstDelete the foreign key constraint of the sub-TABLE, re-establish the foreign key constraint to the new TABLE (add the alter table statement), and finally perform the rename operation.
      • Even if this rename operation fails, it will rename the operation to the new table, so that the reference table does not exist.
      • Disadvantages: If the sub-table is too large, the sub-table may be blocked when the foreign key constraint is added.
    • Drop_swap
      • Before rename executionDisable the foreign key check and delete the original table. The new rename TABLE is the original table name.
      • This process is very fast and there is no congestion
      • This method requires that you specify -- no-swap-tables and -- no-drop-old-table.
      • Disadvantages: when the original table is deleted and the new table has not been rename as the name of the original table, this time is actually very short, but this time does not exist when it is equal to the original table name, when the sub-Table performs some DML operations, errors may occur. During the rename period, if the new table rename the original table fails but the original table has been deleted, operations on its sub-tables during this period will encounter a large area of problems until manual repair
    • None
      • Similar to the drop_swap operation, the difference lies in the processing of the original table.
      • Disable the foreign key constraint according to the normal pt tool process. The original rename TABLE is a temporary table, the new rename TABLE is the original table name, and the temporary table is deleted.
      • Disadvantages: When you rename the original table as a temporary table, but the new table does not rename the name of the original table, this time is actually very short, but during this time, the table with the same name as the original table does not exist. When the sub-table is used for DML, errors may occur.
    • -- Drop-old-table
      • Whether the original table is retained after the operation is successful. The default value is Delete,
      • Default: yes; Optional: -- no-drop-old-table

• -- Dry-run
  • Only create a new table, but do not execute a trigger, copy data, and replace the original table.
• -- Execute
  • Are you sure you want to execute the ALTER operation. Note that if this operation is not specified, you only need to perform the security check and then release it.
  • The tool is executed only after you fully understand the usage of the Tool. If you don't know anything about the test, you can execute the tool online. This will cause pitfalls...
• -- Host
  • Connection Host Name
• -- Max-lag
  • 1 s by default
  • Check the slave database delay time. If the slave database delay is exceeded, stop the copy data and take a rest -- check-interval seconds, and then start the copy data again.
  • View the Latency by viewing the Seconds_Behind_Master
  • If -- check-slave-lag is specified, the tool only checks the server latency, not all servers.
• -- Check-interval
  • The slave database delay exceeds the specified -- max-lag, And the rest time of copy data is interrupted.
  • The default value is 1 s.
• -- Max-load
  • The data copy process monitors the currently running thread of the database. If the thread value exceeds the specified Threads_running value, the data copy will be stopped and Pausing because Threads_runing = 15 will be promised in the output content, until the number of running threads is less than the given value, copy data is restored. In this loop, the data copy is completed.
  • Threads_runing: 25 by default
  • Example: -- max-load = Thread_running = 15
• -- Password
  • Password of the Database User Name
• -- Port
  • Database port number
• -- Socket
  • Database socket File
• -- User
  • Database username

• -- Recursion-method
  • MASTER's method of searching for SLAVE (this option is widely used in pt tools involving MASTER-SLAVE issues)
  • Four options

• • Processlist, use show processlist to find the slave Database
  • Hosts. If the default port number is not 3306, it is more reliable to use the hosts method to find the slave database.
  • Dsn: Use table tdsn to store slave database information. (For detailed Parameter options of DSN, see 3.3 DSN line selection)
    • You need to manually create a dsns table in the database that requires DDL
    • Create table 'dss' ('id' int (11) not null AUTO_INCREMENT, 'parent _ id' int (11) default null, 'dsn 'varchar (255) not null, primary key ('id '));
    • Store slave database information
    • Insert into dsns (dsn) values (h = slave_host, u = repl_user, p = repl_password, P = port );
    • This parameter is used in the following format (assume that the dsns table is created in the database dbosc)
    • -- Recursion-method dsn = D = dbosc, t = dsns
  • None, do not find slave Database

3.2 output

• -- Statistics increases the number of affected rows to print. You can view the copy progress.
• -- Print: prints the alter process in detail. If it is not specified, it simply prints

3.3 DSN options

• A
  • Character Set settings
• D
  • The database where the table to be Alter is located.
• F
  • Mysql read default file. If the options of the data source are stored in the file, use F to specify
• H
  • Host, Database host Name or IP address
• P
  • Password: password of the Database User
• P
  • Port, database instance port number
• S
  • Socket, instance socket File
• T
  • Table Name
• U
  • User Name

4. Example 4.1 General DDLIn general, if a table is referenced by a foreign key, pay attention to the settings of -- alter-foreign-keys-method. This section does not consider slave database latency and foreign keys. Pt-online-schema-change also supports merging multiple SQL statements from the same TABLE into one SQL statement. Since all DDL statements use the COPY TABLE TO NEW TABLE method, you do not need to classify ddl SQL statements.

1 create table 'tbosc '(2 'id' int (11) not null AUTO_INCREMENT, 3 'name' varchar (100) default null, 4 'age' int (11) default null, 5 primary key ('id') 6) ENGINE = InnoDB default charset = utf8; 7 8 # Add column 9 pt-online-schema-change -- socket =/tmp/mysql3310.sock -- user = root -- password = ***** D = dbosc, t = tbosc -- alter "add column stunum int" -- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute10 11 # delete column 12 pt-online-schema-change -- socket =/tmp/mysql3310.sock -- user = root -- password = ***** D = dbosc, t = tbosc -- alter "drop column stunum" -- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute13 14 # modify column data type 15 pt-online-schema-change -- socket =/tmp/mysql3310.sock -- user = root -- password = ***** D = dbosc, t = tbosc -- alter "modify column age varchar (10) "-- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute16 17 # increase the column length by 18 pt-online-schema- change -- socket =/tmp/mysql3310.sock -- user = root -- password = ***** D = dbosc, t = tbosc -- alter "modify column age varchar (100) "-- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute19 20 # create index 21 pt-online-schema-change -- socket =/tmp/mysql3310.sock -- user = root -- password = ***** D = dbosc, t = tbosc -- alter "add index IX_AGE (AGE) "-- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute22 23 # Delete index 24 pt-online-schema-change -- socket =/tmp/mysql3310.sock -- user = root -- password = ***** D = dbosc, t = tbosc -- alter "drop index IX_AGE" -- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute25 26 # settings the default value is 27 pt-online-schema-change -- socket =/tmp/mysql3310.sock -- user = root -- password = ***** D = dbosc, t = tbosc -- alter "ALTER column age set default 100" -- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute28 29 # Can one 30 pt-online-schema-change -- socket =/tmp/mysql3310.sock -- user = root -- password = *** D = dbosc be merged multiple times, t = tbosc -- alter "add column onecol int, add column twocol varchar (100), add index ix_onecol (onecol ), alter column name set default 'x' "-- recursion-method = none -- no-check-replication-filters -- alter-foreign-keys-method auto -- print -- execute

4.2 slave database latency considerationsConsider slave database latency, which means pay attention to the settings of these options

• -- Max-lag
• -- Check-interval
• -- Recursion-method
• -- Check-slave-lag

If the slave database delay exceeds max-lag, the copy data will be stopped and the data will be copied after check-interval seconds. Check-slave-lag specifies the Server Load balancer instance, which only compares the latency of this Server Load balancer instance. Recursion-method is the method for the master database to find the slave database. There are four methods: processlist, hosts, dsn, none. For details, refer to the detailed descriptions of the above options, this section describes the use of dsn and check-slave-lag in detail. Assume that you need to add a column in table tbddl in the dbosc Database: holobby varchar (100), you need to consider the latency of the slave database # create table dsns, record slave database information create table 'dss' ('id' int (11) not null AUTO_INCREMENT, 'parent _ id' int (11) default null, 'dsn 'varchar (255) not null, primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 6 default charset = utf8 # insert slave database information, which has two slave databases, 242 and 3310 insert into dsns (dsn) select "h = 192.168.9.244, u = repl, p = *****, P = 3320 "; insert into dsns (dsn) select "h = 192.168.9.244, u = repl, p = *****, P = 3320 "; If you need to consider the latency of multiple slave databases, you can use the dsns table to record slave database information. If you only need to consider the latency of one slave database, you can use the dsns table or the -- check-slave-lag parameter to specify the slave database.If you do not consider the foreign key relationship, consider the degree of impact from the database, and check that the slave database latency exceeds 1 s, you can rest for 5 s, the specific command is as follows: pt-online-schema-change-P3310 -- user = root -- password = ***** D = dbosc, t = tbddl -- max-lag = 1 s -- check-interval = 10 s -- alter "ADD holobby varchar (100) not null default 'sleep '"-- recursion-method dsn = D = dbosc, t = dsns -- alter-foreign-keys-method auto -- execute if the Seconds_Behind_Master of slave is detected to exceed 1 s, it will take 10 s of rest before monitoring, in this process, Replica lag is 395 seconds on sutest244. Wai is printed in the output file. Ting. Replica lag is 425 seconds on sutest244. Waiting. Replica lag is 456 seconds on sutest244. Waiting. It indicates how many seconds the master-slave delay has occurred. The copy thread is stopped and is Waiting. If only one slave database is specified to view the latency, use the -- check-slave-lag command as follows: pt-online-schema-change-P3310 -- user = root -- password = ***** D = dbosc, t = tbddl -- max-lag = 1 -- check-interval = 10 -- check-slave-lag = h = 192.168.9.244, u = root, p = ****, P = 3310 -- alter "ADD Hober varchar (100) not null default 'sleep '" -- recursion-method -- alter-foreign-keys-method auto -- print -- execute 5 pt-osc or online DDL?This article describes the principles and tests of pt-online-schema-change and the online ddl description in the previous article. You can understand whether pt-osc is a ddl SQL, new tables are created and replaced, regardless of the DDL type, but DDL operations are allowed during execution. online ddl is divided into several types of DDL, and some DDL only need to modify metadata, some DDL only need to create an index page on the ibd file, and some need to rebuild the table. These three types support DML operations during execution, but the copy table type does not support DML operations. Therefore, you can make the following judgments:

• If MySQL versions earlier than MySQL 5.6 do not support online ddl operations, pt-online-schema-change is a good choice;
• If MySQL version is 5.6 or later and supports online-ddl, use online ddl first. However, if ddl SQL is used in online DDL, copy table to tmp table, we recommend that you use pt-online-schema-change for processing. For example, to modify the DDL of the column data type, the online DDL must copy table to tmp table. Only query is supported during this period, DML operations are not supported. At this time, pt-online-schema-change can be used for processing, because it also copies temporary tables and supports DML operations during execution;
• If you execute Online DDL statements but are sensitive to the latency of the slave database, consider whether you can set parallel replication in the slave database for the DDL statements that require copy table and rebuild table, if not, select pt-online-schema-change.

Reference: https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html