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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

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)

What are the digital currency trading apps suitable for beginners? Learn about the coin circle in one article What are the digital currency trading apps suitable for beginners? Learn about the coin circle in one article Apr 22, 2025 am 08:45 AM

When choosing a digital currency trading platform suitable for beginners, you need to consider security, ease of use, educational resources and cost transparency: 1. Priority is given to platforms that provide cold storage, two-factor verification and asset insurance; 2. Apps with a simple interface and clear operation are more suitable for beginners; 3. The platform should provide learning tools such as tutorials and market analysis; 4. Pay attention to hidden costs such as transaction fees and cash withdrawal fees.

What are the digital currency trading platforms in 2025? The latest rankings of the top ten digital currency apps What are the digital currency trading platforms in 2025? The latest rankings of the top ten digital currency apps Apr 22, 2025 pm 03:09 PM

Recommended apps for the top ten virtual currency viewing platforms: 1. OKX, 2. Binance, 3. Gate.io, 4. Huobi, 5. Coinbase, 6. Kraken, 7. Bitfinex, 8. KuCoin, 9. Bybit, 10. Bitstamp, these platforms provide real-time market trends, technical analysis tools and user-friendly interfaces to help investors make effective market analysis and trading decisions.

What are the free market viewing software websites? Ranking of the top ten free market viewing software in the currency circle What are the free market viewing software websites? Ranking of the top ten free market viewing software in the currency circle Apr 22, 2025 am 10:57 AM

The top three top ten free market viewing software in the currency circle are OKX, Binance and gate.io. 1. OKX provides a simple interface and real-time data, supporting a variety of charts and market analysis. 2. Binance has powerful functions, accurate data, and is suitable for all kinds of traders. 3. gate.io is known for its stability and comprehensiveness, and is suitable for long-term and short-term investors.

Meme Coin Exchange Ranking Meme Coin Main Exchange Top 10 Spots Meme Coin Exchange Ranking Meme Coin Main Exchange Top 10 Spots Apr 22, 2025 am 09:57 AM

The most suitable platforms for trading Meme coins include: 1. Binance, the world's largest, with high liquidity and low handling fees; 2. OkX, an efficient trading engine, supporting a variety of Meme coins; 3. XBIT, decentralized, supporting cross-chain trading; 4. Redim (Solana DEX), low cost, combined with Serum order book; 5. PancakeSwap (BSC DEX), low transaction fees and fast speed; 6. Orca (Solana DEX), user experience optimization; 7. Coinbase, high security, suitable for beginners; 8. Huobi, well-known in Asia, rich trading pairs; 9. DEXRabbit, intelligent

Reliable and easy-to-use virtual currency exchange app recommendations The latest ranking of the top ten exchanges in the currency circle Reliable and easy-to-use virtual currency exchange app recommendations The latest ranking of the top ten exchanges in the currency circle Apr 22, 2025 pm 01:21 PM

The reliable and easy-to-use virtual currency exchange apps are: 1. Binance, 2. OKX, 3. Gate.io, 4. Coinbase, 5. Kraken, 6. Huobi Global, 7. Bitfinex, 8. KuCoin, 9. Bittrex, 10. Poloniex. These platforms were selected as the best for their transaction volume, user experience and security, and all offer registration, verification, deposit, withdrawal and transaction operations.

A list of special services for major virtual currency trading platforms A list of special services for major virtual currency trading platforms Apr 22, 2025 am 08:09 AM

Institutional investors should choose compliant platforms such as Coinbase Pro and Genesis Trading, focusing on cold storage ratios and audit transparency; retail investors should choose large platforms such as Binance and Huobi, focusing on user experience and security; users in compliance-sensitive areas can conduct fiat currency trading through Circle Trade and Huobi Global, and mainland Chinese users need to go through compliant over-the-counter channels.

The top ten free platform recommendations for real-time data on currency circle markets are released The top ten free platform recommendations for real-time data on currency circle markets are released Apr 22, 2025 am 08:12 AM

Cryptocurrency data platforms suitable for beginners include CoinMarketCap and non-small trumpet. 1. CoinMarketCap provides global real-time price, market value, and trading volume rankings for novice and basic analysis needs. 2. The non-small quotation provides a Chinese-friendly interface, suitable for Chinese users to quickly screen low-risk potential projects.

Top 10 digital currency exchange app recommendations, top ten virtual currency exchanges in the currency circle Top 10 digital currency exchange app recommendations, top ten virtual currency exchanges in the currency circle Apr 22, 2025 pm 03:03 PM

Recommended apps on top ten digital currency exchanges: 1. OKX, 2. Binance, 3. gate.io, 4. Huobi, 5. Coinbase, 6. KuCoin, 7. Kraken, 8. Bitfinex, 9. Bybit, 10. Bitstamp, these apps provide real-time market trends, technical analysis and price reminders to help users monitor market dynamics in real time and make informed investment decisions.

See all articles