Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Basically my problem is that I have a large table of about 17,000,000 products that I need to apply a bunch of updates to really quickly.

The table has 30 columns with the id set as int(10) AUTO_INCREMENT.

I have another table which all of the updates for this table are stored in, these updates have to be pre-calculated as they take a couple of days to calculate. This table is in the format of [ product_id int(10), update_value int(10) ].

The strategy I'm taking to issue these 17 million updates quickly is to load all of these updates into memory in a ruby script and group them in a hash of arrays so that each update_value is a key and each array is a list of sorted product_id's.

{ 
   150: => [1,2,3,4,5,6],
   160: => [7,8,9,10]
}

Updates are then issued in the format of

UPDATE product SET update_value = 150 WHERE product_id IN (1,2,3,4,5,6);
UPDATE product SET update_value = 160 WHERE product_id IN (7,8,9,10);

I'm pretty sure I'm doing this correctly in the sense that issuing the updates on sorted batches of product_id's should be the optimal way to do it with mysql / innodb.

I'm hitting a weird issue though where when I was testing with updating ~13 million records, this only took around 45 minutes. Now I'm testing with more data, ~17 million records and the updates are taking closer to 120 minutes. I would have expected some sort of speed decrease here but not to the degree that I'm seeing.

Any advice on how I can speed this up or what could be slowing me down with this larger record set?

As far as server specs go they're pretty good, heaps of memory / cpu, the whole DB should fit into memory with plenty of room to grow.

share|improve this question
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.