Amazon RDS index creation on large table over 300 M records

Posted on

Question :

I need to create a new index on large table in RDS MySQL. This index is needed to reduce the scan time on this table. Below is the configuration:

  • Table size: 400 GB
  • Records: 400 Million
  • column – Timestamp-col
  • RDS MySQL – db.m5.4xlarge, Engine – MySQL Community

Looking for below details:

  1. How long such index creation takes place.
  2. Will there be any outage to production DB. If so how to mange with lower impact.
  3. Any good/bad experiences and lessons learned on such deployment.

Appreciate your inputs.

Answer :

There’s no way we can predict how long it will take. This depends on the RDS instance size, the data types of columns you are indexing, as well as concurrent load on the RDS instance, etc.

MySQL 5.6 and later have Online DDL which is supposed to allow some concurrent query traffic against your table. Read for details. There are some limitations to how “online” this is.

However, I can suggest a better solution: a free tool called pt-online-schema-change. This is what we use at my company, where we do hundreds of schema changes per week. It may take longer than creating the index directly, but it’s not a problem because it allows you to read and write the table while it’s running.

Leave a Reply

Your email address will not be published. Required fields are marked *