In a period of two weeks, I had two cases with the exact same symptoms.
Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that INSERTs were causing the locks.
In both cases, tables were InnoDB. In both cases, there was a PRIMARY KEY on the combination of all 5 columns. And in both cases, there was no clear explanation as for why the PRIMARY KEY was chosen as such.
Choosing a proper PRIMARY KEY
Especially with InnoDB, which uses clustered index structure, the PRIMARY KEY is of particular importance. Besides the fact that a bloated PRIMARY KEY bloats the entire clustered index and secondary keys (see: The depth of an index: primer), it is also a source for locks. It’s true that any UNIQUE KEY can serve as a PRIMARY KEY. But not all such keys are good candidates.
Reducing the locks
In both described cases, the solution was to add an AUTO_INCREMENT column to serve as the PRIMARY KEY, and have that 5 column combination under a secondary UNIQUE KEY. The impact was immediate: no further locks on that table were detected, and query responsiveness turned very high.
Keep in mind that an AUTO_INCREMENT key locks the whole index on INSERTs. When you have a high concurrent traffic situations, these locks could create waits damaging performance. In those cases you’ll need to look for other alternatives, example: UUIDs.
My $.02
G
wasn’t there any subset of the 5 colomns that was unique by itself?
(any superset of a unique key is unique as well)
@Gerry –
You are right that AUTO_INCREMENT imposes a serializing lok. However, I’ve clearly seen how inserting unordered rows, as with UUID, significantly reduces insert time due to index fragmentation.
With AUTO_INCREMENT, innodb makes some optimistic assumptions and builds the B+Tree in a far more condensed form.
@hadov –
No, there wansn’t…
You can make a timebased uuid (see http://www.ietf.org/rfc/rfc4122.txt) ordered by changing the order of bytes.
So:
TLTLTLTL-TMTM-VTHT-RCSC-NODENODENODE
Becomes:
NODENODE-NODE-VTHT-RCSC-TMTMTLTLTLTL
@wdk
thank you. Another issue with UUID is its length, which leads to a bloated PK.
It doesn’t appear that we fully understand this problem even though we have a solution that seems to work. I don’t see why a clustered index on the entire column should lead to more locking. also, without secondary indexes, a hug primary index won’t lead to more bloat.
@Seun,
You are right that without a secondary index there wouldn’t be a bloat. The two issues I tackled did have secondary keys. Thanks for pointing this out.
I’m not an expert on the InnoDB internals, so let me explain this only in general: the reason is that InnoDB manager locks on the PK level, locking branches in the BTree. If you happen to attempt using the same branch (this does not necessarily mean you are now INSERTing two very close rows), then you are susceptible to locking.
I should look more closely into this myself.