Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

2 Answers 2

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.

share|improve this answer

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

share|improve this answer
    
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 at 15:22

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.