Comments on: Reducing locks by narrowing primary key https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key Blog by Shlomi Noach Thu, 20 May 2010 04:33:10 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: shlomi https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key/comment-page-1#comment-13852 Thu, 20 May 2010 04:33:10 +0000 https://shlomi-noach.github.io/blog/?p=1269#comment-13852 @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.

]]> By: Seun Osewa https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key/comment-page-1#comment-13851 Thu, 20 May 2010 04:18:39 +0000 https://shlomi-noach.github.io/blog/?p=1269#comment-13851 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.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key/comment-page-1#comment-13733 Tue, 18 May 2010 07:16:11 +0000 https://shlomi-noach.github.io/blog/?p=1269#comment-13733 @wdk
thank you. Another issue with UUID is its length, which leads to a bloated PK.

]]> By: wdk https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key/comment-page-1#comment-13732 Tue, 18 May 2010 06:47:36 +0000 https://shlomi-noach.github.io/blog/?p=1269#comment-13732 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

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key/comment-page-1#comment-13151 Wed, 05 May 2010 02:43:35 +0000 https://shlomi-noach.github.io/blog/?p=1269#comment-13151 @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…

]]> By: hadov https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key/comment-page-1#comment-13124 Tue, 04 May 2010 19:05:14 +0000 https://shlomi-noach.github.io/blog/?p=1269#comment-13124 wasn’t there any subset of the 5 colomns that was unique by itself?
(any superset of a unique key is unique as well)

]]> By: Gerry https://shlomi-noach.github.io/blog/mysql/reducing-locks-by-narrowing-primary-key/comment-page-1#comment-13117 Tue, 04 May 2010 17:14:40 +0000 https://shlomi-noach.github.io/blog/?p=1269#comment-13117 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

]]>