LOW_PRIORITY
doesn't work for two reasons.
- It works only with MyISAM engine.
- It would just cause to wait until there are no more normal or
HIGH_PRIORITY
read requests. If your server happens to be under constant load, it can even happen, that the update doesn't get executed at all.
What you can do, is, to check with a SELECT
first (I mean rewriting your UPDATE
to a SELECT
), that your query is performant, that it uses indexes to find the rows to update (check with EXPLAIN
) and add appropriate indexes if necessary.
Then when you execute your UPDATE
statements, pack them in a transaction.
The InnoDB engine logs information about currernt transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled-back transaction it results in writes that, with a larger buffer, would not need to have been made at all. To set the size of the log buffer, adjust the value (or add it, if it's not set yet) in your my.cnf (Linux) or my.ini (Windows)
[mysqld]
innodb_log_buffer_size = 8M
The default value is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.
If this isn't enough, you can update rows in batches using LIMIT
and spread the update statements over time, so that the row-locks aren't held for too long.
Good luck.