Online Index Rebuild

10-07-2007, 10:45 AM

Hi,

I know that MySQL drops-and-creates the table when doing an alter table of any kind.
Also, i know there is no embedded way to drop and create an index online, to rebuild it.

Is there a method someone here uses that can duplicate this behavior in a reliable way? Meaning, create another table, somehow copy diffs there, and then apply them, without the application being aware of that? I've googled and searched the forum, can't find anything about this.

Comment

Lets say I've got a table with millions of records, and around 20-50 GB in size.
More importantly, it needs to be always on-line (or at least, 99.999% of the time).
The table receives a lot of writes as well as reads.
Regular maintenance on this type of table is nearly impossible since an index rebuild/optimize locks the entire table for a too long time.

In databases such as SQL Server or Oracle there is an option to rebuild index "ONLINE" so that the table is not locked and available as usual.

What "tricks" are there in MySQL to do maintenance such as rebuilds, without stopping the regular flow of work in the application?

Comment

Comment

What you can do sometimes is use the fact that renaming a table is a very fast operation.

So by using a backup copy (to further minimize downtime) you can create a copy of your table. Do the alteration on that copy and then do a fast rename:

RENAME TABLE old_table TO backup_table, new_table TO old_table;

But if you have a lot of INSERT/UPDATEs then you will have to handle the ones that happens between you take the dump and when you switch. By for example replaying the INSERT/UPDATE queries that has taken place during this time.

In any way there is (as shlomoid noted) no ready feature for this scenario.

Comment

This is actually a good option (and the only one possible) - and is also sort of what I had in mind by "manually". The "RENAME" is very fast and more importantly, atomic.

Right now I'm looking for a way to do this using partitions... And I still can't figure out how, since many of the basic features in partitioning that one would expect are still not available. I can't add a table as a partition, I can't replace a partition with another table..

Maybe you have any ideas for a "trick" that can do the same magic? )

(The only option i see at this moment, is to implement partitions in the application itself...)

Comment

The only way that comes to my mind to solve this with as little downtime as possible is that you create the secondary copy of the table from your backup.
And then you write a small script that uses the output from the bin-log (replacing the table name with the name of the secondary table) to continuously write the changes to the secondary table.
And when the changes to the secondary table has caught up with the main table you temporarily place a LOCK on the main table perform the RENAME and the switch is ready.
The tricky part is the re-feeding of the bin-log but I don't see any other way if you want to minimize downtime as much as possible. Just remember to replace the original table name with the name of the secondary copy of the table. Otherwise you have invented the new Perpetuum Mobile.

Comment

The read from bin-log is a great idea - it might save the application from being aware from what is happening. I can just do everything in the background. Hmm.

The bin-log part is indeed complex since it will require filtering of the statements to find out the ones that are relevant and need to be replayed. Never tried anything like this before, good time to learn. I'll report here if I make anything out of it eventually.

Comment

With things like this, speed is always paramount. (Well, second to accuracy.) I've found that python is a great scripting environment for things like this. The tool that I linked to earlier was not made open source because I thought it would server a broad sweeping need. It was a niche tool that I thought could save others some time in creating their niche tools. I would suggest using python for something like your need.

You should perform numerous dry runs in a test env before you unleash this for real. I used Zawodney's wonderful SuperSmack tool for simulating heavy usage while I ran my tests. (HowTo Example) This allowed me to get my process to a state where not a single query fails. This is amazing considering that I am deleting and replacing the tables on the file system while MySQL is receiving about 3000 queries per second.

Also, take advantage of the great resource of #python on freenode.net IRC. Forums and wikis are great, but nothing beats real-time assistance from a human. And unlike other l33tist IRC channels, I've never heard an RTFM come from there. (Though we often post links to specifics docs in the manual with a polite, "see if this makes sense.")

Good Luck!

Comment

Yeah, i agree, python is an amazing tool. I don't program much as a DBA, but python was just so easy to learn and use, i can't think of any other language to script in.
I wrote my own simple "smack" tool, in python in few days while learning the language - and i'm going to use it to test the solution, whichever it is.
I'll keep that IRC channel in mind, thanks )

Maybe there is a way to tweak your solution for MyISAM to work with InnoDB files, when file-per-table is enabled. That would be cool. Much research to be done...