<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="mysql-cluster-replication-mta">
      </a>
      25.7.11 NDB Cluster Replication Using the Multithreaded Applier
     </h3>
    </div>
   </div>
  </div>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-cluster-replication-mta.html#cluster-replication-mta-reqs" title="Requirements">
       Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-cluster-replication-mta.html#cluster-replication-mta-config-source" title="MTA Configuration: Source">
       MTA Configuration: Source
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-cluster-replication-mta.html#cluster-replication-mta-config-replica" title="MTA Configuration: Replica">
       MTA Configuration: Replica
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-cluster-replication-mta.html#cluster-replication-mta-transaction-deps" title="Transaction Dependency and Writeset Handling">
       Transaction Dependency and Writeset Handling
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-cluster-replication-mta.html#cluster-replication-mta-writeset-tracking" title="Writeset Tracking Memory Usage">
       Writeset Tracking Memory Usage
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-cluster-replication-mta.html#cluster-replication-mta-limitations" title="Known Limitations">
       Known Limitations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   NDB replication in NDB 8.4 supports the use of the
      generic MySQL Server Multithreaded Applier mechanism (MTA), which
      allows independent binary log transactions to be applied in
      parallel on a replica, increasing peak replication throughput.
  </p>
  <h4>
   <a name="cluster-replication-mta-reqs">
   </a>
   Requirements
  </h4>
  <p>
   The MySQL Server MTA implementation delegates the processing of
      separate binary log transactions to a pool of worker threads
      (whose size is configurable), and coordinates the worker threads
      to ensure that transaction dependencies encoded in the binary log
      are respected, and that commit ordering is maintained if required
      (see
   <a class="xref" href="replication-threads.html" title="19.2.3 Replication Threads">
    Section 19.2.3, “Replication Threads”
   </a>
   ). To use this
      functionality with NDB Cluster, it is necessary that the replica
      be configured to use multiple worker threads. To do this, set
   <a class="link" href="replication-options-replica.html#sysvar_replica_parallel_workers">
    <code class="literal">
     replica_parallel_workers
    </code>
   </a>
   to
      control the number of worker threads on the replica. The default
      is 4.
  </p>
  <h4>
   <a name="cluster-replication-mta-config-source">
   </a>
   MTA Configuration: Source
  </h4>
  <p>
   If set on the source
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   ,
   <a class="link" href="replication-options-replica.html#sysvar_replica_parallel_type">
    <code class="literal">
     replica_parallel_type
    </code>
   </a>
   must be
   <code class="literal">
    LOGICAL_CLOCK
   </code>
   (the default value).
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    <code class="literal">
     NDB
    </code>
    does not support
    <code class="literal">
     replica_parallel_type=DATABASE
    </code>
    .
   </p>
  </div>
  <p>
   In addition, it is recommended that you set the amount of memory
      used to track binary log transaction writesets on the source
      (
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_transaction_dependency_history_size">
    <code class="literal">
     binlog_transaction_dependency_history_size
    </code>
   </a>
   )
      to
   <code class="literal">
    <em class="replaceable">
     <code>
      E
     </code>
    </em>
    *
    <em class="replaceable">
     <code>
      P
     </code>
    </em>
   </code>
   , where
   <em class="replaceable">
    <code>
     E
    </code>
   </em>
   is the average epoch size (as the
      number of operations per epoch) and
   <em class="replaceable">
    <code>
     P
    </code>
   </em>
   is the maximum expected parallelism. See
   <a class="xref" href="mysql-cluster-replication-mta.html#cluster-replication-mta-writeset-tracking" title="Writeset Tracking Memory Usage">
    Writeset Tracking Memory Usage
   </a>
   , for
      more information.
  </p>
  <h4>
   <a name="cluster-replication-mta-config-replica">
   </a>
   MTA Configuration: Replica
  </h4>
  <p>
   Replica
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   configuration for the
   <code class="literal">
    NDB
   </code>
   MTA requires that
   <a class="link" href="replication-options-replica.html#sysvar_replica_parallel_workers">
    <code class="literal">
     replica_parallel_workers
    </code>
   </a>
   is
      greater than 1. The recommended starting value when first enabling
      MTA is 4, which is the default.
  </p>
  <p>
   In addition,
   <a class="link" href="replication-options-replica.html#sysvar_replica_preserve_commit_order">
    <code class="literal">
     replica_preserve_commit_order
    </code>
   </a>
   must be
   <code class="literal">
    ON
   </code>
   . This is also the default value.
  </p>
  <h4>
   <a name="cluster-replication-mta-transaction-deps">
   </a>
   Transaction Dependency and Writeset Handling
  </h4>
  <p>
   Transaction dependencies are detected using analysis of each
      transaction's writeset, that is, the set of rows (table, key
      values) written by the transaction. Where two transactions modify
      the same row they are considered to be dependent, and must be
      applied in order (in other words, serially) to avoid deadlocks or
      incorrect results. Where a table has secondary unique keys, these
      values are also added to the transaction's writeset to detect
      the case where there are transaction dependencies implied by
      different transactions affecting the same unique key value, and so
      requiring ordering. Where dependencies cannot be efficiently
      determined,
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   falls back to considering
      transactions dependent for reasons of safety.
  </p>
  <p>
   Transaction dependencies are encoded in the binary log by the
      source
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   . Dependencies are encoded in an
   <code class="literal">
    ANONYMOUS_GTID
   </code>
   event using a scheme called
      'Logical clock'. (See
   <a class="xref" href="replication-mode-change-online-concepts.html" title="19.1.4.1 Replication Mode Concepts">
    Section 19.1.4.1, “Replication Mode Concepts”
   </a>
   .)
  </p>
  <p>
   The writeset implementation employed by MySQL (and NDB Cluster)
      uses hash-based conflict detection based on matching 64-bit row
      hashes of relevant table and index values. This detects reliably
      when the same key is seen twice, but can also produce false
      positives if different table and index values hash to the same
      64-bit value; this may result in artificial dependencies which can
      reduce the available parallelism.
  </p>
  <p>
   Transaction dependencies are forced by any of the following:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      DDL statements
     </p>
    </li>
    <li class="listitem">
     <p>
      Binary log rotation or encountering binary log file boundaries
     </p>
    </li>
    <li class="listitem">
     <p>
      Writeset history size limitations
     </p>
    </li>
    <li class="listitem">
     <p>
      Writes which reference parent foreign keys in the target table
     </p>
     <p>
      More specifically, transactions which perform inserts,
          updates, and deletes on foreign key
      <span class="emphasis">
       <em>
        parent
       </em>
      </span>
      tables are serialized relative to
          all preceding and following transactions, and not just to
          those transactions affecting tables involved in a constraint
          relationship. Conversely, transactions performing inserts,
          updates and deletes on foreign key
      <span class="emphasis">
       <em>
        child
       </em>
      </span>
      tables (referencing) are not especially serialized with regard
          to one another.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The MySQL MTA implementation attempts to apply independent binary
      log transactions in parallel.
   <code class="literal">
    NDB
   </code>
   records all
      changes occurring in all user transactions committing in an epoch
      (
   <a class="link" href="mysql-cluster-ndbd-definition.html#ndbparam-ndbd-timebetweenepochs">
    <code class="literal">
     TimeBetweenEpochs
    </code>
   </a>
   ,
      default 100 milliseconds), in one binary log transaction, referred
      to as an epoch transaction. Therefore, for two consecutive epoch
      transactions to be independent, and possible to apply in parallel,
      it is required that no row is modified in both epochs. If any
      single row is modified in both epochs, then they are dependent,
      and are applied serially, which can limit the expolitable
      parallelism available.
  </p>
  <p>
   Epoch transactions are considered independent based on the set of
      rows modified on the source cluster in the epoch, but not
      including the generated
   <code class="literal">
    mysql.ndb_apply_status
   </code>
   <code class="literal">
    WRITE_ROW
   </code>
   events that convey epoch metadata.
      This avoids every epoch transaction being trivially dependent on
      the preceding epoch, but does require that the binlog is applied
      at the replica with the commit order preserved. This also implies
      that an NDB binary log with writeset dependencies is not suitable
      for use by a replica database using a different MySQL storage
      engine.
  </p>
  <p>
   It may be possible or desirable to modify application transaction
      behavior to avoid patterns of repeated modifications to the same
      rows, in separate transactions over a short time period, to
      increase exploitable apply parallelism.
  </p>
  <h4>
   <a name="cluster-replication-mta-writeset-tracking">
   </a>
   Writeset Tracking Memory Usage
  </h4>
  <p>
   The amount of memory used to track binary log transaction
      writesets can be set using the
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_transaction_dependency_history_size">
    <code class="literal">
     binlog_transaction_dependency_history_size
    </code>
   </a>
   server system variable, which defaults to 25000 row hashes.
  </p>
  <p>
   If an average binary log transaction modifies
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   rows, then to be able to identify
      independent (parallelizable) transactions up to a parallelism
      level of
   <em class="replaceable">
    <code>
     P
    </code>
   </em>
   , we need
   <code class="literal">
    binlog_transaction_dependency_history_size
   </code>
   to
      be at least
   <code class="literal">
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    *
    <em class="replaceable">
     <code>
      P
     </code>
    </em>
   </code>
   . (The maximum is 1000000.)
  </p>
  <p>
   The finite size of the history results in a finite maximum
      dependency length that can be reliably determined, giving a finite
      parallelism that can be expressed. Any row not found in the
      history may be dependent on the last transaction purged from the
      history.
  </p>
  <p>
   Writeset history does not act like a sliding window over the last
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   transactions; rather, it is a finite
      buffer which is allowed to fill up completely, then its contents
      entirely discarded when it becomes full. This means that the
      history size follows a sawtooth pattern over time, and therefore
      the maximum detectable dependency length also follows a sawtooth
      pattern over time, such that independent transactions may still be
      marked as dependent if the writeset history buffer has been reset
      between their being processed.
  </p>
  <p>
   In this scheme, each transaction in a binary log file is annotated
      with a
   <code class="literal">
    sequence_number
   </code>
   (1, 2, 3, ...), and as
      well as the sequence number of the most recent binary log
      transaction that it depends on, to which we refer as
   <code class="literal">
    last_committed
   </code>
   .
  </p>
  <p>
   Within a given binary log file, the first transaction has
   <code class="literal">
    sequence_number
   </code>
   1 and
   <code class="literal">
    last_committed
   </code>
   0.
  </p>
  <p>
   Where a binary log transaction depends on its immediate
      predecessor, its application is serialized. If the dependency is
      on an earlier transaction then it may be possible to apply the
      transaction in parallel with the preceding independent
      transactions.
  </p>
  <p>
   The content of
   <code class="literal">
    ANONYMOUS_GTID
   </code>
   events, including
   <code class="literal">
    sequence_number
   </code>
   and
   <code class="literal">
    last_committed
   </code>
   (and thus the transaction
      dependencies), can be seen using
   <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
    <span class="command">
     <strong>
      mysqlbinlog
     </strong>
    </span>
   </a>
   .
  </p>
  <p>
   The
   <code class="literal">
    ANONYMOUS_GTID
   </code>
   events generated on the
      source are handled separately from the compressed transaction
      payload with bulk
   <code class="literal">
    BEGIN
   </code>
   ,
   <code class="literal">
    TABLE_MAP*
   </code>
   ,
   <code class="literal">
    WRITE_ROW*
   </code>
   ,
   <code class="literal">
    UPDATE_ROW*
   </code>
   ,
   <code class="literal">
    DELETE_ROW*
   </code>
   ,
      and
   <code class="literal">
    COMMIT
   </code>
   events, allowing dependencies to be
      determined prior to decompression. This means that the replica
      coordinator thread can delegate transaction payload decompression
      to a worker thread, providing automatic parallel decompression of
      independent transactions on the replica.
  </p>
  <h4>
   <a name="cluster-replication-mta-limitations">
   </a>
   Known Limitations
  </h4>
  <p>
   <b>
    Secondary unique columns.
   </b>
   Tables with secondary unique columns (that is, unique keys other
        than the primary key) have all columns sent to the source so
        that unique-key related conflicts can be detected.
  </p>
  <p>
   Where the current binary logging mode does not include all
      columns, but only changed columns
      (
   <a class="link" href="mysql-cluster-options-variables.html#option_mysqld_ndb-log-updated-only">
    <code class="option">
     --ndb-log-updated-only=OFF
    </code>
   </a>
   ,
   <a class="link" href="mysql-cluster-options-variables.html#option_mysqld_ndb-log-update-minimal">
    <code class="option">
     --ndb-log-update-minimal=ON
    </code>
   </a>
   ,
   <a class="link" href="mysql-cluster-options-variables.html#option_mysqld_ndb-log-update-as-write">
    <code class="option">
     --ndb-log-update-as-write=OFF
    </code>
   </a>
   ),
      this can increase the volume of data sent from data nodes to SQL
      nodes.
  </p>
  <p>
   The impact depends on both the rate of modification (update or
      delete) of rows in such tables and the volume of data in columns
      which are not actually modified.
  </p>
  <p>
   <b>
    Replicating NDB to InnoDB.
   </b>
   <code class="literal">
    NDB
   </code>
   binary log injector transaction
        dependency tracking intentionally ignores the inter-transaction
        dependencies created by generated
   <code class="literal">
    mysql.ndb_apply_status
   </code>
   metadata events, which
        are handled separately as part of the commit of the epoch
        transaction on the replica applier. For replication to
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   , there is no special
        handling; this may result in reduced performance or other issues
        when using an
   <code class="literal">
    InnoDB
   </code>
   multithreaded applier to
        consume an
   <code class="literal">
    NDB
   </code>
   MTA binary log.
  </p>
 </div>
 <br/>
</div>
