Percona-toolkit Pt-online-schema-change (change the table structure online)

Traditional method to modify table structure similar to ALTER TABLE XX Modify, you need to lock the table structure, if the table is large, the operation time will be longer. At present, the vast majority of business requirements 24*7 Non-stop Service, and this process, if a longer time the database can not be updated, it will seriously affect the business. Two possible options have been learned recently: 1. Replace table structure method, 2. Using Percona on-line Modification table Structure tool
Simulation Scenario Description: mysql5.6.25 (mysql5.6 relative to the mysql5.5 has improved a lot, in mysql5.6 reference Percona-toolkit, add Delete index will not lock the table, the same session, additions and deletions are not affected, but different sessions will be affected. ）

Modifying the table structure online will certainly have an impact on real-time tasks, and as a DBA, the best thing to do is to minimize the impact. Let's do a simple test.

Table Test1, the structure is as follows: Main three fields (primary key Id,name,age)

CREATE TABLE ' test1 ' (

' id ' int (one) not NULL auto_increment,
' Name ' varchar (a) DEFAULT NULL,
' Age ' tinyint (4) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=3 DEFAULT charset=latin1

Insert 1 million data in a table

Mysqlslap-uroot--number-of-queries=1000000-c--query= "insert into Test.test1 (name,age) VALUES (' FDS ', 21)" Create-schema= ' Test '

Experiment started

Mysql> Select COUNT (*) from test1;
+----------+
| COUNT (*) |
+----------+
| 1000002 |
+----------+ (previously inserted two data of ha) 1. Replace table structure method

Give a simple example of modifying table attributes

Steps:

① Create a similar TMP table, modify the corresponding column properties

② Modify table structure must lock table, execute flush table with Readlock

③ overwrites the original table structure file with the temporary table structure file, and then releases the table, Unlocktables

④ Insert found There is still a problem, that is because the table was not refreshed, flush tables

⑤ Display Modified table structure

⑥ Insert Data no exception

Note: Must be InnoDB table, MyISAM not Oh, the operation of the varchar type is effective, for other types is not necessarily oh. For example: decimal

The advantage is that you need to lock the table only when the Copy table structure 2. Adopt Percona tool to deal with

Execute a statement that modifies a table

Mysql> ALTER TABLE test1 add column nickname varchar (a) default ';
Query OK, 0 rows affected (12.33 sec)
records:0 duplicates:0 warnings:0

Can see the operation time is still a little long, after all, I only have 1 million data, and the table structure is simple. For the Percona tool, where the advantages, you can first look at the operation of the process as shown in the following figure:

As you can see, there is a temporary table created during its operation, and this processing time is not better than before, but see below:

Pt-online-schema-change alters a table ' s structure without blocking reads or writes. Specify the database and table in the DSN. Do is tool before reading its documentation and checking your backups.

and give the results of the experiment:

Experiment Steps

1. Modify table properties in two ways: direct modification and modification with Percona tools

2. While modifying the table properties, open another session to insert data into the table

The results are as follows:

Direct modification

Session1:

Mysql> ALTER TABLE test1 Modify column nickname varchar (15);
Query OK, 1000002 rows Affected (5.98 sec)
records:1000002 duplicates:0 warnings:0

Session2:

mysql> INSERT INTO test1 (name,age,nickname) VALUES (' FDs ', ' FDS ');

Query OK, 1 row affected (4.47 sec)

Tool Modifications:

Session1:

vagrant@testda:~$ pt-online-schema-change--alter= "modify column nickname varchar () default" "--user=root d=test,t= Test1--execute
Cannot connect to D=test,h=testdp,u=root
No slaves found. --recursion-method If host TESTDA has slaves.
Not checking slave lag because no slaves were found and--check-slave-lag is not specified.
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 ' test '. ' Test1 ' ...
Creating new Table ...
Created new Table Test._test1_new OK.
Altering new Table ...
Altered ' test '. ' _test1_new ' OK.
2015-10-15T02:46:50 Creating triggers ...
2015-10-15T02:46:50 Created triggers OK.
2015-10-15T02:46:50 copying approximately 998585 rows ...
2015-10-15t02:47:04 Copied rows OK.
2015-10-15T02:47:04 Swapping tables ...
2015-10-15T02:47:04 swapped original and new tables OK.
2015-10-15t02:47:04 dropping old table ...
2015-10-15T02:47:04 dropped old table ' test '. ' _test1_old ' OK.
2015-10-15T02:47:04 dropping triggers ...
2015-10-15T02:47:04 dropped triggers OK.
Successfully altered ' test '. ' Test1 '.

Session2:

Insert into test1 (name,age,nickname) VALUES (' FDs ', ', ' FDS ');
Query OK, 1 row affected, 1 warning (0.32 sec)

you can see that the insert operation encountered blocking when the data insertion was not blocked during the tool modification process, and the direct modification was due to the lock table.

Specific can refer to under: https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

Note: From the above description, the tool is good, but it's not without flaws, ①. If the modified table does not have a primary key, it will cause an error ② the operation creates a temporary table, and if there is a master-slave replication, and the master-slave replication table is set, then synchronization from the library fails because the temporary table is not synchronized ③ But not to default value, will also error ④ tool is not the official MySQL tool, so the operation of the proposed backup, to avoid accidents