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.

  • 2
    what does select version(); show?
    – ysth
    Sep 14 at 20:39
  • If you are worried about how this might impact your Production system, you could always create a Snapshot of the database and launch a new (non-production) database from that snapshot. Then, perform the change and see how long it takes and what impact it has. Sep 15 at 1:51
  • For further discussion, please provide SHOW CREATE TABLE.
    – Rick James
    Sep 15 at 2:51
  • @ ysth , select version is 5.6.50
    – nomad123
    Sep 15 at 7:04
  • @ John Rotenstein, Its good idea to first try on snapshot. I will check how this feasible at my work place. I will get back
    – nomad123
    Sep 15 at 7:05

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 https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html 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.

  • Thank you Bill for the response. I will go through the documentation of pt-online-schema-change. At my work place special review is needed before start using open source tools. Question, since your team is releasing many schema changes so I wanted to know how much time taken for a large table scenario in RDS MySQL. Any statistics would help to understand how its working practically.
    – nomad123
    Sep 15 at 21:06
  • We don't have much record of schema changes on large tables on RDS, because most of our MySQL instances are still on physical servers in our datacenter. We don't have many large tables on RDS yet. Also, our results would not be very useful to you, since your databases undoubtedly have very different load than ours. You're just going to have to measure your own schema changes on your own servers under your specific load. Sep 15 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.