Abstract: There is a mature tool in the industry. For large table scenarios, Alter changes can be made online without the risk of locking the table. In addition, it has some other advantages, let’s begin to explore.

background

In our daily work, we often need to change the table structure of the database, which generally involves adding and deleting fields, modifying field attributes and other ALTER operations. However, in the scenario of large tables, especially large tables of tens of millions or hundreds of millions, if handled improperly. In the production environment, if a table is locked for a long time in the process of changing the table structure, the data generated by users cannot be changed to the table for a long time. As a result, the service function is abnormal and the result is disastrous.

To perform this type of Alter change, we might have the following thoughts:

1, stop service, do table structure changes during the stop service, naturally can prevent the impact on users. However, there are many scenarios where the server is not allowed to stop. And if the amount of data in the table reaches hundreds of millions, it may take more than ten hours or even longer to stop the service, which is extremely unrealistic.

2. Perform early in the morning and make changes in the time period when there are few users to minimize the impact on users. However, if there is a lock table, the service will not be available in case a user uses the service.

3. Use a new table, but the disadvantage is that when data is copied to a new table, if the user performs update or DELETE operations during this period, and the data occurs in the replicated part, the part of the data will not be perceived, resulting in the loss of the user’s operation data, which is too risky;

4. The disadvantage of using stored procedures is that they take a long time to execute and may affect the user’s DDL operations. Because to prevent too many rows from being locked in each iteration, we need to control the number of rows in each update. The granularity should not be too large, otherwise the rows that the user is working on May be locked.

Isn’t there a good tool to solve our pain points for the above practical needs? In fact, in the industry, there is a relatively mature tool, for large table scenarios, can make Alter changes online, without the risk of locking the table. In addition, it has some other advantages, let’s begin to explore.

What is PT-OSC

Pt-online-schema-change is a member of Percona-Toolkit. It can modify table structure online without locking tables by improving native DDL. Percona’s pT-OSC tool specifically mentions the fact that ALTER tables do not lock.

How does PS-OSC solve the problem of avoiding locking tables and perceiving user updates and deletes?

The operations of PT-OSC are as follows:

Create a new table that looks exactly like the original table and name it ‘_ official table name _new’;

2. Use THE ALTER statement to change the contents of the newly created table, avoiding the alter operation on the original table;

3, create three triggers in the original table, insert, update and delete, is mainly used for the original table in the new table copying data, if the user has a DDL operation, can trigger will appear during the DDL operation data is written to the new table, ensure that the new table of data is the latest, don’t lost the user’s new operating data;

4. Copy data to new table by block, copy process holds S lock on data row;

5. Rename the original table to the old table and rename the new table to the official table. You can determine whether to delete the old table after the operation.

6. Delete 3 triggers.

Installation of PT-OSC

Installation procedure on Linux:

Download the installation package wget http://szxge1-sw.artifactory.cd-cloud-artifact.tools.huawei.com/artifactory/CommonComponent/common/tool/percona-toolkit- Gz -- install perl-extutils-cBuilder yum install perl-extutils-cBuilder -- install perl-extutils-cBuilder Perl -extutils -makemaker yum -y install perl-digest-md5 CD percona-toolkit-3.1.0 perl makefile. PL make make install Yum install perl-dbd-mysql yum install perl-dbd-mysqlCopy the code

Three, the use of PT-OSC

The pt-osC tool is easy to use. You can directly run commands in pt-osC format on the Linux command line.

Mysql > alter database add MARK (MARK);

Pt-online-schema-change --user="root" --password="*****" --host=" database IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;Copy the code

In the above statement:

1. User and password are the user name and password used to perform database change operations respectively, requiring high permissions.

2. Host is the IP address of the database.

3. Port is the database port number.

4, alter after the specific alter statement;

5, D is the name of database;

6. T is the name of the table to be changed.

7, no-drop-old-table means do not delete

8, charset, character set, utf8;

9. Max-load, when copying data, the tool monitors the number of running threads in the database and suspends replication until it is smaller than the configured Threads_running value. This prevents heavy pressure on the database and affects the normal use of live network services.

Critical-load, which defaults to 50, checks SHOW GLOBAL STATUS after each block. Unlike max-load, if the load is too high, abort directly, not pause. You can configure thresholds based on the database situation.

** Note: ** column names cannot be added to ‘alter statements following –alter, otherwise an error will occur. –alter “ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT ‘MARK source region is 1’; If ‘is added to the MARK field, error will occur. If’ is added to the COMMENT field, error will occur.

Here is the printout of the actual execution of a job using the PT-OSC tool. For security, some log information is hidden and ignored.

[root@ttt ~]# 'pt-online-schema-change --user="root" --password="*****" --host=" database IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none; ` No slaves found. See --recursion-method if host EulerOS-BaseTemplate has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: Analyze_table, 10, 1 COPY_rows triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `my_test`.`t_test`... Creating new table... CREATE TABLE 'my_test'. '_t_test_new' (' ID' int(11) NOT NULL AUTO_INCREMENT COMMENT 'increment ID',............. Construct sentence data................ Created new table my_test._t_test_new OK. Altering new table... ALTER TABLE `my_test`.`_t_test_new` ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1'; Altered `my_test`.`_t_test_new` OK. 2020-10-14T11:14:48 Creating triggers... 2020-10-14T11:14:48 Created triggers OK. 2020-10-14T11:14:48 Copying approximately 346697 rows... INSERT LOW_PRIORITY IGNORE INTO `my_test`.`_t_test_new` (`id`, .. Construction sentence information.... FROM `my_test`.`_t_test_new` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?) ) AND ((`id` <= ?) ) LOCK IN SHARE MODE /*pt-online-schema-change 31340 copy nibble*/ SELECT /*! 40001 SQL_NO_CACHE */ `id` FROM `my_test`.`t_test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?) ) ORDER BY `id` LIMIT ? , 2 /*next chunk boundary*/ 2020-10-14T11:14:53 Copied rows OK. 2020-10-14T11:14:53 Analyzing new table... 2020-10-14T11:14:53 Swapping tables... RENAME TABLE `my_test`.`t_test` TO `my_test`.`_t_test_old`, `my_test`.`_t_test_new` TO `my_test`.`t_test` 2020-10-14T11:14:53 Swapped original and new tables OK. Not dropping old table because --no-drop-old-table was specified. 2020-10-14T11:14:53 Dropping triggers... DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_del` DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_upd` DROP TRIGGER IF EXISTS `my_test`.`pt_osc_my_test_t_test_ins` 2020-10-14T11:14:54 Dropped triggers OK. Successfully altered `my_test`.`t_test`.Copy the code

Fourth, performance comparison

Many of the advantages of PT-OSC, as well as its nice features, were described earlier. So how does it actually work? In the test environment, a special test was done, so that we have a more intuitive feeling.

In the test library, a large table with 16 million data is prepared. The goal is to add a field to the large table. The stored procedure and PT-OSC tool are respectively used for testing.

4.1 Using stored Procedures

The first test was done using stored procedures that update only 200 rows at a time to prevent locking of the table. The entire change took 90 minutes from start to finish. In fact, during the execution of the stored procedure, if the user is also working on the data row being changed in the DDL, the user’s data may be locked, preventing the user from changing the data successfully.

4.2 Using the PT-OSC Tool

Pt-osc takes about 7 minutes from the start to the completion of the change, which is very fast. During the execution, the services in the test environment connect to the database and perform multiple tasks that manipulate the table. During the whole process, the tasks can be executed normally without exceptions.

Five, the conclusion

The above advantages of PS-OSC can help us gracefully implement changes under the requirements of the live network environment, and ensure that during the change, the database will not be affected by locking tables, overload, etc., thus ensuring the normal operation of the business.

This article is shared by Huawei cloud community “Ten million, ten million large table Online Lock table Change field and index”, original article author: Active_ZHAO.

Click to follow, the first time to learn about Huawei cloud fresh technology ~