<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jean-François Gagné on Medium]]></title>
        <description><![CDATA[Stories by Jean-François Gagné on Medium]]></description>
        <link>https://medium.com/@jeanfrancois.gagne?source=rss-4e2e27287ef8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*3fJ6CrB4AiMS6SK0uCYMyw.png</url>
            <title>Stories by Jean-François Gagné on Medium</title>
            <link>https://medium.com/@jeanfrancois.gagne?source=rss-4e2e27287ef8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 22:11:35 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jeanfrancois.gagne/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Evaluating MySQL Parallel Replication Part 4, Annex: Under the Hood]]></title>
            <link>https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-4-annex-under-the-hood-eb456cf8b2fb?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/eb456cf8b2fb</guid>
            <category><![CDATA[mariadb]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[replication]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Tue, 27 Sep 2016 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:22:53.584Z</atom:updated>
            <content:encoded><![CDATA[<h4>This is the annex to <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html">Evaluating MySQL Parallel Replication Part 4: More Benchmarks in Production</a>.</h4><p>There is no introduction or conclusion to this post, only landing sections: reading this post without its context will probably be very hard. You should start with the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html">main post</a> and come back here for more details.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a_4Jdom8odaFUGa-COf4Lg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/AX-ma0j6elM?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Master Wen</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><h3>Implementation Details of MariaDB Optimistic Parallel Replication</h3><p><strong>Rollbacks and Retries</strong></p><p>When transactions T1 to T20 are run concurrently by optimistic parallel replication, if T19 blocks T2, T19 will be killed (rolled-back) for unblocking T2 (T2 must commit before T19). In the current implementation, T19 will be retried once T18 completed. It looks like this could be optimized.</p><p>I thought that retrying T19 as early as T2 completes could improve optimistic replication speed. Kristian Nielsen, the implementer of parallel replication in MariaDB, was kind enough to implement a patch with <a href="https://lists.askmonty.org/pipermail/commits/2015-July/008133.html">more aggressive retries</a>. However, with quicker retries, I got slower results than with delayed retries. So it looks like once a conflict is detected (T19 blocks T2), the probability of another conflict is high, and the gain in retrying T19 earlier is outweighed by the cost of other rollbacks of T19.</p><p><strong>DML vs DDL and Non-Transactional Storage Engines</strong></p><p>The assumption for optimistic parallel replication to work is that a transaction that causes a conflict can be killed and retried. This is the case for InnoDB DML (Data Manipulation Language: INSERT, UPDATE, DELETE, ...) but it is not the case with MyISAM.</p><p>As a transaction involving a MyISAM table (or another non-transactional storage engine) cannot be rolled-back, it is not safe to run those transactions optimistically. When such transaction enters the optimistic parallel replication pipeline, the replication applier will wait for all previous transactions to complete before starting the transaction that cannot be rolled-back. The following transactions could still be run optimistically if they are exclusively using a transactional storage engine (if they can be rolled-back). This means that DMLs that cannot be rolled-back act as a pre-barrier in the parallel replication pipeline.</p><p>In MariaDB, DDL (Data Definition Language: [CREATE | ALTER | TRUNCATE | DROP | ...] TABLE, ...) are also (still) impossible to rollback. So they will also act as a pre-barrier in the parallel replication pipeline. Moreover, DDL are also preventing all next transactions to be optimistically applied because a DML is not safe to run at the same time as a DDL on the same table. So, not only DDLs act as a pre-barrier, but they are also acting as a post-barrier.</p><p><strong>Different Optimistic Parallel Replication Modes</strong></p><p>MariaDB 10.1 optimistic parallel replication can be run in two <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#slave_parallel_mode">slave_parallel_mode</a>: <em>optimistic</em> and <em>aggressive</em>. In the optimistic mode, some heuristics are used to avoid needless conflicts. In the aggressive mode, those heuristics are disabled.</p><p>One of the <a href="https://mariadb.com/kb/en/mariadb/parallel-replication/#optimistic-mode-of-in-order-parallel-replication">heuristics</a> of the optimistic mode is the following: if a transaction executed a row-lock <strong>wait</strong> on the master, it will not be run in parallel on the slave. The behavior is unclear when intermediate masters are used:</p><ul><li>An intermediate master with slave_parallel_mode=none (single threaded) will not have any row-lock wait. So it looks like for a slave of such intermediate master, the optimistic mode would behave the same way as the aggressive mode.</li><li>An intermediate master with slave_parallel_mode=minimal (<a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">slave group committing</a>) will have a row-lock wait for each group commit. So it looks like for a slave of such intermediate master, the optimistic mode would behave the same as the conservative mode.</li><li>An intermediate master with slave_parallel_mode=conservative should generate very few row-lock wait (only for conflicts that will generate retries). So it looks like for a slave of such intermediate master, the optimistic mode will behave mostly the same as the aggressive mode.</li><li>The number of row-lock waits is hard to predict on an intermediate master in optimistic or aggressive mode. So the behavior of a slave of such intermediate master is hard to predict.</li></ul><p>As we are doing tests on a slave of an intermediate master, the optimistic mode is not very interesting to test. It would generate results similar to the aggressive mode if the intermediate master was running in single-threaded or conservative mode, or similar to the conservative mode if the intermediate master was running in minimal mode. Without a true master running MariaDB 10.1, the only tests that we think make sense are with slave_parallel_mode=aggressive.</p><p>This is a good opportunity to remind that <strong>intermediate masters are bad for parallel replication</strong>. As shown in a <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Part 1</a>, intermediate master are doing a poor job at transmitting parallelism information from their master to their slaves. The solution presented in the previous post still applies: use <a href="https://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html">Binlog Servers</a>.</p><h3>Environments</h3><p>As in the previous posts (<a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Part 1</a>, <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">Part 2</a> and <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html">Part 3</a>), we are using the same four environments. Each environment is composed of five servers. For slave_parallel_mode=none and slave_parallel_mode=conservative, only four of the five servers are needed and are organized as below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DtVMvyVopA13J9aIjNxxcQ.png" /></figure><p>The A to C servers are strictly the same as <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#environments">before</a>. The D server has the same hardware specification as before but it is now running MariaDB 10.1.8 <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#fn1">[1]</a>. This means that the conservative results will use the same parallelism information (group commit) as for the tests from <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html">Part 3</a> (we are re-using the same binary logs as the previous tests).</p><p>For optimistic parallel replication to work, a MariaDB 10.1 slave must be connected to a MariaDB 10.1 master <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#fn2">[2]</a>, hence the introduction of a fifth (E) server. For slave_parallel_mode=aggressive, D is replicating from E as shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3KM9pxTCxZuJSIH0Lc6CEg.png" /></figure><p>The hardware specifications of E are not important because it is only serving binary logs. It was built as a clone of D that was upgraded to MariaDB 10.1. Replication was then started from C with slave_parallel_mode=none. This way, we produced 10.1 binary logs so slave_parallel_mode=aggressive will work on D.</p><p>The full test methodology is the same as for the previous tests and can be found in <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#environments">Part 3</a>. The server and database configurations are mostly the same as in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#environments_properties">previous tests</a> with the following modifications:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ADVWXpNwk8leME6VPFPcXQ.png" /></figure><p>Property E1 E2 E3 E4 InnoDB Buffer Pool Size 71 GB 162 GB 71 (from 76) GB 57 (from 76) GB InnoDB Log Size 64 (from 1) GB 32 (from 1) GB 16 (from 4) GB 16 (from 1) GB</p><p>The motivations for the above changes are the following:</p><ul><li>The InnoDB Buffer Pool Size was reduced for E3 and E4 because we were missing RAM to increase slave_parallel_threads to the number we wanted to test (more threads need more available RAM).</li><li>The InnoDB Log Size was increased because checkpointing was a bottleneck during our tests <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#fn3">[3]</a>.</li></ul><h3>Results</h3><p>In the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html#results">main post</a>, speedup graphs are presented for each of the four environments. Here, the underlying data for those graphs is presented.</p><p>The <em>SB</em>, <em>HD</em> and <em>ND</em> notations are explained in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html#reminders">main post</a>.</p><p>The first line of the table below shows the time taken for the single-threaded execution with slave_parallel_mode (<em>SPM</em>) set to none. Then, for slave_parallel_threads (<em>SPT</em>) values of 5, 10, 20 and 40, we have results with both non-optimistic (slave_parallel_mode=conservative) and optimistic (slave_parallel_mode=aggressive) executions. Then, for slave_parallel_threads values of 80, 160, 320, 640, 1280, 2560 and 5120, we have results only for optimistic executions. Note that we cannot have meaningful results for non-optimistic runs with slave_parallel_threads greater than 40 because the maximum group size on C was 35 (see <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#environments">Part 3</a> for more details).</p><p>The times presented below are in the format <em>hours:minutes.seconds</em> and they represent the delay needed to process 24-hours of transactions. The number in <strong>bold</strong> is the speedup achieved from the single-threaded run.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F2i87fSmNbY8xOZ-rtiHcg.png" /><figcaption>Execution Times and Speedups</figcaption></figure><h3>Graphs during Tests</h3><p>If you spot something we might have missed in the graphs below, please post a comment. Those graphs include the number of commits per second, CPU stats, Read IOPS and percentage of Retried Transaction for all tests.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*WvW4OCC1A5BGwx_O.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*Jv6k9M8vp9UBO2wU.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*ZujEU_63lma0YJz_.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*CiiEKga2OTv6DYQW.gif" /><figcaption>Graphs # 1a: E1 Stats — Slave with Binary Logs — High Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*MfUgFR-Byw4156Qr.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*Fto-tPCJKuI2QOWf.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*PPu-lp6Say3KsIGV.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*YhNGIPUfSfzOeEc0.gif" /><figcaption>Graphs # 1b: E1 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*wRj32IaPl1zoxncJ.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*aC8qfZrCJ5tx8rKu.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*y5PcCc58rAdCPqwR.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*YLg5xs6CO6mpq3KA.gif" /><figcaption>Graphs # 2a: E2 Stats — Slave with Binary Logs — High Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*aeX4Q9jKzJHn6IYe.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*GQ0YRyxyeYy4TvXg.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*d_9nGPST18_NtlBi.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*KX1LwVqX_t_xSulJ.gif" /><figcaption>Graphs # 2b: E2 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*1lJSfEgFH7zR9YNm.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*kJEWaok8spQKSpn2.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*PUt_KO6iTu3Vc-WN.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*jhX28oXOqezEgq18.gif" /><figcaption>Graphs # 3a: E3 Stats — Slave with Binary Logs — High Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*QS7J6mFCCATJjbqP.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*Jm8YKm65wFErejxM.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*nZ6UkCY-3QzaCHd1.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*OXA4TFwMi3BQubpe.gif" /><figcaption>Graphs # 4a: E4 Stats — Slave with Binary Logs — High Durability</figcaption></figure><p>[1] At the time of the publication of this post, the latest release of MariaDB 10.1 is 10.1.17. Our tests were done with MariaDB 10.1.8 because they were run a long time ago (I am a little embarrassed to be that late in my blog post editing).</p><p>[2] In the implementation of optimistic parallel replication in MariaDB 10.1, the master is responsible for flagging DDL and non-transactional DML and to pass this information to slaves via the binary logs. This is why a MariaDB 10.1 master is needed to enable optimistic parallel replication on a slave. This also means that for optimistic parallel replication to work, master and slaves must have compatible storage engines for DML: if a DML is transactional on the master, it must be transactional on the slave. So a master using InnoDB and a slave using MyISAM will not work.</p><p>[3] Because the InnoDB Log Size was too small in our previous tests, those tests were run in non-optimal conditions. The results presented in this post should be considered more accurate.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=eb456cf8b2fb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-4-annex-under-the-hood-eb456cf8b2fb">Evaluating MySQL Parallel Replication Part 4, Annex: Under the Hood</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Evaluating MySQL Parallel Replication Part 4: More Benchmarks in Production]]></title>
            <link>https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-4-more-benchmarks-in-production-49ee255043ab?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/49ee255043ab</guid>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[mariadb]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Tue, 27 Sep 2016 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:23:18.576Z</atom:updated>
            <content:encoded><![CDATA[<h4>Parallel replication is a highly-expected feature of MySQL available in MariaDB 10.0 and in MySQL 5.7. We already presented benchmark results with MariaDB 10.0 in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html">previous post</a> of the series; in this post, we will look at a new type of replication introduced in MariaDB 10.1: <em>optimistic parallel replication</em>.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a_4Jdom8odaFUGa-COf4Lg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/AX-ma0j6elM?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Master Wen</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>This post has an annex: <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html">Under the Hood</a>. Benchmarking is a <a href="https://www.percona.com/live/london-2014/sessions/performance-bechmarking-tips-tricks-and-lessons-learned">complex art</a> and reporting results accurately is even harder. If all the details were put in a single article, it would make a very long post. The links to the annex should satisfy readers eager for more details.</p><p>Optimistic parallel replication is built on top of the previous implementation of parallel replication. To fully understand optimistic parallel replication in MariaDB 10.1, we must dive back in the implementation details of parallel replication in MariaDB 10.0.</p><h3>Key Design Decision: In-Order Commit</h3><p>In <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Part 1</a> of the series, we explained how parallel replication works in MariaDB 10.0 and in early version of MySQL 5.7. In short, both MySQL 5.7 and MariaDB 10.0 identify parallelism on the master and send this information to the slaves via the binary logs <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html#fn1">[1]</a>. However, MariaDB and MySQL differ in the way transactions are committed on slaves.</p><p>In MariaDB, transactions run in parallel on slaves are committed in the same order as they appear in the binary logs of the master <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html#fn2">[2]</a>. So if a slave runs T1 and T2 in parallel and those transactions appear in this order in the binary logs of the master, the thread running T2 will wait for T1 to complete before committing T2 (even if T2 is ready to commit before T1).</p><p>By default, a MySQL slave running transactions in parallel is simply committing transactions as they complete without enforcing any ordering. In the above example, T2 will commit before T1. This could (and will most of the time) generate different transactions ordering in the binary logs of the slaves <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html#fn3">[3]</a> <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html#fn4">[4]</a>.</p><p>When committing in-order, a transaction that needs to commit first (T1) can be blocked by another transaction that will commit later (T2). It is surprising that such dependencies are generated by transactions committing at the same time on the master, but this can still happen. This is a deadlock situation: T1 is blocked by T2 and T2 is waiting for T1.</p><p>The storage engine will not detect this deadlock because both dependencies are not under its control. The first dependency is known to the storage engine: T2 is holding a resource needed by T1. However, the second dependency is in the server, more precisely in the parallel replication code: T2 cannot commit before T1. To resolve this situation, the in-order parallel replication applier must detect that T2 is blocking T1 and it must kill T2. Once killed, T2 will release its resources, allowing T1 to proceed. T2 will then be retried.</p><p>This transaction killing and retrying does not happen very often in MariaDB 10.0, but its implementation is essential to avoid blocking replication. The occurrence of those retries can be monitored with the <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-status-variables/#slave_retried_transactions">slave_retried_transactions</a> global status. Below is a graph from such monitoring where we can see that three retries were needed for a four-minute interval. This is especially small considering that ~2.5K transactions were run per seconds on this slave (three retries for ~600,000 transactions).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*7xxemdFgfy0livGu.png" /><figcaption>Graph # 0: Retried Transactions on a MariaDB 10.0 Slave</figcaption></figure><p>Once this deadlock detection and resolution is implemented, the following becomes very straightforward:</p><blockquote><em>Run all transactions in parallel, with in-order commit making sure that the data is consistent, and with deadlock detection avoiding replication to block.</em></blockquote><p>This is called <a href="https://mariadb.com/kb/en/mariadb/parallel-replication/#optimistic-mode-of-in-order-parallel-replication"><em>optimistic parallel replication</em></a> and is implemented in MariaDB 10.1. This type of parallel replication does not rely on the master to identify parallelism. The slave tries to run as many transactions in parallel as possible to the limit of the <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#slave_parallel_threads">slave_parallel_threads</a> parameter while enforcing in-order commit. If no conflict happens, maximum speed is achieved. If a conflict (deadlock) is detected, a transaction that was optimistically run will be rolled-back to unblock in-order commit (this transaction will be retried later).</p><p>As long as there are not too many conflicts, or as long as the cost of rollbacks does not outweigh the benefit of running more transactions in parallel, optimistic parallel replication should give good results. What is yet unknown is how those results will compare to the <a href="https://mariadb.com/kb/en/mariadb/parallel-replication/#conservative-mode-of-in-order-parallel-replication"><em>conservative parallel replication</em></a> (conservative is the MariaDB 10.1 name for the parallel replication in MariaDB 10.0). To answer that question, tests need to be done. The results are presented below.</p><p>Of course, the full implementation details are more complex (more details can be found in the annex about <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#rollbacks">rollback and retries</a>, <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#optimistic_n_trx">DML vs DDL and non-transactional storage engines</a> and the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#optimistic_modes">different optimistic parallel replication modes</a>). However, this introduction is enough to understand the rest of this post, so let us go directly to benchmarking.</p><h3>The Test</h3><p>The test is the same as in the previous post: <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#results">catching up with 24 hours of transactions</a>. The four test environments are also very similar, their description can be found in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#environments">annex</a>. In the graphs below, we compare speedups for non-optimistic executions (<a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#slave_parallel_mode">slave_parallel_mode</a>=conservative) with speedups for optimistic execution (slave_parallel_mode=aggressive). We had to choose aggressive over optimistic because the latter will not give good results replicating via an intermediate master (more details can be found in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#optimistic_modes">annex</a>).</p><p>In the graphs, the Y axis shows the speedups with a value of one being the reference time without using parallel replication (slave_parallel_mode=none). The X axis shows the number of threads used: notice the logarithmic scale. The curve stopping at 40 on the X axis is showing the non-optimistic results. The data used to plot those graphs can be found in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#results">annex</a>.</p><p>Some reminders from the previous post:</p><ul><li>Parallelism identification (<a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">slave group commit</a>) was done with a maximum group size of 35, so increasing the number of threads past 40 for non-optimistic tests is not interesting.</li><li>The binary logging configuration had a noticeable <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#obs_str">impact on performance</a>. The catch-up times are generally longer when enabling log-slave-updates, and disabling binary logging is not such a big win. The results below are obtained with binary logging enabled but with log-slave-updates disabled, referred to as <em>SB</em> in <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#results">Part 3</a>.</li><li>The <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#workloads">workload</a> of the four environments are different: E2 is a CPU-bound workload, E1 is also mostly CPU-bound but with some cache misses, E3 is a mixed CPU and IO workload, and E4 is an IO-bound workload.</li><li>As E4 is an IO-bound workload (mostly cache misses in the InnoDB buffer pool), reducing durability - referred to as <em>ND</em> in <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#results">Part 3</a> - does not get a noticeable improvement (similar behavior has been observed for E3). For that reason, the results presented below only include high durability for E3 and E4 (referred to as <em>HD</em>).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*ARGpM36cipwYOi-K.png" /><figcaption>Graph # 1a: E1 SB-HD — Conservative vs Aggressive Speedups<br> (CPU-bound workload with some cache misses)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*Mm9SvQudcf9ODoHZ.png" /><figcaption>Graph # 1b: E1 SB-ND — Conservative vs Aggressive Speedups<br> (CPU-bound workload with some cache misses)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*42ZTRFMTyA-K5w0I.png" /><figcaption>Graph # 2a: E2 SB-HD — Conservative vs Aggressive Speedups<br> (CPU-bound workload)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*xUm4bQCWx_n3DjxR.png" /><figcaption>Graph # 2b: E2 SB-ND — Conservative vs Aggressive Speedups<br> (CPU-bound workload)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*-3QL6gr5f0eLyfqv.png" /><figcaption>Graph # 3a: E3 SB-HD — Conservative vs Aggressive Speedups<br> (Mixed CPU and IO workload)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*w5YTcFO4hOZJ2JuQ.png" /><figcaption>Graph # 4a: E4 SB-HD — Conservative vs Aggressive Speedups<br> (IO-bound workload)</figcaption></figure><h3>Discussion</h3><p>The first surprise comes from observing speedups with very high number of threads. Even though the servers used for the tests only have 12 hyper-threaded cores for a total of 24 threads, speedups are still increasing past 80 threads for all configurations and up to 2560 threads for E3. Obviously, we cannot use more processing power when raising slave_parallel_threads from 40 to 80, so those growing speedups cannot be explained simply by using more CPU.</p><p>We think that those speedups are caused by replication prefetching. By increasing the number of threads, transactions that need data that is not in cache will trigger a read from disk earlier (prefetching). Triggering that read earlier is a big win and does not consume much CPU because the thread will go in IOWait state. Even if that transaction causes a conflict and is rolled-back, the data will be in cache for the retry. Also in this case, the cost of the rollback is insignificant compared to the gain of having the data in cache for the retry, so extra conflicts are not a problem. This concept is not new; it is known as replication prefetching and has already been discussed before by <a href="https://www.xaprb.com/blog/2008/01/13/how-pre-fetching-relay-logs-speeds-up-mysql-replication-slaves/">Baron Schwartz</a>, <a href="https://dom.as/2011/12/03/replication-prefetching/">Domas Mituzas</a> and <a href="https://yoshinorimatsunobu.blogspot.co.uk/2011/10/making-slave-pre-fetching-work-better.html">Yoshinori Matsunobu</a>.</p><p>For all environments, aggressive parallel replication can produce much better speedups than conservative parallel replication. It is also very nice to see more significant speedups with low durability on E1 and E2 (conservative parallel replication is not giving good speedups there). It is absolutely obvious that aggressive parallel replication is a great improvement over conservative parallel replication.</p><p>I suspect (and I hope) that there are still bottlenecks to be removed. Optimistic parallel replication is a great improvement over conservative parallel replication, but we are still far from the speedup of 6 to 10 that <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#expectations">we are looking for</a> (maybe those expectations are unrealistic…).</p><p>Something that might slow down our tests are DDL (Data Definition Language: [CREATE | ALTER | TRUNCATE | DROP | ...] TABLE, ...) because DDL instructions are blocking the replication pipeline. As explained in detail in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-under_the_hood.html#optimistic_n_trx">annex</a>, before being able to run a DDL, all previously started transactions need to commit. Moreover, before starting any transactions after a DDL, the DDL must complete. Below are some extracts from SHOW GLOBAL STATUS during the test:</p><ul><li>E1: 197,499 Com_create_table</li><li>E1: 1148 Com_truncate</li><li>E1: 489 Com_drop_table</li><li>E1: 484 Com_rename_table</li></ul><p>So in the quickest run of E1 (7060 seconds), we were doing in average 27 CREATE TABLE per second <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html#fn5">[5]</a>. That obviously cannot be good for parallel replication. To ease identifying such problems, I opened <a href="https://jira.mariadb.org/browse/MDEV-10664">MDEV-10664 - Add statuses about optimistic parallel replication stalls</a>.</p><h3>Conclusion</h3><p>As stated in <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#conclusion">Part 3</a> (but is still worth repeating): it is possible to test MariaDB parallel replication even if the master is an old version of MySQL/MariaDB. In our case, our masters were running MySQL 5.6, but the same could be applied to other versions.</p><p>Overall, the optimistic parallel replication shows very promising results: almost all speedups are better than conservative parallel replication.</p><p>The biggest surprise was to see speedup increasing past 80 threads. We could have thought that more threads than processing units would slow things down, but it is not the case. This is probably caused by threads being most of the time in a waiting state: either waiting for a previous transaction to commit, or waiting for an IO. For a pure CPU workload, we can expect contention and this is probably what explains the thrashing for E2.</p><p>Another surprise is that the best speedup (3.78) is achieved for an IO bound workload (E4). In this case, the biggest win of parallel replication seems to be getting more read IOPS from the disks subsystem by scheduling many IOs in parallel. In this environment (and probably in others), optimistic parallel replication with a high number of threads is acting as a replication prefetcher.</p><p>Up to now, all our tests were done using magnetic disks. It is unclear how both conservative and optimistic parallel replication would behave with solid state disks (SSDs). More tests are needed to understand how parallel replication will behave with SSDs.</p><p>If you are interested in this topic and would like to learn more, I will be giving a talk about <a href="https://www.percona.com/live/plam16/sessions/mysqlmariadb-parallel-replication-inventory-use-cases-and-limitations">MySQL and MariaDB Parallel Replication</a> at <a href="https://www.percona.com/live/plam16/">Percona Live Amsterdam</a> in October. All talks by <a href="https://www.percona.com/live/plam16/sponsors/bookingcom">Booking.com</a> are:</p><p>We will also be hosting the <a href="https://www.eventbrite.com/e/community-dinner-bookingcom-tickets-26929408586">Percona Live Community Dinner</a> on October 4th. You can also meet us there if you want to know more about the cool things we do at Booking.com.</p><p>[1] MySQL 5.7 and MariaDB 10.0 have slightly different implementation of parallelism identification on the master. MariaDB 10.0 uses the binary log group commit optimization as accurately described in <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Part 1</a> (the group commit id is shown in MariaDB mysqlbinlog output as <a href="https://www.slideshare.net/JeanFranoisGagn/mysql-parallel-replication-inventory-usecase-and-limitations/16">cid</a>). From version 5.7.6, MySQL is tagging each transaction with <a href="https://www.slideshare.net/JeanFranoisGagn/mysql-parallel-replication-inventory-usecase-and-limitations/30">two logical timestamps</a> (last_committed and sequence_number in MySQL mysqlbinlog output).</p><p>[2] MariaDB also has an <a href="https://mariadb.com/kb/en/mariadb/parallel-replication/#out-of-order-parallel-replication">out-of-order</a> parallel replication functionality based on its <a href="https://mariadb.com/kb/en/mariadb/gtid/#the-domain-id">GTID</a> implementation. This type of parallel replication might not commit transactions in the same order as they appear in the binary logs of the master. To take advantage of out-of-order parallel replication, hints must be given by the application to advertise what can be run in parallel. This is not the type of parallel replication we are discussing in this post (we are focusing on the in-order type).</p><p>[3] By allowing out-of-order transaction commit on slaves, MySQL can alleviate a problem previously discussed in <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#additional_discussions">Part 3</a> where too small a value for slave_parallel_threads could give suboptimal speedups.</p><p>[4] The <a href="https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_preserve_commit_order">slave-preserve-commit-order</a> option allows enabling in-order commit in MySQL but this option needs <a href="https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_log-slave-updates">log-slave-updates</a>. I opened Bug#<a href="https://bugs.mysql.com/bug.php?id=75396">75396</a> to have this restriction removed as needing log-slave-updates to enable slave-preserve-commit-order looks like a step backward:</p><ul><li>The goal of parallel replication is to get faster replication</li><li>According to most of our tests, log-slave-updates slows down replication</li></ul><p>Moreover, keeping a copy of the master transactions on slaves (log-slave-updates) is not needed when <a href="https://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html">Binlog Servers</a> are already keeping an exact copy of the binary logs of the master. So needing log-slave-updates to enable slave-preserve-commit-order is a restriction we could do without.</p><p>[5] This number of CREATE TABLE is very high. It looks like the pattern CREATE TABLE IF NOT EXISTS is very common for E1. Checking for table existence and avoiding the CREATE TABLE when it already exists might be a big win for parallel replication in E1. And for the people that do not believe 27 CREATE TABLE per second is possible (I also had to check it by myself):</p><pre>$ for i in $(seq 4978 5003); do<br>  mysqlbinlog $(printf &quot;binlog.%06d&quot; $i) |<br>    grep -i &quot;CREATE TABLE IF NOT EXISTS&quot;; done | wc -l<br>211753</pre><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=49ee255043ab" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-4-more-benchmarks-in-production-49ee255043ab">Evaluating MySQL Parallel Replication Part 4: More Benchmarks in Production</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Abstracting Binlog Servers and MySQL Master Promotion without Reconfiguring all Slaves]]></title>
            <link>https://medium.com/booking-com-infrastructure/abstracting-binlog-servers-and-mysql-master-promotion-without-reconfiguring-all-slaves-44be1febc8a0?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/44be1febc8a0</guid>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Wed, 09 Sep 2015 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:24:23.113Z</atom:updated>
            <content:encoded><![CDATA[<h4>In a MySQL replication deployment, the master is a single point of failure. To recover after the failure of this critical component, a common solution is to promote a slave to be the new master. However, when doing so using classic methods, the slaves need to be reconfigured. This is a tedious operation in which many things can go wrong. We found a more simple way to achieve master promotion using Binlog Server. Read on for more details.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JiwK8QhWGVvlVghywCNG3g.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/LqKhnDzSF-8?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Joshua Sortino</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>When a master fails in a MySQL replication deployment, the classic way to promote a slave to be the new master is the following:</p><ol><li>Find the most up-to-date slave.</li><li>If the most up-to-date slave is not a good candidate master, level a suitable candidate with the most up-to-date slave <a href="https://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html#fn1">[1]</a>.</li><li>Repoint the remaining slaves to the new master.</li></ol><p>The procedure above needs to contact all slaves in step #1, and to reconfigure all slaves in step #3. This becomes increasingly complex in <a href="https://booking.com/">Booking.com</a> environments where we have very wide, and still growing, replication topologies; it is not uncommon to have more than fifty (and sometimes more than a hundred) slaves replicating from the same master. Many things can go wrong when tens of slaves need to be contacted and reconfigured:</p><ul><li>some slaves might be down for maintenance or for taking a backup,</li><li>some slaves could be temporarily unreachable for other reasons,</li><li>and a few slaves could be processing a big backlog of relay logs (including delayed slaves), which will make them hard/unsuitable to reconfigure.</li></ul><p>A way to reduce the complexity of master promotion is presented below, but to get there, we must first give some context about Binlog Servers and abstract them into a service.</p><h3>Reminders about Binlog Servers</h3><p>In a previous post, I described how to take advantage of <a href="https://blog.booking.com/mysql_slave_scaling_and_more.html">Binlog Server</a> to perform master promotion without GTIDs and without log-slave-updates, while still requiring to reconfigure all slaves. To do this, the slaves must replicate through a Binlog Server. This gives us the following deployment with a single Binlog Server:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gt9lNqG--OTHzcS6uFZzeQ.png" /></figure><p>or with redundant Binlog Servers:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wGe3ePXK-Tz3TpnVjAfU-w.png" /></figure><p>or with more than one site with redundant Binlog Servers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b4WEapUly7LXll6MztfN4Q.png" /></figure><p>These schemas are becoming increasingly complex - let&#39;s simplify them by abstracting the Binlog Servers.</p><h3>Binlog Server Abstraction</h3><p>By hiding the Binlog Servers in an abstracted layer, which I call the Distributed Binlog Serving Server (<em>DBSS</em>), a deployment on three sites becomes the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JFZ_fEkOBRTaxwwtdyHAjw.png" /></figure><p>Of course, the DBSS is built with many Binlog Servers. One way to build the layer above minimizing the number of slaves served by the master is described below. Other ways to build this layer can be imagined <a href="https://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html#fn2">[2]</a>, but let&#39;s stick to this one, for now.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y1Ldl09pGiJFTTZs6rFmGQ.png" /></figure><p>In the deployment above, using one DNS A record per site resolving to both Xi and Yi, if a Binlog Server fails, its slaves will reconnect to the other one. If the Yi Binlog Server fails, nothing more needs to be done. If the Xi Binlog Server fails, the corresponding Yi must be repointed to the master. This repointing is easy, as, by design, a Binlog Server is identical to its master. Only the destination server must be changed, and the binary log filename and position stay the same.</p><h3>When the Master Fails...</h3><p>Equipped with the above implementation of the DBSS, in a situation when the master fails, we end up with the state below; each site might be at a different position in the binary log stream of the failed master.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ckUc4tpJDRY5ZQI1MF2yQw.png" /></figure><p>The first step of master promotion is to level the Binlog Servers in the DBSS. To do so, the most up-do-date Binlog Server must be found and all other Binlog Servers must be chained to it. In the deployment above, only three servers must be contacted, which is much easier than tens of slaves. If the most up-to-date Binlog Server is X2, levelling the Binlog Servers consists of the temporary replication architecture below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2OuAGt59VsbMCqiqSDLzQg.png" /></figure><p>Levelling should happen very quickly (if it does not, one of the Binlog Servers is lagging, which should not happen). After that, the slaves will quickly follow. Once a slave is up to date (this actually does not need levelling, a slave of X2 or Y2 could have been promoted before levelling), master promotion can be performed. Shown below, a slave from the third site on the right has been chosen to be the new master, but any slave on any of the three sites could have been used.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y1Ldl09pGiJFTTZs6rFmGQ.png" /></figure><p>Note that the other slaves have not been touched: they are still connected to their Binlog Server. This means that this solution works well even if one of the slaves is unavailable during master promotion. This solution also works very well with delayed or lagging slaves, as those slaves are simply not good candidates for becoming the new master. For some time, the lagging slaves will process the binary logs of the old master that are still stored on the Binlog Servers.</p><h3>The Trick for not Reconfiguring every Slave</h3><p>Promoting a slave to be the new master in a DBSS deployment requires working some magic on a slave to make its binary log position (SHOW MASTER STATUS) matches what is expected by the Binlog Servers. Let&#39;s take an example: if the last binary log stored on the levelled Binlog Servers is binlog.000163, we could repoint the Binlog Servers to a new master if the SHOW MASTER STATUS of this new master is at the beginning of binary log filename binlog.000164.</p><p>When doing that promotion, from the point of view of the Binlog Servers, their master is simply restarted with a different server_id and server_uuid. From the point of view of the slaves, they are processing the binary logs of the old master (up to and including binlog.000163) followed by the binary logs of the new master (starting at binlog.000164).</p><p>So, the trick is to have our candidate master at the right binary log position. This can be made possible by:</p><ol><li>configuring all nodes with binary logging enabled,</li><li>with all identical log-bin value (binlog in the example above),</li><li>and without enabling log-slave-updates.</li></ol><p>Configuration #3 above allows us to assume that the master will consume binary log filenames much faster than the slaves. This way, the slaves will always be behind the master in their binary log filenames <a href="https://blog.booking.com/abstracting_binlog_servers_and_mysql_master_promotion_wo_reconfiguring_slaves.html#fn3">[3]</a>. As such, bringing a slave to the right binary log filename is as simple as doing FLUSH BINARY LOGS in a loop until the slave is in the correct position. To avoid this loop from taking too much time, we can run a cron job on our slaves that makes sure they are not too far away from their master (maximum ten binary logs away, for an example).</p><h3>Summary of Master Promotion</h3><p>In the following replication deployment, with log-bin=binlog and with log-slave-updates disabled:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JFZ_fEkOBRTaxwwtdyHAjw.png" /></figure><p>If M fails, we first level the Binlog Servers in the DBSS.</p><p>If M fails, we first level the Binlog Servers in the DBSS.</p><p>Once this is done, and let’s take T1 as our candidate master, we need to perform the following on it:</p><ol><li>FLUSH BINARY LOGS until the binary log filename follows the last one from the levelled DBSS,</li><li>PURGE BINARY LOGS TO &lt;latest binary log file&gt;,</li><li>RESET SLAVE ALL.</li></ol><p>The step #2 above drops all binary logs on the new master that could conflict with the one from the previous master. The binary logs of the old master are stored on the DBSS and we must be sure to avoid having similar, but misleading data, on the new master.</p><p>We now have this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hnPj7nxt8A4MuUx-RB8SLQ.png" /></figure><p>where we repoint the DBSS to T1 to get the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eT3Rt8rlasfub7SsB0zlTg.png" /></figure><p>and we have achieved master promotion without reconfiguring all slaves.</p><h3>A Cleaner Way</h3><p>The trick above works well, but preforming FLUSH BINARY LOGS in a loop is not the cleanest of solutions. It would be much better if there was a way to set the binary log to the desired filename in a single operation. With this idea in mind, we created the following two feature requests:</p><ul><li><a href="https://bugs.mysql.com/bug.php?id=77438">https://bugs.mysql.com/bug.php?id=77438</a></li><li><a href="https://mariadb.atlassian.net/browse/MDEV-8355">https://mariadb.atlassian.net/browse/MDEV-8355</a></li></ul><p>MariaDB 10.1.6 is already implementing a <a href="https://mariadb.com/kb/en/mariadb/reset-master/">RESET MASTER TO</a> syntax. Let’s hope that Oracle will provide something similar in MySQL 5.7.</p><h3>What about the Software?</h3><p>This idea and procedure is all well and good, but it is not very useful if you cannot use it yourself. The currently available version of the Binlog Server, the <a href="https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-as-a-replication-proxy/">MaxScale Binlog Router plugin</a>, does not yet implement all the configuration hooks needed to make this procedure easy. Booking.com is currently working with <a href="https://mariadb.com/">MariaDB</a> to implement the missing hooks in a new version of MaxScale. We are in the last testing phase of a Binlog Router plugin that support the following:</p><ul><li>STOP SLAVE, START SLAVE, SHOW MASTER STATUS, SHOW SLAVE STATUS,CHANGE MASTER TO: these new commands allow easier configuration of the Binlog Server.</li><li>The CHANGE MASTER TO command not only allows to easily chain Binlog Servers, but also to bootstrap a Binlog Server without editing the configuration file. Moreover, this command allows to repoint MaxScale to a new master at binary log filename N+1, effectively enabling to perform master promotion.</li><li><em>Transaction safety</em>: when the master fails, the Binlog Server could have downloaded a partial transaction. If we replace the master with a slave, this transaction should not be sent to slaves. So this feature of the next version of MaxScale will make sure such partial transactions are not sent downstream.</li><li><em>DBSS identity</em>: the initial design of the Binlog Server was intended to impersonate the master, and did not consider swapping the master at the top of the hierarchy. In a DBSS deployment, swapping the master should not be made visible to slaves, so the Binlog Servers should present the slave with a different server_id and server_uuid to those of the master. The next version of the MaxScale Binlog Router supports that <em>virtual master</em> feature.</li></ul><p>This next version of the MaxScale Binlog Router will be generally available once we are done with the testing. Stay tuned on the MariaDB web site for the announcement and the failover procedure. In the meantime, you can still experiment with master promotion without reconfiguring all slaves by using the current version of MaxScale and following this <a href="https://jfg-mysql.blogspot.nl/2015/04/maxscale-binlog-server-howto-poc-master-promotion.html">proof of concept</a> procedure.</p><p>If you are interested in this topic and would like to learn more, I am giving a talk about <a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/binlog-servers-bookingcom">Binlog Servers</a> at <a href="https://www.percona.com/live/europe-amsterdam-2015/">Percona Live</a> Amsterdam. Feel free to grab me after the talk, catch me at the Booking.com booth (#205) or share a drink with me at the<a href="https://www.eventbrite.com/e/community-dinner-bookingcom-tickets-17901675393">Community Dinner</a>, to exchange thoughts on this subject. (You can also post a comment below.)</p><p>I will also be giving a talk about <a href="https://events.rainfocus.com/oow15/catalog/oracle.jsp?search=CON4098&amp;search.event=openworldEvent">Binlog Servers</a> at <a href="https://www.oracle.com/openworld/index.html">Oracle Open World</a> in San Francisco at the end of October.</p><p>One last thing: if you want to know more about other cool things we do at Booking.com, I suggest you come to our other talks at <a href="https://www.percona.com/live/europe-amsterdam-2015/">Percona Live</a> Amsterdam in September:</p><ul><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/virtues-boring-technology">The Virtues of Boring Technology</a></li><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/combining-redis-and-mysql-store-http-cookie-data">Combining Redis and MySQL to store HTTP cookie data</a></li><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/events-storage-and-analysis-riak-bookingcom">Events storage and analysis with Riak at Booking.com</a></li><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/encrypted-mysql-backups-and-instant-recoverability-large-scale">Encrypted MySQL Backups and instant recoverability on large scale</a></li><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/unicode-and-mysql">Unicode and MySQL</a></li><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/managing-and-visualizing-your-replication-topologies-orchestrator">Managing and Visualizing your replication topologies with Orchestrator</a></li><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/your-clone-army-better-scalability-through-more-database-servers">Your Clone Army: Better scalability through more database servers</a></li><li><a href="https://www.percona.com/live/europe-amsterdam-2015/sessions/riding-binlog-deep-dissection-replication-stream">Riding the Binlog: an in Deep Dissection of the Replication Stream</a></li></ul><p>[1] Slave levelling can be done with <a href="https://code.google.com/p/mysql-master-ha/">MHA</a>, with <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html">MySQL 5.6</a> or <a href="https://mariadb.com/kb/en/mariadb/global-transaction-id/">MariaDB 10.0</a> GTIDs, or with <a href="https://code.openark.org/blog/mysql/speaking-at-percona-live-pseudo-gtid-and-easy-replication-topology-management">Pseudo-GTIDs</a> when using earlier versions of MySQL and MariaDB.</p><p>[2] If we were not concerned about WAN bandwidth, all Binlog Servers could be directly connected to the master. Another solution could be to connect all master-local Binlog Servers directly to the master and to use the chained strategy for remote Binlog Servers. (This hybrid deployment could be well-suited to a semi-sync deployment, but I am diverging from the subject of this post.)</p><p>[3] The same can be achieved when using log-slave-updates, by using smaller<a href="https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_max_binlog_size">max_binlog_size</a> on the master than on all the slaves.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=44be1febc8a0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/abstracting-binlog-servers-and-mysql-master-promotion-without-reconfiguring-all-slaves-44be1febc8a0">Abstracting Binlog Servers and MySQL Master Promotion without Reconfiguring all Slaves</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Evaluating MySQL Parallel Replication Part 3: Benchmarks in Production]]></title>
            <link>https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-3-benchmarks-in-production-db5811058d74?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/db5811058d74</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[mariadb]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Wed, 22 Jul 2015 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:24:04.713Z</atom:updated>
            <content:encoded><![CDATA[<h4>Parallel replication is a much-expected feature of MySQL. It is available in MariaDB 10.0 and in MySQL 5.7. In this 3rd post of the series, we present benchmark results from <a href="https://www.booking.com/">Booking.com</a> production environments.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a_4Jdom8odaFUGa-COf4Lg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/AX-ma0j6elM?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Master Wen</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Note: this post has an annex: <a href="https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-3-annex-under-the-hood-9391c3609b57?source=collection_archive---------9----------------">Under the Hood</a>. Benchmarking is a <a href="https://www.percona.com/live/london-2014/sessions/performance-bechmarking-tips-tricks-and-lessons-learned">complex art</a> and reporting results accurately is even harder. If all the details were put in a single article, it would make a very long post. The links to the annex should satisfy readers eager for more details.</p><p>Parallel replication is on its way and with it comes the hope that more transactions can be run on a master without introducing slave lag. But it remains to be seen whether this dream will come true — will parallel replication hold its promise or will there be surprises after its deployment? We would like to know whether or not we can count on that feature in the future.</p><p>To get answers, nothing is better than experimenting with the technology. Benchmark results have already been published (<a href="https://kristiannielsen.livejournal.com/18435.html">MariaDB 10.0</a>: 10 times improvement; and <a href="https://www.clusterdb.com/mysql-replication/mysql-replication-multi-threaded-slave-just-got-a-whole-lot-faster">MySQL 5.7</a>: 3 to 6 times improvement) but results might be different in our production environments. The best test would be to run parallel replication on our real workloads but this is not trivial. To be able to run transactions in parallel, a slave needs parallelism information from the master (for more details, see <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Part 1</a>). With a master in an older version (MySQL 5.6 in our case), slaves do not have this information.</p><p>Luckily, we can use <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html"><em>slave group commit</em></a> on an intermediate master to identify transactions that can be run in parallel. The trick is to execute transactions sequentially on a slave, but to delay their commit. While the commit is delayed, the next transaction is started. If the two transactions are non-conflicting, the second could complete and the two transactions would commit together. In this scenario, grouping has succeeded (the two transactions committed in a single group) and parallelism is identified (as they commit together, the transactions are non-conflicting, thus can be run in parallel on slaves). For more details on slave group commit, see <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">Part 2</a>.</p><p>Our benchmarks are established within four production environments: E1, E2, E3, and E4. Each of these environments is composed of one production master, with some intermediate masters, and a leaf slave. A complete description of those environments can be found in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#environments">annex</a>.</p><p>Slave group commit identifies less parallelism than a parallel execution on a master would identify (details in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#group_commit_slave_vs_master">Group Commit: Slave vs. Master</a> section of the annex). Even so, decent group sizes are obtained, as shown in the group commit size <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#group_commit_graphs">graphs</a> in the annex. Usually we have group sizes of at least 5, most of the time they are larger than 10, and sometimes they are as big as 15 or even 20. These will allow us to test parallel replication with real production workload.</p><p>Before closing this long introduction, let’s talk a little about our expectations. InnoDB is getting better at using many cores (<a href="https://dimitrik.free.fr/blog/archives/2015/04/mysql-performance-pushing-yet-more-far-scalability-limits-with-mysql-57.html">RO</a> and <a href="https://dimitrik.free.fr/blog/archives/2014/10/indeed-mysql-57-rocks-oltp_rorw-1table-benchmarks.html">RW</a> benchmark results) but single-threaded replication gets in the way of pushing more writes in a replicating environment. Without parallel replication, a single core can be used on a master to perform writes without incurring slave lag. This is disappointing as servers come with 12 and more cores (only one can be used for writes). Ideally, we would like to use a significant percentage of the cores of a server for writes (25% could be a good start). So speedups of 3 would be good results at this point (12 cores), and speedups of 6 and 10 would be needed in the near future (24 and 40 cores).</p><h3>The Test: Catching Up with 24 Hours of Transactions</h3><p>Our benchmark scenario is as follows: after restoring a backup of the database, starting MariaDB, waiting for the buffer pool to be loaded, and running the slave for at least 12 hours, we measure the length of time it has taken to process 24 hours of production transactions.</p><p>The tests are run in the following binary log configurations:</p><ul><li><strong>Intermediary Master</strong> (<em>IM</em>): both binary logs and log-slave-updates are enabled.</li><li><strong>Slave with Binary Logs</strong> (<em>SB</em>): binary logs are enabled but log-slave-updates is disabled.</li><li><strong>Standard Slave</strong> (<em>SS</em>): both binary logs and log-slave-updates are disabled.</li></ul><p>And in the following durability configurations:</p><ul><li><strong>High Durability</strong> (<em>HD</em>): sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1.</li><li><strong>No Durability</strong> (<em>ND</em>): sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2 (also described/known as <em>relaxed durability</em>).</li></ul><p>For each of those configurations (6 in total: <em>IM-HD</em>, <em>IM-ND</em>, <em>SB-HD</em>, <em>SB-ND</em>, <em>SS-HD</em>, and <em>SS-ND</em>), the tests are run with different values of <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#slave_parallel_threads">slave_parallel_threads</a> (<em>SPT</em>). The full results are presented in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#results">annex</a> and the most interesting results are presented below (<em>SB-HD</em> and <em>SB-ND</em>). The times presented are in the format <em>hours:minutes.seconds</em>. Below the time taken to process 24-hours of transactions, the speedup achieved from the single-threaded run is presented in <strong>bold</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nWNVKBYvOS8aw-wxWsV73A.png" /><figcaption>Execution Times and Speedups for Slave with Binary Logs</figcaption></figure><p>In the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#graphs">Graph during Tests</a> section of the annex, you can find many details about the different test runs.</p><h3>Discussion</h3><p>There are lots of things to say about those results. Let’s start with observations that are not related to parallel replication (SPT=0):</p><ul><li><strong>Obs1</strong>: Standard Slave results (without binary logs) are very close to the results of Slave with Binary Logs (without log-slave-updates) (details in the <a href="https://jgagne-base6.dev.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#results">annex</a>).</li><li><strong>Obs2</strong>: log-slave-updates has visible cost for E1 (time difference between <em>IM-HD</em> and <em>SB-HD</em>), a less obvious but still noticeable cost for E2 and E3, and it is a win for E4 (that last one is disturbing, the numbers are in the <a href="https://jgagne-base6.dev.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#results">annex</a>).</li><li><strong>Obs3</strong>: relaxing durability is a huge win for E1 and E2, a more limited win for E3, and a much smaller one for E4.</li></ul><p>With reference to <strong>Obs1</strong> above, this shows that binary logs should probably not be disabled on slave: the cost is almost inexistent and the wins are big (tracing errant transactions and being a candidate for master promotion). However, slaves with log-slave-updates are slower than slaves with only binary logs enabled (<strong>Obs2</strong> above), so log-slave-updates should be avoided when possible. Binlog Servers can be used to replace log-slave-updates for intermediate masters, see <a href="https://blog.booking.com/mysql_slave_scaling_and_more.html">MySQL Slave Scaling</a> for more details (see also <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Better Parallel Replication for MySQL</a> for an explanation why log-slave-updates is bad on intermediate masters for parallel replication).</p><p>With reference to <strong>Obs3</strong> above, this can be explained by the different workload of the four environments (more details about the workloads can be found in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#workloads">annex</a>):</p><ul><li>E2 is a CPU-bound workload (the dataset fits in RAM).</li><li>E1 is also mostly CPU-bound but with some cache misses in the InnoDB buffer pool, so it needs a page fetch from disk before doing a write.</li><li>E3 is a mixed CPU and IO workload (more cache misses in the InnoDB buffer pool but still with enough cache hit to get a good commit throughput).</li><li>E4 is an IO-bound workload (mostly cache misses).</li></ul><p>Relaxing durability on CPU-bound workloads achieves good throughput improvements, but this does not happen on IO-bound workloads.</p><p>Now, let’s focus on parallel replication. The Standard Slave results (<em>SS-HD</em> and <em>SS-ND</em>) are not worth discussing as they are very close to the Slave with Binary Logs results (<strong>Obs1</strong> above). We will also not discuss Intermediate Master results (<em>IM-HD</em> and <em>IM-ND</em>) as they should be replaced by Binlog Servers. So all observations below are made on the results of Slave with Binary Logs (<em>SB-HD</em> and <em>SB-ND</em>):</p><ul><li><strong>Obs4</strong>: the best speedup (~2.10) is in E2 with high durability. E1 follows with a speedup of ~1.56 (always with high durability).</li><li><strong>Obs5</strong>: the speedups for E4 are modest (~1.29) and the results are almost identical for both durability settings.</li><li><strong>Obs6</strong>: for E1 and E2, the speedups with no durability are almost non-existent (less than 1.10).</li><li><strong>Obs7</strong>: for both E1 and E2, relaxing durability with single-threaded replication leads to faster execution than enabling parallel replication.</li><li><strong>Obs8</strong>: the results for E3 are halfway between E1/E2 and E4: both <em>SB-HD</em> and <em>SB-ND</em> get some modest speedups from parallel replication (like E4 and opposite to E1/E2) and relaxing durability makes things run a little faster (like E1/E2 and opposite to E4), but not to the point where single-threaded low durability is faster than multi-threaded high durability.</li></ul><p>All those observations point to the importance of the workload in parallel replication speedups:</p><ul><li>CPU-bound workloads seem to get modest speedups in high-durability configurations.</li><li>Relaxing durability for CPU-bound workloads looks like a better option than enabling parallel replication on a high-durability configuration.</li><li>IO-bound workloads get more limited speedups.</li></ul><p>Our first reaction is disappointment: the speedups are not as high as expected. Don’t get us wrong: faster is always better, especially when the only thing to do is to upgrade the software, which we will do anyway eventually. However, having only 25% more writes on a master (or 110% depending on which environment we look at) will not help us in the long term. Parallel replication is not the solution (at least not the only solution) that will allow us to stop/avoid sharding.</p><h3>Ideas and Future Work</h3><p>We have a hypothesis explaining the modest speedups: <strong>long-running transactions</strong>. In the presence of long-running transactions, the parallel replication pipeline on the slave stalls. Let’s take the following six transactions committing on the master in two commit groups (B for begin and C for commit):</p><pre>--------Time--------&gt;<br>T1:  B-----------C<br>T2:           B--C<br>T3:           B--C<br>T4:           B--C<br>T5:              B--C<br>T6:              B--C</pre><p>Running those transactions on a single-threaded slave takes 33 units of time (time scale is at the bottom):</p><pre>----------------Time----------------&gt;<br>T1:  B-----------C<br>T2:               B--C<br>T3:                   B--C<br>T4:                       B--C<br>T5:                           B--C<br>T6:                               B--C<br>              1         2         3<br>     123456789012345678901234567890123</pre><p>Running those transactions on a multi-threaded slave with SPT=4 takes 17 units of time:</p><pre>---------Time--------&gt;<br>T1:  B-----------C<br>T2:  B-- . . . . C<br>T3:  B-- . . . . C<br>T4:  B-- . . . . C<br>T5:               B--C<br>T6:               B--C<br>              1<br>     12345678901234567</pre><p>So we barely achieve a speedup of 2 (and the second commit group does not even contain a large transaction). The low speedup is explained by T1 being much bigger than the other transactions in the group. So our intuition is that to get better speedup with parallel replication, all transactions should be of similar size, and bigger transactions should be broken down into smaller ones (when possible).</p><p>We have many of those big transactions in our workload at Booking.com. Most of our design choices predate MySQL 5.6, where a commit was expensive. Reducing the number of commits was a good optimization at that time, so doing many changes in a single transaction was a good thing. Now, with binary log group commit, this optimization is less useful but does not harm. However, this optimization is very bad for parallel replication.</p><p>There are at least two other things to discuss from those results but this post is already too long, so you will have to go in the annex to read the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-under_the_hood.html#additional_discussions">Additional Discussions</a>.</p><h3>Conclusion</h3><p>It is possible to test parallel replication with true production workload, even if the master is running an old version of MySQL. Thanks to <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">slave group commit</a> in MariaDB 10.0, we can identify parallelism on intermediate master and enable parallel replication on a slave. Even if this parallelism identification is not as good as it would be on a master, we get decent group sizes.</p><p>Our CPU-bound workloads are getting speedups of ~1.56 to ~2.10 with high-durability constraints. This is a little disappointing: we would like to have more than two cores busy applying writes on slaves. Our guess is that better speedup could be obtained by hunting down large transactions, but that still needs to be verified. At this point, and for this type of workload, our tests show that relaxing durability is a better optimization than enabling parallel replication. Finally, with relaxed durability, parallel replication shows almost no improvement (4% to 6% improvement), and it is still unknown if hunting down large transactions and splitting them would result in better speedups.</p><p>Our IO-bound workloads are getting speedups of ~1.23 to ~1.29, which is also disappointing but expected because it is hard to fight against seek time of magnetic disks. In this type of workload, relaxed durability setting benefits from parallel replication. However, at high enough parallelism on the slave and for this type of workload, relaxing durability is not very beneficial. It is hard to tell what types of improvement would come from hunting down large transactions for this type of workload.</p><p>The next step on parallel replication evaluation would be to try <a href="https://mariadb.com/kb/en/mariadb/parallel-replication/#optimistic-mode-of-in-order-parallel-replication">optimistic parallel replication</a>. This will make a good fourth part in the series.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=db5811058d74" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-3-benchmarks-in-production-db5811058d74">Evaluating MySQL Parallel Replication Part 3: Benchmarks in Production</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Evaluating MySQL Parallel Replication Part 3, Annex: Under the Hood]]></title>
            <link>https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-3-annex-under-the-hood-9391c3609b57?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/9391c3609b57</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[mariadb]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Wed, 22 Jul 2015 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:23:35.974Z</atom:updated>
            <content:encoded><![CDATA[<h4>This is the annex to <a href="https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-3-benchmarks-in-production-db5811058d74">Evaluating MySQL Parallel Replication Part 3: Benchmarks in Production</a>.</h4><p>There is no introduction or conclusion to this post, only landing sections: reading this post without its context will probably be very hard. You should start with the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html">main post</a> and come back here for more details.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a_4Jdom8odaFUGa-COf4Lg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/AX-ma0j6elM?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Master Wen</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><h3>Environments</h3><p>As in the two previous posts (<a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Part 1</a> and <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">Part 2</a>), we are using the same four environments. Each environment is composed of four servers:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DtVMvyVopA13J9aIjNxxcQ.png" /></figure><ul><li>A is a true production master running MySQL 5.6,</li><li>B is an intermediate master running MariaDB 10.0.16 without parallel replication enabled (slave_parallel_threads = 0),</li><li>C is an intermediate master running MariaDB 10.0.16 with <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">slave group commit</a> enabled (slave_parallel_threads &gt; 1, binlog_commit_wait_count &gt; 1 and binlog_commit_wait_usec &gt; 1),</li><li>D is the slave where parallel replication tests are run with different parameters.</li></ul><p>The slave group commit parameters on C are the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9o9Cwy86Nre9aGobdzC8bg.png" /></figure><p>To monitor parallelism identification (<a href="https://mariadb.com/kb/en/mariadb/group-commit-for-the-binary-log/">group committing</a>) on C, the <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-status-variables/#binlog_commits">binlog_commits</a> and <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-status-variables/#binlog_group_commits">binlog_group_commits</a> global statuses are gathered regularly.</p><p>The D slave was stopped on a Thursday between 17:20 and 17:30 and a backup of the database was taken. Every test starts by recreating the filesystem that hosts the database and restoring the backup on this empty filesystem.</p><p>When stopping the database (for backup), the buffer pool was dumped using <a href="https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_dump_at_shutdown">innodb_buffer_pool_dump_at_shutdown</a>. When starting MariaDB (after backup restoration), <a href="https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_load_at_startup">innodb_buffer_pool_load_at_startup</a> was used to load the buffer pool. No test was started before the buffer pool was fully loaded.</p><p>Every test then started by running the D slave until Friday at 06:00 (a little more than 12 hours). This allowed further warming up of InnoDB, including giving work to purge and write threads.</p><p>Then, the D slave ran 24 hours of transactions where parallelism was identified using slave group commit on the C intermediate master. The number of commits and the group commit sizes on C for that period are shown in the graphs below for the four test environments.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*0KHniUbfAl2DlFqH.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*kWFqMx65h9eAgaTT.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*mdvuiQd4HP_UambM.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*XwpoXT1sI4mkWNnK.png" /></figure><p>Graphs # 0: Commits and Group Commit Sizes in the four Environments</p><p>The D servers have the following properties:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QKnaQ06f57juLRr5OqOUBQ.png" /></figure><p>To monitor the commit rate on D, the <a href="https://mariadb.com/kb/en/mariadb/server-status-variables/#com_commit">COM_COMMIT</a> global status is gathered regularly.</p><p>To prevent D from filling up its disk, <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#relay_log_space_limit">relay_log_space_limit</a> is set to 4 GB (some D do not have enough disk space to store all the binary logs of C).</p><h3>Group Commit: Slave vs. Master</h3><p>It is briefly mentioned in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#group_commit_slave_vs_master">main post</a> that <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html">slave group commit</a> identifies less parallelism than a true master would. More details are given here.</p><p>Let’s consider this execution timeline which shows a master where T5 is conflicting with one of the four previous transactions:</p><pre>------Time-----&gt;<br>T1:      B--C<br>T2:   B-----C<br>T3:   B-----C<br>T4:    B----C<br>T5:  B-- . . --C<br>T6:       B----C</pre><p>Using slave group commit and from the binary logs of the timeline above, parallelism can be identified in two groups as shown below.</p><pre>---------------Time----------------&gt;<br>T1:  B-- . . . . . . . . C<br>T2:     B----- . . . . . C<br>T3:           B----- . . C<br>T4:                 B----C<br>T5:                       B---- . . C<br>T6:                            B----C</pre><p>But if we run the master with delayed commit, we could get the execution timeline below, in which T6 passed T5 to join the first commit group.</p><pre>------Time-----&gt;<br>T1:      B-- . C<br>T2:   B----- . C<br>T3:   B----- . C<br>T4:    B---- . C<br>T5:  B-- . . . .--C<br>T6:       B----C</pre><p>So on a master, transactions blocked in their execution do not block other non-conflicting transactions from joining a commit group, but those block parallelism identification in slave group commit. This is why slave group commit is not as efficient at maximizing group size as a parallel execution on the master would be.</p><h3>Results</h3><p>As outlined in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#results">main post</a>, our tests are run in the following binary log configurations:</p><ul><li><strong>Intermediary Master</strong> (<em>IM</em>): binary logs and log-slave-updates enabled.</li><li><strong>Slave with Binary Logs</strong> (<em>SB</em>): binary logs enabled and log-slave-updates disabled.</li><li><strong>Standard Slave</strong> (<em>SS</em>): binary logs and log-slave-updates disabled.</li></ul><p>And in the following durability configurations:</p><ul><li><strong>High Durability</strong> (<em>HD</em>): sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1.</li><li><strong>No Durability</strong> (<em>ND</em>): sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2 (also described/known as <em>relaxed durability</em>).</li></ul><p>For each of those configurations (6 in total: <em>IM-HD</em>, <em>IM-ND</em>, <em>SB-HD</em>, <em>SB-ND</em>, <em>SS-HD</em>, and <em>SS-ND</em>), the tests are run with different values of <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#slave_parallel_threads">slave_parallel_threads</a> (<em>SPT</em>). The full results are presented in the following four tables. The times presented are in the format <em>hours:minutes.seconds</em>. Below the time taken to process 24 hours of transactions, the speedup achieved from the single-threaded run is presented (in <strong>bold</strong>) and when applicable, the speedup from the previous run is also presented (in normal font).</p><p>Normally, the tests are run at least twice and the shorter time is kept. This is relaxed for E1, E3, and E4 in the <em>SS-HD</em> and <em>SS-ND</em> configurations where only one run is performed: the results from E2 show that <em>SS-HD</em> and <em>SS-ND</em> are very similar to <em>SB-HD</em> and <em>SB-ND</em> respectively, and the limited results with E1, E3, and E4 are consistent with this observation. Moreover, again for E1, E3, and E4 in <em>SS-HD</em> and <em>SS-ND</em> and for the same reasons, some SPT values are skipped (the skipped values are shown as N/A in the tables below).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E4DrXNUPDF1fAHqGiavlxQ.png" /><figcaption>Table # 1: E1 Execution Times and Speedups</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*r5lwdO0CpxEEMDLdWbrHlA.png" /><figcaption>Table # 2: E2 Execution Times and Speedups</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fzu8hX0lzSbOzq0NB_vC7Q.png" /><figcaption>Table # 3: E3 Execution Times and Speedups</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8FYIB7RrrFtGQU_qA_zlyw.png" /><figcaption>Table # 4: E4 Execution Times and Speedups</figcaption></figure><h3>Graphs during Tests</h3><p>If you spot something we might have missed in the graphs below, please post a comment. Those graphs include the number of commits per second, CPU stats and Read IOPS for all environments, for the Slave with Binary Logs configuration (log-slave-updates disabled), in both durability settings (high and no/relaxed).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*O8junhhvSNhe0QVW.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*TVJT-SoWju4ttd1G.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*IA-uJisvDqYdRojw.gif" /><figcaption>Graphs # 1a: E1 Stats — Slave with Binary Logs — High Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*uILujVWZl8Ah5E-K.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*ydvahZOA38EiIK7V.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*JaXGQfa7ocrW6H8f.gif" /><figcaption>Graphs # 1b: E1 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*CGIxN1x-3dCumBFq.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*nVvHQ-PRfrzXaame.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*xnlo1SSxURNtHKQx.gif" /><figcaption>Graphs # 2a: E2 Stats — Slave with Binary Logs — High Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*a_o5AwygcWiMbaF5.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*zmOd635juhgRjExt.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*ugFtCOsgJi9jK6h8.gif" /><figcaption>Graphs # 2b: E2 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*1mBcanJ6e3WfcQyG.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*tCUNA4LogyDpoI0z.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*JPxwTRF2uuq6U2dq.gif" /><figcaption>Graphs # 3a: E3 Stats — Slave with Binary Logs — High Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*ElWvE9V9G9AKSxJr.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*55GLD33lAArKFbTp.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*Qx28vXyd7Xde9gGw.gif" /><figcaption>Graphs # 3b: E3 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*q81pBrtkY2hmhoIc.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*ex-VUdjUup6G7IbA.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*-Au_8bZgUrsSxCLo.gif" /><figcaption>Graphs # 4a: E4 Stats — Slave with Binary Logs — High Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*xoi4doAXfhJen3fd.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*j9bAjArd-jvbFI2M.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*-6asfAKnZsM9Fzil.gif" /><figcaption>Graphs # 4b: E4 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><h3>Workloads</h3><p>It is mentioned briefly in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#workloads">main post</a> that the four test environments have different workloads:</p><ul><li>E2 is a CPU-bound workload (the dataset fits in RAM).</li><li>E1 is also mostly CPU-bound but with some cache misses in the InnoDB buffer pool, needing a page fetch from disk before doing a write.</li><li>E3 is a mixed CPU and IO workload (more cache misses in the InnoDB buffer pool but still with enough cache hit to get a good commit throughput).</li><li>E4 is an IO-bound workload (mostly cache misses).</li></ul><p>We can confirm that E2 does not do much IO looking at IOWait and Read IOPS in the graphs below (commit throughput is also good).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*MLMFV1esdziJPBhp.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*sU6Pph6ZT_SeJofA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*zDq8X8NkdjM6Ee5G.png" /><figcaption>Graphs # 5: E2 is CPU-bound (good commit throughput, low IOWait and few Read IOPS).</figcaption></figure><p>E1 has a little more IOWait and Read IOPS but still not that much as you can see in the graphs below. However, commit throughput is smaller than E2, but still good (the transaction sizes are bigger).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*HmKciYWX8CLUF3nE.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*D3u2v4LoUHpf9qO2.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*W0tUK0NOWohscSa-.png" /><figcaption>Graphs # 6: E1 is also mostly CPU-bound (good commit throughput, low IOWait and few Read IOPS).</figcaption></figure><p>E4 is clearly an IO-bound workload: commit throughput is low, IOWait is high, and Read IOPS are high as shown in the graphs below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*C9dNzyd1g7fo7fiz.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*rRhPif9-Xk0-FYpl.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*DOttiyNeu-nZZgy8.png" /><figcaption>Graphs # 7: E4 is IO-bound (low commit throughput, high IOWait and high Read IOPS)</figcaption></figure><p>E3 has a much higher commit throughput than E4 but with lots of Read IOPS as shown in the graphs below. This makes us put this workload in a mixed CPU and IO-bound category (confirmed by a high IOWait but still lower than E4).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*QEfe7T1KPZxysVMt.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*TFSxqOfdhVT0CDHv.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*sBHdFb5CZjLbYCYj.png" /><figcaption>Graphs # 8: E3 is mixed CPU and IO-bound (respectable commit throughput, high IOWait and high Read IOPS)</figcaption></figure><p>Note: the Write IOPS are not as important because they are buffered in the RAID controller write cache. The Read IOPS are important because a write operation to the database that needs an InnoDB page fetch from disk is bounded by the disk seek time.</p><h3>Additional Discussions</h3><p><strong>Another Problem with Long-Running Transactions</strong></p><p>In the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#ideas">main post</a>, we discussed the impact of long-running transactions on the parallel replication pipeline (idle threads while the long transaction is running). When the number of slave threads is not smaller than the group commit size, the time taken to execute the group is bound by the time taken to run the longest transaction, as shown below.</p><pre>--------Time--------&gt;<br>T1:  B-----------C<br>T2:           B--C<br>T3:           B--C<br>T4:           B--C<br>T5:              B--C<br>T6:              B--C</pre><p>The transactions above on the master are executed as below on the slave with SPT=4.</p><pre>--------Time--------&gt;<br>T1:  B-----------C<br>T2:  B-- . . . . C<br>T3:  B-- . . . . C<br>T4:  B-- . . . . C<br>T5:               B--C<br>T6:               B--C<br>              1<br>     12345678901234567</pre><p>But if the number of slave threads is smaller than the group commit size, we get a longer run, as shown below (SPT=2):</p><pre>---------Time--------&gt;<br>T1:  B-----------C<br>T2:  B-- . . . . C<br>T3:               B--C<br>T4:               B--C<br>T5:                   B--C<br>T6:                   B--C<br>              1         2<br>     123456789012345678901</pre><p>So we lost 4 units of time in the global run by decreasing SPT. What is interesting is that there is enough time for executing both T3 and T4 while T1 executes (and while the thread running T2 is idle), so maybe it is possible to avoid this situation.</p><p>Note that MySQL 5.7 solves this problem by allowing <a href="https://mysqlhighavailability.com/preserve-masters-commit-order-on-slave/">out-of-order commit</a>. With <a href="https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#sysvar_slave_preserve_commit_order">slave-preserve-commit-order</a> set to 0 (the default), MySQL will commit T2 without waiting for T1 to commit. This will allow T3 to start executing while T1 is still running. However, this changes the behavior of the slave: T2 (and eventually T3 and T4) becomes visible before T1, which is not possible in single-threaded replication. Some people (including the author of this post) might be nervous about this and would prefer to set slave-preserve-commit-order to 1.</p><p>A future solution to manage long-running transactions and group size larger than SPT could be to delegate committing transactions. If the thread running T2 delegates its commit to another thread, it can start running T3 event if T2 is not committed. This could be an interesting optimization to implement in MariaDB or in MySQL.</p><p>(Note that the problem described above — group size larger than SPT — cannot explain the modest speedup observed in our tests as we identify parallelism with binlog_commit_wait_count = 35 and we run tests with SPT up to 40.)</p><p><strong>Relaxed Durability Burns CPU for E1 and E2</strong></p><p>As presented in the <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_3-benchmarks_in_production.html#e1e2_nd">main post</a>, E1 and E2 have respective speedups of ~1.06 and ~1.04 for SB-ND. However, as shown in the graphs below, while increasing SPT, CPU consumption also increases noticeably without significantly improving the commit throughput.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*dmlYeK6G8yMqYhIh.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*yl6i_I1PmUaKmh_t.gif" /><figcaption>Graphs # 9: E1 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*ZPJzG6ZCRoyV3Bz9.gif" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*E7Vxq3vbCHUZPUCi.gif" /><figcaption>Graphs # 10: E2 Stats — Slave with Binary Logs — Relaxed Durability</figcaption></figure><p>Either we hit a synchronization bottleneck in the parallel applier or there is room for optimization: there is probably interesting work to be done here.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9391c3609b57" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-3-annex-under-the-hood-9391c3609b57">Evaluating MySQL Parallel Replication Part 3, Annex: Under the Hood</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Evaluating MySQL Parallel Replication Part 2: Slave Group Commit]]></title>
            <link>https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-2-slave-group-commit-459026a141d2?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/459026a141d2</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[mariadb]]></category>
            <category><![CDATA[replication]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Tue, 31 Mar 2015 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:24:43.946Z</atom:updated>
            <content:encoded><![CDATA[<h4>(The previous post, <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">Better Parallel Replication for MySQL</a>, is Part 1 of the series.) Parallel replication is a much expected feature of MySQL. It is already available in MariaDB 10.0 and in MySQL 5.7. In this post, a very nice side effect of the MariaDB implementation is presented: <em>Slave Group Commit</em>.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a_4Jdom8odaFUGa-COf4Lg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/AX-ma0j6elM?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Master Wen</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>(If you are not familiar with parallel replication and its implementation, read the <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">previous post</a> in this series to better understand this post.)</p><p>To allow better parallelization on slaves, MariaDB 10.0 and MySQL 5.7 have parameters that control the commit group sizes on the master. Those parameters are <a href="https://mariadb.com/kb/en/mariadb/parallel-replication/">binlog_commit_wait_count / </a><a href="https://mariadb.com/kb/en/mariadb/parallel-replication/">binlog_commit_wait_usec</a> in MariaDB and <a href="https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_delay">binlog_group_commit_sync_delay</a> / <a href="https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_group_commit_sync_no_delay_count">binlog_group_commit_sync_no_delay_count</a> in MySQL. The purpose of those parameters is to delay the commit of transactions on the master to allow more transactions to join a commit group. This increases the commit group size, and thus the number of transactions that can be run in parallel on slaves. Those parameters can be seen as a way to speed up slaves at the expense of slowing down the master.</p><p>When using those parameters on a MariaDB <strong>slave</strong> with parallel replication enabled (slave_parallel_threads &gt; 1) and when this slave has a MariaDB 10.0 master running a single-threaded workload, transactions will be run sequentially with their commits delayed to try grouping transactions, hence achieving group commit on the slave.</p><p>Let’s take the previous sentence one element at a time and explain each step in more detail.</p><p>In the replication hierarchy shown below:</p><ul><li>X is the master (any of MySQL 5.5, MySQL 5.6, MariaDB 5.5 or MariaDB 10.0)</li><li>Y is an intermediate master running MariaDB 10.0</li><li>Z is a slave running MariaDB 10.0 with binary logs and log-slave-updates enabled</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*IzOQwq1JgM5XKgZEpEdKOw.png" /></figure><p>The Y intermediate master is configured with the following parameters (single threaded slave):</p><ul><li>SET GLOBAL slave_parallel_threads = 0;</li><li>SET GLOBAL binlog_commit_wait_count = 0;</li><li>SET GLOBAL binlog_commit_wait_usec = 0;</li></ul><p>And the Z slave is configured with the following parameters:</p><ul><li>SET GLOBAL slave_parallel_threads = <em>&quot;value greater than 1&quot;</em>;</li><li>SET GLOBAL binlog_commit_wait_count = <em>&quot;value greater than 1&quot;</em>;</li><li>SET GLOBAL binlog_commit_wait_usec = <em>&quot;value greater than 0&quot;</em>;</li></ul><p>Let’s follow two transactions (T1 and T2) on Y and Z. On Y, the transactions are executed sequentially as shown below (<em>B</em> for begin and <em>C</em> for commit).</p><pre>------Time-----&gt;<br>T1:  B----C<br>T2:        B----C</pre><p>Once those are in the binary logs of Y, Z will execute them. As Y is single-threaded, Z cannot run the transactions in parallel (T1 and T2 are each in their own commit group in the binary logs of Y). However, as Z is delaying commit (binlog_commit_wait_count &gt; 1 and binlog_commit_wait_usec &gt; 0), T2 will start executing in another thread once T1 is ready to commit (slave_parallel_threads &gt; 1). If T2 completes before the delay expires, T1 and T2 will group commit as shown below.</p><pre>-----Time-----&gt;<br>T1:  B---- . . C<br>T2:       B----C</pre><p>Group committing is not limited to two transactions, it can extend to the transactions that follow and can result in much larger groups as shown below.</p><pre>----------Time----------&gt;<br>T1:  B-- . . . . . . . . C<br>T2:     B----- . . . . . C<br>T3:           B----- . . C<br>T4:                 B----C</pre><p>Transaction grouping will stop in the following three situations:</p><ol><li>The group size reaches binlog_commit_wait_count, grouping stops and all transactions get committed together.</li><li>binlog_commit_wait_usec expires, grouping stops, T1 and all the following <strong>completed</strong> transactions commit together.</li><li>While the commit of T1 is delayed, a subsequent transaction is blocked by a lock held by a previous transaction that got delayed at the commit stage, a transaction dependency has been found and the group commit pipeline will stall until binlog_commit_wait_usec expires (MariaDB 10.0.16).</li></ol><p>Situation 3 above must be understood clearly. Each time a transaction dependency is found, the slave stops applying transactions until binlog_commit_wait_usec expires. This means that if transaction dependencies are frequent and binlog_commit_wait_usec is too big, the slave throughput will suffer. This could result in slave lag as shown below (T3 depends on T1 or T2).</p><pre>----------------Time-----------&gt;<br>T1:  B-- . . . . . . . . C<br>T2:     B----- . . . . . C<br>T3:                       B-----C</pre><p>This could be optimized in one of the next versions of MariaDB by detecting dependencies and triggering commit omitting the waiting <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html#fn1">[1]</a>.</p><p>(Note: in MariaDB 10.1, slave group commit can be enabled with the <em>minimal</em> <a href="https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#slave_parallel_mode">slave_parallel_mode</a>.)</p><p>Back to group committing on slaves, it allows the following:</p><ol><li>Identifying transaction parallelism on a slave.</li><li>Speeding up the slave by reducing the number of disk syncs needed to write the binary logs when strong durability constraints are set (sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1).</li></ol><p>With reference to 1 above, this way of identifying parallelism will be the starting point of the next post in this series. Part 3 of the series will present speedups of parallel applying on slaves using <a href="https://www.booking.com/">Booking.com</a> production workload. Moreover, you now know how the graphs presented in <a href="https://blog.booking.com/better_parallel_replication_for_mysql.html">part 1</a> of the series were obtained.</p><p>With reference to 2 above, this could be a solution to a problem we are facing at Booking.com. To understand this problem, let’s look at a Booking.com typical MySQL deployment:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UPn1kQCX5GXEVdcB7HIqwA.png" /></figure><p>M is the standard master</p><ul><li>Si are the slaves of this master in the same datacenter</li><li>M1 is an intermediate master on a remote datacenter</li><li>Tj are the slaves of this intermediate master in the remote datacenter</li></ul><p>To be able to survive the hardware failure of M1, the database is hosted on shared disks. Such shared disks could be implemented using DRBD or using filer-based storage accessed over fiber channel (SAN) or iSCSI (LAN). To be able to recover the database (and have the Tj slaves recover replication), high durability constraints need to be set on M1 (sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1).</p><p>However, high durability constraints on a single-threaded slave mean that, after each transaction, a sync to the binary logs and a sync to the REDO logs are required. When a sync takes 1 millisecond (network round-trip), we cannot run more than 500 (or 1000 <a href="https://blog.booking.com/evaluating_mysql_parallel_replication_2-slave_group_commit.html#fn2">[2]</a>) transactions per second.</p><p>The other leaf slaves (Si and Tj) do not have this limitation as their databases are hosted on the local disks with a battery backed-up write cache on the RAID controller (syncs are in fact just writes in the controller memory, so they are less expensive). Moreover, those slaves do not need binary logs and we can relax durability on them (innodb_flush_log_at_trx_commit = 0 or 2).</p><p>So M1 is an embarrassing bottleneck in the replication, and bursts of transactions on M occasionally cause replication delay in the remote datacenter.</p><p>Slave group commit could be a solution to alleviate this bottleneck, but would this work in the real life? To validate that, we did the following test:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*ar5dvq2YxxH9FSmfzbK_Wg.png" /></figure><ul><li>A is our true production master running MySQL 5.6</li><li>B is an intermediate master running MariaDB 10.0.16 without parallel replication enabled (slave_parallel_threads = 0)</li><li>C is a MariaDB 10.0.16 slave with binary log and log-slave-updates enabled, and with high durability enforced (sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1)</li></ul><p>The database on C is hosted on local disks with a battery backed-up write cache on the RAID controller. We ran the C slave with the following configuration:</p><ul><li>Until 17:05 in the graphs below, C is running in single-threaded mode (slave_parallel_threads = 0)</li><li>At 17:05, slave group committing is enabled (slave_parallel_threads = 20, binlog_commit_wait_count = 15 and binlog_commit_wait_usec = 40.000)</li><li>At 17:10, the write cache of the RAID controller is disabled (the slave is still group committing)</li><li>At 17:15, group committing is disabled (slave_parallel_threads = 0 binlog_commit_wait_count = 0 and binlog_commit_wait_usec = 0)</li><li>Finally at 17:17, the write cache is re-enabled</li></ul><p>During those tests, the BINLOG_COMMITS and BINLOG_GROUP_COMMITS global statuses are gathered regularly giving the following graphs:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*-u9zTlFhZzVDThTU.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*s37pPi9Qm7G5bIOO.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*qAvXoQKspsedKwrR.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*gfQpmaj6VUCJD2AA.png" /></figure><p>As we can see on the graphs above, in single-threaded mode and with the write cache enabled, the slaves are able to keep up with the flow of transactions from the master. When enabling group commit (at 17:05), the slaves are still able to keep up with the flow of transactions (binlog_commit_wait_usec does not cause a delay). We can see small behaviour changes after 17:10 when the write cache is disabled but three out of four slaves are able to keep up with the flow of transactions (the 3rd graph shows some slow down because lots of dirty page writes are happening at the same time, but no major delays happen). When the slaves are put back in single-threaded mode and stop group committing (17:15), we can see that their transaction processing capabilities are greatly impaired and that they are not able to keep up with the masters (they are committing much less than the master).</p><p>This shows that slave group commit is a good way of increasing throughput on slaves that have expensive disk sync (no write cache or SAN/LAN-attached storage) when binary logs, log-slave-updates and high durability settings are needed.</p><p>You might have noticed that the B intermediate master does not play a very important role in these tests. It is still needed as the parallel replication code path in MariaDB 10.0.16 is only used when the master is also running MariaDB 10.0. This restriction could be lifted in one of the next versions of MariaDB and then B could be removed.</p><p>This concludes this post on slave group commit. The next post will present speedup results of MariaDB 10.0 slaves running parallel replication on true production workload (with parallelism identified by a group committing intermediate master).</p><p>One last thing, if you want to know more about the cool things we do with MySQL at Booking.com and if you are at <a href="https://www.percona.com/live/mysql-conference-2015/">Percona Live</a> Santa Clara in April, we are giving three talks:</p><p>You can also come to meet us at booth 315. We are looking for smart people to work with us on MySQL and help us to solve hard problems. See our <a href="https://workingatbooking.com/">careers page</a> for more details.</p><p>[1] After completing this post, it was brought to our attention that the not yet released MariaDB versions 10.0.18 and 10.1.4 include <a href="https://mariadb.atlassian.net/browse/MDEV-7249">MDEV-7249</a> that implement skipping the delay when a transaction dependency is found.</p><p>[2] 500 transactions if two syncs are needed per transaction, 1000 transactions if only one is needed: the exact details are not obvious, if you knows more about that, please post a comment below.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=459026a141d2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/evaluating-mysql-parallel-replication-part-2-slave-group-commit-459026a141d2">Evaluating MySQL Parallel Replication Part 2: Slave Group Commit</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Better Parallel Replication for MySQL]]></title>
            <link>https://medium.com/booking-com-infrastructure/better-parallel-replication-for-mysql-14e2d7857813?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/14e2d7857813</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mariadb]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[replication]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Fri, 13 Mar 2015 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:25:02.307Z</atom:updated>
            <content:encoded><![CDATA[<h4>Parallel replication is a much-expected feature of MySQL. It’s available in MariaDB 10.0 and in MySQL 5.7. Yet, both lose efficiency when replicating through intermediate masters. In this post, we’ll explain how parallel replication works and why it does not play well with intermediate masters. We’ll also offer a solution (hint: it involves <a href="https://mariadb.com/blog/mariadb-replication-maxscale-and-need-binlog-server/">Binlog Servers</a>).</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a_4Jdom8odaFUGa-COf4Lg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/AX-ma0j6elM?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Master Wen</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>In MySQL 5.5 and MariaDB 5.5, replication is single threaded: on a slave, the previous transaction must complete (commit) before the next transaction can start. This is not the case on a master where many transactions can make progress at the same time. The consequence is that a slave has less transaction processing capabilities than its master (for writes). This means that care must be taken to avoid loading a master with more writes than its slaves can execute. If this is not done well, the slaves will start lagging.</p><p>This single threaded implementation is disappointing:</p><ul><li>Year after year, servers get an increasing number of cores but only one can be used on slaves to apply transactions from the master</li><li>MySQL (and its storage engines) gets better at using many cores but those improvements do not speed up replication on slaves</li></ul><p>On the other hand, running transactions in parallel on a slave is not simple. If done badly, conflicting transactions could be run at the same time, which risks getting different results on master and slave. In order to be able to run transactions in parallel, a way to prevent that from happening must be implemented. For parallel replication in MySQL 5.6, transactions in different schemas are assumed to be non-conflicting and can be run in parallel on slaves. This is a good start but what about transactions in the same schema?</p><p>To provide parallel execution of transactions in the same schema, MariaDB 10.0 and MySQL 5.7 take advantage of the binary log group commit optimization. This optimization reduces the number of operations needed to produce the binary logs by grouping transactions. When transactions are committing at the same time, they are written to the binary log in a single operation. But if transactions commit at the same time, then they are not sharing any locks, which means they are not conflicting thus can be executed in parallel on slaves. So by adding group commit information in the binary logs on the master, the slaves can safely run transactions in parallel.</p><p>So far we have:</p><ul><li>The master identifying potential parallelism with group commit and making this information available to the slaves via the binary logs</li><li>The slaves executing transactions in parallel using the information provided by the master</li></ul><p>What happens when some of those slaves are intermediate masters? What kind of parallelism information will they pass to their slaves? To answer these questions, the first thing that needs to be highlighted is the fact that transactions committing together might have started at different points of time in the past. This situation is illustrated below where T1 and T2 started (<em>B</em> for begin) after T3 and T4, even if they are all committing at the same time (<em>C</em> for commit).</p><pre>----Time----&gt;<br>T1:      B---C<br>T2:      B---C<br>T3:  B-------C<br>T4:  B-------C</pre><p>Because those transactions commit together on the master, they start together on the slaves but do not commit at the same time:</p><pre>----Time----&gt;<br>T1:  B---C<br>T2:  B---C<br>T3:  B-------C<br>T4:  B-------C</pre><p>This will cause the group of 4 transactions from the master to be split into 2 groups of 2 transactions on slaves. This is not a big problem on leaf slaves but it is a major issue on intermediate masters. As an intermediate master will write those transactions as 2 commit groups in the binary logs, its slaves will not run 4 transactions in parallel but only 2 transactions at a time with a barrier between the groups as shown below.</p><pre>----Time---------&gt;<br>T1:  B---C<br>T2:  B---C<br>T3:       B-------C<br>T4:       B-------C</pre><p>So, when using parallel replication (based on the group commit) with transactions of different execution times, the intermediate masters show less parallelism to their slaves than what is shown by their master. This is not only a supposition, this is clearly observed in our test environments.</p><p>To test parallel replication at <a href="https://www.booking.com/">Booking.com</a> we set up four test environments, each consisting of a master and a slave, where each node is running MariaDB 10.0.16 (we’ve also done similiar tests with MySQL 5.7 and found results close to the ones you’ll see below). On each master, we run transactions from production and identify parallelism using group commit (more on the exact methodology in a follow up post). To monitor parallelism identification, the global status BINLOG_COMMITS and BINLOG_GROUP_COMMITS are checked regularly. The graphs below show the collected data (number of commits and group commits) for both master and slave in the four tests environments.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*n-quUtoBTnHgYyHu.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*uoVHybe50j04xUOP.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*Rhbz7snsUg2Xe6f4.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*7CsrmHsJkC4zZFTp.png" /></figure><p>For the most part, the master and the slave are executing the same number of transactions (commits). But, the slave is executing more group commits than the master (less is better). If we divide the number of commits by the number of group commits, we get the average group commit size. Those are shown on the following graphs with the number of applier threads on the slave:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*m0jdaPx84_tJ6T4y.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*7pJOYPfyiipnx0AL.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*Tmwgt-bR3gWGYsOR.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*IAAISSe-9ovXzqgx.png" /></figure><p>A smaller group size means that, if those slaves were intermediate masters, parallelism is lost when replicating through them. Looking at ratios, we can see how bad this is (slave divided by master, less than one for group commit means that the slave identifies less parallelism than its master). Note: we expect the commit ratio to be 1, as if it were not, the slave would be lagging.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*7q8dvGPa4OWeMPj0.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*fD7FoPa_jMfSO8qA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*NX3akTkLESVSkzrt.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*f7XKB5h2eiOisUFR.png" /></figure><p>This is very disappointing. In our test environments, using true production workload, intermediate masters divide the parallelism available to their slaves by at least 2 and sometimes by almost 4.</p><p>What can be done to improve that? Having all slaves replicating directly from the master could be a solution. But that would increase the load on masters. It would also be impractical for remote site replication because it consumes too much WAN bandwidth as we often have 50 and sometimes more than 100 slaves per site.</p><p>People that have read previous posts from this blog might have guessed the solution:</p><blockquote><em>Replace intermediate masters by </em><a href="https://blog.booking.com/mysql_slave_scaling_and_more.html"><em>Binlog Servers</em></a><em>.</em></blockquote><p>(Reminder: a Binlog Server sends its slaves exactly the same binary logs it fetched from its master.)</p><p>By doing this, all slaves replicate using the same binary logs (the one from the master) and all slaves, either directly connected to the master or replicating from a Binlog Server, see the same parallelism information.</p><p>To take full advantage of parallel replication, and while intermediate masters are unable to offer this information to their slaves, intermediate masters must disappear! The tool that will allow us to do that at <a href="https://www.booking.com/">Booking.com</a> is the Binlog Server.</p><p>If you’re interested in this topic and would like to learn more, I’ll be giving a talk about <a href="https://www.percona.com/live/mysql-conference-2015/sessions/binlog-servers-bookingcom">Binlog Servers</a> at <a href="https://www.percona.com/live/mysql-conference-2015/">Percona Live</a> Santa Clara in April. I am looking forward to meeting you there.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14e2d7857813" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/better-parallel-replication-for-mysql-14e2d7857813">Better Parallel Replication for MySQL</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Better Crash-safe replication for MySQL]]></title>
            <link>https://medium.com/booking-com-infrastructure/better-crash-safe-replication-for-mysql-a336a69b317f?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/a336a69b317f</guid>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[devops]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Wed, 22 Oct 2014 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:25:20.516Z</atom:updated>
            <content:encoded><![CDATA[<h4>At <a href="https://booking.com/">Booking.com</a>, we use MySQL replication extensively for read scaling. However, because we have so many slaves (we have thousands), sooner or later, one of them will fail. In MySQL 5.5 and earlier, replication was not crash-safe. MySQL 5.6 provides crash-safe replication when modifying the default configuration. But this safety comes with several downsides. Read on to find out more about crash-safe replication in MySQL 5.6 and learn how to mitigate its downsides.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a_4Jdom8odaFUGa-COf4Lg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/AX-ma0j6elM?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Master Wen</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>MySQL replication involves two tasks on a slave: the IO thread and the SQL thread:</p><ul><li>The IO thread downloads events from the master binary logs and appends them to the relay logs.</li><li>The SQL thread reads the events from the relay logs and executes them on the local database.</li></ul><p>To keep track of its progress, each thread needs to store a state:</p><ul><li>The IO thread keeps track of the next event to download from the master binary logs using two variables: a filename and a position (Master_Log_File and Read_Master_Log_Pos from <a href="https://dev.mysql.com/doc/refman/5.6/en/show-slave-status.html">SHOW SLAVE STATUS</a>).</li><li>The SQL thread keeps track of the next event to execute using four variables: the filename and position in the slave relay logs and the two corresponding values in the master binary logs (respectively Relay_Log_File, Relay_Log_Pos, Relay_Master_Log_File and Exec_Master_Log_Pos from SHOW SLAVE STATUS).</li></ul><p>In MySQL 5.5 and earlier, those states are persisted in files (see the <a href="https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_master-info-file">master-info-file</a> and <a href="https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_relay-log-info-file">relay-log-info-file</a> options for more information about those files). However, the IO thread and the SQL thread cannot make progress and update their state files at the same time (atomically). This means that inconsistencies can be introduced by a crash:</p><ol><li>If the IO thread has appended data to the relay logs but has not updated its state file when MySQL crashes, <strong>the same event is re-appended</strong> to the relay logs after restart.</li><li>If the SQL thread has committed a transaction but has not updated its state file when MySQL crashes, <strong>the same transaction is re-run</strong> after restart.</li></ol><p>Those two inconsistencies are the cause of the infamous “Duplicate entry for key PRIMARY” errors when restarting a slave after a crash. This error happens when running an INSERT twice. Other errors can also happen on UPDATE and DELETE statements depending on many factors, the list of which is too long and complex to give here. In the worst cases silent data corruption will occur.</p><p>To solve inconsistency #2 above, MySQL 5.6 introduces the option to persist the state of the SQL thread in the database. By setting <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-info-repository">relay-log-info-repository</a> to TABLE (this is not the default option), the SQL thread saves its state at the same time as it commits a transaction. If the storage engine is atomic (like InnoDB), inconsistency in the state of the SQL thread is prevented.</p><p>However, it is not possible to avoid inconsistency #1. The IO thread has no way to synchronize its progress (appending events to the relay logs) and its state (saving data in a FILE or a TABLE). The consequence is that it is impossible, once a crash has occurred, to trust both the content of the relay log and the state of the IO thread.</p><p>If we cannot trust those, how can MySQL 5.6 provide crash-safe replication? The implemented solution is to:</p><ul><li>Initialize the state of the IO thread to the position of the SQL thread (which can be trusted when relay-log-info-repository is set to TABLE and the storage engine is atomic), and</li><li>Advance the position of the SQL thread to the tail of the relay logs.</li></ul><p>In other words, all the relay logs are considered <em>un-proper for SQL thread consumption</em>. This behaviour is not enabled by default. To benefit from it, the option <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-recovery">relay-log-recovery</a> must be set to 1 (the default value is 0).</p><p>In summary, to obtain crash-safe replication in MySQL 5.6, one must:</p><ul><li>Set relay-log-info-repository to TABLE,</li><li>Set relay-log-recovery to 1, and</li><li>Use only atomic storage engines.</li></ul><p>More information about <a href="https://dev.mysql.com/doc/refman/5.6/en/slave-logs.html#replication-implementation-crash-safe">MySQL 5.6 crash-safe replication</a> can be found in the manual.</p><h3>The problems with MySQL 5.6 Crash-safe replication</h3><p>So far, everything looks good, so what are the downsides? We identified a number of problems:</p><ol><li>When setting relay-log-recovery to 1 in the configuration file, the relay logs are considered un-proper for SQL thread consumption every time MySQL is started.</li><li>After a restart or a crash, if the IO thread had been ahead of the SQL thread, unnecessary traffic caused by downloading binary logs will be incurred on both the master and on the network.</li><li>After a restart or a crash, if the master is not available, no progress can be made by the SQL thread, even if there are plenty of unexecuted relay logs on the slave.</li><li>After a restart or a crash, if critical binary logs have been purged from the master, the slave will not be able to resume replication.</li><li>If we want to keep a copy of the relay logs on the slave by setting <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-purge">relay-log-purge</a> to 0, a restart or a crash could corrupt the stored relay logs.</li></ol><p>Problem #1 means that all restarts must be done with extra care. If a lagging slave is restarted, it can trigger problems #2, #3, #4, and #5. Having those problems after a crash could be acceptable, but having those after a simple restart is harder to accept.</p><p>Problem #2 is the one that worries us most at Booking.com. As presented in a <a href="https://blog.booking.com/mysql_slave_scaling_and_more.html">previous post</a>, the network load on our master is a concern for us. The idea of a master network interface being overloaded as a result of a restart/crash of a slave is hard to accept.</p><p>A special case of problem #2 is <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html">delayed replication</a>. In the case of a restart/crash of a delayed slave the amount of the binary log that needs to be re-downloaded is proportional to the delay of the slave. We have delayed slaves that store tens of GBs of unexecuted relay logs. A restart/crash of those would overload the network interface or a master for minutes. This is unbearable: we must find something better.</p><p>Again on problem #2, the restarted/crashed slave could be remote from the master. If the bandwidth of the WAN link is limited, it can be saturated by the slave downloading binary logs from the master. This is also an undesirable effect.</p><p>Problem #3 is less of a concern for us, but still not an enjoyable proposition (it might be more of a concern for others).</p><p>Problem #4 means that before purging binary logs, a DBA must make sure that they have been <strong>executed</strong> by all slaves. This is an additional task that DBAs could do without.</p><p>Problem #5 is more subtle since it is dependent upon the preference of our DBAs to keep a copy of the relay logs on the slave (setting relay-log-purge to 0). With relay-log-recovery=1, this copy of the relay logs cannot be trusted after a restart or a crash:</p><ul><li>If before a restart or a crash there are unexecuted events in the relay logs, which are appended again to the relay logs after a restart, resulting in duplicate events in the relay logs (not executed in double though).</li><li>If just before an operating system crash some events are executed by the SQL thread but not synced to the relay logs by the IO thread, those events are missing from the relay logs after the restart.</li></ul><p>The following bugs/feature requests have been logged to trace those problems:</p><ul><li><a href="https://bugs.mysql.com/bug.php?id=74089">Bug # 74089</a>: Report Relay_Log_File and Relay_Log_Pos on relay-log-recovery.</li><li><a href="https://bugs.mysql.com/bug.php?id=74321">Bug # 74321</a>: Execute relay-log-recovery only when needed.</li><li><a href="https://bugs.mysql.com/bug.php?id=74323">Bug # 74323</a>: Avoid overloading the master NIC on crash recovery of a lagging slave.</li><li><a href="https://bugs.mysql.com/bug.php?id=74324">Bug # 74324</a>: Make keeping relay logs (relay_log_purge=0) crash safe.</li></ul><h3>Our wishes for MySQL Crash-safe replication</h3><p>Do not get us wrong. Having a way to make replication crash-safe is a great step forward since MySQL 5.5. Still, some other small steps need to be carried out to have ideal crash-safe replication. For us, ideal crash-safe replication should:</p><ol><li>Not take destructive measures if no crash has happened</li><li>Minimize the number of binary logs downloaded from the master on crash recovery</li><li>Have consistent relay logs after crash recovery when relay_log_purge=0.</li></ol><p>Our wish #2 above can be implemented by adding four variables to the IO thread state. Those variables would indicate the last trusted (synced) position in the relay logs and the corresponding position in the master binary logs. When restarting MySQL, the relay logs would be truncated at that last synced position and the IO thread would be initialized at the corresponding position in the master binary logs. (Note that if the IO thread was stopped correctly, nothing is truncated, which satisfies our wish #1 above.) After the truncation, if the position of the SQL thread is above (or at) the truncation point (IO thread ahead of or at the same position as the SQL thread), nothing special needs to be done. If the position of the SQL thread is after the truncation point, the behaviour will depend on relay_log_purge:</p><ul><li>If relay_log_purge is 1, all the previous relay logs are dropped and the IO thread position is set to the SQL position.</li><li>If relay_log_purge is 0, more work must be done to honour the DBA’s wish to keep the relay logs. To do that, the IO thread must be started UNTIL the position of the SQL thread. When the IO thread reaches this position, the SQL thread can be started after updating its corresponding relay log position. This satisfies our wish #3 above.</li></ul><h3>Our workaround</h3><p>Until ideal crash-safe replication is implemented in a future version of MySQL, we need to mitigate the problems caused by the current implementation (for our deployment at Booking.com, the biggest problem is unnecessarily downloading binary logs from the master). The solution we are currently investigating is to deploy a <a href="https://mariadb.com/blog/mariadb-replication-maxscale-and-need-binlog-server/">Binlog Server</a> on our slaves:</p><ul><li>The Binlog Server downloads binary logs from the master</li><li>The slave downloads the binary logs from the local Binlog Server</li><li>If the slave is restarted (after a crash or a planned restart), it will not put undesired load on the master because the binary logs will be downloaded locally</li></ul><p>This way, we avoid the downsides of MySQL relay log recovery by pushing the problem to the Binlog Server. And because making the Binlog Server crash-safe is trivial, we successfully work-around the downsides of MySQL relay log recovery.</p><p>But if we are not careful with this approach, we could end up consuming twice the disk space that is necessary by storing binary logs in the Binlog Server directory, and storing the relay logs in the MySQL directory.</p><p>The <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-space-limit">relay-log-space-limit</a> option can be used to solve this problem. By setting this option to twice the max-relay-log-size (as recommended in the documentation), we put an upper limit on the extra space that can be used. But when using this option, care must be taken to set it right as changing the value will necessitate restarting MySQL <a href="https://blog.booking.com/better_crash_safe_replication_for_mysql.html#fn1">[1]</a>. There is a feature request (bug #68236) to <a href="https://bugs.mysql.com/bug.php?id=68236">make the relay-log-space-limit option dynamic</a>. Hopefully this will be implemented soon.</p><h3>Conclusion</h3><p>As promised in <a href="https://blog.booking.com/mysql_slave_scaling_and_more.html">our last post</a>, we presented another case for using the Binlog Server. Hopefully, this will be a temporary use that will become obsolete. When a better relay log recovery is implemented in MySQL, we will not need to deploy additional software on our MySQL servers.</p><p>We have yet another instance where using the Binlog Server is beneficial. I will present this at <a href="https://www.percona.com/live/london-2014/">Percona Live London</a> in my talk: <a href="https://www.percona.com/live/london-2014/sessions/high-availability-disaster-recovery-and-extreme-read-scaling-using-binlog-servers">High Availability, Disaster Recovery and Extreme Read Scaling using Binlog Servers</a>.</p><p>[1]: When restarting MySQL to change the option, care must be taken not to restart MySQL with relay-log-recovery=1 as this could cause the unnecessary re-downloading of binary logs. With a local Binlog Server, this becomes less of a concern.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a336a69b317f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/better-crash-safe-replication-for-mysql-a336a69b317f">Better Crash-safe replication for MySQL</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL Slave Scaling (and more)]]></title>
            <link>https://medium.com/booking-com-infrastructure/mysql-slave-scaling-and-more-a09d88713a20?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/a09d88713a20</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Tue, 15 Jul 2014 07:00:00 GMT</pubDate>
            <atom:updated>2018-02-28T22:19:44.543Z</atom:updated>
            <content:encoded><![CDATA[<h4>At <a href="https://booking.com/">Booking.com</a>, we have very wide replication topologies. It is not uncommon to have more than fifty (and sometimes more than a hundred) slaves replicating from the same master. When reaching this number of slaves, one must be careful not to saturate the network interface of the master. A solution exists but it has its weaknesses. We came up with an alternative approach that better fits our needs: the Binlog Server. We think that the Binlog Server can also be used to simplify disaster recovery and to ease promoting a slave as a new master after failure. Read on for more details.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NcPJDcHZLb4KAwZFkG9JIg.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/H0vuplqoX0c?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Viktor Jakovlev</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>When having many slaves replicating from the same master, serving binary logs can saturate the network interface of the master as every change is requested by every slave. It is not unusual to have changes that generate lots of binary logs, two examples are:</p><ol><li>deleting lots of records in a single statement when using row based replication</li><li>executing an <a href="https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html">online schema change</a> on a large table</li></ol><p>In the replication topology described in Figure # 1, producing one megabyte of binary logs per second on M will generate replication traffic of a hundred megabytes per second if we deploy a hundred slaves. This is very close to the limit of a 1 Gbit/s network interface and this is something we see happening in our replication chains.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1011/1*8I4WTe5Hcl8EjJJ3rG7thQ.png" /><figcaption>Figure # 1: Replication Tree with many Slaves.</figcaption></figure><p>The traditional solution to this problem is to place intermediate masters between M and its slaves. In the deployment of Figure # 2, instead of having slaves directly connected to M, we have intermediate masters replicating from M with slaves replicating from each intermediate master. When having 100 slaves and ten intermediate masters, this allows producing ten times more binary logs on M before saturating the network interface (ten MB/s instead of one MB/s).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MZn3v2A_m3RXDSAhwdCAVw.png" /><figcaption>Figure # 2: Replication Tree with Intermediate Masters.</figcaption></figure><p>However, using intermediate masters has its problems:</p><ol><li>replication lag on an intermediate master will generate delay on all of its slaves</li><li>if an intermediate master fails, all of its slaves stop replicating and they need to be reinitialized <a href="https://blog.booking.com/mysql_slave_scaling_and_more.html#fn1">[1]</a></li></ol><p>Diving deeper on the second problem in the context of Figure # 2, one could think that, in case of a failure of M1, its slave could be repointed to the other intermediate masters but this is not that simple:</p><ul><li>S1 replicating from M1 depends on the binary logs of M1,</li><li>M1 and M2 have different binary logs (they are different databases),</li><li>manually extrapolating the position of S1 in the binary log of M2 is hard.</li></ul><p>GTID can help us in repointing slaves, but it will not solve the first problem above.</p><p>It should be noticed that <em>we do not need the databases</em> in the intermediate layer at all: we only need to serve binary logs. And, if <em>the binary logs served by M1 and M2 were the same</em>, we could easily swap each of their respective slaves. From those two observations, we build the idea of the <strong>Binlog Server</strong>.</p><p>Binlog Servers replace intermediate masters in Figure # 2. Each Binlog Server:</p><ul><li>downloads the binary logs from the master</li><li>saves them to disk using the same structure as the master (filename and content)</li><li>serves them to slaves <strong>as if they were served from the master</strong></li></ul><p>And of course, if a Binlog Server fails, we can simply repoint its slaves to the other Binlog Servers. Even better, since these hosts do not apply changes to a local dataset before serving them downstream, latency is greatly improved in comparison to using actual database servers.</p><p>We are working in collaboration with <a href="https://www.skysql.com/">SkySQL</a> to implement the Binlog Server as a module to the <a href="https://mariadb.com/resources/maxscale/">MaxScale</a> pluggable framework. You can read this <a href="https://mariadb.com/blog/mariadb-replication-maxscale-and-need-binlog-server/">blog post</a> by SkySQL for an introduction on MySQL replication, MaxScale and the Binlog Server.</p><h3>Other use-case: avoiding deep nested replication on remote sites</h3><p>The Binlog Server can also be used to reduce the problems with <a href="https://code.openark.org/blog/mysql/using-deep-nested-replication-topologies">deep nested replication</a> on remote sites.</p><p>If someone needs four database servers per site on two sites, the topology from Figure # 3 is a typical deployment when WAN bandwidth is a concern (E, F, G and H are on the remote site).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OVOS1YCv-b-p3j6b4Wk-3w.png" /><figcaption>Figure # 3: Remote Site Deployment with an Intermediate Master.</figcaption></figure><p>But this topology suffers from the problems explained above (replication delay on E slows down F, G and H, and losing F, G and H in case of a failure of E). It would be better if we could set it up like Figure # 4 but it needs more WAN bandwidth and, in case of a disaster, on the main site, the slaves of the remote site need to be reorganized in a new tree.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-7PKBO-nJus0lGKWtN5IvQ.png" /><figcaption>Figure # 4: Remote Site Deployment without Intermediate Master.</figcaption></figure><p>Using a Binlog Server on the remote site, we can combine the best of both solutions (low bandwidth usage and no delay introduced by an intermediary database). The topology becomes the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xk2jMFp0ZAKhzG_4SZAFCg.png" /><figcaption>Figure # 5: Remote Side Deployment with a Binlog Server.</figcaption></figure><p>The Binlog Server (X) looks like a single point of failure in the topology from Figure # 5 but if it fails, it is trivial to restart another one. It is also possible to run two Binlog Servers on the remote site as illustrated in Figure # 6. In this deployment, if Y fails, G and H are repointed to X. If X fails, E and F are repointed to Y and Y is repointed to A.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DCHvy34dXynv7yokTTfjbA.png" /><figcaption>Figure # 6: Remote Site Deployment with two Binlog Servers.</figcaption></figure><p>Note that running Binlog Servers does not necessarily mean more hardware. In Figure # 6, X can be installed on the same server as E and Y on the same server as G.</p><p>Finally, those deployments (with one or two Binlog Servers) have an interesting property: if a disaster happens on the main site, the slaves on the remote site will converge to a common state (from the binary logs available on X). This makes reorganizing the slaves in a new replication tree easy:</p><ul><li>any slave can be the new master</li><li>the binary log position of the new master is noted before sending write to it (SHOW MASTER STATUS)</li><li>the other nodes are placed as slaves of this new master at the position noted in the previous step</li></ul><h3>Other use-case: easy high availability</h3><p>The Binlog Server can also be used as a high availability building brick. If we want to be able to elect a new master in case of the failure of A in Figure # 7, we can deploy GTIDs or use <a href="https://code.google.com/p/mysql-master-ha/">MHA</a> but both have downsides.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SlyLOsFRJdqv13C5d-EHCw.png" /><figcaption>Figure # 7: Replication Tree with 6 Slaves.</figcaption></figure><p>If we deploy a Binlog Server between the master and the slaves as illustrated in Figure #8:</p><ul><li>if X fails, we can repoint all the slaves to A</li><li>if A fails, all the slaves will converge to a common state which makes reorganizing the slaves in a new replication tree easy (explained above)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*764qublIYYExZO9BdqcxHA.png" /><figcaption>Figure # 8: Replication Tree with a Binlog Server.</figcaption></figure><p>If we want to combine extreme slave scaling and high availability, we can use the topology of Figure # 9.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ycUAhgZbBLRhhGwaxhkpag.png" /><figcaption>Figure # 9: Replication Tree with many Binlog Servers.</figcaption></figure><p>If a Binlog Server fails, its slaves are repointed to the other Binlog Servers. If A fails:</p><ul><li>we find the Binlog Server that has more binary logs (we suppose in this example that it is I2)</li><li>we repoint the other Binlog Servers to I2 as illustrated in Figure # 10</li><li>and once all the slaves have converged to a common state, we reorganize the topology</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ycUAhgZbBLRhhGwaxhkpag.png" /><figcaption>Figure # 10: Converging Slaves after the Failure of the Master.</figcaption></figure><h3>Conclusion</h3><p>We have presented a new component that we are introducing in our replication topologies: the Binlog Server. It allows us to horizontally scale our slaves without fear of overloading the network interface of the master and without the downsides of the traditional intermediate master solution.</p><p>We think that the Binlog Server can also be used in at least two other use-cases: remote site replication and easy topology reorganization after a master failure. In a follow up post, we will describe another use-case of the Binlog Server. Stay tuned for more details.</p><p>[1] Slave reinitialization can be avoided using GTIDs or by using highly available storage for intermediate masters (DRBD or filers) but each of those two solutions brings new problems.</p><p>Would you like to be an Engineer at Booking.com? <a href="https://workingatbooking.com/vacancies/?filter-searchphrase=infra+devop+site+reliability">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a09d88713a20" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/mysql-slave-scaling-and-more-a09d88713a20">MySQL Slave Scaling (and more)</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL 5.6 GTIDs: Evaluation and Online Migration]]></title>
            <link>https://medium.com/booking-com-infrastructure/mysql-5-6-gtids-evaluation-and-online-migration-139693719ff2?source=rss-4e2e27287ef8------2</link>
            <guid isPermaLink="false">https://medium.com/p/139693719ff2</guid>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[web-development]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Jean-François Gagné]]></dc:creator>
            <pubDate>Wed, 26 Mar 2014 07:00:00 GMT</pubDate>
            <atom:updated>2017-11-14T10:49:19.268Z</atom:updated>
            <content:encoded><![CDATA[<h4>At <a href="https://booking.com/">Booking.com</a>, we use MySQL to operate our web site where tens of million of pages are viewed and over 625,000 room nights are reserved every day. This is a lot to ask for our database infrastructure. To be able to sustain such load, we split, shard and replicate our data extensively. Unfortunately, a conventional master-slave setup has a problem at our scale and that problem is specifically around failing over master databases and repointing slaves. Luckily, there are some advancements in the MySQL space that can help us achieve the flexibility we need. And like any new technology there is always a drawback.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pnWgxh4MhZdmaMYLWaSrSw.png" /><figcaption>Photo by <a href="https://unsplash.com/photos/fPkvU7RDmCo?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Caspar Rubin</a> on <a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>MySQL 5.6 and MariaDB 10.0 implement a feature called Global Transaction IDs (<em>GTIDs</em>) to provide a facility for easily repointing a slave to a new master. In previous versions, doing this was fairly error prone and not always possible without lots of manual work. This feature could save us quite a bit of headache except MySQL 5.6 GTIDs <a href="https://bugs.mysql.com/bug.php?id=69059">lack a reasonable deployment strategy (bug#69059)</a>. MariaDB 10.0 does not have this limitation, but since a large part of our infrastructure is MySQL we need to be able to deploy these changes with a minimal amount trouble. Oracle is working on allowing <a href="https://dev.mysql.com/worklog/task/?id=7083">setting GTID_MODE=ON online (worklog#7083)</a> but we could use this feature a lot sooner than in MySQL 5.7.</p><p>Before going further, let’s briefly describe <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html">replication with GTIDs in MySQL 5.6</a>. When GTIDs are disabled (the default configuration) all transactions are <em>ANONYMOUS</em> meaning there isn’t a global identifier, and instead, each transaction is identified by file and position only. When GTIDs are enabled, each transaction is assigned a globally unique identifier stored in the binary logs of the master. When the slave downloads the binary logs and executes the transactions, it remembers their GTIDs. If then we need to repoint the slave, the GTIDs stored on the slave are sent to the new master which uses them to determine what transactions need to be sent downstream.</p><p>When a <strong>new</strong> replication tree is built using MySQL 5.6, it can be initialized with GTIDs enabled. However, with an existing replication tree (with GTIDs disabled), enabling GTIDs requires a step where all databases are stopped: a GTID enabled database was not designed to replicate from/to a GTID disabled database. To enforce that, a check is done at slave start up validating that the master GTID mode is compatible with the slave mode. This is a serious hurdle.</p><p>We have lots of very large replication trees. We cannot stop all databases in a tree simultaneously without a severe negative impact to our customers. Plus, before we can enable GTIDs everywhere, we need to evaluate the feature and train ourselves on its usage. And it requires adjusting our automation and monitoring systems to be GTID aware.</p><p>That isn’t to say we aren’t interested in the feature, but there had to be a better way to try it out and also deploy it without killing our business. We believe we have found that solution.</p><p>While testing GTIDs in our lab environment, we were able to replicate ANONYMOUS transactions to a GTID enabled slave. This can lead to unexpected results because each slave would generate different GTIDs for the same transaction. We <a href="https://bugs.mysql.com/bug.php?id=71527">reported this as a bug (#71527)</a>. However, after opening this bug, we realized that this behavior can be used to our advantage to evaluate and migrate to GTIDs. Basically, we want to introduce an intermediate master to generate GTIDs in a replication tree. We submitted a feature request specifically for this mode of operation (<a href="https://bugs.mysql.com/bug.php?id=71543">new GTID mode: ANONYMOUS_IN-GTID_OUT (#71543)</a>). After some deeper investigation, we realized that this mode can be implemented with very small patch to the MySQL source. All we needed to do was disable the GTID mode compatibility check on the intermediate master.</p><p>From the root of the MySQL source tree, in the file sql/rpl_slave.cc, there is a function called get_master_version_and_clock(). In this function, the following code is responsible for checking the slave and master having compatible GTID modes:</p><pre>if (mi-&gt;master_gtid_mode &gt; gtid_mode + 1 ||<br>    gtid_mode &gt; mi-&gt;master_gtid_mode + 1)<br>{<br>  mi-&gt;report(ERROR_LEVEL, ER_SLAVE_FATAL_ERROR,<br>             &quot;The slave IO thread stops because the master has &quot;<br>             &quot;@@GLOBAL.GTID_MODE %s and this server has &quot;<br>             &quot;@@GLOBAL.GTID_MODE %s&quot;,<br>             gtid_mode_names[mi-&gt;master_gtid_mode],<br>             gtid_mode_names[gtid_mode]);<br>  DBUG_RETURN(1);<br>}</pre><p>Commenting this block of code results in a mysqld with the new ANONYMOUS_IN-GTID_OUT mode when setting its GTID mode to ON.</p><p>Let’s see how this new mode can be used. We will work on the replication tree below where <em>A</em> is the master and <em>B</em> to <em>F</em> are slaves. For simplicity, we suppose that <strong>binary logs and </strong><a href="https://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_enforce_gtid_consistency"><strong>enforce_gtid_consistency</strong></a><strong> are already enabled</strong> on all databases.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ofqJ6oRNp0t8xR5feB_zRA.png" /></figure><p>The first step to evaluate/migrate to GTIDs is to restart <em>B</em> in the new GTID mode ANONYMOUS_IN-GTID_OUT. For us, that means deploying our patched mysqld and enable GTIDs on <em>B</em>. Then, <em>C</em> can be migrated under <em>B</em> using the following commands:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7d2120fffef3914f99f710bdbb0d3e24/href">https://medium.com/media/7d2120fffef3914f99f710bdbb0d3e24/href</a></iframe><p>After repeating the commands above with <em>D</em>, we have the new replication tree below where GTIDs are partially enabled:</p><ul><li>WRITES on <em>A</em> are propagated to <em>B</em>, <em>E</em> and <em>F</em>,</li><li><em>B</em> generates GTIDs for those transactions,</li><li>the generated GTIDs are propagated to <em>C</em> and <em>D</em>.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9wSSw1wN3VJaE3I0iwXCJg.png" /></figure><p>After evaluating GTIDs on <em>B</em>, <em>C</em> and <em>D</em> (and learning about how to operate them in production, including handling of unexpected problems), enabling GTIDs everywhere is straightforward:</p><ul><li>Point the WRITES to <em>B</em>,</li><li>Move <em>A</em> under <em>B</em>.</li></ul><p>The resulting replication tree is the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uOWKy2PbfCY6ZekK6BCgiw.png" /></figure><p><em>B</em> can stay in the mode ANONYMOUS_IN-GTID_OUT as it is not a slave anymore. However, if we want to have <em>A</em> as the master and/or all nodes in pure GTID mode, we can:</p><ul><li>Move <em>C</em> through <em>F</em> under <em>A</em>,</li><li>Point the WRITES to <em>A</em>,</li><li>Restore original binaries on <em>B</em>,</li><li>Move <em>B</em> under <em>A</em>.</li></ul><p>And that is how we’ve been evaluating MySQL 5.6 GTIDs. In some of our replication trees we’ve deployed a patched mysqld that has ANONYMOUS_IN-GTID_OUT mode enabled. And under those intermediate masters, we&#39;ve deployed unpatched mysqld with GTIDs enabled.</p><p>However, we are not quite ready to completely switch our mission critical databases and replication trees over to GTIDs. We need to continue evaluating to be sure we have not missed something important. Nonetheless, we are very happy to have unlocked a path to online migration.</p><p>If you want to know more about the cool things we do with MySQL at Booking.com and if you are at <a href="https://www.percona.com/live/mysql-conference-2014/">Percona Live</a> at the beginning of April, we are giving a talk on <a href="https://www.percona.com/live/mysql-conference-2014/sessions/data-materialisation-high-performance-web-front-ends">Data materialisation for high-performance Web front-ends</a>. You can also come to meet us at <a href="https://www.percona.com/live/mysql-conference-2014/exhibit-hall">booth #318</a>. We are also looking for smart people to work with us on MySQL and help us solve deeper problems similar to implementing GTIDs, see our <a href="https://workingatbooking.com/">careers page</a> for more details.</p><p>Would you like to be an Engineer at Booking.com? <a href="http://grnh.se/t7ny6l1">Work with us</a>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=139693719ff2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/booking-com-infrastructure/mysql-5-6-gtids-evaluation-and-online-migration-139693719ff2">MySQL 5.6 GTIDs: Evaluation and Online Migration</a> was originally published in <a href="https://medium.com/booking-com-infrastructure">Booking.com Infrastructure</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>