MySQL ON DUPLICATE KEY UPDATE performance issue

Question

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.

Answer 1

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.

Answer 2

SUGGESTION #1

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.

SUGGESTION #2

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 !!!