1

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the table). The updates that occur happen all at once everytime, so I am usually updating 30-40 million out of the 60 million rows once a day. This table is only used by one user so I am not worried about multiple users trying to connect to the table at the same time or anything like that. Which engine (and parameters) would be fastest to perform just updates and nothing else on (well there will be one select statement once in a while, but I am not concerned about the performance of select statements)? I am currently using MyISAM as I am not worried about ACID, but it currently is quite slow for mass updates. Essentially I am trying to find the fastest way to do MASS Updates.

  • Shouldn't the middle sentence in your question read: "This table is only used by one user so I am not worried about multiple users trying to connect to the table at the same time or anything like that." ? – Colin 't Hart Sep 15 '14 at 7:29
  • Yes, you are correct. I have just changed that, thanks! – user2924127 Sep 15 '14 at 16:09
1

Your question is somewhat like a question I answered back on Sep 06, 2013 : mySQL for analytics and updating same row

In that answer, I recommended the following

  • Adding a third column to the table to represent a slot
  • Select an arbitrary value for the number of slots
  • Making a composite PRIMARY KEY
    • hour boundary
    • slot number
  • Doing an INSERT with ON DUPLICATE KEY UPDATE to increment the counter

Since you are doing 30-40 million updates, you may need to choose a very high number of slots. In your case, I would go with 250,000 slots. The high the number of slots, the lower the incidence of collision (trying to update the same slot).

As far as the choice of storage engine, you cannot stay with MyISAM. Why ? MyISAM will do a full table lock for every update. Doing 40 million updates require 40 million table locks. My posted answer works well with InnoDB's row level locking. Even if you implement the slot paradigm, MyISAM will still do 40 million table locks with 40 million updates.

Definitely, you must go with InnoDB. You also need to tune InnoDB.

If you are using MySQL 5.1, upgrade to MySQL 5.6.

Give it a Try !!!

UPDATE 2014-09-15 00:21 EDT

I learned the concept of slots for counters from page 140 of the Book

the Book

My post (dated Sep 06, 2013) is the first post in the DBA StackExchange to mention this book for your specific issue. Someone else mentioned this technique one month later in this same forum (counter table in mysql dates Oct 18, 2013)

I just tried googling for this concept search for mysql counter slot

I found some posts:

The main point I am making is that this concept is not new. You can implement it.

Give it a Try !!!

  • Thanks for the reply! I am unaware of the concept of slots. So if I have a table with |colA|colB|slot| what value goes into the slot rows? Could you possibly give a small example of how this would look like? – user2924127 Sep 15 '14 at 3:34
  • Sorry, I just read your other post. I don't understand what the slots do? I am trying to find the concept on google, but can't find anything. – user2924127 Sep 15 '14 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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