Parallel Replication in MySQL and MariaDB

Parallel Replication in MySQL & MariaDB Mahto Monu Associate Database Consultant , Mydbops

• Interested in Open Source Database technologies • 3 Years of Experience with MySQL • Oracle certified MySQL DBA • Experienced in both On Premises and Cloud About Me

• Services on top open source databases • Founded in 2016 • 70 Member team • Assisted over 500+ Customers • AWS Partner and a PCI Certified Organisation About Mydbops

Database Consulting Services Managed Database Services Focuses on Top Opensource database MySQL,MariaDB, MongoDB and PostgreSQL ON Premises and Cloud Mydbops Services

500 + Clients In 5 Yrs. of Operations Our Clients

• What is Replication ? • Evolution of Replication • Parallel Replication in MySQL • Parallel Replication in MariaDB Agenda

▪ Data sync among the servers ▪ Native and inbuilt feature ▪ Asynchronous by default ▪ Logical replication ▪ One of the most popular feature What is Replication

Basic Replication Flow Source Replica

▪ Scale-out solutions (Load distribution) ▪ Backup and High availability ▪ Analytics purpose ▪ Long-distance data distribution ▪ Delayed Replication Advantages of Replication

Methods Of Replications Replication Binlog-pos-based GTID

Evolution of Replication in MySQL

▪ In MySQL 3.23 the replication on slave were just one thread. ▪ From MySQL 4.0, the replication slaves run on two threads i.e IO & SQL . ▪ Till MySQL 5.5 and before there is only file and position based replication. ▪ From MySQL 5.6, GTID and parallel replication introduced. ▪ MySQL 5.6, the multi-threaded on per database level. ▪ MySQL 5.7, the MTS based on Logical_clock. ▪ MySQL 8.0, write-sets based parallel replication. Evolution of Replication in MySQL

Cons with Single threaded Replication ▪ Lack of resource utilisation ▪ Slave apply the transactions sequentially. ▪ Lags on slave server ▪ High sync time ▪ Can't promote as master immediately

Parallel Replication ▪ Parallel replication on schema level. ▪ Two transactions on different schema can be run in parallel on slaves. ▪ More effective on those workloads that had multiple schemas. MySQL 5.6 :

Parallel Replication ▪ slave_parallel_workers=N(N>0) ▪ slave_pending_jobs_size_max ▪ relay_log_info_repository = TABLE ▪ relay_log_recovery = ON ▪ master_info_repository = TABLE Variables to be consider :

Parallel Replication ▪ Transaction ordering can be different on master and slave ▪ Gaps in transactions order. ▪ Parallel replication was not crash safe without GTIDS. Cons :

Parallel Replication ▪ Parallel replication even on intra-schema level ▪ Based on binlog group commit information ▪ slave_parallel_type - Logical clock (timestamp), Database (default) ▪ Binary log - last_committed & sequence number used for grouping the transactions. ▪ slave_preserve_commit avoids gaps MySQL 5.7 :

Parallel Replication ▪ slave_parallel_type = LOGICAL_CLOCK ▪ slave_parallel_workers=N(N>0) ▪ slave_preserve_commit_order = 1 ▪ Binlog_group_commit_sync_delay = N (In microseconds) ▪ Binlog_group_commit_sync_no_delay_count = N(N>0) Variables to consider :

Parallel Replication ▪ Optimising the binlog group commit can slow down the writes on master.. ▪ Every delay in committing a transaction could eventually affect application. ▪ slave_preserve_commit - requires log_bin and log_slave_updates ▪ Longer transactions can cause blocking and slow down the parallelism. Cons :

Parallel Replication ▪ Parallel replication using write-set. ▪ Transactions that affect different tuples(row) can be parallelized. ▪ Dependency information is generated from the source's write set. ▪ Write-set is a mechanism to track independent transactions that can be executed in parallel in the slave. ▪ Writeset based replication is the fastest replication method in MySQL. MySQL 8.0 :

Parallel Replication Variables to consider : Variables Values Info slave_parallel_type LOGICAL_CLOCK deprecated from 8.0.27 and default ≥ 8.0.27 slave_parallel_workers N from 8.0.27 default 4 binlog_transaction_dependency_tracking WRITESET / WRITESET_SESSION - transaction_write_set_extraction XXHASH64 / MURMUR32 deprecated from 8.0.26 and default XXHASH64 slave_preserve_commit_order 1 deprecated from 8.0.26 and will enable default ≥ 8.0.27 master_info_repository TABLE deprecated from 8.0.23 and default will be TABLE relay_log_info_repository TABLE deprecated from 8.0.23 and default will be TABLE relay_log_recovery ON

Parallel Replication ▪ Supports only InnoDB tables. ▪ Primary key is needed on all the tables. ▪ Do not work well foreign keys relationship. ▪ slave_parallel_workers should be > 0. ▪ Row based replication required (default in 8.0) Limitations :

Parallel Replication ▪ All tables must be InnoDB engine. ▪ Primary key is must for all tables. ▪ Enable GTID's for crash safe replication. ▪ relay_log_recovery should be enabled. ▪ Set slave_preserve_commit = 1 to avoid gaps (default from 8.0.27) ▪ Row based replication is faster and better for most cases. ▪ master_info_repository and relay_log_info_repository should be table (default from 8.0.23) Recommendations for efficient MySQL Parallel Replication

Evolution of Replication in MariaDB

▪ Before MariaDB 10 the replication was single threaded same like MySQL 5. ▪ Parallel replication was introduced in MariaDB 10.0.5.. ▪ Till 10.5.0 the conservative slave_parallel_mode was by default. ▪ The optimistic mode of in-order parallel replication was introduced in 10.1.13 which become default from 10.5.1 Evolution of Replication in MariaDB

Parallel Replication ▪ Parallel replication - MariaDB 10.0.5 ▪ Required version - 10.0.5 on both the Master and Slave. ▪ From MariaDB 10, the event is applied by a pool of separate replication worker threads on slave. ▪ Set the slave_parallel_threads > 0. ▪ Applying DML statements in parallel by reading ahead in the relay log and allocating to each worker threads to apply in parallel on replica. MariaDB :

Parallel Replication MariaDB : Parallel Replication In-order Out-of-order

Parallel Replication ▪ Parallel replication can be in-order or out-of-order. ▪ In-order executes transactions in parallel, but orders the commit step of the transactions to happen in the exact same order as on the master. ▪ Parallelism is completely transparent to the application. ▪ Out-of-order can execute and commit transactions in different order on the slave than originally on the master. ▪ Application must be tolerant to seeing updates occur in random order.

Parallel Replication MariaDB : In-order In-order Replication Optimistic Aggressive Minimal Conservative

Parallel Replication ▪ Introduced in MariaDB 10.1.3. ▪ Parallel replication can be applied on the slave while still preserving exact transaction semantics. ▪ The term "optimistic" is used for this mode, because the server optimistically assumes that few conflicts will occur. ▪ Tries to apply most transactional DML in parallel, and handles any conflicts with rollback and retry. ▪ Transactional DML is allowed to run in parallel up to the limit slave_domain_parallel_threads. ▪ Non-transactional DML and DDL is not safe to optimistically apply in parallel. MariaDB : Optimistic mode

Parallel Replication ▪ Introduced in MariaDB 10.1.3. ▪ Similar to optimistic mode. ▪ Tries to maximise the parallelism at cost of increased conflict rate. ▪ Slave does not consider whether transactions conflicted on the master when deciding whether to apply the transactions in parallel. MariaDB : Aggressive Mode

Parallel Replication ▪ Introduced in MariaDB 10.0. ▪ Based on group commit (similar to MySQL 5.7 logical_clock) ▪ Binlog events are marked with commit id. ▪ Events which are having same commit id can be replicate in parallel on replica. ▪ Conflict are avoided and events can be scheduled by the parallel replication to run in different worker threads. ▪ binlog_commit_wait_count and binlog_commit_wait_usec variables for tuning. ▪ Limit parallelism in an effort to avoid any conflicts. MariaDB : Conservative Mode

Parallel Replication ▪ Parallelizes the commit steps of transactions and all other steps are applied serially. ▪ Configured by setting the slave_parallel_mode system variable to minimal on the slave. MariaDB : Minimal Mode

Parallel Replication ▪ Out-of-order parallel replication happens only when using GTID mode. ▪ Transactions having GTIDs with different domain_id are applied in parallel. ▪ Application has to set different domain_ids for transactions that are truly independent each other. ▪ Transactions with different domain_id are committing in different orders. ▪ Effective for long-running statements like Alters in separate replication domain. ▪ Works efficiently for multi-source replication. MariaDB : Out-of-order

Parallel Replication ▪ slave_parallel_threads = N (N > 0) ▪ slave_parallel_mode = optimistic / conservative / aggressive / minimal ▪ slave_parallel_max_queued (configure size for parallel slave queue) ▪ slave_domain_parallel_threads = N (N < slave_parallel_threads) Variables to be consider :

Parallel Replication Variables Comparison : MySQL MariaDB slave_parallel_threads slave_parallel_workers slave_parallel_type slave_parallel_mode slave_parallel_job_size_max slave_parallel_max_queued binlog_group_commit_sync_delay(microseco nds) binlog_commit_wait_usec(microseconds) binlog_group_commit_sync_no_delay_count binlog_commit_wait_count

Parallel Replication ▪ https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html ▪ https://mariadb.com/kb/en/parallel-replication/ ▪ https://mydbops.wordpress.com/2018/08/13/presentationevolution-of-mysql-parallel-replication/ ▪ https://medium.com/booking-com-infrastructure/better-parallel-replication-for-mysql- 14e2d7857813 ▪ https://medium.com/booking-com-infrastructure/better-crash-safe-replication-for-mysql- a336a69b317f ▪ https://mariadb.com/resources/blog/understanding-and-using-in-order-parallel-replication/ References :

Reach Us : Info@mydbops.com Thank You

Parallel Replication in MySQL and MariaDB

More Related Content

What's hot

Similar to Parallel Replication in MySQL and MariaDB

More from Mydbops

Recently uploaded

In this document

Parallel Replication in MySQL and MariaDB