How to use LOW_PRIORITY for heavy queries?

Question

I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources.

I do not want them to stop end users from basic tasks when performing such heavy UPDATE tasks.

Is LOW_PRIORITY for this purpose? or I should follow another strategy?

UPDATE LOW_PRIORITY ....

Note that my engine is innoDB.

Answer 1

LOW_PRIORITY doesn't work for two reasons.

1. It works only with MyISAM engine.
2. 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.