MySQL Forums
Forum List  »  Performance

Bulk Insert On Duplicate Key Update Performance
Posted by: Dan Bress
Date: July 09, 2007 02:39PM

I have written an application which tries to do insert/on duplicate key updates as fast as possible.

What we have right now is:
- an InnoDB table with approx 55 million rows that we are doing the insert/updates to
- a stored procedure which takes 10 records as parameters and then performs
10 insert into blah (a,b,c) values ('x', 'y', 'z') on duplicate key update b='y', c='z';
- multiple processes/threads all concurrently calling this stored procedure
- when i look in MySQL Administrator I see MANY of these insert calls sitting there, but they all have a time of '0' or '1'

The reason for the stored procedure is so that we can send these 10 statements over in one query.

The reason we do insert on duplicate key update is we believe this is faster than:
1) selecting what is in the database
2) updating what is already in there
3) inserting what is not in there(using a bulk insert)

What were seeing is the insert takes about 6 seconds to call the stored proc which inserts/updates 10 rows.

Does anyone have any ideas of what I could look at to improve this?
- A different approach?
- parameters to tune?

Options: ReplyQuote

Written By
Bulk Insert On Duplicate Key Update Performance
July 09, 2007 02:39PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.