Skip to content

# Reducing locks by narrowing primary key

MySQL Indexing InnoDB Performance May 4, 2010May 4, 2010

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
7
Leave a Reply

7 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
5 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Gerry

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

Guest
hadov

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

Author
shlomi

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

Guest
wdk

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

Author
shlomi

@wdk
thank you. Another issue with UUID is its length, which leads to a bloated PK.

Guest
Seun Osewa

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.

Author
shlomi

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

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.