Performing online schema changes in Azure Database for MySQL by using gh-ost

Performing schema changes, or DDL operations, on a MySQL table is a laborious task that often requires careful planning and execution to minimize application downtime, thereby limiting the impact on end users. This is especially important because schema change operations consume a lot of resources and often cause metadata locks in the database.

In this blog post, I’ll walk you through how to use the gh-ost solution to perform online schema changes on large tables in Azure Database for MySQL – Single Server and Flexible Server.

Note: Azure Database for MySQL supports MySQL community major versions 5.7 and 8.0. If you’re using MySQL version 8.0, you can take advantage of a newly introduced INSTANT algorithm to perform some, but not all, schema change operations. More details follow in a later section.

What is gh-ost?

GitHub’s online schema migration solution (gh-ost) is a triggerless online schema migration solution for MySQL. Gh-ost uses the binary log stream to capture table changes and asynchronously applies them onto the ghost table. Gh-ost provides capabilities to test schema changes against replicas and can pause the operation by throttling itself, thereby helping the primary database server to cater to the application workload.

Prerequisites

Before beginning this process, ensure that the:

• log_bin, binlog_format and log_slave_updates parameters for your Azure Database for MySQL instance are set to ON (the default setting).
• binlog_row_image parameter is set to FULL. This allows the gh-ost operation to read the binlogs and perform the necessary DDL operations.
• Table(s) on which you are performing the operation have a primary or unique key so that gh-ost can iterate through the before and after tables for the row copying operation.
• User running the gh-ost operation has the following privileges:
  • ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE on the database that contains the tables to be upgraded.
  • REPLICATION CLIENT, REPLICATION SLAVE ON *.*

Installing gh-ost

To download and install the gh-ost solution, perform the following steps.

1. Download the latest stable release for your operating system from gh-ost releases page.

For the purposes of this blog, we’re using a virtual machine (VM) running the Linux operating system. To download the gh-ost solution to this VM, run the following command:

$ wget https://github.com/github/gh-ost/releases/download/v1.1.2/gh-ost-binary-linux-20210617134741.tar.gz

2. Extract the downloaded file by running the following command:

$ tar -xvf gh-ost-binary-linux-20210617134741.tar.gz

Using gh-ost to perform a schema change

In the following example, we’re going to use the gh-ost solution to modify an Azure Database for MySQL - Flexible Server database by adding a secondary index to a table that contains 100k rows.

Before adding the secondary index, you can view the table structure by running the following command:

mysql> show create table demo\G

The results of the command show the basic table structure:

       Table: demo
Create Table: CREATE TABLE 'demo' (
   'id' int(11) NOT NULL AUTO_INCREMENT,
   'firstname' varchar(15) DEFAULT NULL,
   'lastname" varchar(20) DEFAULT NULL,
   'email' varchar(20) DEFAULT NULL,
   PRIMARY KEY ('id')
)  ENGINE=InnoDB AUTO_INCREMENT=`100001 DEFAULT CHARSET=utf8mb4

Performing a dry run of a schema change

Before making any actual changes to a table schema, it’s recommended to perform a “dry run” of the operation by running a no operation command. The results of a no operation command will showcase the table structure changes that will be made by the operation. Be sure to analyze this information carefully before proceeding with any actual schema changes.

To perform a dry run of adding the secondary index, run the following gh-ost command:

~$./gh-ost --host=demo-replica.mysql.database.azure.com --user=dbadmin --password='xxxxxxxxxxx' --database=demodb --table=demo --alter="ALTER TABLE demo ADD INDEX IX_DEMO_LASTNAME(lastname)" --assume-rbr --chunk-size=2000 --max-load=Threads_connected=20 --initially-drop-ghost-table --max-lag-millis=10000 --verbose

This command includes several flags, which are described in the following table.

Note: For more information about the flags associated with running gh-ost, see the official documentation at gh-ost flags.

The dry-run operation gives detailed information about the target schema of the table and the number of applicable rows to copy from the before schema change table to the after schema change table, as shown in the following output:

# Migrating `demodb`.`demo`; Ghost table is `demodb`.`_demo_gho`
# Migrating a7f9dd8b6662:3306; inspecting 21dda7ef2767:3306; executing on vm-ubuntu18
# Migration started at Wed Jan 26 21:50:28 +0000 2022
# chunk-size: 2000; max-lag-millis: 10000ms; dml-batch-size: 10; max-load: Threads_connected=20; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.demodb.demo.sock
2022-01-26 21:50:31 INFO Row copy complete
Copy: 0/97323 0.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 0s(copy); streamer: mysql-bin.000002:39811620; Lag: 0.57s, HeartbeatLag: 9223372036.85s, State: migrating; ETA: N/A
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 0s(copy); streamer: mysql-bin.000002:39811620; Lag: 0.57s, HeartbeatLag: 9223372036.85s, State: migrating; ETA: due
2022-01-26 21:50:31 INFO New table structure follows
CREATE TABLE `_demo_gho` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(15) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_DEMO_LASTNAME` (`lastname`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4

Performing an actual schema change

When you’ve verified that everything looks good after performing the gh-ost dry run, you can run the command again, this time using the --execute flag to apply the actual schema changes.

~$./gh-ost --host=demo-replica.mysql.database.azure.com --user=dbadmin --password='xxxxxxxxxxx' --database=demodb --table=demo --alter="ALTER TABLE demo ADD INDEX IX_DEMO_LASTNAME(lastname)" --assume-rbr --chunk-size=2000 --max-load=Threads_connected=20 --initially-drop-ghost-table --max-lag-millis=10000 –verbose --execute

After starting the operation, you will notice output similar to the following, which indicates that row copy operations are being performed from the before schema change table to the after schema change table and that the cutover is being performed.

Copy: 0/97323 0.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 0s(copy); streamer: mysql-bin.000002:39818123; Lag: 0.32s, HeartbeatLag: 0.32s, State: migrating; ETA: N/A
Copy: 0/97323 0.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: mysql-bin.000002:39822999; Lag: 0.10s, HeartbeatLag: 0.12s, State: migrating; ETA: N/A
Copy: 20000/97323 20.6%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 2s(copy); streamer: mysql-bin.000002:40424886; Lag: 0.10s, HeartbeatLag: 0.12s, State: migrating; ETA: 7s
Copy: 56000/97323 57.5%; Applied: 0; Backlog: 0/1000; Time: 5s(total), 3s(copy); streamer: mysql-bin.000002:41445231; Lag: 0.10s, HeartbeatLag: 0.12s, State: migrating; ETA: 2s
Copy: 90000/97323 92.5%; Applied: 0; Backlog: 0/1000; Time: 6s(total), 4s(copy); streamer: mysql-bin.000002:42405959; Lag: 0.10s, HeartbeatLag: 0.12s, State: migrating; ETA: due
2022-01-26 22:20:39 INFO Row copy complete
Copy: 100000/100000 100.0%; Applied: 0; Backlog: 0/1000; Time: 7s(total), 4s(copy); streamer: mysql-bin.000002:42828800; Lag: 0.27s, HeartbeatLag: 0.22s, State: migrating; ETA: due
2022-01-26 22:20:41 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2022-01-26 22:20:41 INFO Checking session lock: gh-ost.107.lock
2022-01-26 22:20:41 INFO Connection holding lock on original table still exists
2022-01-26 22:20:41 INFO Will now proceed to drop magic table and unlock tables
2022-01-26 22:20:41 INFO Dropping magic cut-over table
2022-01-26 22:20:41 INFO Releasing lock from `demodb`.`demo`, `demodb`.`_demo_del`
2022-01-26 22:20:41 INFO Tables unlocked
Copy: 100000/100000 100.0%; Applied: 0; Backlog: 0/1000; Time: 9s(total), 4s(copy); streamer: mysql-bin.000002:42838955; Lag: 0.19s, HeartbeatLag: 0.21s, State: migrating; ETA: due
2022-01-26 22:20:41 INFO Tables renamed
2022-01-26 22:20:41 INFO Lock & rename duration: 2.343975946s. During this time, queries on `demo` were blocked

Verifying the update to the table structure

After cutover is complete, verify the changes to the table structure by running the mysql> show create table demo\G command.

Running this command will display the table structure after adding the secondary index, as shown in the following output:

       Table: demo
Create Table: CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(15) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_DEMO_LASTNAME` (`lastname`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4

Testing the solution

Be sure that you perform any necessary testing that’s applicable for your application against the replica or the primary database instance. This will confirm that the schema has successfully been modified without causing any impact to the application workload. Also be sure to check any limitations that may apply to your workloads.

This completes the process of using gh-ost to modify a table schema with minimal impact.

Understanding the improvements in MySQL version 8.0

As mentioned earlier in this post, MySQL 8.0 introduces an INSTANT algorithm that you can use to perform certain DDL operations instantly, without any impact to the foreground operations of your applications. With the INSTANT algorithm, DDL operations performed on tables modify only the metadata in the data dictionary. This ensures that tables aren’t impacted during the preparation and execution phases of the DDL operation. In addition, table data remains unaffected, so operations are instantaneous, which allows for concurrent DML operations (Inserts, Updates, and Deletes).

MySQL version 8.0 supports using the INSTANT algorithm for the following operations:

• Adding a column
• Setting a column default value
• Modifying column
• Renaming a column
• Changing index type
• Dropping column default value
• Modifying definition of an ENUM or SET column
• Renaming a table

Conclusion

Using the procedure outlined in this post, you can reduce or eliminate application down-time associated with database schema changes in Azure Database for MySQL. The steps and the solution used in this process allow you to perform database schema changes with little to no impact on your application, in turn improving the end-user experience.

If you have any questions about the detail I’ve provided, please leave a comment below or email our team at MySQLMariaDBNinjas@service.microsoft.com.

Thank you!