Skip navigation links

MySQL Forums :: Performance :: Bulk Insert On Duplicate Key Update Performance


Advanced Search

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


Subject Views Written By Posted
Bulk Insert On Duplicate Key Update Performance 7476 Dan Bress 07/09/2007 02:39PM
Re: Bulk Insert On Duplicate Key Update Performance 3296 KimSeong Loh 07/09/2007 07:26PM
Re: Bulk Insert On Duplicate Key Update Performance 2666 Dan Bress 07/10/2007 07:26AM
Re: Bulk Insert On Duplicate Key Update Performance 2670 KimSeong Loh 07/10/2007 06:09PM
Re: Bulk Insert On Duplicate Key Update Performance 2347 Dan Bress 07/10/2007 07:37AM
Re: Bulk Insert On Duplicate Key Update Performance 2382 Hiroki Tamakoshi 07/11/2007 04:10PM
Re: Bulk Insert On Duplicate Key Update Performance 2015 Dan Bress 07/12/2007 08:37AM


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.