Geek Answers Handbook

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

We have a moderately large MySQL production database. From time to time, we will run commands, usually using rail migration, which drown the database during operation. As a concrete example, we could add an index to a large table.

Is there any method that can reduce the priority of MySQL that gives a specific task. Some kind of "good" inside MySQL itself? I found this, which inspired the question:
Tips and tricks for PostgreSQL

Since adding an index causes the work to be done during the database and MySQL process, lowering the priority of the Rails migration process seems like it will not help. Are there other ways to lower priority?

+4
source share
2 answers

We use several replicated database servers to make these changes.

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

Let's start by changing to db2. If everything is blocked, replication will stop, but that's OK.

Move your traffic to db2. Any remaining traffic going to db1 will be repeated, so you won’t lose anything.

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

This is a general idea, and you get very little downtime, and you don’t need to pull everything! In fact, we have three servers, so it is a little more complicated, but not much.

Good luck.

+2
source

Unfortunately, there is no easy way to do this: commands that change the structure of the database do not have a priority.

If your tables are MyISAM, you can try the following:

  • mysqlhotcopy to backup table
  • import the backup to another database server (one that is not loaded)
  • make changes there
  • backup mysqlhotcopy modified table
  • import it into a live server

Note that this may or may not be faster than adding an index on a real server, depending on the time it takes to move the table back and forth.

+2
source

More articles:


All Articles