{"id":1819,"date":"2009-05-04T20:50:13","date_gmt":"2009-05-05T00:50:13","guid":{"rendered":"http:\/\/blogs.n1zyy.com\/n1zyy\/?p=1819"},"modified":"2009-05-04T20:50:13","modified_gmt":"2009-05-05T00:50:13","slug":"sync_binlog-on-ext3","status":"publish","type":"post","link":"https:\/\/blogs.n1zyy.com\/n1zyy\/2009\/05\/04\/sync_binlog-on-ext3\/","title":{"rendered":"sync_binlog on ext3"},"content":{"rendered":"<p>I&#8217;ve <a href=\"http:\/\/blogs.n1zyy.com\/n1zyy\/2009\/01\/27\/mysql-replication-lessons-learned\/\">mentioned<\/a> before about how the <tt>sync_binlog<\/tt> setting in MySQL can be especially slow on ext3. Of course, I wasn&#8217;t the first to discover this; the <a href=\"http:\/\/www.mysqlperformanceblog.com\/2009\/01\/21\/beware-ext3-and-sync-binlog-do-not-play-well-together\/\">MySQL Performance Blog<\/a> mentioned it months ago.<\/p>\n<p>I was reading through some of the slides I mentioned in my last post, and remembered that I&#8217;d left <tt>sync_binlog<\/tt> off on an in-house replicated slave. You&#8217;re able to set it on the fly, so a quick <tt>set global sync_binlog=1<\/tt> was all it took to ensure we flushed everything to disk.<\/p>\n<p>A while later I blogged about <a href=\"http:\/\/dag.wieers.com\/home-made\/dstat\/\">dstat<\/a> and thought to run it on the in-house MySQL slave. I was confused to notice that the CPU was about 50% idle, 50% &#8220;wai&#8221; (I\/O wait). For a box that&#8217;s just replaying INSERTs from production, that&#8217;s really bad. Below is a graph of load on the system. Care to guess when I enabled <tt>sync_binlog<\/tt>?<\/p>\n<div id=\"attachment_1820\" style=\"width: 613px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1820\" class=\"size-full wp-image-1820\" src=\"http:\/\/blogs.n1zyy.com\/n1zyy\/files\/2009\/05\/syncbinlog.png\" alt=\"Performace with and without sync_binlog enabled\" width=\"603\" height=\"304\" \/><p id=\"caption-attachment-1820\" class=\"wp-caption-text\">Performace with and without sync_binlog enabled<\/p><\/div>\n<p>Disk I\/O does roughly the same thing, but it&#8217;s less pronounced, &#8220;only&#8221; doubling in volume. But the difference is still pretty insane!<\/p>","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve mentioned before about how the sync_binlog setting in MySQL can be especially slow on ext3. Of course, I wasn&#8217;t the first to discover this; the MySQL Performance Blog mentioned it months ago. I was reading through some of the &hellip; <a href=\"https:\/\/blogs.n1zyy.com\/n1zyy\/2009\/05\/04\/sync_binlog-on-ext3\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,19,24],"tags":[],"class_list":["post-1819","post","type-post","status-publish","format-standard","hentry","category-linux-tips","category-performance","category-rants-raves"],"_links":{"self":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/posts\/1819","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/comments?post=1819"}],"version-history":[{"count":0,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/posts\/1819\/revisions"}],"wp:attachment":[{"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/media?parent=1819"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/categories?post=1819"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.n1zyy.com\/n1zyy\/wp-json\/wp\/v2\/tags?post=1819"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}