is there a way to throttle the indexing of Mysql tables so overall performance is not impacted?

Question

I need to load a large data set onto a production database.

15 files need to each be uploaded and inserted into a table. Each is about 500 Mb.

I have two ID columns that need to be indexed. If I load the files with indexes in place, the upload takes around 3 hours. If I drop indexes, load data local infile, then re-add the indexes, the whole operation takes about 30 minutes.

The problem is, database responsiveness takes a big hit while indexing the freshly imported data. Is there a way to make the indexing run at a "low priority" so that other queries still get 95-100% speed and the indexing kind of chugs along in the background?

I'm using Amazon RDS, so I don't have the option of just loading on a different server then copying over the table files.

Adding a bounty to this as I still want to see if there is a way to get good performance while indexing on a specific box.

Answer 1

Well, I never found a way to throttle, but I did figure out a way to alleviate my problem. The solution was unique to my problem, but I'll post it in case someone else finds it useful.

I wrote a class named CautiousIndexer.

1. First I stored the create table statement to recreate the table structure without indexes. I stored an array of read slave databases, looped through them renaming the table with the unindexed data to prevent_indexing_($name).
2. Then I ran the create table statement on the slaves only. This effectively moved the data out of the way of indexing statements that would happen on the master.
3. Then I ran the index query against the master. Read slaves had no performance impact while the master was indexing because the newly created tables were empty.
4. When the master finished indexing, I took 1 of the slaves out of production rotation, dropped the empty table, moved the full table back in place, then indexed the table on the out of production slave.
5. When that finished I put it back in production and repeated the slave indexing procedure on the remaining slaves.
6. When all slaves were indexed, I put the table into production.

This was still fine in terms of efficiency, but during the indexing on the master server write performance was unacceptably slowed. Still looking for a way to index with throttling.