Mysql--pt-osc Tool Learning

##====================================== ===============##
pt-osc Workflow:
1, check if the change table has a primary key or a unique index, whether there is a trigger
Span style= "FONT-SIZE:14PX; Font-family: ' Courier New ', courier; >2, check the table structure of the modified table, create a temporary table, 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 staging table, during the copy process, The update operation to the source table is written to the new table in
5, The temporary table and the source table rename (requires metadata to modify the lock, need a short time lock table)
6, delete the source table and trigger, complete the table structure modification.

##====================================== ===============##
pt-osc tools Limit
2, if the on-line replication environment filter operation is too complex, the tool will not work
3, If the replication delay check is turned on, but the master-slave delay, the tool pauses the data copy work
4, If the primary server load check is turned on, but the primary server load is high, the tool pauses the operation

##=====================================================##
Copy data of Pt-osc
In the process of copying data, the tool splits the data by primary key or unique key, limiting the number of rows per copy of the data to ensure that the copy does not consume too much server resources. To ensure that the source and target table data are the same, the lock in SHARE mode is used to obtain the most recent data to copy the data segment and to organize additional reply modification data for the data sharing lock, using low_priority Ignore to insert the data into the new table, the keyword Low_ Priorit causes the insert operation to wait for other operations that access the table to complete before executing, and the keyword Ingore causes the primary key or unique index key in the table to repeat when the newly data is ignored and not inserted.

Data copy script for modification of table ' testdb1 '. ' tb1001 ':

# # Gets the next copy of the data boundary, forcing the index to effectively avoid problems with the execution plan
SELECT/*!40001 Sql_no_cache */' id ' from ' testdb1 '. ' tb1001 ' Force INDEX (' PRIMARY ') WHERE (' id ' >= ' 8394306 ') ' ORDER B Y ' id ' LIMIT 22256, 2/*next Chunk boundary*/

# # To prevent a single copy of too much data and block other sessions for a long time by copying the boundary limits of the data
INSERT low_priority IGNORE into ' testdb1 '. ' _tb1001_new ' (' IDs ', ' 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's Trigger

The Pt-osc tool creates three after triggers on the source table for the Insert UPDATE delete operation, and the delete trigger uses the delete ignore to ensure that the data for the source and new tables are deleted. Insert and update triggers use replace into to ensure that the new table data is consistent with the source table data.

Because MySQL restricts only one trigger of the same type, it requires a primary key or a unique index on the table to check for triggers on the source table before running, to ensure efficiency and ease of deletion and update, and to shard the source table data.

##=====================================================##
Host performance impact of Pt-osc

To avoid excessive impact on host performance, the Pt-osc tool is limited by the following:
1. Control each copy data size by parameter Chunk-size and Chunk-time
2, through the parameter max-load to check the current pressure of the host, each chunk copy completed, will run the show GLOBAL STATUS like ' threads_running ' command check the number of currently running Threads, the default Threads_ RUNNING=25, if the maximum value is not specified, 120% of the current value is taken as the maximum value and the data copy is paused if the threshold is exceeded

##=====================================================##
Pt-osc Copy delay from library

For businesses that are more sensitive to replication latency, you can control replication latency by using the following parameters:

--max-log
The default is 1s, and after each chunks copy is completed, the delay information from the library specified by the Check-slave-lag parameter is reviewed, and if the threshold value of Max-log is exceeded, the replication data is paused until the replication delay is less than the max-log threshold. Check that replication latency information relies on the value of the Seconds_behind_master column returned in the show SLAVE status statement.

--check-interval
When replication latency pauses replication data, the replication delay is checked at the time specified by Check-interval until the delay time is below the max-log threshold and then the data copy is resumed

--check-slave-lag
Need to check replication latency from the library IP
If the Check-slave-lag parameter is specified and is not connected from the library properly or stopped from the library IO thread and the SQL thread, the master-slave delay is considered to cause the replication data operation to remain paused.
If the Check-slave-lag parameter is not specified, the delay from the library is checked by default, but replication latency does not cause data replication to pause.

##=====================================================##
Pt-osc's chunk settings
In Pt-osc's help documentation, 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 all data-copy query takes this long to execute (default 0.5)

When both Chunk-size and Chunk-time are not specified, the Chunk-size default value is 1000, The Chunk-time default value is 0.5S, the first time the data is copied according to Chunk-size, and then dynamically adjusts the size of the chumk-size based on the time of the first replication to accommodate the server performance changes, such as the last replication of 1000 rows consumes 0.1S, the next dynamic adjustment chumk-size 5 000.
If you explicitly specify the value of Chumk-size or specify Chunk-time as 0, the data is copied each time according to Chunk-size.

##=====================================================##
Pt-osc The ALTER statement limit
1, do not need to include the ALTER TABLE keyword, can contain multiple modification operations, separated by commas, such as "drop Clolumn c1, add column C2 int"
2. Rename statements are not supported to rename a table
3. Renaming an index is not supported
4, if delete foreign key, need to underline the foreign key name, such as delete foreign key fk_uid, modify the statement as "DROP FOREIGN key _fk_uid"
##=====================================================##
Pt-osc's command template
# #--execute indicates execution
# #--dry-run means only simulation tests are performed
# # Table name can only be set with parameter T, no long parameter
pt-online-schema-change \
--host= "127.0.0.1" \
--port=3358 \
--user= "root" \
--password= "[email protected]" \
--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

##=====================================================##
command output of Pt-osc
The above command executes the output as follows:
No slaves found. See--recursion-method If host 171db166 have slaves.
Would check slave lag on:
170db166
operation, tries, wait:
Copy_rows, ten, 0.25
Create_triggers, ten, 1
Drop_triggers, ten, 1
Swap_tables, ten, 1
Update_foreign_keys, ten, 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 '.

##=====================================================##

Mysql--pt-osc Tool Learning