Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL Replication: Latest Developments
Luís Soares ([email protected])Principal Software Engineer, MySQL Replication Technologies Lead
1
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 2
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 3
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Program Agenda
Background
Latest Developments in Replication GA
Latest Developments in Replication Plugins
Latest Developments in Replication Core
Roadmap
Summary
1
2
3
4
5
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
6
4
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Background1
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 5
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
MySQL Database Replication: Overview
Tuesday, October 4, 2016
INSERT ...Server
B
binary log
INSERT ...
relay log
INSERT ...Server
A
binary log
App
ReceiverMeta-data
Update
ApplierMeta-data
Update
Percona Live 2016 - Amsterdam, The Netherlands
INSERT ...
Replicationstream/log Threaded
applier
Comm. Framework
Capturestatements
or datachanges.
Persistentreplicationstream/log
buffer
6
Send, Receive,ACK, NACK,
Heartbeating,...
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
MySQL Database Replication: Some Notes
• Logical log that records the changes on the master.
• Statement or Row based format (may be intermixed).
• One of the reasons that made MySQL (Replication) so popular.
• Enables seamless integration with other systems.
The Binary Log
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
Layout of the BinaryLog (i.e., the replication stream).
BEGIN ...E1 E2 COMMIT BEGIN ...E1 E2 COMMITGTID GTID
7
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
MySQL Database Replication: Some NotesCoordination Between Servers
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
A B Since 3.23
A B
semi-synchronous (plugin)
A CB
group replication (plugin)
transactions
transactions
Since 5.5
5.7-labs
asynchronous (native)
transactions, membership, coordination
acks
The Corner Stone of MySQL InnoDB Cluster
8
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
MySQL Database Replication: Use Cases
• Common Use Cases
1. Read Scale-out
2. Redundancy
3. Dedicated Replicas
4. Disaster Recovery
5. Data Integration
Collection of Common Use Cases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
C
B
D
A
read clientswrite clients
1.
C
B
A
C
B
ACrash
C
B
A
2.
A B
3.
Image fromwww.ginkgomaps.com
CA
B
5.
9
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Highly Available Replication Group – Group Replication Plugin
MySQL Database Replication: Use Cases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
A B C D E
• Automated failure detection.• Split-brain protection.• Conflict detection and resolution.• Automatic Coordination.
10
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
MySQL Group Replication: The EnablerMySQL InnoDB Cluster
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 11
M
M M
MySQL Connector
Application
MySQL Router
MySQL Connector
Application
MySQL Router
MySQL Shell
HA
Rep
licaS
et1
• MySQL Group Replication:• Replication.• Automated HA procedures.• Cluster-wide automated
coordination.• Core of MySQL InnoDB Cluster!
• MySQL Router:• Transparent connection fail-
over.• MySQL Shell:
• One-stop tool to manage the cluster.
MySQL Group Replication + Tooling = MySQL InnoDB Cluster (Full Stack HA)
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Background1
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
2 Latest Developments in Replication GA
12
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA Releases and Major Replication Enhancements
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
Lets Take a SmallStep Back First!
13
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – How MySQL Replication Has Evolved Over the Years!
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
14
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Interesting Notes Overall
• MySQL Replication is very popular and has a huge deployment base.
– 15+ years of production ready releases.
– Simple to understand, deploy and use.
– Ubiquitous.
– Recurring comment: “Our business runs on MySQL Replication.”
• MySQL Replication has seen an accelerated development over the years.
– Increased quality, increased feature set, increased reliability!
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 15
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Interesting Stats About MySQL 5.7
• 40 replication worklogs pushed to MySQL 5.7
– 29 related to MySQL Replication Core
– 11 were ground work for MySQL Group Replication.
• 8 replication related contributions merged into MySQL 5.7 (and counting).– Some contributions even though rejected, served as inspiration for our work.
– Countless technical discussions and feedback received from our users.
– You are an active part of this too!
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 16
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA Releases and Major Replication Enhancements
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
Great!
Lets check out some of thenice Replication features
in MySQL 5.7.
17
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Online Reconfiguration of Global Transaction Identifiers
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
S1
S2 S3
S4
S1
S2 S3
S4
S1
S2 S3
S4
S1
S2 S3
S4
SET @@GLOBAL.GTID_MODE =ON_PERMISSIVE;
SET @@GLOBAL.GTID_MODE =OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE =ON;
Details: http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html
File nameand offsetbased replicationpositioning
GTID basedreplicationpositioning
18
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
S1
S2 S3
S4
S1
S2 S3
S4
S1
S2 S3
S4
S1
S2 S3
S4
SET @@GLOBAL.GTID_MODE =ON_PERMISSIVE;
SET @@GLOBAL.GTID_MODE =OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE =ON;
Online Reconfiguration of Global Transaction Identifiers
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
Details: http://dev.mysql.com/doc/refman/5.7/en/replication-mode-change-online-enable-gtids.html
GTID basedreplicationpositioning
File nameand offsetbased replicationpositioning
• Reads and writes always allowed.• No need to synchronize servers, ever.• No need to restart servers.• No need to change replication topology.• Easy to roll back and forth and to switch between modes.
19
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Improved Replication Monitoring
Tuesday, October 4, 2016
mysql> select * from performance_schema.replication_applier_status_by_worker\G*************************** 1. row ***************************
CHANNEL_NAME: WORKER_ID: 1THREAD_ID: 35
SERVICE_STATE: ONLAST_SEEN_TRANSACTION: 4ba0eb86-63c3-11e4-92ba-28b2bd168d07:2368
LAST_ERROR_NUMBER: 0LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00*************************** 2. row ***************************
CHANNEL_NAME: WORKER_ID: 2THREAD_ID: 36
SERVICE_STATE: ONLAST_SEEN_TRANSACTION: 4ba0eb86-63c3-11e4-92ba-28b2bd168d07:2367
LAST_ERROR_NUMBER: 0LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:002 rows in set (0,00 sec)
Percona Live 2016 - Amsterdam, The Netherlands 20
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Improved Applier Throughput – Locking-based Parallelism
Tuesday, October 4, 2016
Single Threaded Apply 8 Threads Applier 24 Threads Applier 48 Threads Applier 96 Threads Applier
0%
50%
100%
150%
200%
250%
Fast and Scalable Multi-threaded Replication Applier
(statement-based replication, durable settings, SSD storage)
Sla
ve a
pply
tim
e (
Sysbench R
W o
n t
he m
aste
r w
ith 9
6 t
hre
ads =
100%
)
10X
Percona Live 2016 - Amsterdam, The Netherlands 21
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Improved Applier Throughput – Locking-based Parallelism
• Concurrent transactions, which have not blocked each other on the master, are marked as non-contending in the binary log.
• Non-contending transactions are applied in parallel on the other end.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
trx1
trx2
Commit intervals for trx1 and trx2 overlap, thence they have not blocked
each other during execution. Consequently, they can be applied in parallel.
time
Commit procedure
begins
Commit procedure
ends. Locks are released.
22
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Faster Semi-sync Replication – ACK Receiver Thread
• Sender thread does not wait for ACKs anymore, it is just responsible to send binary logs.
• ACK receiver thread is responsible to receive ACKs.
• ACK receiver thread starts and stops when semi-sync is activated and deactivated respectively
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
trx1
timetrx1 trx2
trx2
ACK ACK
trx1
trx2
trx2ACK
ACK
trx1time
Before After
master
slave
master
slave
23
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Faster Semi-sync: Durability over the Network
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
Orange bar’s percentage shows the throughput gain when compared to the corresponding blue bar. Blue and stacked bars show throughput ratio computed against the best blue bar.
24
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Loss-less Semi-sync Replication
Tuesday, October 4, 2016
Master
Slave
T1: INSERT INTO t1 VALUES (1000)
ACK
Time
T2: SELECT * FROM t1; empty set
execute prepare binlog
relay log
commitexecute
Percona Live 2016 - Amsterdam, The Netherlands
mysql> SET rpl_semi_sync_master_wait_point= [AFTER_SYNC|AFTER_COMMIT]
25
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Semi-sync Replication – Wait for Multiple ACKs
• Master does not commit transaction until it receives N ACKs from N slaves.
• Dynamically settable:
Tuesday, October 4, 2016
mysql> SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= N;
Master
Slave 2
T1: COMMIT
ACK
Slave 1ACK
T1: COMMITsucceeds
mysql> SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= 2;
Slave 3
Time
relay log
relay log
relay log
Percona Live 2016 - Amsterdam, The Netherlands
ACK
26
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Global Transaction Identifiers History in a System TableUse Cases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
S1
S2
Clients
GITD
GITD
S1
S2
Clients
GITD• Potential New Master• Auto positioning in
the stream• Skips automatically
executed transactions• Persists GTID history in
the Binary Log
• Cannot become a Master• Auto positioning in
the stream• Skips automatically
executed transactions• Persists GTID history in a
System Table
27
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Multi-Source Replication
Tuesday, October 4, 2016
S
M M Slave can have more than one master.M M
Percona Live 2016 - Amsterdam, The Netherlands
The need for gathering data in a central server:• Integrated backup;• Complex queries for analytics purposes;• Data HUB for inter-cluster replication.
28
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
“Smaller”, yet interesting, enhancements!• Multi-threaded applier is able to retry failed transactions.
• Option to make the Multi-threaded applier preserve commit order.
• SSL options for mysqlbinlog tool.
• Rewrite DB rules for the mysqlbinlog tool.
• Function to wait for transactions to be applied, regardless of the replication stream they come from.
• Options to track global transaction identifiers in the return packet of the mysqlprotocol. Useful for tracking GTID session state and enabler for session consistency.
• Support for XA transactions when the binary log is enabled.
• Change in the defaults. E.g., binlog_format=ROW and sync_binlog=1.
• Options to fine tune the binary log group commit procedure.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 29
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA Releases and Major Replication Enhancements
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
The road to MySQL 5.7gave us features...
... and infrastructure to take MySQL Replication and HA to
the next level!30
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Features and Infrastructure Notes• Better replication stream handling with Global Transaction Identifiers.
– Global Transaction Identifiers easier roll out.
• Better and more scalable replication monitoring.
– Instrumentation and performance schema tables for replication enable different topologies and generic monitoring.
• Faster applier threads make replication more efficient and faster.
– Enabler for different parallelization schemes.
• Flexible deployments enable more advanced setups.
– Replication slave can now replicate from multiple sources at once.
– Infrastructure became generic: paved the way for other replication schemes (e.g., Group Replication)
• Best part is... It does not end here.
– Plenty of interesting work to do and interesting features to deliver! ;)
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 31
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Background1
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
2 Latest Developments in Replication GA3 Latest Developments in Replication Plugins
32
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
MySQL Group Replication Plugin (GR plugin)
Tuesday, October 4, 2016
A B C D E
Replication Group
• Multi-master update everywhere with
row level conflict detection.
• Automatic group membership management and failure detection.
• No need for server fail-over.
• Automatic reconfiguration.
• No single point of failure.
• Shared-nothing state machine replication.
• InnoDB compliant. Off-the-shelf hardware friendly.
Percona Live 2016 - Amsterdam, The Netherlands 33
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA and GR Releases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
Oh! Wait...
Lets take anothersmall step back!
34
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA and GR Releases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 5.6.10
MySQL 5.7.9lifecycle interfacesP_S tables for GRServer side changes
GR 0.2.0 labsHello world!
GR 0.3.0 labssupport for corosync 2.x
GR 0.4.0 labsversion handling
GR 0.5.0 labsauto-inc fields handlingrecovery enhancements
GR 0.6.0 labsmulti-platform supportPaxos-based consensus
GR 0.7.0 labsbug fixes
GR 0.8.0 labs (Beta)performance enhancementsreplication stream compressionSSL supportIP whitelistingread-only modeerror log enhancementssplit brain handling
GR 0.9.0 labs (RC)multi-threaded applier supportsingle primary mode
35
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Comments
• Feature rich new replication plugin based on proven distributed systems algorithms (Paxos).
– Compression, multi-platform, dynamic membership, distributed agreement, quorum based message passing, SSL, IP whitelisting, integrated with the server, ...
• Frequent releases on Labs.
– Enabled community involvement early in the design and implementation.
– Increasing interest on the technology.
• Large changes done to server core, replication framework and plugin.
– Suggests really fast development pace.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 36
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Comments
• Modular design and implementation modernized also the server core.
• Latest release presents two features/enhancements:
–MTS support (servers now are able to apply the group replication stream in parallel).
– Single Primary Mode (with automatic leader election on primary failure and group-wide information propagation).• For those that do not want to deploy GR in multi-master mode.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 37
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA and GR Releases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
Alright!
Lets have a lookunder the hood!
38
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ArchitectureGeneral Architecture
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
APIs: Capture / Apply / Lifecycle
Capture Applier Recovery
Replication Protocol Logics
Group Communication System API
Group Communication Engine (Paxos)
MySQL Server
MyS
QL
Gro
up
Re
plic
atio
nP
lugi
n
Group
Built on top of proven technology!Shares many pieces of async Replication.Multi-Master approach to replication.
Built on reusable components!Layered implementation approach.Interface driven development.Decoupled from the server core.The plugin listens to server events.Capture procedure of async replication.Communication decoupled from the rest.
39
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ArchitectureServer and Plugin APIs
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
APIs: Capture / Apply / Lifecycle
Capture Applier Recovery
Replication Protocol Logics
Group Communication System API
Group Communication Engine (Paxos)
MySQL Server
MyS
QL
Gro
up
Re
plic
atio
nP
lugi
n
Group
Server to Plugin:Lifecycle events: server start/stop, readyfor connections, before/after commit, ...Some semi-sync interfaces reused.New interfaces created.Internals are hidden from the plugin.
Plugin to Server:Communicate fate of a transaction.Communicate GTID, Recovery Actions.Engage the relay log.
40
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Maintains distributed execution context.Conflict detection.Distributed recovery:Handle membership changes;Donate state if needed;Collect state if needed.
Inject transactions into the relay log.Decide the fate of on-going transactions.
ArchitecturePlugin Core
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
APIs: Capture / Apply / Lifecycle
Capture Applier Recovery
Replication Protocol Logics
Group Communication System API
Group Communication Engine (Paxos)
MySQL Server
MyS
QL
Gro
up
Re
plic
atio
nP
lugi
n
Group
41
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Abstracts the communication engine.
Maps the interface to a specific communication engine.
ArchitectureGeneric Communication API
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
APIs: Capture / Apply / Lifecycle
Capture Applier Recovery
Replication Protocol Logics
Group Communication System API
Group Communication Engine (Paxos)
MySQL Server
MyS
QL
Gro
up
Re
plic
atio
nP
lugi
n
Group
42
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Optimized Paxos implementation.Distributed agreement between servers.Totally ordered message delivery.Safe message delivery.Closed group communication.View synchrony.
ArchitectureGroup Communication Engine
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
APIs: Capture / Apply / Lifecycle
Capture Applier Recovery
Replication Protocol Logics
Group Communication System API
Group Communication Engine (Paxos)
MySQL Server
MyS
QL
Gro
up
Re
plic
atio
nP
lugi
n
Group
43
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA and GR Releases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
Very well!
Lets check how it performsand how does it scale!
44
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Performance
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
5000
10000
15000
20000
25000
30000
2 3 5 7 9
THR
OU
GH
PU
T (T
PS)
NUMBER OF SERVERS IN THE GROUP.
Group Replication Throughput(as perceived by the client application)
Single-master Sustained Single-master Peak Multi-master Sustained
Multi-master Peak Single-server (MySQL 5.7.14)
Replication Performance blogs at: http://mysqlhighavailability.com/category/performance/
Servers9 Dual Xeon E5-2660-v3Enterprise SSD Storage10Gbps Ethernet Network
Client1 Dual Xeon E5-2699-v310Gbps Ethernet NetworkSysbench 0.5 RW workload
Peak ThroughputThe number of transactions that writers can propagate to the group (per second).
Sustained ThroughputThe number of transactions that can be propagated to the group without increasing the replication lag on any node (per second).
45
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Performance
• On a sustained throughput:
–Multi-master performance degrades gracefully while going from a group with 2 servers to a group with 9 servers.
– Single primary performance degrades marginally when growing the group size.
• On a peak throughput:–Multi-master exhibits 1.8X speedup when compared to the single server.• Read load is balanced across the servers in the group.
• Write load is lower since execution is balanced across the group, whereas in single primary mode the primary becomes a bottleneck.
–With a single primary there is no lag on the other members.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 46
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA and GR Releases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
The fine print?Here it is...
47
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Requirements (by design)
• Requires InnoDB storage engine.
• Requires a primary key on every table.
• Requires global transaction identifiers turned on.
• Requires binary log turned on.
• Requires binary log row format.
• Optimistic execution: transactions may abort on COMMIT.
• Maximum 9 servers in the group.
48Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
• Serializable (on multi-master)
• Cascading Foreign Keys (on multi-master)
• Transaction savepoints
• Binary log events checksum
Prohibited
• Concurrent DDL (on multi-master)
Heads Up
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Background1
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
2 Latest Developments in Replication GA3 Latest Developments in Replication Plugins4 Latest Developments in Replication Core
49
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA and GR Releases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
Last but not least, MySQL 8.0.0came out, and together with
it some interesting replicationfeatures. Lets have a look.
50
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Cleaning up and Incremental Improvements
• START SLAVE supports other clauses than just SQL_BEFORE_GTIDSwhen multi-threaded is active.
• Removed deprecated binary log events from the codebase.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
mysql> SET GLOBAL slave_parallel_workers=4;Query OK, 0 rows affected (0,00 sec)
mysql> START SLAVE UNTIL MASTER_LOG_FILE="master-bin.000001", MASTER_LOG_POS=4;Query OK, 0 rows affected (0,00 sec)
51
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Stages to track Row-based replication progress.
• Three new performance schema stages added:
– “Applying batch of row changes (write)”
– “Applying batch of row changes (update)”
– “Applying batch of row changes (delete)”
• Progress for each stage is reported on the performance schema table EVENTS_STAGES_CURRENT.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
mysql> SELECT THREAD_ID, EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM events_stages_currentWHERE EVENT_NAME LIKE '%Applying batch of row%'\G*************************** 1. row ***************************
THREAD_ID: 36EVENT_NAME: stage/sql/Applying batch of row changes (write)
WORK_COMPLETED: 98WORK_ESTIMATED: 2001 row in set (0,00 sec)
52
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Provision to set GTID_PURGED.
• The user is now able to set GTID_PURGED without having to have GTID_EXECUTED empty.
• SET GLOBAL GTID_PURGED= +’<gtid_set>’
– Adds this range to the existing GTID_PURGED.
• SET GLOBAL GTID_PURGED=‘<gtid_set>’
– Sets GTID_PURGED to the gtid_set provided.
• Noteworthy:– gtid_set must be a super set of the existing GTID_PURGED – do not forget GTIDs;
– gtid_set must not intersect the set of gtids in GTID_EXECUTED that have not yet been purged – same GTID cannot be purged and not purged at the same time.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 53
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
mysql> SELECT @@GLOBAL.GTID_PURGED\G*************************** 1. row ***************************@@GLOBAL.GTID_PURGED: 1 row in set (0,00 sec)
mysql> SET GLOBAL GTID_PURGED=+'00000000-0000-0000-0000-000000000000:1';Query OK, 0 rows affected (0,00 sec)
mysql> SELECT @@global.GTID_PURGED\G*************************** 1. row ***************************@@global.GTID_PURGED: 00000000-0000-0000-0000-000000000000:11 row in set (0,00 sec)
mysql> SET GLOBAL GTID_PURGED='00000000-0000-0000-0000-000000000000:1-10';Query OK, 0 rows affected (0,00 sec)
mysql> SELECT @@global.GTID_PURGED\G*************************** 1. row ***************************@@global.GTID_PURGED: 00000000-0000-0000-0000-000000000000:1-101 row in set (0,00 sec)
Provision to set GTID_PURGED.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 54
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Background1
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
2 Latest Developments in Replication GA3 Latest Developments in Replication Plugins4 Latest Developments in Replication Core5 Roadmap
55
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Timeline – Server GA and GR Releases
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
MySQL 3.23.15replication added to MySQLstatement-based replication
MySQL 5.1.30row-based replication
MySQL 4.0.12
MySQL 4.1.7
MySQL 5.5.8semi-sync replication
MySQL 5.6.10crash-safe replication metadataglobal transaction Identifiersmulti-threaded applierbinary log group commit
MySQL 5.7.9large semi-sync enhancements2nd generation of multi-threaded appliermulti-source replicationgroup replication server core changesXA support in replication
MySQL 5.0.15
What now?
Well, there is more to come.We are living exciting times!
56
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
The End Goal: MySQL InnoDB Clusters
57Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
MySQL Connector
Application
MySQL Router
MySQL Shell
HA
Rep
licaS
et1
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
HA
Rep
licaS
et 2
Rep
licaS
et 3
MySQL Connector
Application
MySQL Router
S1 S2 S3 S4
M
M M
HA
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
The Road Ahead
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
Read Scale-Out
Async Replication + Auto Failover
Write Scale-Out
Sharding
S1
S2
S3
S4
Timeline
MySQL Document Store
Relational & Document Model
MySQL HA
Out-of-the-box HA
58
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Specifically on the Replication Infrastructure!
• Production-ready MySQL Group Replication
– Then, continue to extend Group Replication and Group Communication functionality.
• MySQL Replication Usability
– Instrument even more replication and extend replication P_S tables.
– Simpler administrative commands, more online and distributed operations.
• MySQL Replication Performance– Continue to improve replication stream multi-threaded (slave) applier.
– Continue to improve replication stream pipeline performance and efficiency.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 59
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Background1
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands
2 Latest Developments in Replication GA3 Latest Developments in Replication Plugins4 Latest Developments in Replication Core5 Roadmap
Summary6
60
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Summary
• Replication feature set in MySQL 5.7 shows many appealing features… and infrastructure to build more advanced solutions based on MySQL technology.
• Group Replication plugin is the next big thing in MySQL Replication horizon.
– High Availability, Multi-master, Conflict detection
– Performance, usability, flexibility, all in one package.
• MySQL 8.0.0 already shows some interesting replication features, such as more observability and flexibility.
• The vision and roadmap are sound and clearly show where MySQL Replication is heading.
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 61
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Where to go from here?
• Packages
– http://dev.mysql.com
– http://labs.mysql.com
• Reference Documentation– http://dev.mysql.com/doc/refman/5.7/en/replication.html
• Blogs from the Engineers (news, quirks, technical information and much more)
– http://mysqlhighavailability.com
• Technical documentation about Group Replication (by the engineers):
– http://mysqlhighavailability.com/gr/doc
Tuesday, October 4, 2016 Percona Live 2016 - Amsterdam, The Netherlands 62