About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/MySQL Online DDL and pt-online-schema-change

MySQL Online DDL and pt-online-schema-change

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
 
 

overview

When adding specifications for a service or developing a new one, it may be necessary to perform DDL (Data Definition Language) operations such as adding columns or indexes to existing tables.
If you perform a normal DDL operation while the service is running, a table lock may occur, which could lead to a serious service failure.
By using the following method, you can prevent table locking and perform DDL operations online.
 

conclusion

💡
AWS AuroraIn this case - Use the official MySQL online DDL feature - If this is not possible for the reasons described below, the next best option is pt-online-schema-change RDS(MySQL) ,orPure MySQL (5.5~5.7)In this case, use pt-online-schema-change.
 

method

Use official MySQL features (MySQL 5.6 and later)

The online DDL feature implemented in MySQL 5.6 allows some operations to be performed without locking.
To summarize, the following operations can be performed online:
  • OPTIMIZE TABLE
  • ADD INDEX
  • CREATE INDEX
  • DROP INDEX
  • Adding a Column
  • Deleting columns (beware of high costs)
  • Adding and deleting foreign key constraints
 
In addition, MySQL 8.0.12 added a feature called INSTANT DDL, which allows you to perform DDL operations quickly by updating only the metadata.
⚠️
However, there are the following restrictions when executing with ALGORITHM=INSTANT.
  • Adding a column cannot be combined in the same statement with other ALTER TABLE actions that do not support ALGORITHM=INSTANT.
  • A column can only be added as the last column in a table. Adding a column in any other position between other columns is not supported.
  • You cannot add a column to a table that uses ROW_FORMAT=COMPRESSED.
  • You cannot add a column to a table that has a FULLTEXT index.
  • Columns cannot be added to temporary tables. Only ALGORITHM=COPY is supported for temporary tables.
  • You cannot add a column to a table that resides in the Data Dictionary tablespace.
  • The row size limit is not evaluated when adding a column. However, the row size limit is checked during DML operations that insert and update rows in a table.
 
In 8.0.29 and later, when executing an ALTER statement, if the operation supports INSTANT, INSTANT will be used by default even if ALGORITHM is not specified.
In addition, the following features have been added:
  • It is possible to add columns not only to the end but also to other positions.
  • Drop column compatible
For more information, please refer to the Online DDL section of the official documentation.
https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html
 
Please note the following points when using this online DDL function:
If ALGORITHM=INSTANT cannot be executed and the table has a large data size, replication delays will occur.
That is the point.
If a DDL execution on the master DB takes 20 minutes, it will be executed on the slave DB after the execution is completed on the master,
That translates directly into a 20 minute replication delay.
💡
If the execution environment is Amazon RDS Aurora, this is not the case because the architecture is different.
 
To prevent this, execute the DDL on the slave first, then execute the DDL on the master DB with the binlog temporarily disabled.
This is commonly called a Rolling Schema Upgrade, but this method also has its drawbacks.
Since the column type change and column addition are already performed on the slave,
You need to check in advance whether the application will still function properly in this state.
If these points are not acceptable, you can use the Percona tool described below.

Use pt-online-schema-change

pt-online-schema-change (hereinafter referred to as pt-osc) is a tool included in the Percona ToolKit that executes ALTER statements without locking tables, which normally require a table lock.
Please refer to the following page for details and architecture of this tool.
"pt-online-schema-change": Safe online schema changes
While overseeing Ameba's overall social gaming infrastructure, I also spend my days and nights studying the floor plans of rental apartments and the differences in soundproofing between partition wall construction methods. I hope to one day live in an apartment with a D-value of 60 or higher. This time, I'll talk about a common issue I encounter when running a web service: "I need to add an index or column to develop a new feature." Until a few years ago, such changes required putting the game into maintenance mode before making the schema changes. However, putting the game into maintenance mode requires extensive coordination and impacts users. Therefore, our team decided to implement pt-online-schema-change (pt-osc), which allows for online schema changes. pt-online-schema-change is a tool included in the Percona Toolkit that allows ALTER TABLE queries, which normally require a lock, to be executed without a lock. Since MySQL 5.6 introduced online DDL, which allows for non-disruptive addition of indexes and columns, the game database we use is MySQL 5.5, so we use this tool to make schema changes. 3. Create three triggers so that inserts, deletes, and updates to target table A are reflected in work table B. Installation is simple (for example, when installing on CentOS): # yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm # yum install percona-toolkit # pt-online-schema-change --execute --alter="ADD INDEX idx_hogehoge(hoge_id)" h=localhost,D=DB_NAME,t=TABLE_NAME,u=root Due to its operating mechanism, table copying and trigger processing are performed, which can easily cause higher loads than usual during execution. For this reason, pt-online-schema-change has a convenient option that allows you to control its operation while checking resource status to prevent adverse impacts on the production environment. While it is a useful tool with many functions, there have been cases where it was not usable in operation and accidents have occurred. I haven't found many examples like this online, so I'd like to introduce a few. # pt-online-schema-change --execute --alter="ADD INDEX idx_hoge(id_hoge)" h=localhost,D=d1,t=t_1,u=root You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table.
https://ameblo.jp/principia-ca/entry-12129289966.html
 
Please refer to the official documentation for how to install the percona toolkit.
 
By using pt-osc, you can execute online DDL on MySQL 5.5, which does not have the online DDL function.
DDL that takes a long time to execute can also be executed with relatively little replication delay.
 
Execution sample when adding a column
 
This tool also has limitations and may not be usable in all situations.
Please test it in a verification environment beforehand.
 

Conclusion

SRG is looking for people to work with us. If you're interested, please contact us here.