Rails: How to Safely Add New Index to Large MySQL Table

Environments

• Rails 6
• MySQL 8 (AWS RDS)

Context

Problem

My Rails app had a large table. The table had millions of records and started introducing slow queries and causing time-out errors in certain cases.

In order to improve the query performance, I needed to add a new index to the large MySQL table without introducing any downtime on production.

Solution strategy

MySQL 8 supports online DDL changes using the ALGORITHM=INPLACE, LOCK=NONE options, which allow reads and writes to be able to continue while the index is being created, and no extra disk space is required for a temporary table. Therefore, I decided to utilize these options for the ALTER TABLE query to add the index with the least impact on the system’s availability.

However, these options are not available in the Rails migration and it would take time to complete adding the index to the entire table, which could impact the deployment process. Therefore, I concluded it would be safer to run the ALTER TABLE query not from the migration, but manually after the deployment.

Preparation

Setup a test DB

Prepare a test database that has a similar data size and spec to production. In my case, I was planning to just measure the performance of the limited queries so I decided to create a clone RDS instance from a snapshot and used it exclusively for the following experiment.

Determine the right index to add

Find the performance bottleneck with EXPLAIN. I analyzed the result, added the index as an experiment, and discovered it would improve the performance 4 times 🎉

Test the index query

Manually run the ALTER TABLE query to add the index on the test DB and confirm whether it would not cause any issues and how long it would take.

You would need to extend the DB connection read time-out configuration of your client tool as the query would take a long time to complete. For example, if you’re using MySQL Workbench, you would need to set 0 to “DBMS connection read timeout interval” at “Preferences > SQL Editor” as follows:

Steps

1. Create the migration file

Create the migration file to add the index, for example:

class AddCreatedAtIndexToMyModel < ActiveRecord::Migration[6.0]
  def change
    add_index :my_models, [:reference_id, :created_at]
  end
end

As mentioned, ALGRITHM=INPLACE and LOCK=NONEoptions are not available in the Rails migration so you will need to manually execute the ALTER TABLE query with these options on production after the deployment. In other words, this migration is basically just for non-production environments.

2. Add the migration version to schema_migrations

If your deployment process automatically runs rails db:migrate, you would need to prevent the migration file from running because as mentioned it would be safer to manually execute the long-running query not during the deployment but after.

In order to achieve this, add the migration version (the timestamp value that you can find at the beginning of the migration file name) to the table schema_migrations so that Rails will consider the new migration already executed and ignore it during the deployment process.

3. Add the index manually

This is the final step. Once you make sure your query works on the test DB, you’re ready to execute it on production. The query needs to add the same index as the migration file, for example:

ALTER TABLE my_models
 ADD INDEX index_my_models_on_reference_id_and_created_at (reference_id, created_at),
 ALGORITHM=INPLACE,
 LOCK=NONE;

References

• https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
• https://stackoverflow.com/questions/15712512/mysql-workbench-how-to-keep-the-connection-alive