MySQL ON DUPLICATE KEY UPDATE performance issue 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.
Try to start a transaction or set autocommit=false prior the updates.

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


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.