Home Database Mysql Tutorial Introduction and use of MySQL--pt-osc

Introduction and use of MySQL--pt-osc

Jul 27, 2017 am 09:21 AM
study tool

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)
Copy after login

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=&#39;xxx.xxx.xxx.xxx&#39; \--recursion-method="hosts" \--check-interval=2 \--database="testdb1" \t="tb001" \--alter="add column c4 int" \--execute
Copy after login

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`.
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to send a POST request containing JSON data using PHP's cURL library? How to send a POST request containing JSON data using PHP's cURL library? Apr 01, 2025 pm 03:12 PM

Sending JSON data using PHP's cURL library In PHP development, it is often necessary to interact with external APIs. One of the common ways is to use cURL library to send POST�...

How to efficiently integrate Node.js or Python services under LAMP architecture? How to efficiently integrate Node.js or Python services under LAMP architecture? Apr 01, 2025 pm 02:48 PM

Many website developers face the problem of integrating Node.js or Python services under the LAMP architecture: the existing LAMP (Linux Apache MySQL PHP) architecture website needs...

How to configure apscheduler timing task as a service on macOS? How to configure apscheduler timing task as a service on macOS? Apr 01, 2025 pm 06:09 PM

Configure the apscheduler timing task as a service on macOS platform, if you want to configure the apscheduler timing task as a service, similar to ngin...

In LangChain, how do I use AgentExecutor to replace the disabled initialize_agent function? In LangChain, how do I use AgentExecutor to replace the disabled initialize_agent function? Apr 01, 2025 pm 04:18 PM

How to replace the disabled initialize_agent function in LangChain? In the LangChain library, initialize_agent...

Can Python parameter annotations use strings? Can Python parameter annotations use strings? Apr 01, 2025 pm 08:39 PM

Alternative usage of Python parameter annotations In Python programming, parameter annotations are a very useful function that can help developers better understand and use functions...

Can the Python interpreter be deleted in Linux system? Can the Python interpreter be deleted in Linux system? Apr 02, 2025 am 07:00 AM

Regarding the problem of removing the Python interpreter that comes with Linux systems, many Linux distributions will preinstall the Python interpreter when installed, and it does not use the package manager...

How to ensure high availability of MongoDB on Debian How to ensure high availability of MongoDB on Debian Apr 02, 2025 am 07:21 AM

This article describes how to build a highly available MongoDB database on a Debian system. We will explore multiple ways to ensure data security and services continue to operate. Key strategy: ReplicaSet: ReplicaSet: Use replicasets to achieve data redundancy and automatic failover. When a master node fails, the replica set will automatically elect a new master node to ensure the continuous availability of the service. Data backup and recovery: Regularly use the mongodump command to backup the database and formulate effective recovery strategies to deal with the risk of data loss. Monitoring and Alarms: Deploy monitoring tools (such as Prometheus, Grafana) to monitor the running status of MongoDB in real time, and

PostgreSQL monitoring method under Debian PostgreSQL monitoring method under Debian Apr 02, 2025 am 07:27 AM

This article introduces a variety of methods and tools to monitor PostgreSQL databases under the Debian system, helping you to fully grasp database performance monitoring. 1. Use PostgreSQL to build-in monitoring view PostgreSQL itself provides multiple views for monitoring database activities: pg_stat_activity: displays database activities in real time, including connections, queries, transactions and other information. pg_stat_replication: Monitors replication status, especially suitable for stream replication clusters. pg_stat_database: Provides database statistics, such as database size, transaction commit/rollback times and other key indicators. 2. Use log analysis tool pgBadg

See all articles