Apply parallel replication threads for performance
Serial replication uses one thread to replicate data in the transaction log. Typically, a single thread cannot keep up with many database connections simultaneously writing data to a table. But if a table only has one thread writing to it, serial replication is often preferred.
Parallel replication uses many threads to replicate data simultaneously. It can keep up with multiple database connections that are simultaneously writing data. In many cases, parallel replication is preferable over serial replication. Parallel replication requires more CPU to handle the additional threads it uses to process replication. Because CPU resources are limited, the number of threads is configurable. Parallel replication also has additional overhead because it does dependency checking to ensure data consistency. Dependency checking ensures that changes to the same record occur in the original transaction order. Dependent changes must be serialized. In the unusual case where many transactions have dependencies, parallel replication becomes slower than serial replication because of the overhead of dependency checking combined with the net result of serialized transactions.
Parallel replication can be enabled and configured within a replication plan under .
Check Dependencies
The dependency thread reads entries from the dependency queue. Because more than one analyzer thread could be putting transactions into the dependency queue, the dependency thread checks the transaction sequence number of entries that it reads and it acts on the entries in the order of the transaction sequence number. For each transaction, the analyzer compares its writeset to the writeset of each transaction in the dependency graph. If the writesets overlap, the transaction that is being added to the graph is listed as a follower of the transaction in the graph whose writeset overlaps. If the transaction has no overlap with any transactions in the graph, the dependency thread writes the transaction to the transaction-ready queue. A transaction remains in the dependency graph until an apply thread has committed it.
The dependency graph is implemented as a doubly-linked list. A mutex controls access by the dependency thread and the apply threads to the dependency graph. If the number of entries in the dependency graph becomes larger than a compile-time threshold that is a multiple of the number of apply threads, the dependency thread waits for the number of entries to decrease before it adds more entries to the graph, since having too large a graph can cause the graph to become a performance bottleneck.
Sync Log Writes
Sync Log Writes forces each write to the local transaction log to be flushed to disk. The default is no, which means log writes are periodically flushed to disk.
Analyzer Threads Count
One or more analyzer threads read entries from an analyzer queue. For each transaction, an analyzer thread calculates the transaction's writeset and then writes the transaction to the dependency check queue.
Apply Threads Count
One or more apply threads read from the transaction-ready queue and apply the transactions to the target server. Each apply thread has its own connection to the target server. When the transaction has been committed, the apply thread removes the transaction from the dependency graph, updating all the transaction's followers. Any follower whose dependency count has dropped to zero is added to the transaction-ready queue.