0

I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, each in a transaction.

Because there may be clashes, I need to make a decision for each row:

INSERT INTO my_table (a, b, c)
values ('a', 'b', 3)
ON DUPLICATE KEY UPDATE
a = IF(c<VALUES(c), a, VALUES(a)),
b = IF(c<VALUES(c), b, VALUES(b)),
c = IF(c<VALUES(c), c, VALUES(c));

If I run a few (e.g. 10) parallel insertion threads, is this a good idea? Will it cause me deadlocks due to contention on the index?

1

SHOW CREATE TABLE -- need to see the PRIMARY KEY and the "index".

100-1000 is optimal (10K might be ok) for "batching" INSERTs -- That is, a single INSERT statement with 100 to 1000 rows in it.

You have shown a single row being inserted. Will it work change to

values ('a', 'b', 3), ('c', 'd', 4), ...

That might give you a 10-fold speedup.

That is also pretty good for the size of a transaction. You could also set innodb_flush_log_at_trx_commit = 2.

Parallel threads might help. In that case, transactions are vital, plus code to replay a transaction if it fails. And you might be better off with 100 rows at a time -- to minimize deadlocks or delays due to other locking.

innodb_buffer_pool_size should be 70% of available RAM (assuming you are using ENGINE=InnoDB).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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