Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the shortcomings of pt-osc using incremental data triggers, I believe most people do not know much about it, so share this article for your reference, I hope you will gain a lot after reading this article, let's go to know it!

The principle of pt-osc is as follows:

1. Create a new table with the same table structure as the source table

2. Execute the DDL statement in the new table

3. Create three triggers in the source table corresponding to insert, update and delete operations.

4. Copy the data from the source table to the new table. During the copying process, the source table updates the new DML operation to the new table through triggers.

5. Rename the source table to the old table, rename the new table as the source table, and finally delete the source table

The function of a trigger is to synchronize incremental data between the source table and the new table. The shortcomings are summarized as follows:

Because triggers are implemented with the steps of a stored procedure, the overhead required by the stored procedure itself cannot be avoided.

Increased the execution steps of the same transaction, more lock contention.

The whole process cannot be paused, and if you find that it affects the performance of the main library and stops Online DDL, you will need to start all over again next time.

Multiple parallel operations are not safe.

Cannot be tested in a production environment.

The trigger and the source operation are still in the same transaction space.

Remarks:

It is recommended to use gh-ost tool to do online DDL.

Gh-ost first connects to the main database, creates shadow tables according to alter statements, and then connects to one of the real standby libraries as a "standby library". While copying existing data to the shadow table on the main database, it pulls the binlog of incremental data from the standby database, and then constantly applies binlog back to the main library.

These are all the contents of this article entitled "what are the shortcomings of pt-osc 's use of incremental data triggers?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.