{"id":4895,"date":"2012-05-03T16:17:19","date_gmt":"2012-05-03T14:17:19","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4895"},"modified":"2012-05-03T16:17:19","modified_gmt":"2012-05-03T14:17:19","slug":"webinar-review-zero-downtime-schema-changes-in-mysql","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/webinar-review-zero-downtime-schema-changes-in-mysql","title":{"rendered":"Webinar review: Zero-Downtime Schema Changes In MySQL"},"content":{"rendered":"<p>Yesterday I attended the <a href=\"http:\/\/www.percona.com\/webinars\/2012-05-02-zero-downtime-schema-changes-in-mysql\/\">Zero-Downtime Schema Changes In MySQL<\/a> webinar by Baron Schwartz, Percona (<em>do you say &#8220;attended&#8221; for something you listened to from your home office?<\/em>)<\/p>\n<p>I was keen to learn about possible enhancements and improvements of <a href=\"http:\/\/www.percona.com\/doc\/percona-toolkit\/2.1\/pt-online-schema-change.html\">pt-online-schema-change<\/a> over <a href=\"http:\/\/openarkkit.googlecode.com\/svn\/trunk\/openarkkit\/doc\/html\/oak-online-alter-table.html\">oak-online-alter-table<\/a>. Here are my impressions:<\/p>\n<p>The base logic of <em>pt-online-schema-change<\/em> is essentially the same as of <em>oak-online-alter-table<\/em>. You create a ghost\/shadow table, create complex triggers, copy in chunks, freeze and swap. Both work on any type of <strong>PRIMARY KEY<\/strong> (<em>oak-online-alter-table<\/em> can work with any <strong>UNIQUE KEY<\/strong>, I&#8217;m not sure about <em>pt-online-schema-change<\/em> on this), be it an <strong>INTEGER<\/strong>, other type, or a multi column one.<\/p>\n<p>However, <em>pt-online-schema-change<\/em> also adds the following:<\/p>\n<ul>\n<li>It supports <strong>FOREIGN KEY<\/strong>s (to some extent). This is something I&#8217;ve wanted to do with <em>oak-online-alter-table<\/em> but never got around to it. Foreign keys are very tricky, as Baron noted. With child-side keys, things are reasonably manageable. With parent-side this becomes a nightmare, sometimes unsolvable (when I say &#8220;unsolvable&#8221;, I mean that under the constraint of having the operation run in a non-blocking, transparent way).<\/li>\n<li>Chunk size is auto-calculated by the script. This is a cool addition. Instead of letting the user throwing out numbers like <strong>1,000<\/strong> rows per chunk, in the hope that this is neither too small nor too large, the tool monitors the time it takes a chunk to complete, then adjusts the size of next chunk accordingly. Hopefully this leads to a more optimized run, where locks are only held for very short periods, yet enough rows are being processed at a time.<\/li>\n<li>The tool looks into replicating slaves to verify they&#8217;re up to the job. If the slave lags too far, the tool slows down the work. This is an excellent feature, and again, one that I always wanted to have. Great work!<\/li>\n<\/ul>\n<p>So the three bullets above are what I understand to be the major advantages of Percona&#8217;s tool over <em>oak-online-alter-table<\/em>.<\/p>\n<h4>Q &amp; A<\/h4>\n<p>The presentation itself was very good, and Baron answered some questions. There was one question he did not answer during the webinar, nor here, and I though I may pop in and answer it. Although I can&#8217;t speak for the coders of <em>pt-online-schema-change<\/em>, I safely assume that since the logic follows that of <em>oak-online-alter-table<\/em>, the same answer applies in the case of Percona&#8217;s toolkit.<!--more--><\/p>\n<p>But, first, a background question (asked and answered during the webinar):<\/p>\n<p><strong>Q<\/strong>: What if my table already has <strong>AFTER TRIGGER<\/strong>s?<\/p>\n<p><strong>A<\/strong>: Then this can&#8217;t work out. The table must not have triggers.<\/p>\n<p>Which led to the next question:<\/p>\n<p><strong>Q<\/strong>: Can&#8217;t the tool use <strong>BEFORE TRIGGER<\/strong>s instead?<\/p>\n<p>Imagine a <strong>MyISAM<\/strong> table being altered to <strong>InnoDB<\/strong> (this is a major task for which my tool was built). Suppose we used a <strong>BEFORE<\/strong> trigger on an <strong>INSERT<\/strong>, but the <strong>INSERT<\/strong> failed. That would make the shadow table inconsistent with the original table. Which is the reason why the trigger must be an <strong>AFTER<\/strong> trigger.<\/p>\n<p>With <strong>InnoDB<\/strong> this should not be an issue, since triggers and actions all play within the same transaction, so all succeed or all fail. I have this nagging feeling at the back of my head which says I&#8217;ve already had thoughts on this and have found a problem with <strong>InnoDB<\/strong> tables as well. I can&#8217;t put my finger on it now, so no comment on this one at this stage.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Yesterday I attended the Zero-Downtime Schema Changes In MySQL webinar by Baron Schwartz, Percona (do you say &#8220;attended&#8221; for something you listened to from your home office?) I was keen to learn about possible enhancements and improvements of pt-online-schema-change over oak-online-alter-table. Here are my impressions: The base logic of pt-online-schema-change is essentially the same as [&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":[34],"class_list":["post-4895","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-openark-kit"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1gX","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4895","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=4895"}],"version-history":[{"count":7,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4895\/revisions"}],"predecessor-version":[{"id":4902,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4895\/revisions\/4902"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4895"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4895"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4895"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}