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'm running into a problem where multiple MySQL updates being performed at the same time will lock up and takes several minutes to finish. I'm using InnoDB, so I'm confused as to why this could be happening since each update is updating only 1 row. I'm also using a m2.4xlarge RDS instance (the largest they come).

Stuck updating

Here's what I'm doing: I have a table with about 100M rows in it, with "views" being a column (which is indexed), and I want to update the views on about 1M rows. On several different servers I have a loop like this where each server has it's own set of rows to be updated (pseudo code):

mysql("set autocommit=0");
mysql("start transaction");

foreach($rows as $row) {
    mysql("update table set views=views+1 where id=$row[id]");
}

mysql("commit");

This loops through all the rows that need to be updated. It works perfectly when the number of servers is small, like around 4, but when it grows to 10+ the updates start to hang in the "Updating" state all at once. Nothing says that it's waiting on a lock, it's just "Updating". This happens for about 5 minutes, where it will finally make the updates and continue through the loop and eventually happen again.

I'm not looking for alternative ways to do the updates. Having things like a tmp table and

update table,tmp_table set table.views = table.views+tmp_table.views where
  table.id = tmp_table.id

lock all the rows that are being updated until they all finish (which could be hours), which won't work for me. They MUST be in these awful loops.

I'm wondering why they could be getting stuck in the "Updating" state, and what I can do to prevent it.

tldr; Having 10+ "update" loops will eventually lock up all the updates being done, at the same time, for an unknown reason until they decide to finally make updates and continue through the loops, only for it to happen again seconds later.

SHOW VARIABLES: http://pastebin.com/NdmAeJrz

SHOW ENGINE INNODB STATUS: http://pastebin.com/Ubwu4F1h

share|improve this question

migrated from serverfault.com Jan 8 '12 at 17:23

This question came from our site for professional system and network administrators.

1  
Why are you not doing this in a single query? –  Ignacio Vazquez-Abrams Jan 8 '12 at 2:50
    
@IgnacioVazquez-Abrams You can't do it with a single query, without using a tmp table like a described. –  Alan Jan 8 '12 at 3:12
2  
Why are you using transactions for a simply update where only one table is involved? That just adds a whole lot of unnecessary processing, which will explain at least some of your performance issue. –  John Gardeniers Jan 8 '12 at 4:07
    
@JohnGardeniers It's my understanding that transactions don't re-index updated rows until they are commited. –  Alan Jan 8 '12 at 4:53
    
Is the views column a string? I see a lot of these: update images_new set views = views+'2', –  Aaron Brown Jan 9 '12 at 2:06
show 4 more comments

3 Answers

up vote 10 down vote accepted

I'm not looking for alternative ways to do the updates. Having things like a tmp table [will] lock all the rows that are being updated until they all finish (which could be hours), which won't work for me. They MUST be in these awful loops.

I disagree.

The strength of an RDBMS is in performing set operations like "update all these rows plz". Given this, your intuition should tell you that these "awful loops" are not the best way to go except under very rare circumstances.

Let's take a look at your current update logic and understand what it's doing.

First off, the set autocommit=0 line in your script is unnecessary. Because you explicitly open a transaction immediately after that with start transaction, autocommit automatically becomes disabled until you end the transaction with COMMIT or ROLLBACK.

Now for the meat of the logic: You've wrapped all these individual updates inside the loop in one big transaction. If your intention behind the iterative updates was to reduce locking and increase concurrency, the wrapped transaction defeats that intention. MySQL must maintain locks on every row it updates until the transaction commits so it can roll them all back at once if the transaction fails or is cancelled. Furthermore, instead of knowing in advance that it is about to lock this range of rows (which would enable MySQL to issue locks with the appropriate granularity) the engine is forced to issue a large number of row-level locks in rapid-fire. Given that you are updating 1 million rows, this is a massive burden on the engine.

I propose two solutions:

  1. Turn autocommit on and remove the transaction wrapper. MySQL will then be able to release every row lock right after it finishes updating the row. It is still forced to issue and release a massive number of locks in a short period of time, so I doubt this will be an appropriate fix for you. Furthermore, if some error occurs halfway through the loop, nothing will be rolled back since the work is not transaction-bound.

  2. Batch your updates in a temp table. You mentioned and then dismissed this solution, but I bet it will perform best. Have you already tried it? I would first test the full million-row update. If that takes too long then batch the work into progressively smaller chunks until you've found the sweet spot: the batches are big enough to get the total work done quickly, but no individual batch blocks other processes for too long. This is a common technique DBAs use when they have to modify a large number of rows during live operations. Remember, since your goal is to maximize your concurrency, keep autocommit on and don't wrap any of this work into a massive transaction so MySQL releases its locks as soon as possible.

    Notice that as the batches become progressively smaller, this solution eventually approximates the first one. That is why I am confident this solution will perform better: When the database engine can group its work into chunks, it flies.

share|improve this answer
    
Thanks so much for the in-depth answer! I have tried the temp table, and it just ends up taking far too long. However, the idea of doing it in batches is terrific. I'm going to give that a shot asap. Thanks! –  Alan Jan 12 '12 at 7:04
    
@Alan - How did it go? –  Nick Chammas Jan 17 '12 at 1:52
2  
I agree with your disagreement. Love the details. +1 !!! –  RolandoMySQLDBA Jan 30 '12 at 17:24
add comment

There is always the imminent threat of deadlocking, even with InnoDB. In this particular case, I can see rows even in InnoDB running headfirst into deadlock situations because you are updating data via the PRIMARY KEY of the views tables. This will initate aggreesive locking within the clustered index.

You can see this locked using SHOW ENGINE INNODB STATUS\G

I answered three very tough questions addressing a similar issue.

SELECT/UPDATE queries can perform locks on the gen_clust_index, aka the Clustered Index when updating via the PRIMARY KEY.

Here are three DBA Stack Exchanges questions I aggressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

In all three of these questions, a row lock involved a corresponding lock in the clustered index of the same table. Neighboring keys of locked rows were involved and thus contributed to the issues.

MORAL OF THE STORY : Deadlocks with InnoDB is still a possibility. Setting up a proper algorithm for individual row-level locks and individual updating the rows in question is a whole lot safer that bulk updating via multiple row-level locks anyday.

Make sure to use autocommit=1 when heavily updating a table in this manner. Even still, updating a row in InnoDB will cause all sorts of MVCC data to be shrouded around the previous contents of the row to allow concurrent transactions. Given the nature of the UPDATE, there will a lot of MVCC data being generated.

share|improve this answer
add comment

Looking at your innodb status I see the latest deadlock w/ the views table is also due to this query:

update low_priority reddit_new 
join images_new on images_new.hash = reddit_new.hash 
set reddit_new.score = images_new.views 
where date > date(now() - interval 1 day)

Is reddit_new.date indexed? Are the hash columns from both tables indexed?

share|improve this answer
add comment

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.