I'm running an ETL process and streaming data into a MySQL table.

Now it is being written over a web connection (fairly fast one) -- so that can be a bottleneck.

Anyway, it's a basic insert/ update function. It's a list of IDs as the primary key/ index .... and then a few attributes.

If a new ID is found, insert, otherwise, update ... you get the idea.

Currently doing an "update, else insert" function based on the ID (indexed) is taking 13 rows/ second (which seems pretty abysmal, right?). This is comparing 1000 rows to a database of 250k records, for context.

When doing a "pure" insert everything approach, for comparison, already speeds up the process to 26 rows/ second.

The thing with the pure "insert" approach is that I can have 20 parallel connections "inserting" at once ... (20 is max allowed by web host) ... whereas any "update" function cannot have any parallels running.

Thus 26 x 20 = 520 r/s. Quite greater than 13 r/s, especially if I can rig something up that allows even more data pushed through in parallel.

My question is ... given the massive benefit of inserting vs. updating, is there a way to duplicate the 'update' functionality (I only want the most recent insert of a given ID to survive) .... by doing a massive insert, then running a delete function after the fact, that deletes duplicate IDs that aren't the 'newest' ?

Is this something easy to implement, or something that comes up often?

What else I can do to ensure this update process is faster? I know getting rid of the 'web connection' between the ETL tool and DB is a start, but what else? This seems like it would be a fairly common problem.

Ultimately there are 20 columns, max of probably varchar(50) ... should I be getting a lot more than 13 rows processed/ second?

2 Answers 2

  1. You may use REPLACE INTO. The disadvantage of it is that it creats high IO, as each existing record will be deleted and then inserted (as opposed to being updated).
  2. Try loading the new rows' IDs into a separate table on the destination server, then run a delete on the destination joining this new table with the existing table using the ID. After that you run your ETL with INSERTs only
  • Thanks. Unfortunately i'm not certain what IO means ... input output? Memory? I'll try REPLACE INTO --- I know this may simply be faster ... the question is, can REPLACE INTO ... be run on parallel connections? My hunch is .... well, maybe, maybe not. Particularly since 'which update is the last update' then becomes problematic. ..... I like your second idea which I've been trying to figure out. Aka optimize the insert process, then do one single delete that essentially checks for duplicates. This may be faster. Thanks
    – user45867
    Commented Apr 30, 2015 at 21:34
  • Another comment ... tried REPLACE INTO ... still at 13 rows/ second. I wonder if the web latency is the key bottleneck here (transferring data over the web) -- though I have my doubts.
    – user45867
    Commented Apr 30, 2015 at 22:00
  • It turns out I can run REPLACE INTO ... in parallel. The issue is --- say I'm putting a 1000 row document into a table (update/ insert) .... if I split that into 4 parallel processes of 250 rows, then will it necessarily have a particular ID -- say 305 ... that shows up twice .... will the last one necessarily update now? Perhaps not. The one with the highest remainder / 250 on the list will be the last update. That said, not sure if there are ever duplicates within a given document anyway. hmmm
    – user45867
    Commented Apr 30, 2015 at 22:04
  • Yes, IO is Input/Output, which is related mainly to writing on hard drives. That is the usual bottleneck in DB operations! Commented May 1, 2015 at 14:31
  • In some cases it's much worse than just IO load. REPLACE INTO does a delete and then an insert. The delete can cause all sorts of things to happen through foreign key constraints and triggers. There are many cases where this won't be a problem, but do check that, and make sure all your future developers understand the implications. See the first comment on the page linked to in this answer.
    – mc0e
    Commented Aug 10, 2015 at 13:35

Can't comment yet, so posting as answer - you state in one comment that "not sure if there are ever duplicates within a given document anyway" - in that case (and in most other cases too) you should use INSERT ... ON DUPLICATE KEY UPDATE as it does not need to delete the existing row, so update overhead is only seen when the target row actually exists.

Syntax is not as simple as REPLACE INTO, but there are not "weird" side effects (because REPLACE works as DELETE/INSERT pair, it can break on foreign keys or delete referencing rows when you didn't want it to)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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