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?
views:
74answers:
2
+1
A:
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.)
Charles
2010-07-18 07:08:39
Well, it's extremely critical that we don't lose data...
TheOnly92
2010-07-18 07:15:04
+3
A:
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
jim
2010-07-18 08:24:09
you have a more specific method? I think I understand what you mean, but I'm a little confuse. As I have a lot of parts of code editing/inserting records to the database, and I don't think I can stop everything for 1 hour as it is the main part of the whole site...
TheOnly92
2010-07-18 09:09:28
+1 Scheduled downtime is a perfectly acceptable solution for maintenance purposes, and should remain so even in this 24x7x365 world. As long as due warning is given for the downtime in advance, and an estimate provided for when it will be available once more, then most users of the system will accept it. As jim points out, most of the seriously large web sites on the internet (ebay, amazon, twitter, etc) do this.
Mark Baker
2010-07-18 10:08:22
It works as long as you have a fairly accurate estimate of how long the work will take (you should be able to run your tasks against a test system to get an estimate, as well as test what it is that you want to do), and as long as you plan everything that you intend to do, with contingency for rollback if any step fails.
Mark Baker
2010-07-18 10:12:59
mark - nicely summarised. and agreed, 24/7/265 does have the odd 'escape' passage!! :)
jim
2010-07-18 10:22:37
Better to do this than try some complicated method that ends in a balls-up. That way the website is down while you're changing the index, not down while you are recovering the database.
Brian Hooper
2010-07-18 12:05:05
Hmm, well, I'm not the one who can decide whether to pull the site down or not. But how can I roughly estimate how long will it take? 18 GBs of data with approx. 9,000,000 rows of data in innodb?
TheOnly92
2010-07-18 12:19:04
@TheOnly92 - Clone your database to another server (of similar spec if possible) and run the updates on that. This will tell you (a) if your update methods actually work (b) how long it takes to execute... If you don't have another server of similar spec, you'll need to estimate a "power ratio" between the two servers for adjusting your estimate
Mark Baker
2010-07-18 17:13:02