Reducing priority of MySQL commands/jobs (add an index/other commands)?

We have a moderately large production MySQL database. Periodically, we will run commands, usually 开发者_开发知识库via a rails migration, that while running, bog down the database. As a specific example, we might add an index to a large table.

Is there any method that can reduce the priority MySQL gives to a specific task. A sort of "nice" within MySQL itself? I found this, which is what inspired the question:

PostgreSQL tips and tricks

Since adding an index causes the work to be done within the DB and MySQL process, lowering the priority of the Rails migration process seems like it won't help. Are there other ways we can lower the priority?

We use multiple, replicated database servers to make changes like this.

In our case, db1 is the master, replicated to db2. (db1->db2).

Start by making the change to db2. If things lock, replication will stall, but that's OK.

Move your traffic to db2. Any remnant traffic going to db1 will replicate over, so you won't lose anything.

Once there's no traffic on db1, rebuild it as a slave of db2 (db2->db1).

That's the general idea and you get very little downtime and you don't have to pull an all-nighter! We actually have three servers, so it's a little more complicated, but not much.

Good luck.

Unfortunately, there is no simple way to do this: commands that alter the database structure don't have a priority option.

If your tables are MyISAM, you can try this:

• mysqlhotcopy to make a backup of the table
• import that backup it into a different database server (one that's not under load)
• make the changes there
• make a mysqlhotcopy backup of the altered table
• import it into the live server

Note that this may or may not be faster than adding the index on the live server, depending on the time it takes you to transfer the table back and forth.