Introduction and use of MySQL--pt-osc

pt-osc workflow:
1. Check whether the changed table has a primary key or unique index, and whether there is a trigger
2. Check Modify the table structure, create a temporary table, and execute the ALTER TABLE statement on the new table
3. Create three triggers on the source table for the INSERT UPDATE DELETE operation
4. Copy data from the source table to the temporary table. During the copy process, the update operation on the source table will be written to the new table.
5. Copy the temporary table and source table rename (requires metadata modification lock and short-term table lock)
6. Delete the source table and trigger to complete the modification of the table structure.

##==================================== =================
##pt-osc tool restrictions
1. The source table must have a primary key Or unique index, if there is no tool, it will stop working
2. If the online replication environment filter operation is too complicated, the tool will not work
3. If it is turned on Replication delay check, but when the master and slave are delayed, the tool will suspend the data copy work
4. If the master server load check is enabled, but the master server load is high, the tool will suspend the operation
5. However, when the table uses foreign keys, if the --alter-foreign-keys-method parameter is not used, the tool will not be executed.
6. Only Innodb storage engine tables are supported. And it requires more than 1 times the free space of the table on the server.

##==================================== =================
##pt-osc copy data
In the process of copying data, the tool The data will be split according to the primary key or unique key, and the number of rows of data copied each time will be limited to ensure that the copy does not consume too much server resources. In order to ensure that the data in the source table and the target table are the same, use LOCK IN SHARE MODE to obtain the latest data of the data segment to be copied and add a shared lock to the data to prevent other sessions from modifying the data. Use LOW_PRIORITY IGNORE to insert the data into the new table. Keyword LOW_PRIORIT causes the insertion operation to wait for other operations that access the table to complete before executing it. The keyword INGORE causes the new data to be ignored and not inserted when there is a duplicate primary key or unique index key in the table.

Data copy script when modifying table `testdb1`.`tb1001`:

## Get the next copy of data first Boundary, forced index can effectively avoid problems with the execution plan
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `testdb1`.`tb1001` FORCE INDEX(`PRIMARY`) WHERE ((` id` >= '8394306')) ORDER BY `id` LIMIT 22256, 2 /*next chunk boundary*/

## By copying the boundary limit of data, prevent single Copying too much data blocks other sessions for a long time
INSERT LOW_PRIORITY IGNORE INTO `testdb1`.`_tb1001_new` (`id`, `c1`, `c6`) SELECT `id`, `c1 `, `c6` FROM `testdb1`.`tb1001` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '8394306')) AND ((`id` <= '8416562')) LOCK IN SHARE MODE /*pt-online-schema-change 14648 copy nibble*/

##================== ====================================
#pt-osc Trigger

The pt-osc tool creates three AFTER triggers on the source table for the INSERT UPDATE DELETE operation. The DELETE trigger uses DELETE IGNORE to ensure that the data in the source table and the new table are deleted, while INSERT and UPDATE The trigger uses REPLACE INTO to ensure that the new table data is consistent with the source table data.

Since MySQL limits that there can only be one trigger of the same type, it is necessary to check whether there is a trigger on the source table before running. In order to ensure the efficiency and convenience of deletion and update, the source table Table data is sharded, so it requires a primary key or unique index on the table.

##==================================== =================
##pt-osc host performance impact

To avoid excessive Affecting host performance, the pt-osc tool limits it through the following aspects:
1. Control the data size of each copy through the parameters chunk-size and chunk-time
2. Check the current pressure of the host through the parameter max-load. After each chunk copy is completed, the SHOW GLOBAL STATUS LIKE 'Threads_running' command will be run to check the number of currently running Threads. The default Threads_running=25, if the maximum value is not specified, 120% of the current value will be taken as the maximum value. If it exceeds the threshold, the data copy will be suspended

##================ ======================================
##pt -osc's replication delay from the library

For businesses that are sensitive to replication delays, you can control the replication delay through the following parameters:

-- max-log
The default is 1s. After each chunks copy is completed, the delay information of the slave library specified by the check-slave-lag parameter will be checked. If it exceeds the max-log threshold, then Pause copying data until the copy delay is less than the max-log threshold. Checking replication latency information relies on the value of the Seconds_Behind_Master column returned in the SHOW SLAVE STATUS statement.

--check-interval
When a replication delay occurs and the replication data is paused, the replication delay is checked periodically according to the time specified by check-interval until the delay The time is lower than the max-log threshold, and then restore the data copy

--check-slave-lag
Need to check the slave IP of the replication delay
If the check-slave-lag parameter is specified and the slave library cannot connect normally or the slave library IO thread and SQL thread are stopped, it will be considered that there is a delay between the master and slave, causing the data copy operation to be suspended.
If the check-slave-lag parameter is not specified, the delay of the slave library will still be checked by default, but the replication delay will not cause data replication to be paused.

##==================================== =================
##pt-osc chunk settings
In the help document of pt-osc , the parameters about chunk are as follows:

--chunk-index=s                  Prefer this index for chunking tables
--chunk-index-columns=i          Use only this many left-most columns of a --chunk-index
--chunk-size=z                   Number of rows to select for each chunk copied (default 1000)
--chunk-size-limit=f             Do not copy chunks this much larger than the desired chunk size (default 4.0)
--chunk-time=f                   Adjust the chunk size dynamically so each data-copy query takes this long to execute (default 0.5)

When neither chunk-size nor chunk-time is specified, chunk-size defaults The value is 1000, and the default value of chunk-time is 0.5S. The data is copied according to chunk-size for the first time, and then the size of chunk-size is dynamically adjusted according to the time of the first copy to adapt to the performance changes of the server, such as the last time Copying 1000 rows consumes 0.1S, then dynamically adjust chumk-size to 5000 next time.
If the value of chumk-size is explicitly specified or chunk-time is specified as 0, data will be copied according to chunk-size every time.

##=====================================================##
pt-osc之alter语句限制
1、不需要包含alter table关键字，可以包含多个修改操作，使用逗号分开，如"drop clolumn c1, add column c2 int"
2、不支持rename语句来对表进行重命名操作
3、不支持对索引进行重命名操作
4、如果删除外键，需要对外键名加下划线，如删除外键fk_uid, 修改语句为"DROP FOREIGN KEY _fk_uid"
##=====================================================##
pt-osc之命令模板
## --execute表示执行
## --dry-run表示只进行模拟测试
## 表名只能使用参数t来设置，没有长参数

pt-online-schema-change \--host="127.0.0.1" \--port=3358 \--user="root" \--password="root@root" \--charset="utf8" \--max-lag=10 \--check-salve-lag='xxx.xxx.xxx.xxx' \--recursion-method="hosts" \--check-interval=2 \--database="testdb1" \t="tb001" \--alter="add column c4 int" \--execute

pt-osc之命令输出
上面命令执行输出如下：

No slaves found.  See --recursion-method if host 171DB166 has slaves.
Will check slave lag on:
  170DB166
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `testdb1`.`tb001`...
Creating new table...
Created new table testdb1._tb001_new OK.
Altering new table...
Altered `testdb1`.`_tb001_new` OK.
2016-04-28T23:18:04 Creating triggers...
2016-04-28T23:18:04 Created triggers OK.
2016-04-28T23:18:04 Copying approximately 1 rows...
2016-04-28T23:18:04 Copied rows OK.
2016-04-28T23:18:04 Swapping tables...
2016-04-28T23:18:04 Swapped original and new tables OK.
2016-04-28T23:18:04 Dropping old table...
2016-04-28T23:18:04 Dropped old table `testdb1`.`_tb001_old` OK.
2016-04-28T23:18:04 Dropping triggers...
2016-04-28T23:18:04 Dropped triggers OK.
Successfully altered `testdb1`.`tb001`.

The above is the detailed content of Introduction and use of MySQL--pt-osc. For more information, please follow other related articles on the PHP Chinese website!