5

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.

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • 3
    I have never heard it stated that InnoDB updates indexes at commit time and it seems rather implausible. Deletions from indexes are done with markers and serviced in the background, but otherwise, I'm skeptical, in light of the way foreign key and unique constraint violations are not deferred, at least. Can you cite this in the documentation or an authoritative source, please?
    – Michael - sqlbot
    Jan 9, 2014 at 4:31
  • 1
    @Michael-sqlbot Holy, I got a little confused there it seems. Edited my answer. Thanks.
    – tombom
    Jan 9, 2014 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.