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

Today, I will talk to you about GitHub's open source MySQL online change Schema tool gh-ost, which may not be understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

There are many tools for MySQL to change schema online, such as Percona's pt-online-schema-change, Facebook's OSC and LHM, etc., but these are all based on Trigger. The gh-ost we introduce today claims to be an online tool for changing table structure without Triggerless support.

Original address: gh-ost: GitHub's online schema migration tool for MySQL

This paper first introduces the use scenarios and principles of these tools that already exist in the industry, and then introduces the working principle and characteristics of gh-ost in detail.

Today we have opened up gh-ost, a tool used internally by GitHub that does not require trigger support for MySQL to change the table structure online.

Gh-ost was developed to meet the continuous and changing needs of GitHub to modify table structures online in a production environment. Gh-ost has changed the working mode of existing online migration table tools by providing low-impact, controllable, auditable and operation-friendly solutions.

MySQL table migration and structure change operations are well-known problems in the industry, which can be solved by online (non-stop service) change tools since 2009. Rapidly growing, fast iterative products often need to change the structure of the database frequently. Add / change / delete / fields and indexes, etc., all of which lock tables by default in MySQL, affecting online services. We are faced with changes to this database structure several times a day, of course, this operation should not affect the normal services of users.

Before we begin to introduce the gh-ost tools, let's take a look at the solutions for the existing tools.

Modify the table structure online, existing scenarios

Today, modifying the table structure online can be done in three ways:

Modify the table structure on the slave library, and the operation will take effect on other slave libraries, setting the slave library with the changed structure as the master library.

Use the online DDL features provided by the MySQL InnoDB storage engine

Use tools that modify the table structure online. Now * is pt-online-schema-change and Facebook's OSC;, as well as LHM and the more primitive oak-online-alter-table tools.

Others include Schema rolling updates for Galera clusters, as well as other non-InnoDB storage engines waiting, where we use the general master-slave architecture and InnoDB storage engine at GitHub.

Why did we decide to start a new solution instead of using the above? Each of the existing solutions has its limitations, and the common problems of these approaches are briefly described below, but the problems of trigger-based online change tools are described in detail.

The migration mode based on master-slave replication requires a lot of pre-work, such as a large number of hosts, long transmission time, complex management and so on. The change operation needs to be performed on a specified slave library or in a sub-tree-based master-slave structure. There are also many situations that are needed, such as host downtime, host recovery from previous backups, new hosts joining the cluster, and so on, all of which may affect our operations. The most fatal thing is that these operations may have to be done many times a day, and if we use this method, our operators will be very efficient every day.

MySQL's online DDL operations against the Innodb storage engine require an exclusive lock (exclusive) for a short period of time to prepare the environment before starting, so after the alter command is issued, it will first wait for other operations on the table to complete, and requests after the alter command will wait for waiting meta data lock. Also before the end of the ddl, you have to wait for all transactions to be completed during the alter, which will be blocked for a short period of time, which is very dangerous for busy database services. In addition, the DDL operation cannot be interrupted. If kill is dropped midway, it will cause long-term transaction rollback and metadata corruption. It is not so Nice to operate, can not limit the current and pause, and even affect the normal business in the heavy load environment.

We have used pt-online-schema-change tools for many years. However, with our growing business and traffic, we encounter a lot of problems, and we have to consider which dangerous operations in the operation. (translator's note: there are often dangerous hints in the documentation of the pt toolset). Some operations must be done away from peak hours, or the MySQL may fail. All existing tools for modifying the structure of online tables are performed using MySQL triggers, which has some hidden problems.

What are the problems with online modification based on triggers?

All online table structure modification tools operate similarly: create temporary tables that are consistent with the original table structure, which has been modified as required, copy data slowly and incrementally from the original table, and record changes to the original table (all INSERT, DELETE, UPDATE operations) and apply to the temporary table. When the tool confirms that the table data has been synchronized, it replaces and renames the temporary table to the original table.

Tools such as pt-online-schema-change, LHM and oak-online-alter-table all use synchronization to synchronize changes to temporary tables when there are changes in the original table. Facebook's tools write changes to the changelog table asynchronously, and then repeatedly apply the changes to the changelog table to the temporary table. All of these tools use triggers to identify the change operations of the original table.

The stored trigger is called when each row of data in the table has INSERT, DELETE, and UPDATE operations. A trigger may contain a series of query operations in a transaction space. This causes an atomic operation not only to be performed in the original table, but also to invoke the corresponding trigger to perform multiple operations.

In the practice of trigger-based migration, the following problems are encountered:

Triggers are saved as interpreted code. MySQL does not precompile this code. They are called in each transaction space, and they are added to the parser and interpreter before each query behavior of the table being manipulated.

Locking table: triggers share the same transaction space in the original table query, and these queries have competitive locks in this table, and triggers monopolize competitive locks in another table. In this extreme case, the lock contention in synchronous mode is directly related to the concurrent write performance of the main library. In our experience, when a competitive lock approaches or ends in a production environment, the database may be blocked by the competitive lock. Another aspect of triggering locks is the metadata locks needed to create or destroy them. We have encountered in busy tables that it can take seconds to minutes to delete triggers after the table structure has been modified.

Untrustworthy: we want to slow down or pause operations when the load on the main library rises, but trigger-based operations cannot do so. Although it can pause row replication operations, it cannot pause triggers. Deleting triggers may result in data loss, so triggers need to be present throughout the operation. In our busy servers, we have encountered an example of dragging the main library to death due to the fact that triggers occupy CPU resources.

Concurrent migration: we or others may be more concerned with multiple scenarios where the table structure (different tables) are modified at the same time. Given the overhead of the above triggers, we are not interested in making online changes to multiple tables at the same time, and we are not sure if anyone has done so in a production environment.

Testing: we may modify the table structure just to test or evaluate its load overhead. The table structure modification operation based on trigger can only be simulated by sentence-based replication, which is still far away from the real main database operation and can not truly reflect the actual situation.

Gh-ost

Gh-ost GitHub's online Schema modification tool, the following working schematic:

Gh-ost has the following characteristics:

No trigger

Lightweight

Can be paused

Dynamic control

Auditable

Testable

Be trustworthy

No trigger

Gh-ost does not use triggers. It listens for data changes in the table by analyzing the form of binlog logs. Therefore, its working mode is asynchronous, synchronizing the changes to the temporary table (ghost table) only after the changes to the original table have been committed.

Gh-ost requires binlog to be in RBR format (line-based replication); however, that doesn't mean you can't perform online change operations on the main library based on SBR (statement-based replication) log format. Actually, it can. Gh-ost can convert SBR logs from the library to RBR logs by reconfiguring them.

Lightweight

Since no triggers are used, the impact on the main library during the operation is minimal. Of course, you don't have to worry about concurrency and locking during the operation. The change operations are written to the binlog file in sequence as a stream, and gh-ost just reads them and applies them to the gh-ost table. In fact, gh-ost performs sequential row copy operations by reading the write events of binlog. Therefore, the main library will only have a separate join order to write data to the temporary table (ghost table). This is very different from the ETL operation.

Can be paused

All write operations are controlled by gh-ost and read binlog asynchronously. When the speed limit is limited, gh-ost can stop writing data to the main library, which means that there will be no replication in the main library and no row updates. When the speed limit occurs, gh-ost creates an internal tracking (tracking) table to write heartbeat events to this table with minimal system overhead.

Gh-ost supports a variety of speed limits:

Load: provides similar functionality for users who are familiar with pt-online-schema-change tools, and can set state thresholds in MySQL, such as Threads_running=30

Replication delay: gh-ost has a built-in heartbeat mechanism, which can specify different slave libraries to monitor the replication delay time of the master and slave. If the set delay threshold is reached, the program will automatically enter the speed limit mode.

Query: users can set a current limit SQL, such as SELECT HOUR (NOW ()) BETWEEN 8 and 17, so that the current limit time can be set dynamically.

Mark file: you can limit the speed of the program by creating a mark file, and you can resume normal operation after deleting the file.

User command: you can dynamically connect to gh-ost (mentioned below) to achieve speed limit by connecting to the network.

Dynamic control

Today's tools, when the load rises during the operation, DBA has to terminate the operation, reconfigure parameters such as chunk-size, and then re-execute the operation command, which we find to be very inefficient.

Gh-ost can listen for requests through unix socket files or TCP ports (configurable). Operators can change the corresponding parameters after the command runs. Refer to the following example:

Echo throttle | socat-/ tmp/gh-ost.sock turns on the speed limit. Similarly, you can use no-throttle to turn off the current limit.

Change the execution parameters: chunk-size=1500, max-lag-millis=2000, max-load=Thread_running=30 these parameters can be changed at run time.

Auditable

Similarly, the status of the gh-ost can be obtained using the program interface mentioned above. Gh-ost can report the current progress, the configuration of the main parameters, the identification of the current server and so on. This information can be obtained through the network interface, which is much more flexible than the traditional tail log.

Testable

Because the log file has little relationship with the load of the master library, the operation of modifying the table structure on the slave library can more truly reflect the actual impact of these operation locks. Although it is not very ideal, we will do the optimization work later.

Gh-ost built-in support testing function, by using the parameter-- test-on-replica to specify: it can change the operation on the slave library, at the end of the operation gh-ost will stop replication, exchange tables, reverse exchange tables, keep two tables and keep synchronized, stop replication. You can test and compare the data of the two tables in your spare time.

This is our test in GitHub's production environment: we have multiple slave libraries in our production environment; some of the slave libraries do not provide services to users, but are used to run continuous coverage migration tests on all tables. For tables in our production environment, the small ones may have no data and the large ones may reach hundreds of GB. We are just making a tag and will not be modifying the table structure (engine=innodb). When replication is stopped at the end of each migration, we will conduct a complete checksum of the data of the original table and temporary table to ensure their data consistency. Then we will resume replication and move on to the next table. The slave library of our production environment has successfully manipulated a lot of tables through gh-ost.

Be trustworthy

So much has been mentioned above, all in order to improve people's trust in gh-ost. After all, it is still a novice in the industry, and similar tools have been around for many years.

Before *, we recommend that users test on the slave database to verify the consistency of the data. We have successfully migrated thousands of operations from the library.

If you use gh-ost on the main library, you can observe the load of the main library in real time. If you find that the load changes greatly, you can limit the speed through the various forms mentioned above until the load returns to normal, and then fine-tune the parameters through the command, which can dynamically control the operation risk.

If the pre-completion time (ETA) after the start of the migration operation shows that it will not be completed until 2: 00 p.m., and you need to switch tables at the end, do you want to stay and watch? You can ask gh-ost to postpone the switch operation by marking the file. Gh-ost completes the row replication, but does not switch the table, it continuously synchronizes the data update operations of the original table to the temporary table. When you come to the office the next day, delete the tag file or tell gh-ost through the interface echo unpostpone to start switching tables. We don't want our software to bind users, it's supposed to be for us.

Speaking of ETA, you might like the-- exact-rowcount parameter. In contrast to a long SELECT COUNT (*) statement, gh-ost estimates how long the migration operation will take and updates the estimated time based on the current working condition of the migration. Although the time of the ETA changes at any time, the display of the progress percentage is accurate.

Gh-ost operation mode

Gh-ost can connect to multiple servers at the same time, and in order to obtain binary data streams, it copies data from one library to another as a slave library. It has a variety of different modes of operation, depending on your settings, configuration, and to run the migration environment.

a. Connect to the slave library and do the migration in the master library

This is the default way for gh-ost to work. Gh-ost will check the slave database status, find and connect the master database in the cluster structure, and then perform the migration operation:

Row data is read and written on the main database

Read the binary log of the slave library and apply the changes to the master library

Collecting table format, field & index, number of rows and other information from the library

Read internal change events (such as heartbeat events) from the library

Switch tables in the main library

If the log format of your main library is SBR, the tool will also work. However, the slave library must enable secondary logging (log_bin, log_slave_updates) and set binlog_format=ROW (gh-ost is the secondary file that reads from the slave library).

If you operate directly on the main library, of course you also need the binary log format to be RBR.

b. Connect to the main library

If you do not have a slave library, or do not want to use the slave library, you can operate directly on the master library. Gh-ost will do all operations directly on the main library. You need to keep an eye on replication delays.

The binary log of your main library must be in RBR format.

In this mode you must specify the-- allow-on-master parameter

c. Migrating / testing from the library

This mode performs a migration operation from the library. Gh-ost will simply connect to the master library, and all operations will be performed from the slave library without any changes to the master library. During the whole operation, gh-ost will control the speed to ensure that the data can be synchronized in time from the library.

-- migrate-on-replica indicates that gh-ost will migrate directly from the library. Table switching can be performed even during the replication run phase.

-- test-on-replica indicates that the migration operation is just to test that replication will stop before switching, then switch, and then switch back, and your original table will eventually be the original table. Both tables are saved and the copy operation is stopped. You can perform test operations such as consistency checking on these two tables.

Gh-ost at GitHub

We have used gh-ost in all online database operations online, and we need to use it every day, possibly multiple times a day, depending on the database modification requirements. With its audit and control capabilities, we have integrated it into the ChatOps process. Our engineers can clearly understand the progress of the migration operation, and can flexibly control their behavior.

Open source

Gh-ost is released to the open source community under the license of MIT.

Although gh-ost is stable in use, we are constantly improving and improving it. We open source it and welcome friends from all walks of life to participate and contribute. Then we will post a page of contributions and suggestions.

We will actively maintain the gh-ost project, and hope that the majority of users can try and test this tool, we have made great efforts to make it more trustworthy.

After reading the above, do you have any further understanding of GitHub's open source MySQL online change Schema tool gh-ost? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.