We have a table with over 200k rows. Every row belongs to a category, there are almost 50k rows per category. What is the best way to update all rows of that category? We are using a script like

insert into foo (catgeory_id, family, text) 
values ('1', 'test', 'my text'), (50k more rows)  
on duplicate key update family = values(family), text = values(text)

Running that solely is relatively fast. Our production server has many databases and when we run the script on the different databases at the same time the whole machine starts swapping and on some point the kernel is killing the mysql process.

Where should we start to optimize? We are running MySQL 5.5.

  • Have you tried splitting it into multiple transactions (e.g. 5k rows each)?
    – Vatev
    Jul 9 '14 at 13:41
  • Yes, splitting was making the whole update procedure much longer.
    – Laoneo
    Jul 9 '14 at 13:43

Try to start a transaction or set autocommit=false prior the updates.

start transaction;
.... a lot of updates here ...

Also, the swapping may yield to the lack of physical RAM which occurs in some situations (big resources created by MySQL).

You may also try to
- increase the "key_buffer_size" to the maximum permitted by your SO and MySQL version (check manual). This will improve index updates.
- reduce "max_heap_table_size" (128MB or less) and increase "tmp_table_size" (the bigger, the better).
The idea is to avoid swappiness when some resources needs too much RAM, by sending the work to on-disk temporary tables by default. Some operations will suffer on speed (the bigger ones), but in whole the server will run smoother.

Of course, you may have a lot of memory so a perfect answer cannot be supplied without all informations, at least RAM quantity and server variables, because your problems may start there.



If catgeory_id, family, text are the only three columns in the table, use REPLACE

replace into foo (catgeory_id, family, text) 
values ('1', 'test', 'my text'), (50k more rows);

REPLACE is nothing more that a mechanical DELETE and INSERT.


If you are deleting 50k rows of category_id 1, then do a DELETE of all of them first

delete from foo where catgeory_id = 1;
replace into foo (catgeory_id, family, text) 
values ('1', 'test', 'my text'), (50k more rows);

Give it a Try !!!

  • With the replace approach I have worries that the unique key is running out of space on some point as we do the import daily. The delete first approach is not safe enough as the insert script can contain an error and then we are left with an empty category. We really tried many approaches.
    – Laoneo
    Jul 9 '14 at 15:22

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.