6

We have a table with 500 million rows. Unfortunately, one of the columns was int(11), which is a signed int, and it was an incrementing value that just rolled over the 2.1 billion magic number. This immediately caused downtime for about 10.000 users. We discussed many solutions, and decided that we could just roll back this value safely, by say, a billion. But we had to roll it back for every row.

Here is what we did:

update Table1 Set MessageId = case when MessageId < 1073741824 then 0 else MessageId - 1073741824 end;

I tested this on a table with 10 million rows and it took 11 minutes. So I assumed the larger table would take 550 minutes, or 9 hours. This was going to be our biggest downtime in 3 years. (We're a startup). It's now going on 18 hours.

What should we do?

Please don't say what we should have done. I think we should have updated a few million rows at a time.

Is there a way we can see progress? Could Mysql have hung? Using mysql 5.0.22.

Thanks!

2
  • I realize that right now this next question will be the very last thing you want to hear, but...once this process has completed for you, would you be able to post a link with some findings to share with others? I'm sure that other people would be interested in knowing about the process you're using for recovery. Sorry to hear that you're stuck with this ATM, hopefully it clears out soon. Oh, and if possible - try to free up every little bit and piece of RAM that you can on the server (stop unneeded processes) so that you can increase your disk buffers. Every little bit helps. Feb 27, 2010 at 21:40
  • Thanks, and I will post some learnings here after it's complete, although my estimates tell me it's still another 20 hours, more than I thought.
    – Tim
    Feb 27, 2010 at 22:04

2 Answers 2

1

Try looking at the output of SHOW PROCESSLIST; it should give you a brief idea of what's going on inside that thread; worst case scenario, you could kill that thread (using KILL $threadID) and then cleaning up manually.

HTH.

3
  • Thanks, I've been using SHOW FULL PROCESSLIST non stop to check if it's still running. I've found another way to find the progress, looks like 62% now. 15 hours to go. Ugh.
    – Tim
    Feb 27, 2010 at 15:49
  • @Tim - Just for my own interest, how are you measuring progress? Are you querying the underlying tables that it's modifying, or something more funky?
    – caelyx
    Feb 27, 2010 at 21:09
  • The primary key on this table is an int, and it is a database-wide unique, incrementing value, aptly named UniqueId. I found that the update is happening in order of primary key (note, it's another value that is being updated - MessageId). First, need to do dirty reads, so run this: set session transaction isolation level read uncommitted; Now I can read the uncommitted values that the long query has updated. I narrow it down, searching ranges, until I find it, e.g.: select count(uniqueid) from Table where messageid < 1073741824 and uniqueid > 484000000 and uniqueid < 490000000
    – Tim
    Feb 27, 2010 at 22:03
0

Did you mean: update Table1 Set MessageId = case when MessageId < 1073741824 then MessageId else MessageId - 1073741824 end;

Otherwise I can see a bunch of messages with the same id.

Other way that may be faster, at the sql level:

create table new_table as
select (all other fields),
       case msg id > xxx then msg_id - 1000000 else msg_id
  from old_table;

That way you full scan one table and write the other one in one go as opposed to doing a lot of random i/o (which you can check with iostat).

1
  • It's alright that many MessageId values will be 0. I like your idea, but it's too late now to restart.
    – Tim
    Feb 27, 2010 at 21:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .