Reducing locks by narrowing primary key

MySQL

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.

7 thoughts on “Reducing locks by narrowing primary key”

  1. 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

  2. wasn’t there any subset of the 5 colomns that was unique by itself?
    (any superset of a unique key is unique as well)

  3. @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…

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.