Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 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.

share|improve this answer
2  
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 at 4:31
1  
@Michael-sqlbot Holy, I got a little confused there it seems. Edited my answer. Thanks. –  tombom Jan 9 at 9:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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