Replication Whats New in Mysql 8

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
Replication: What's new in MySQL 8
Luís Soares
Software Development Director
MySQL Replication
1
Nuno Carvalho
Principal Software Engineer
MySQL Replication
Vitor Oliveira
Performance Architect
MySQL Replication

Copyright © 2018, 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.
2

Program Agenda
3

Introduction
Use Cases
Enhancements in MySQL 8 (and 5.7)
Roadmap
Conclusion
Program Agenda
1
2
3
4
4
5

Introduction1
5

Copyright © 2018, Oracle and/or its affiliates. All rights reserved. | 6
Boom
Web Explodes

Today...
– Technology mesh.
– All things distributed.
– Large amounts of data to handle, transform, store.
– Offline periods are horribly expensive, simply unaffordable.
– Go green requires dynamic and adaptative behavior.
– Much more data to store – e.g. social media, “Look at all of my pictures!”;
Monitoring – Keeping logs for N years! ; IoT – and much more.
– Moving, transforming and processing data quicker than anyone else means
having an edge over competitors.
– It is a zoo. Distributed coordination and monitoring is key.
7

Database Replication
Server
B
Server
A
App
INSERT ...
INSERT ... INSERT ...
Replication
“The process of generating and reproducing
multiple copies of data at one or more sites.”,
Database Systems: A Practical Approach to Design, Implementation, and
Management, Thomas M. Connolly, Carolyn E. Begg, Third Edition, 2002.
8

MySQL Database Replication: Overview
INSERT ...
Server
B
binary log
INSERT ...
relay log
INSERT ...
Server
A
binary log
App
Receiver
Meta-data
Update
Applier
Meta-data
Update
INSERT ...
Comm.
Framework
9

MySQL Database Replication: Overview
INSERT ...
Server
B
binary log
INSERT ...
relay log
INSERT ...
Server
A
binary log
App
Receiver
Meta-data
Update
Applier
Meta-data
Update
INSERT ...
Persistent
log buffer Threaded
applier
Send, Receive,
ACK, NACK,
Heartbeating,
...
Comm.
Framework
Capture
statements
or data
changes.
Persistent
log buffer
10

MySQL Database Replication: Some Notes
• Logical replication log recording master changes (binary log).
• Row or statement based format (may be intermixed).
• Each transaction is split into groups of events.
• Control events: Rotate, Format Description, Gtid, and more.
Binary Log
Layout of the Binary Log.
BEGIN ...E1 E2 COMMIT BEGIN ...E1 E2 COMMITGTID GTID
11

Coordination Between Servers
MySQL Database Replication: Some Notes
12
A B Since 3.23
A B
semi-synchronous (plugin)
A CB
group replication (plugin)
transactions
transactions
Since 5.5
Since 5.7.17
asynchronous (native)
transactions, membership, coordination
acks
And in MySQL 8 as of 8.0.1

Use cases2
13

Clustering Made Practical
R
A
I
S
E
eplicate
utomate
ntegrate
cale
nhance
14

Replicate
• For highly available infrastructures where:
– the number of servers has to grow or shrink dynamically;
– with as little pain as possible.
Group Replication
A B C D E
15

Automate
• Single-primary mode
– Automatic PRIMARY/SECONDARY role assignment
– Automatic new PRIMARY election on PRIMARY failures
– Automatic setup of read/write modes on PRIMARY and SECONDARIES
– Automatic global consistent view of which server is the PRIMARY
Group Replication
S S S* S P S
Next Primary
Old Primary
16

Integrate
Binary Log
• Logical replication log
– Extract, transform and load.
– MySQL fits nicely with other
technologies.
INSERT ...
Server
A
Replication log
App
INSERT ...
Hadoop
Kafka
Solr
Lucene
...
Transform
17

Scale
• Replicate between clusters
– For disaster recovery
• Read Replicas
– For read-scale out. Deploy
asynchronous read replicas
connected to the cluster
Asynchronous Replication A B C
J K L
D
I
M
Z
.
.
.
.
.
.
18

InnoDB Cluster
Enhance
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
MySQL Connector
Application
MySQL Router
MySQL Shell
HA
ReplicaSet1
• InnoDB Cluster Integrated Solution
– Group Replication for high availability.
– Asynchronous Replication for Read Scale-
out.
– One-stop shell to deploy and manage the
cluster.
– Seamlessly and automatically route the
workload to the proper database server in
the cluster.
– Hide failures from the application.
19

Binary Log Enhancements
3.1
3
20
Operations3.2
Performance3.3
Monitoring3.4
Other3.5

New Metadata in the Binary Log
Easy to extract, transform and load into other systems.
Gtid_log_event
Transaction length, …
Commit timestamps
Table_map event
Types, primary key, …
Sign, charset, …
Write_rows
Commit
Server
A
Binary log
App
INSERT ...
21
• New Metadata
– Easy to decode what is in the
binary log.
– Further facilitates connecting
MySQL to other systems using the
binary log stream.
– Capturing data changes through
the binary log is simplified.
– Also more stats showing where
the data is/was at a certain point
in time.

Binary Log Enhancements3.1
3
22
Operations3.2
Performance3.3
Monitoring3.4
Other3.5

Replicate, Filter, Aggregate, Query
Multi-Source Replication Filters
23
A
B
C
users
bio
groups
users
posts
comments
users bio groups posts comments
only users

Automatic protection against involuntarily tainting of offline replicas
Preventing Updates On Replicas that Leave the Cluster
24
B C
A
B CA
A joins a single primary cluster and
it allows update operations prior
to joining.
A becomes a secondary and is
automatically set to read only.
B C
A
A leaves the group and remains
read only until the DBA reverts.
Backported to 5.7.20

Choose next primary by assigning election weights to the candidates.
Primary Election Weights
25
B CA
w=100 w=90w=50
B
CA
w=100
w=90w=50
CA
w=90w=50
B is the primary.
B is not in the group anymore.
C has higher weight than A.
C is elected the new primary.

Automatically Shutdown When Replica Leaves the Group Involuntarily
Automatically Abort Replicas that Leave the Group
26
B C
A
A shuts itself down, thus becoming
effectively unavailble for both reads
and write operations.
B CA
A is a secondary and was
automatically set to read only.
B C
A
A leaves the group involuntarily
(failed to apply changes, network
partitioned, etc).
New in 8.0.12
@@group_replication_exit_state_action={ READ_ONLY | ABORT_SERVER }

User tells current primary to give up its role and assign it to another server.
Trigger Primary Election Online
27
B CA B CA
B is the primary.
Now I want A to be the primary, not B.
New in 8.0.13
A CB
A is the primary. B stepped down.

User can specify on which mode the group operates.
Change Group Mode Online
28
B CA B CA
Single-primary mode,
B is the primary.
Now I want the group operating in
multi-primary mode.
New in 8.0.13
A CB
Return to single-primary mode.

User controls the amount of time to wait until others decide to evict a member from
the group.
Relaxed Member Eviction
29
B CA B CA
I think A is not around anymore.
Lets kick it out of the group!
New in 8.0.13
C
See… I told you. A is back!
Good thing we waited.
Hold on. Lets wait for sometime!
Maybe it is just too busy to talk to us
right now...
OK. Lets give it some
more time!
UNREACHABLE
A B

3
30
Operations3.2
Performance3.3
Monitoring3.4
Other3.6

Highly Efficient Replication Applier
• WRITESET dependency tracking allows applying a single threaded
workload in parallel.
– Delivers the best throughput of the three dependency trackers, at any concurrency
level.
• WRITESET_SESSION in addition to writesets tracks sessions dependencies
as well. Two transactions executed on the same session are always
scheduled in execution order on replica servers.
• Fast Group Replication recovery – time to catch up.
Write set parallelization
31

Write set parallelization
Highly Efficient Replication Applier
32
0
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
1 2 4 8 16 32 64 128 256
Updates/secondAppliesontheReplica
Number of Clients on the Master
Applier Throughput: Sysbench Update Index
COMMIT_ORDER WRITESET WRITESET_SESSION

Replica quickly online by using WRITESET
Fast Group Replication Recovery
33
0
1
2
3
4
5
6
7
8
9
10
Sysbench RW at 33% capacity
(workload: 9K TPS on 64 threads)
Timetocacth-uppertimeofworloadmissing(ratio)
Group Replication Recovery Time: Sysbench
Update Index (durable settings)
MySQL 5.7.20 MySQL 8.0.3
0
1
2
3
4
5
6
7
8
9
10
Timetocacth-uppertimeofworloadmissing(ratio)
Group Replication Recovery Time: Sysbench
RW (durable settings)
MySQL 5.7.20 MySQL 8.0.3

High Cluster Throughput
– At lower thread count, the throughput of the system doubles in MySQL 8.0 compared
to MySQL 5.7 on durable settings.
– At lower thread count, the throughput of the system more than doubles in MySQL 8.0
compared to MySQL 5.7 on non-durable settings.
More transactions per second while sustaining zero lag on any replica
34

More transactions per second while sustaining zero lag on any replica
High Cluster Throughput
35
8 16 32 64
Asynchronous Replication Sustained Throughput
(Sysbench Update Index, non-durable settings)
MySQL 5.7 MySQL 8.0.3
0
5000
10000
15000
20000
25000
30000
35000
40000
45000
8 16 32 64
Updatespersecond
Asynchronous Replication Sustained Throughput
(Sysbench Update Index, durable settings)
MySQL 5.7 MySQL 8.0.3

Replicate only changed fields of documents (Partial JSON Updates)
Efficient Replication of JSON Documents
36
0
10000
20000
30000
40000
50000
60000
Entire JSON Partial JSON
Bytespertransaction
Binary Log Space Per Transaction
FULL MINIMAL
• Numbers are from a specially
designed benchmark:
• tables have 10 JSON fields,
• each transaction modifies
around 10% of the data

0,0
500,0
1000,0
1500,0
2000,0
2500,0
1 2 3 4
Transactionspersecond
Throughput on the Master:
Partial JSON vs Complete JSON
4 8 16 32 64
Replicate only fields of the document that changed (Partial JSON Updates)
Efficient Replication of JSON Documents
37
0
1000
2000
3000
4000
5000
6000
7000
8000
9000
Throughput on the Slave:
Partial JSON vs Ccomplete JSON
4 8 16 32 64
FULL MINIMAL FULL MINIMAL FULL MINIMAL FULL MINIMAL
COMPLETE JSON PARTIAL JSON COMPLETE JSON PARTIAL JSON

3
38
Operations3.2
Performance3.3
Monitoring3.4
Other3.5

Through the entire asynchronous topology
Monitor Lag With Microsecond Precision
39
A B C D
How much time does my data take to reach
D coming from A?

From the immediate master
Monitor Lag With Microsecond Precision
40
A B C D
How much time does my data originated in
A takes to flow from B to C?

For each stage of the replication applier process
Monitor Lag with Microsecond Precision
41
D
Data
Worker ThreadsRelay Log
Coordinator
Thread
Receiver
Thread
• Per Stage Timestamps
– User can monitor how much time
it takes for a specific transaction
to traverse the pipeline.

Version, Role and more
Global Group Stats Available on Every Server
42
A B C
A is primary.
B, C are secondaries.
A: 5.7.20
B, C: 8.0.3
A is primary.
A: 5.7.20
B, C: 8.0.3
A is primary.
A: 5.7.20
B, C: 8.0.3
• Query one Replica, Get status
of all
– Every replica reports group-wide
information about roles and
versions of the members of the
group.
– Also available at any replica are
group-wide status.

Group Replication Message Cache Memory Usage
• GCS/XCom’s Paxos message cache is instrumented.
• GCS/XCom’s Paxos message cache memory usage is exposed in
performance schema.
43
-- This is a session open on ServerA and the user is reading stats on GCS_Xcom message cache
ServerA> select * from memory_summary_global_by_event_name where event_name
like "%GCS_XCom%"G
*************************** 1. row ***************************
EVENT_NAME: memory/group_rpl/GCS_XCom::xcom_cache
COUNT_ALLOC: 28890317
COUNT_FREE: 28840318
SUM_NUMBER_OF_BYTES_ALLOC: 24499151783
SUM_NUMBER_OF_BYTES_FREE: 24470424555
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 49999
HIGH_COUNT_USED: 50000
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 28727228
HIGH_NUMBER_OF_BYTES_USED: 135676530
1 row in set (0.01 sec)
New in 8.0.12

3
44
Operations3.2
Monitoring3.3
Performance3.4
Other3.5

Changes to defaults in MySQL 8
• Binary log is on by default.
• Logging of slave updates is on by default.
• Replication metadata is stored in InnoDB tables by default instead of files.
• Row-based applier uses hash scans to find rows instead of table scans.
• Transaction write-set extraction is on by default.
• Binary log expiration is set to 30 days by default.
• Server-id is set to 1 by default instead of 0.
High performance replication enabled out-of-the-box
45

Other MySQL 8 Replication Enhancements
• Monitoring: Monitor replication even when disk full
• Monitoring: Current query being applied, even for row-based replication
• Monitoring: Replication filters statistics in performance schema
• Monitoring: Group Replication threads instrumented and shown in
performance schema
• Monitoring: Group Replication conditional variables and mutexes
instrumented and shown in performance schema
• Monitoring: Replication worker transaction retries counter added to
performance schema applier table.
46
New in 8.0.13

• Operations: Restore global transaction identifiers metadata on a non-
empty server
• Operations: Specify binary log file number after RESET MASTER
• Operations: Specify when binary log files are automatically purged (with
second precision)
• Operations: SAVEPOINT support when write sets are being extracted
• Operations: P_S table for consistent log positions (replacing potentially
expensive FLUSH TABLE WITH READ LOCKS)
• Operations: Support hostnames in Group Replication whitelist
• Operations: New options to fine tune the cluster automatic flow control.
47

• Troubleshooting: Dynamic and high performance debugging of group
replication inter-node messaging
• Recoverability: Recover DDL and binary log together after a crash
48

Roadmap4
49

The Road to MySQL 8 Group Replication and InnoDB Clusters
MySQL 5.7.9
5.7 is GA
lifecycle interfaces
P_S tables for GR
server side changes
GR 0.2.0 labs
Hello world!
MySQL 5.7.17
GR is GA
MySQL 8.0.1 DMR
GR is released with 8
InnoDB Cluster is GA
Lots of replication
enhancements
MySQL 8.0.3 RC1
bug fixes
partial json updates
monitoring enhancements
50
MySQL 8.0.4 RC2
bug fixes
monitoring
MySQL 8.0.11 GA
bug fixesMySQL 8.0.0 DMR
MySQL 8.0.2 DMR
More replication
enhancements
MySQL 8.0.12 GA
Member auto-shutdown
Instrumentation
MySQL 8.0.13 GA
Select Primary
Monitoring
Performance
Relax Eviction

MySQL InnoDB Cluster: The End Goal
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
MySQL Connector
Application
MySQL Router
MySQL Shell
HA
ReplicaSet1
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
HA
ReplicaSetN
51
...

Conclusion5
52

Conclusion
Latest MySQL 8 GA is out:
• Performance/efficiency improvements
– Group Replication copes better with high latency networks.
• Replication instrumentation
– More memory and replication stats instrumentation.
• Improved Dev-Ops and DBA experience
– Options to relax automatic member eviction
– Trigger primary election and select new primary online.
– Automatically shutdown the server if member gets out of the group involuntarily.
53

Where to go from here?
• Packages
– http://www.mysql.com/downloads/
• Documentation
– https://dev.mysql.com/doc/refman/8.0/en/
• Blogs from the Engineers (news, technical information, and much more)
– http://mysqlhighavailability.com
54

Replication Whats New in Mysql 8

More Related Content

What's hot

What's hot (20)

Similar to Replication Whats New in Mysql 8

Similar to Replication Whats New in Mysql 8 (20)

Recently uploaded

Recently uploaded (20)

Replication Whats New in Mysql 8