How Can You Index a Huge MySQL Production Table Without Disrupting Operations?

Create an Index on a Huge MySQL Production Table

Problem Statement:

Creating an index on a large MySQL production table can potentially cause database lockouts. This presents a challenge in ensuring uninterrupted access to the database during the indexing process.

Solution:

MySQL 5.6 and Higher: Online Index Updates

Since MySQL 5.6, the database supports incremental index updates during operations. This means that index creation or deletion can occur simultaneously with reads and writes, reducing the risk of blockages and ensuring data integrity.

MySQL 5.5 and Earlier

In MySQL 5.5 and earlier versions, UPDATE operations on a table with an active index update will encounter blocks. This can significantly impact performance and cause outages.

To avoid these blockages, consider the following approach:

1. Circular Master Servers:

• Establish two servers, with one serving as the primary master and the other as a slave.
• Perform the index update on the slave server while it replicates writes from the master.
• Allow the slave to catch up with the master, ensuring compatibility between the updated and old schemas.
• Atomically switch clients from the master to the slave, making the slave the new master with the updated schema.
• Repeat the process with the master server, making it the new slave.

2. Percona's pt-online-schema-change:

• Create a new table with an identical structure to the original.
• Update the schema on the new table.
• Establish a trigger on the original table to keep changes in sync with the copy.
• Migrate data in batches from the original table to the new table.
• Replace the original table with the new table.
• Drop the old table.

3. RDS Read Replica Promotion:

If using Amazon's RDS, you can create a read-only replica and perform the index update on it. Once complete, promote the replica to become the new master. This approach simplifies the process by automating the failover and data migration.

Note: This approach requires restarting the application to reconnect to the updated database instance.

The above is the detailed content of How Can You Index a Huge MySQL Production Table Without Disrupting Operations?. For more information, please follow other related articles on the PHP Chinese website!