Fast Inserts, Slow Updates

[Tony Dillon]

Hello there,

I wonder if somebody can help explain the situation I am experiencing with a large InnoDB import process.

The import process ultimately ends up with an database about 120GB in size. The import is split into the insert process, which gets the database up to size, followed by an equal number of updates which does not grow the database any larger, but merely updates the existing data.

Rows are inserted/updated in batches of 5000, and the code run is identical for updates and inserts. First I check to see if the record already exists, and if not I insert - otherwise I update.

The interesting thing is that the insert process peaks at only 30 seconds for each 5000 records inserted (of course it is significantly quicker in the beginning). What perplexes me is as soon as the import switches to the updates process this time more than quadruples to between 180 and 400 seconds per 5000 updates.

There are some minor differences to the process for an update, in that it will delete some records before reinserting them. But it seems excessive that this would account for a 6+ times decrease in performance.

One final point is that during the insert process the time per 5000 increased absolutely linerarly by about 0.01 seconds per 5000. The update process varies wildly between 60 and 400 seconds.

I can include any my.cnf settings or table index definitions if anybody would like.

Many thanks for your time,
Tony