{"id":524767,"date":"2010-04-12T14:52:00","date_gmt":"2010-04-12T18:52:00","guid":{"rendered":"http:\/\/37signals.com\/svn\/posts\/2266-keeping-your-slave-warm"},"modified":"2010-04-12T14:52:00","modified_gmt":"2010-04-12T18:52:00","slug":"keeping-your-slave-warm","status":"publish","type":"post","link":"https:\/\/mereja.media\/index\/524767","title":{"rendered":"Keeping your slave warm"},"content":{"rendered":"<p>In 2009 we ran into some problems when failing over to the Basecamp slave database. Basecamp relies on a keeping large working set of recently-accessed data in its InnoDB buffer cache for speed. Normal MySQL replication only sends writes, not reads, to the slave. How could we ensure the data in the slave&#8217;s cache is up to date?<\/p>\n<p>We contracted <a href=\"http:\/\/www.percona.com\/\">Percona<\/a> to build a solution into their <a href=\"http:\/\/www.maatkit.org\/\">Maatkit<\/a> toolset based on their <a href=\"http:\/\/www.mysqlperformanceblog.com\/2009\/02\/01\/fast-mysql-master-master-failover-with-select-mirroring\/\">experiments with <span class=\"caps\">SELECT<\/span> query mirroring<\/a>. It involves a clever usage of <a href=\"http:\/\/www.tcpdump.org\/\">tcpdump<\/a> to capture and replay <span class=\"caps\">SELECT<\/span> queries from the master to the slave database.<\/p>\n<p>Here&#8217;s the resulting command.<\/p>\n<pre>\n<code>\n\/usr\/bin\/mk-query-digest --statistics --iterations 4 --run-time 15m --type tcpdump\n--filter '$event-&gt;{arg} &#38;&#38; $event-&gt;{arg} =~ m\/^SELECT\/i'\n--statistics --execute \\\"h=db-slave,P=3306,u=slave,p=password,D=production\\\" \n--execute-throttle 70,30,5\n<\/code>\n<\/pre>\n<p>The tcpdump utility captures MySQL traffic from the master and feeds the data into the mk-query-digest script. This script filters only the <span class=\"caps\">SELECT<\/span> queries and executes them on the slave database. The throttle argument sets the percentage of time the script should execute queries on the slave, how often to check that value, and a percentage probability that queries will be skipped when the threshold is exceeded.<\/p>\n<p>Here&#8217;s some sample statistical output:<\/p>\n<pre>\n<code>\n# execute_executed      124668\n# throttle_checked_rate     29\n# throttle_rate_avg      29.84\n# throttle_rate_ok          29\n<\/code>\n<\/pre>\n<p>According to these values, the script didn&#8217;t reach the 70% query execution threshold we set. Our queries are executing on the slave cleanly.<\/p>\n<p>Since we began using this tool we switched production database servers without a performance reduction.<\/p>\n<div class=\"feedflare\">\n<a href=\"http:\/\/feeds.feedburner.com\/~ff\/37signals\/beMH?a=cF59B4UqScs:3v_nr-vvyzg:yIl2AUoC8zA\"><img decoding=\"async\" src=\"http:\/\/feeds.feedburner.com\/~ff\/37signals\/beMH?d=yIl2AUoC8zA\" border=\"0\"><\/img><\/a> <a href=\"http:\/\/feeds.feedburner.com\/~ff\/37signals\/beMH?a=cF59B4UqScs:3v_nr-vvyzg:7Q72WNTAKBA\"><img decoding=\"async\" src=\"http:\/\/feeds.feedburner.com\/~ff\/37signals\/beMH?d=7Q72WNTAKBA\" border=\"0\"><\/img><\/a>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>In 2009 we ran into some problems when failing over to the Basecamp slave database. Basecamp relies on a keeping large working set of recently-accessed data in its InnoDB buffer cache for speed. Normal MySQL replication only sends writes, not reads, to the slave. How could we ensure the data in the slave&#8217;s cache is [&hellip;]<\/p>\n","protected":false},"author":6727,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-524767","post","type-post","status-publish","format-standard","hentry","category-news"],"_links":{"self":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/posts\/524767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/users\/6727"}],"replies":[{"embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/comments?post=524767"}],"version-history":[{"count":0,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/posts\/524767\/revisions"}],"wp:attachment":[{"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/media?parent=524767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/categories?post=524767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mereja.media\/index\/wp-json\/wp\/v2\/tags?post=524767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}