improving performance of concurrent inserts on innodb with randomized primary

Question

I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator. It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up generating surrogate keys in monotonic increasing order.

Therefore I am worried with inserts from lots of different users to the same table for that primary surrogate key being used, since the inserts will all be using numbers near each other at a given point in time, might there be enough gap lock contention to slow down the insert?

I have two workarounds in mind. One is to use an md5 hash of the BIGINT number as the actual primary key. This way, all concurrent inserts are likely to lock different gaps. The other solution is that even though for programming simplicity, it would be best to use the the new BIGINT given by my sequence generator as the surrogate id, instead use a primary key tuple (user_id, surrogate_int_id).

Also, if there secondary indices on the table do inserts take gap locks on all of these as well?

Answer 1

One INSERT per transaction, containing 50 rows, with "consecutive" (or "nearby") PRIMARY KEY values will be optimal, regardless of one or many connections.

Using MD5, UUID, etc, is a disaster for huge tables. When the table is too big to be fully cached in RAM, the inserts become I/O bound. In contrast, 50 'adjacent' records will cause almost no I/O.

A secondary index that is a MD5 or UUID is just as bad as the PRIMARY KEY being such. This is because a secondary key is a BTree, just like the PRIMARY KEY (and the data) is a BTree. (OK, the secondary key is not quite as bad, because it is probably smaller.)

If you have multiple connections doing inserts, plan on having occasional deadlocks. Deal with them by re-executing the batch INSERT.

As for "gap locks", first let's get the big picture figured out. That is, deal with the advice I have given you, and provide a SHOW CREATE TABLE.