MySQL -- pt-osc tool learning, mysql -- pt-osc Tool

MySQL -- pt-osc tool learning, mysql -- pt-osc Tool

##=================================================== ====================== ##
Pt-osc workflow:
1. Check whether the table has a primary key or a unique index and whether a trigger exists.
2. Check the TABLE structure of the modified TABLE, 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 respectively.
4. Copy data from the source table to the temporary table. During the copy process, update operations on the source table will be written to the new table.
5. rename the temporary table and source table (metadata modification locks are required and tables need to be locked for a short time)
6. Delete the source table and trigger to modify the table structure.

##=================================================== ====================== ##
Pt-osc tool restrictions
1. The source table must have a primary key or a unique index. If no tool is available, it will stop working.
2. If the online replication environment filters are too complicated, the tool will not work.
3. If the replication delay check is enabled but the master/Slave delay is enabled, the tool will suspend data copying.
4. If the master server load check is enabled but the master server load is high, the tool will suspend the operation.
5. If the table uses a foreign key and the -- alter-foreign-keys-method parameter is not used, the tool cannot execute
6. Only Innodb Storage engine tables are supported, and the server must have a free space of more than doubled.

##=================================================== ====================== ##
Copy data from pt-osc
During data copying, the tool splits the data according to the primary key or unique key, and limits the number of rows for each copy to ensure that the copy operation does not consume more server resources. To ensure that the data IN the source and target tables are the same, use lock in share mode to obtain the latest data IN the data segment to be copied and add a shared LOCK to the data to organize other calls to modify the data, LOW_PRIORITY IGNORE is used to insert data into a new table. The keyword LOW_PRIORIT enables the insert operation to wait for other operations to access the table to complete and then execute, the keyword INGORE indicates that new data is ignored and not inserted when the primary key or unique index key is repeated in the table.

The data copy script when the table 'testdb1'. 'tb1001' is modified:

# Obtain the boundaries of the next copy of data first, and force the index can effectively avoid problems in the execution plan
SELECT /*! 40001 SQL _NO_CACHE */'id' FROM 'testdb1 '. 'tb1001' force index ('Primary ') WHERE ('id'> = '000000') order by 'id' LIMIT 8394306, 2/* next chunk boundary */

# Avoid blocking other calls by copying too much data at a time through the limitations of copying data
INSERT LOW_PRIORITY ignore into 'testdb1 '. '_ tb1001_new' ('id', 'c1 ', 'c6') SELECT 'id', 'c1 ', 'c6' FROM 'testdb1 '. 'tb1001' force index ('Primary ') WHERE ('id'> = '000000') AND ('id' <= '000000 ')) lock in share mode/* pt-online-schema-change 14648 copy nibble */

##=================================================== ====================== ##
Pt-osc trigger

The pt-osc tool creates three AFTER triggers for the insert update delete operation on the source table. The DELETE trigger uses delete ignore to ensure that the data in the source table and the new table is deleted, the INSERT and UPDATE triggers use replace into to ensure that the new table data is consistent with the source table data.

Because MySQL only supports one trigger of the same type, you need to check whether there is a trigger in the source table before running. To ensure the efficiency and convenience of deleting and updating the source table data, and to partition the source table data, therefore, a table must have a primary key or a unique index.

##=================================================== ====================== ##
Pt-osc impact on host Performance

To avoid excessive impact on host performance, the pt-osc tool has the following restrictions:
1. The chunk-size and chunk-time parameters are used to control the data size of each copy.
2. Check the current pressure on the host through the max-load parameter. After each chunk copy is completed, the show global status like 'threads _ running' command is run to check the number of currently running Threads, the default value is Threads_running = 25. If the maximum value is not specified, 120% of the current value is taken as the maximum value. If the threshold value is exceeded, data copying is paused.

##=================================================== ====================== ##
Slave Database Replication latency of pt-osc

For services with high replication latency, you can use the following parameters to control replication latency:

-- Max-log
The default value is 1 s. After each chunks is copied, the latency information of the slave database specified by the check-slave-lag parameter is displayed. If the latency exceeds the threshold value of max-log, the replication data is paused until the replication delay is less than the threshold value of max-log. Check the replication delay information depends on the value of the Seconds_Behind_Master column returned by the show slave status statement.

-- Check-interval
After a replication delay occurs, check the replication delay periodically according to the time specified by check-interval until the delay time is lower than the max-log threshold value, and then resume data copying.

-- Check-slave-lag
Slave database IP address that needs to check replication latency
If the check-slave-lag parameter is specified and the slave database cannot be connected normally or the slave database IO thread and SQL thread are stopped, the Master/slave database has a delay, causing data replication to be suspended.
If the check-slave-lag parameter is not specified, the slave database delay will be checked by default, but the replication delay will not cause data replication to be suspended.

##=================================================== ====================== ##
Chunk settings for pt-osc
In the pt-osc help document, the chunk parameters are as follows:
-- Chunk-index = s Prefer this index for chunking tables

-- Chunk-index-columns = I Use only this operation 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, the default value of chunk-size is 1000, and the default value of chunk-time is 0.5 S. Data is replicated Based on chunk-size for the first time, then, the chumk-size is dynamically adjusted based on the time of the first replication to adapt to the performance changes of the server. If the previous copy consumes 1000 S, the chumk-size will be adjusted to 0.1 next time.
If the value of chumk-size is explicitly specified or the value of chunk-time is set to 0, data is copied Based on chunk-size every time.

##=================================================== ====================== ##
Pt-osc alter statement restrictions
1. You do not need to include the alter table keyword. You can include multiple modification operations separated by commas (,), such as "drop clolumn c1, add column c2 int"
2. rename statements are not supported for table renaming.
3. indexes cannot be renamed.
4. If a foreign key is deleted, the foreign key name must be underlined. For example, to delete the foreign key fk_uid, modify the statement to "drop foreign key _ fk_uid"
##=================================================== ====================== ##
Pt-osc command Template
# -- Execute indicates execution
# -- Dry-run indicates that only simulated tests are performed.
# The table name can only be set using parameter t without long parameters
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 command output
The preceding command output is as follows:
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 '.

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