About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to 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 a column to an existing table or adding an index.
If you perform a normal DDL operation while the service is running at that time, a table lock will occur, which may lead to a serious service interruption.
It is possible to perform DDL operations online, avoiding table locking, by using the following method:
 

conclusion

💡
AWS AuroraIn this case - Use the official MySQL online DDL feature - If you cannot use it 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 above)

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/removing foreign key constraints
 
In addition, MySQL 8.0.12 introduced a feature called INSTANT DDL, which allows you to perform DDL operations quickly by updating only the metadata.
⚠️
However, there are some restrictions to running 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 a 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.
  • Supports DROP COLOMN
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 will be delayed.
That is the point.
If a DDL execution takes 20 minutes on the master DB, it will be executed on the slave DB after it is completed on the master.
This translates directly into a 20 minute replication lag.
💡
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 not being output.
This is commonly called a Rolling Schema Upgrade, but this method also has disadvantages.
Since the column type change or column addition will be performed on the slave first,
You need to check in advance whether the application will still be problem-free in that 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 I am in charge of the infrastructure for Ameba's social games, I also look at the layout of rental apartments and study the difference in soundproofing between different methods of construction for partition walls. I would like to live in an apartment with a D value of 60 or more someday. Now, this time, I would like to talk about a problem that frequently occurs when operating a web service: "I want to add an index or column to develop a new function." Until a few years ago, when making such changes, we would put the game into maintenance mode before making the schema change. However, putting the game into maintenance mode requires adjustments in various areas and has a large impact on users, so our team decided to introduce pt-online-schema-change (pt-osc), which allows for online schema changes. pt-online-schema-change is a tool included in "Percona-Toolkit", which allows you to execute ALTER TABLE queries without locking, which normally require locking. was implemented, but from MySQL5.6, online DDL that allows you to add indexes and columns without stopping Since the database of the game we are operating is MySQL5.5, we use this tool to change the schema. 3. Create three triggers so that inserts, deletes, and updates to target table A are reflected in work table B. Installation is easy (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 the way it works, table copies and trigger processing are performed, so it is likely to be under higher load than usual while it is running. For this reason, pt-online-schema-change has a convenient option that allows you to control operation while checking resource status so as not to adversely affect the production environment. As you can see, it is a convenient tool with many functions, but there have been cases where it could not be used in operations and accidents have occurred. I haven't found many articles about this kind of case study on the Internet, so I'd like to introduce some of them. # 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.
It is possible to execute DDL that takes a long time to execute 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 are interested, please contact us here.