MySQL – Adding Indexes to Production Database Without Downtime

high-availabilityindexmigrationMySQL

I am looking to improve the performance of queries on a large MySQL database that is running in production by adding proper indexes. We have tried to add the indexes on the live database, but of course this locks the database and makes our application unavailable.

We have thought of creating a secondary database with the updated indexes and then migrating the database data over. This would involve some downtime.

Would there be a better way about doing this? That doesn't involve downtime?

Thank you in advance for your help

Best Answer

If you wanted to add indexes to a live table, there are two major approaches:

APPROACH #1 : pt-online-schema-change (Preferred Choice)

This tool is spectacular. It will create a temp table for you and populate it.

In oversimplified terms, it does this for mydb.mytable:

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
RENAME mydb.mytable TO mydb.mytable_old,mydb.mytable_new TO mydb.mytable;
DROP TABLE mydb.mytable_old;

However, pt-online-schema-change goes way beyond this.

What if rows already copied to the temp table are updated or deleted ? Well, pt-online-schema-change adds triggers to the source table to handle those post-INSERT changes for you. When the copy to the temp table is complete, the triggers are removed just before the switch.

There also are options to throttle (such as --max-load and --critical-load) or halt (such as --pause-file) the copy to the temp table if the live system has a high load or if the server has slave that fall behind a certain number of seconds (such as --max-lag).

You also have options to test the syntax only (--dry-run) or actually run the change (--execute).

There are options to set variables like sql_log_bin to 0 and check for foreign key constraints.

The limitations to this tool ?

  1. Make sure there is enough room for the temp table before the switch happens. For example, if the source table is 200G, I would make sure there is at least 250G free on disk.
  2. Momentary outage in seconds to switch the temp table and the old table.

APPROACH #2 : Online DDL

MySQL is also capable of performing DDL online using native SQL.

See my old posts

Unfortunately, Online DDL does not have options to throttle or halt the operation.

EPILOGUE

These days I use pt-online-schema-change because I can control how fast or slow the process takes. I can even stop and resume it over a series of day. One time I, along with two other DBAs, performed pt-online-schema-change on a 30GB that had super high reads and writes. The Dev Team we worked with asked us to pause the copy to the temp table during the day and resume the copy overnight. We did this in a span of three days. Everything was successful and applications never suffered.

Related Question