We're trying to add an index to one of our tables in production database. CREATE TABLE `abcd_s3_metadata` ( `rowId` bigint(20) NOT NULL AUTO_INCREMENT, `sId` varchar(50) NOT NULL, `ruid` varchar(200) NOT NULL, `s3Pointer` varchar(250) NOT NULL, `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `latest` tinyint(1) NOT NULL DEFAULT '1', `version` int(11) NOT NULL, PRIMARY KEY (`rowId`), UNIQUE KEY `ruid_UNIQUE` (`ruid`) )

The table has ~ 1B rows. We created a copy of the live table and I tried to add an index on updateTime field. With the following statement:

ALTER TABLE `innodb`.`ruids_s3_metadata` ADD INDEX `updateTime_idx` (`updateTime` ASC);

The ALTER statement ran for 3 hours resulted in extra read 1500 IOPS consumed all free InnoDB memory. And eventually caused the DB to choke and become unusable.

What can we do better? Are there other options?



  • What you have is troublesome. What version of MySQL are you using? Newer versions have better index adding. Check the RDS manual to see if they included the new ONLINE and/or INPLACE options. – Rick James Aug 22 '18 at 3:50
  • 1
    Part of the performance problem, as you are seeing it now, is the existence of two unique keys. Rethink the need for two. – Rick James Aug 22 '18 at 3:51
  • 1
    If ruid is a UUID in disguise, say so. there are things that can help it. (But mostly you are SOL when using UUIDs on huge tables.) – Rick James Aug 22 '18 at 3:52

You should look at using a 3rd party schema change tool to avoid causing issues. You can start with pt-online-schema-change from the Percona Toolkit and if that doesn't meet your needs there's a schema change tool written by github named Ghost.



| improve this answer | |
  • I think I'll run into same problem with those tools, once the tool will attempt to add index to new table – AlexV Aug 7 '18 at 11:39
  • Alex, pt-online-schema-change will create an empty copy of your base table with the new index. It will then back fill the table with your existing rows whilst triggers will reflect all changes made to the base table. This is done in batches small enough to avoid causing impact to the operational performance. If for any reason there's any issues seen in the performance you'd be able to back-out of the change. – eroomydna Aug 7 '18 at 13:32
  • Oh, I wasn't aware that it pt-online-schema-change inserts into a table with indexes. I wanted to add indexes after loading, to improve insert speed and to have indexes optimized and not fragmented after lots of inserts. – AlexV Aug 7 '18 at 13:41
  • 2
    @AlexV - The improvements of adding the indexes later are not that great. And pt-osc will get the job done eventually. – Rick James Aug 22 '18 at 3:49

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.