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.
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.
- Larger Buffer Pool (increasing innodb_buffer_pool_size)
- Larger Log Buffer (increasing innodb_log_buffer_size to 128M)
- More Write I/O Threads (setting innodb_write_io_threads to 8 or more)
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
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:
- Baron Schwartz, one of the authors of the book, has a URL to that very reference
- Avoid locks when storing counters in MySQL
The main point I am making is that this concept is not new. You can implement it.