Shulou(Shulou.com)06/01 Report--

The limitation of Online DDL is mentioned in "glance of InnoDB Online DDL". In the master-slave replication environment, if it takes a long time to change the ALTER TABLE of a large table on the master database, the process will generally take a long time to be played back from the slave database, during which the DML operation of the data table on the master database will not be reflected in the slave database in time, so the availability of the slave database will be affected.

The reason can be attributed to the replication delay caused by the failure of a big thing to be executed in time (in fact, ALTER TABLE is not atomic in MySQL 5.6,5.7, just for illustration purposes). For the optimization of big things, we first try to decompose it into several small things. The protagonist pt-online-schema-change tool (hereinafter referred to as pt-osc) makes use of this idea, which can effectively control the problem of replication delay.

Pt-osc changes the data table without blocking reads and writes. First, it creates a new data table that meets the requirements, and then copies the data from the original data table to the new data table in blocks. During this period, the DML operation on the original data table will be reflected on the new data table through the triggers previously created on the original data table. Throughout the process, the tool will control replication latency and primary database load in a reasonable range in a variety of ways.

Take a look at the actual example used by pt-osc, and its log output also shows how it works.

Mysql@db01: ~ $pt-online-schema-change-- alter "ADD COLUMN org_id BIGINT UNSIGNED NOTNULL DEFAULT 0"-- nocheck-replication-filters-- recursion-method=processlist hobbies 192.168.19.168

Found 1 slaves:

Db02-> 192.168.19.190pur3316

Will check slave lagon:

Db02-> 192.168.19.190pur3316

Operation, tries,wait:

Analyze_table,10, 1

Copy_rows, 10,0.25

Create_triggers, 10, 1

Drop_triggers,10, 1

Swap_tables,10, 1

Update_foreign_keys, 10, 1

Altering`test`.`test _ zzzz`.

Creating new table...

Created new tabletest._test_zzzz_new OK.

Waiting forever fornew table `test`.` _ test_zzzz_ new` to replicate to db02...

Altering new table...

Alternate `test`.` _ test_zzzz_ new` OK.

2017-09-15T16:12:11Creating triggers...

2017-09-15T16:12:11Created triggers OK.

2017-09-15T16:12:11Copying approximately 4861821 rows...

Copy `test`.`test _ zzzz`: 6% 07:42 remain

...

Copy 'test`.`test _ zzzz`: 89% 00:41 remain

Copy 'test`.`test _ zzzz`: 97% 00:08 remain

2017-09-15T16:18:42Copied rows OK.

2017-09-15T16:18:42Analyzing new table...

2017-09-15T16:18:42Swapping tables...

2017-09-15T16:18:42Swapped original and new tables OK.

2017-09-15T16:18:42Dropping old table...

2017-09-15T16:18:42Dropped old table `test`.` _ test_zzzz_ old` OK.

2017-09-15T16:18:42Dropping triggers...

2017-09-15T16:18:42Dropped triggers OK.

Successfully altered`test`.`test _ zzzz`.

Through the general log generated in the process of changing the data sheet, we can understand the details of the operation behind the pt-osc, which can also confirm the main working principle mentioned above.

Step1, set various timeouts to avoid lock waiting and other situations, you can exit as soon as possible, without affecting other operations.

57049 Query SHOW VARIABLES LIKE 'innodb\ _ lock_wait_timeout'

57049 Query SET SESSION innodb_lock_wait_timeout=1

57049 Query SHOW VARIABLES LIKE 'lock\ _ wait_timeout'

57049 Query SET SESSION lock_wait_timeout=60

57049 Query SHOW VARIABLES LIKE 'wait\ _ timeout'

57049 Query SET SESSION wait_timeout=10000

Step2, create a new table that meets the requirements of the change.

57049 Query CREATE TABLE `test`.` _ test_zzzz_ new` (

`id` bigint (20) unsigned NOT NULL AUTO_INCREMENT

...

PRIMARY KEY (`id`)

...

) ENGINE=InnoDBAUTO_INCREMENT=5342221 DEFAULT CHARSET=utf8mb4

57049 Query ALTER TABLE `test`.` _ test_zzzz_ new` ADD COLUMN org_id BIGINT UNSIGNED NOT NULL DEFAULT 0

Step3, create a trigger to reflect the DML operation on the original data table to the new data table.

57049 Query CREATE TRIGGER `pt_osc_test_test_zzzz_ del`After DELETE ON `test`.`test _ zzzz`FOR EACH ROW DELETE IGNORE FROM`test`.` _ test_zzzz_ new`WHERE `test`.` _ test_zzzz_ new`.`id`OLD.`id`

57049 Query CREATE TRIGGER `pt_osc_test_test_zzzz_ upd`After UPDATE ON `test`.`test _ zzzz` FOR EACH ROW BEGIN DELETE IGNORE FROM `test`.` _ test_zzzz_ new`where! (OLD.`id`NEW.`id`) AND `test`.` _ test_zzzz_ new`.`id`OLD.`id`; REPLACE INTO `test`.` _ test_zzzz_ new` (`id`,...) VALUES (NEW.`id`,...); END

57049 Query CREATE TRIGGER `pt_osc_test_test_zzzz_ ins`after INSERT ON `test`.`test _ zzzz` FOR EACH ROW REPLACE INTO`test`.` _ test_zzzz_ new` (`id`,...) VALUES (NEW.`id`,...)

Step4, which copies data in blocks, during which time it monitors latency and load.

57049 Query INSERT LOW_PRIORITY IGNORE INTO`test`.` _ test_zzzz_ new` (`id`,...) SELECT `id`,... FROM `test`.`test _ zzzz`FORCE INDEX (`PRIMARY`) WHERE ((`id` > = '147592')) AND ((`id` SHOW CREATE TABLE dsns\ G)

* * 1. Row *

Table: dsns

Create Table: CREATE TABLE `dsns` (

`id`int (11) NOT NULL AUTO_INCREMENT

`parent_ id`int (11) DEFAULT NULL

`dsn`varchar (255) NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDBAUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

1 row in set (0.00sec)

(root@localhost) [test] > SELECT * FROM dsns

+-- +

| | id | parent_id | dsn | |

+-- +

| | 1 | NULL | 192.168.19.190 |

+-- +

1 row in set (0.05sec)

Finally, the pt-osc command line looks like this:

Pt-online-schema-change-- alter "ADD COLUMN org_id BIGINT UNSIGNED NOT NULL DEFAULT 0"-- nocheck-replication-filters-- recursion-method=dsn=D=test,t=dsns hobbies 192.168.19.168

Limitations of pt-osc

1. A data table should have a primary key or a unique index, which is also the design specification of any InnoDB data table.

two。 In the case of foreign key constraints, it is more complicated to use pt-osc. In actual business, logical foreign key constraints are generally implemented in applications.

3. In version 5.6of MySQL, if there is already a trigger on the data table to be changed, pt-osc will not be used. This situation has been improved in version 5.7.

Compared with OnlineDDL, pt-osc executes slowly and requires more disk space, but it ensures the availability of slave libraries. It is generally recommended that when the amount of data in the datasheet is small, Online DDL; can be used. If the amount of data is large (greater than 5 million or 10 million), if you want to think that Online DDL will cause delay, you can consider pt-osc.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.