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`
##================== ====================================
#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!

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL is an open source relational database management system suitable for data storage, management, query and security. 1. It supports a variety of operating systems and is widely used in Web applications and other fields. 2. Through the client-server architecture and different storage engines, MySQL processes data efficiently. 3. Basic usage includes creating databases and tables, inserting, querying and updating data. 4. Advanced usage involves complex queries and stored procedures. 5. Common errors can be debugged through the EXPLAIN statement. 6. Performance optimization includes the rational use of indexes and optimized query statements.

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

InnoDB's lock mechanisms include shared locks, exclusive locks, intention locks, record locks, gap locks and next key locks. 1. Shared lock allows transactions to read data without preventing other transactions from reading. 2. Exclusive lock prevents other transactions from reading and modifying data. 3. Intention lock optimizes lock efficiency. 4. Record lock lock index record. 5. Gap lock locks index recording gap. 6. The next key lock is a combination of record lock and gap lock to ensure data consistency.

The main reasons for poor MySQL query performance include not using indexes, wrong execution plan selection by the query optimizer, unreasonable table design, excessive data volume and lock competition. 1. No index causes slow querying, and adding indexes can significantly improve performance. 2. Use the EXPLAIN command to analyze the query plan and find out the optimizer error. 3. Reconstructing the table structure and optimizing JOIN conditions can improve table design problems. 4. When the data volume is large, partitioning and table division strategies are adopted. 5. In a high concurrency environment, optimizing transactions and locking strategies can reduce lock competition.

In database optimization, indexing strategies should be selected according to query requirements: 1. When the query involves multiple columns and the order of conditions is fixed, use composite indexes; 2. When the query involves multiple columns but the order of conditions is not fixed, use multiple single-column indexes. Composite indexes are suitable for optimizing multi-column queries, while single-column indexes are suitable for single-column queries.

To optimize MySQL slow query, slowquerylog and performance_schema need to be used: 1. Enable slowquerylog and set thresholds to record slow query; 2. Use performance_schema to analyze query execution details, find out performance bottlenecks and optimize.

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 Chinese version
Chinese version, very easy to use

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function