{"id":1269,"date":"2010-05-04T08:46:01","date_gmt":"2010-05-04T06:46:01","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1269"},"modified":"2010-05-04T10:35:12","modified_gmt":"2010-05-04T08:35:12","slug":"reducing-locks-by-narrowing-primary-key","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/reducing-locks-by-narrowing-primary-key","title":{"rendered":"Reducing locks by narrowing primary key"},"content":{"rendered":"<p>In a period of two weeks, I had two cases with the exact same symptoms.<\/p>\n<p>Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that <strong>INSERT<\/strong>s were causing the locks.<\/p>\n<p>In both cases, tables were InnoDB. In both cases, there was a <strong>PRIMARY KEY<\/strong> on the combination of all <strong>5<\/strong> columns. And in both cases, there was no clear explanation as for why the <strong>PRIMARY KEY<\/strong> was chosen as such.<\/p>\n<h4>Choosing a proper PRIMARY KEY<\/h4>\n<p>Especially with InnoDB, which uses clustered index structure, the <strong>PRIMARY KEY<\/strong> is of particular importance. Besides the fact that a bloated <strong>PRIMARY KEY<\/strong> bloats the entire clustered index and secondary keys (see: <a href=\"http:\/\/code.openark.org\/blog\/mysql\/the-depth-of-an-index-primer\">The depth of an index: primer<\/a>), it is also a source for locks. It&#8217;s true that any <strong>UNIQUE KEY<\/strong> can serve as a <strong>PRIMARY KEY<\/strong>. But not all such keys are good candidates.<!--more--><\/p>\n<h4>Reducing the locks<\/h4>\n<p>In both described cases, the solution was to add an <strong>AUTO_INCREMENT<\/strong> column to serve as the <strong>PRIMARY KEY<\/strong>, and have that <strong>5<\/strong> column combination under a secondary <strong>UNIQUE KEY<\/strong>. The impact was immediate: no further locks on that table were detected, and query responsiveness turned very high.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[26,14,52],"class_list":["post-1269","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-indexing","tag-innodb","tag-performance"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-kt","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1269","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1269"}],"version-history":[{"count":15,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1269\/revisions"}],"predecessor-version":[{"id":2380,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1269\/revisions\/2380"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1269"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1269"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1269"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}