CODEWITHSUNDEEP
phpmysql
TheOnly92
TheOnly92

Reputation: 1763

How to add index to 18 GB innodb mysql table without affecting production performance?

How can I add index to an 18 GB innodb mysql table without affecting the production performance? The table is frequently accessed, I tried altering the table just now and it turns up to have locked more than 200 queries out, and that's bad for performance. Are there any other ways to do it?

Upvotes: 2

Views: 2134

Answers (3)

Itay Kahana
Itay Kahana

Reputation: 2445

Another option is to use pt-online-schema-change. It will create a copy of the old table with the new index and create trigger that will reflect all changes from the old table to the new one. In end, it will change the name of the old table.

Upvotes: 3

jim tollan
jim tollan

Reputation: 22485

TheOnly92 - there is another option, one that even amazon and ebay use. it's not considered 'bad' form to have infrequesnt maintenence periods where the site is unaccesible. on those occassions, you'll see a 404 maintenence page being displayed with a user friendly messge saying that the site is undergoing essential upgrades between the hours of .... etc

this might be the most pragmatic solution as creating this page will take you 5 mins, whereas the other option may take many hours to figure out and many more to implement. also, as it would be infrequent, then it's unlikely that your users would be put off by such a message or period of downtime.

jim

Upvotes: 3

Charles
Charles

Reputation: 51411

It depends, how critical is it that you don't lose new records?

Duplicate the table structure using CREATE TABLE ... LIKE ..., add the new index to the duplicate table, do a INSERT INTO ... SELECT ... FROM ... to grab all the data, then run a pair of ALTERs to rename the old table, then rename the new table to the old table's name.

Unfortunately if any data in the old table changes between the time that the INSERT/SELECT runs and the tables get renamed, it may be lost. It might be possible to get the data back using one of the Maatkit tools for table comparison.

Another common pattern relies on duplicate hardware. Set up a replication slave, add the index there, then make the slave the master.

Neither of these is likely to be fast, but they'll probably be faster than adding the index directly. If you can afford the downtime, however, you really should just take the system down while you're altering/copying/switching slaves. Not having to worry about getting the data back in sync will make your life easier.

(You may wish to consider switching to a database that lets you add indexes without locking the table.)

Upvotes: 2

Related Questions