Percona XtraDB Cluster

Built-in MySQL Replication is known for its capability to enable to scale reads easily. However, there are some limitations and known issues with this solution because of the asynchronous nature of this replication. This talk will describe another way of doing MySQL replication, by using synchronous replication, available in Percona XtraDB Cluster. The open source solution will be explained and compared to traditional asynchronous MySQL replication, as well as some known use cases will be described. Percona XtraDB Cluster is an, open source, high availability and high scalability solution for MySQL clustering. Features include: Synchronous replication, Multi-master replication support, Parallel replication, Automatic node provisioning.

1. Synchronous*Replica1on*for*MySQL
Kenny%Gryp

<[email protected]>%
29%Oct%2013
1

2. Agenda
•Default%asynchronous%MySQL%replication%
•Percona%XtraDB%Cluster:%
•Introduction%/%Features%/%Load%Balancing%
•Use%Cases:%
•High%Availability%/%WAN%Replication%/%Read%Scaling%
•Limitations%
•Future
2

3. Percona
•Percona%is%the%oldest%and%largest%independent%MySQL%
Support,%Consulting,%Remote%DBA,%Training,%and%
Software%Development%company%with%a%global,%24x7%
staff%of%over%100%serving%more%than%2,000%customers%
in%50+%countries%since&2006%!%
•Our%contributions%to%the%MySQL%community%include:%
•Percona*Server,*Percona*XtraDB*Cluster*
•Percona*XtraBackup:*online*backup*
•Percona*Toolkit,*Percona*Playback…*
•books,%and%research%published%on%the%MySQL%Performance%Blog.
3

4. Agenda
•Default*asynchronous*MySQL*Replication*
•Percona%XtraDB%Cluster:%
•Introduction%/%Features%/%Load%Balancing%
•Use%Cases:%
•High%Availability%/%WAN%Replication%/%Read%Scaling%
•Limitations%
•Future
4

5. MySQL*Replication
If your HA is based on MySQL Replication -!
You may be playing a dangerous game !
5

6. Traditional*Replication*Approach
Server*1 Server*2
replication*stream
“master” “slave”
6

7. MySQL*Replication
7
1 2
3 4
5 6
•Common%Topologies:%
•MastercMaster%(Only%1%active%master)%
•1%or%more%layers%of%replication

8. •Slaves%can%be%used%for%reads:%
•asynchronous,%stale%data%is%the%rule%
•data%loss%possible%(*semicsync)
MySQL*Replication
8
1 2
3 4
5 6

9. •nonctrivial:%
•external%monitoring%
•scripts%for%failover%
•add%node%==%restore%backup%
•much%better%in%

MySQL%5.6:%GTIDs
MySQL*Replication
1 2
3 4
5 6
9

10. Agenda
•Default%asynchronous%MySQL%Replication%
•Percona*XtraDB*Cluster:*
•Introduction*/*Features*/*Load*Balancing*
•Use%Cases:%
•High%Availability%/%WAN%Replication%/%Read%Scaling%
•Limitations%
•Future
10

11. 11Percona*XtraDB*Cluster

12. 12Percona*XtraDB*Cluster
•All%nodes%have%a%full%copy%of%the%data%
•Every%node%is%equal%
•No%central%management,%no%SPOF

13. 13CAP*Theorem*
•MySQL%(Asynchronous)%Replication:%
•Availability%
•Partition%Tolerance%
•Percona%XtraDB%Cluster%
•Consistency%
•Availability
Consistency
Availability
Partition%
Tolerance

14. What*is*Percona*XtraDB*Cluster*?
•Percona%Server%
•+%WSREP%patches%
•+%Galera%library%
•+%Utilities%(init,%SST%and%cluster%check%scripts)%
14

15. 15
•This%is%a%free$open$source%solution,%Percona%Server%is%a%
MySQL&alternative%which%offers%breakthrough%
performance,%scalability,%features,%and%instrumentation.%
Selfctuning%algorithms%and%support%for%extremely%highc
performance%hardware%make%it%the%clear%choice%for%
organisations%that%demand%excellent%performance%and%
reliability%from%their%MySQL%database%server.
Percona&Server

16. WSREP&and&Galera
•WSREP&API%is%a%project%to%develop%generic%replication%
plugin%interface%for%databases%(WriteSet%Replication)%
•Galera%is%a%wsrep%provider%that%implements%multicmaster,%
synchronous%replication
16

17. What&is&Percona&XtraDB&Cluster&?
Full&
compatibility&&
with&existing&
systems
17

18. What&is&Percona&XtraDB&Cluster&?
Minimal&efforts&
to&migrate
18

19. What&is&Percona&XtraDB&Cluster&?
Minimal&efforts&
to&return&back&
to&MySQL
19

20. 20Features
•Synchronous%Replication%
•Multi%Master%
•Parallel%Applying%
•Quorum%Based%
•Certification/Optimistic%Locking%
•Automatic%Node%Provisioning

21. 21Features
•Synchronous&Replication&
•Multi%Master%
•Parallel%Applying%
•Quorum%Based%
•Certification/Optimistic%Locking%
•Automatic%Node%Provisioning

22. 22(Virtual)&Synchronous&Replication
•Writesets%(transactions)%are%replicated%to%all%
available%nodes%on&commit%(and%queued%on%
each)%
•Writesets%are%individually&“certified”&on%every%
node,%deterministically.Either%it%is%committed%
on%all%nodes%or%no%node%at%all%(NO%2PC)%
•Queued%writesets%are%applied%on%those%nodes%
independently%and%asynchronously%
•Flow&Control&avoids%too%much%‘lag’

23. 23(Virtual)&Synchronous&Replication

24. 24(Virtual)&Synchronous&Replication
•Reads%can%read%old%data%
•Flow%Control%(by%default%16%trx)%avoids%lag%
•wsrep_causal_reads-can%be%enabled%to%
ensure%full%synchronous%reads%
•Latency:%writes%are%fast,%only%at%COMMIT,%
communication%with%other%nodes%happen

25. 25Stale&Reads

26. 26Latency

27. 27Features
•Synchronous%Replication%
•Multi&Master&
•Parallel%Applying%
•Quorum%Based%
•Certification/Optimistic%Locking%
•Automatic%Node%Provisioning

28. MultiPMaster&Replication
•You%can%write%to%any%node%in%your%cluster*%
•Writes%are%ordered%inside%the%cluster%
writes
writes
writes
28

29. 29Features
•Synchronous%Replication%
•Multi%Master%
•Parallel&Applying&
•Quorum%Based%
•Certification/Optimistic%Locking%
•Automatic%Node%Provisioning

30. Parallel&Replication
•Standard%MySQL
Application 

writes N threads
Apply&1&thread&
(MySQL&5.6:&
max&1&thread&&
per&schema)
30

31. Parallel&Replication
•PXC%/%Galera
Application 

writes N threads
Apply M threads 

(wsrep_slave_threads)
31

32. 32Features
•Synchronous%Replication%
•Multi%Master%
•Parallel%Applying%
•Quorum&Based&
•Certification/Optimistic%Locking%
•Automatic%Node%Provisioning

33. Quorum&Based
•If%a%node%does%not%see%more%than%50%%of%the%total%
amount%of%nodes:%reads/writes%are%not%accepted.%
•Split%brain%is%prevented%
•This%requires%at%least%3%nodes%to%be%effective%
•a%node%can%be%an%arbitrator%(garbd),%joining%the%
communication,%but%not%having%any%MySQL%running%
•Can%be%disabled%(but%be%warned!)
33

34. •Loss%of%connectivity
Quorum&Based
34
Network%Problem
Does%not%accept%Reads%&%Writes

35. •4%Nodes
Quorum&Based
35

36. •Default%quorum%configuration:

4%Nodes,%0%Nodes%have%quorum
Quorum&Based
36
Network%Problem

37. 37Features
•Synchronous%Replication%
•Multi%Master%
•Parallel%Applying%
•Quorum%Based%
•Certification/Optimistic&Locking&
•Automatic%Node%Provisioning

38. 38Certification

39. 39Optimistic&Locking
•Communication%to%the%other%nodes%of%the%
cluster%only%happens%during%COMMIT,%this%
affects%locking%behavior.%
•Optimistic%Locking%is%done:%
•InnoDB%Locking%happens%local%to%the%node%
•During%COMMIT/Certification,%the%other%
nodes%bring%deadlocks

40. 40Optimistic&Locking
•Some%Characteristics:%
•also%COMMIT%and%SELECT’s%can%fail%on%
deadlock&
•Might%require%application%changes:

Not%all%applications%handle%this%properly

41. 41Traditional&InnoDB&Locking
system 1
Transaction 1
Transaction 2
BEGIN
Transaction1
BEGIN
UPDATE t WHERE id=14
UPDATE t WHERE id=14
...
COMMIT
Waits on COMMIT in trx 1

42. 42Traditional&InnoDB&Locking
system 1
Transaction 1
Transaction 2
BEGIN
Transaction1
BEGIN
UPDATE t WHERE id=14
UPDATE t WHERE id=14
...
COMMIT
DML,COMMIT or SELECT
ERROR due row conflict
system 2
...

43. 43Traditional&InnoDB&Locking
system 1
Transaction 1
Transaction 2
BEGIN
Transaction1
BEGIN
UPDATE t WHERE id=14
UPDATE t WHERE id=14
...
COMMIT
COMMIT
ERROR due row conflict
system 2
...ERROR 1213 (40001): Deadlock found when trying
to get lock; try restarting transaction

44. 44Optimistic&Locking

45. 45Features
•Synchronous%Replication%
•Multi%Master%
•Parallel%Applying%
•Quorum%Based%
•Certification/Optimistic%Locking%
•Automatic&Node&Provisioning

46. Automatic&Node&Provisioning
•When%a%node%joins%the%cluster:%
•the%data%is%automatically%copied%%
•when%finished:%the%new%node%is%automatically%
ready%and%accepting%connections%
•2%different%types%of%joining:%
–SST%(state-snapshot-transfer):%full%copy%of%the%data%
–IST%(incremental-state-transfer):%send%only%the%
missing%writesets%(if-available)-
46

47. StateTransfer&Summary
Full data!
SST
Incremental!
IST
New node
Node long!
time!
disconnected
Node!
disconnected!
short time
47

48. Snapshot&State&Transfer
mysqldump
Small!
databases
rsync
Donor!
disconnected!
for copy time
Faster
XtraBackup
Donor!
available
Slower
48

49. Incremental&State&Transfer
Node was!
in the cluster
Disconnected!
for maintenance
Node!
crashed
49

50. Automatic&Node&Provisioning
writes
writes
writes
new node joining
data is copied via SST or IST
50

51. Automatic&Node&Provisioning
writes
writes
writes
new node joining
when ready
writes
51

52. PXC&with&a&Load&balancer
•PXC%is%often%integrated%with%a%load%balancer%
•service%can%be%checked%using%clustercheck%or%
pyclustercheck-
•The%load%balancer%can%
•be%a%dedicated%layer%
•integrated%at%application%layer%
•integrated%at%database%layer
52

53. Dedicated&shared&HAProxy&
application server 1 application server 2 application server 3
PXC node 1 PXC node 2 PXC node 3
HA PROXY
53

54. Dedicated&shared&HAProxy&
application server 1 application server 2 application server 3
PXC node 1 PXC node 2 PXC node 3
HA PROXY
54

55. Dedicated&shared&HAProxy&
application server 1 application server 2 application server 3
PXC node 1 PXC node 2 PXC node 3
HA PROXY
SST
available_when_donor=0
5

56. HAProxy&on&application&side
56

57. Agenda
•Default%asynchronous%MySQL%Replication%
•Percona%XtraDB%Cluster:%
•Introduction%/%Features%/%Load%Balancing%
•Use&Cases:&
•High&Availability&/&WAN&Replication&/&Read&Scaling&
•Limitations%
•Future
57

58. 58Use&Cases
•High%Availability%
•WAN%Replication%
•Read%Scaling

59. High&Availability&
59
•Each%node%is%the%same%(no%mastercslave)%
•Consistency%ensured,%no%data%loss%
•Quorum%avoids%splitcbrain%
•Cluster%issues%are%immediately%handled%on%
•no%‘failover’%necessary%
•no%external%scripts,%no%SPOF

60. 60WAN&replication
MySQL
MySQL
MySQL
•No%impact%on%reads%
•No%impact%within%a%trx%
•Communication%only%happens%during

COMMIT%(or%if%autocommit=1)%
•Use%higher%timeouts%and

send%windows

61. •Beware%of%increased%latency%
•Within%EUROPE%EC2%
•COMMIT:%0.005100%sec%
•EUROPE%<c>%JAPAN%EC2%
•COMMIT:%0.275642%sec
61WAN&replication&P&latency
MySQL
MySQL
MySQL

62. 62
WAN&replication&with&MySQL&asynchronous&
replication
MySQL
MySQL
MySQL
•You%can%mix%both%types%of%replication%
•Good%option%on%slow%WAN%link%
•Requires%more%nodes%
•If%binlog%position%is%lost,%

full%cluster%must%be%

reprovisioned%(*)
MySQL
MySQL
MySQL
MySQL
MySQLMySQL

63. Agenda
•Default%asynchronous%MySQL%Replication%
•Percona%XtraDB%Cluster:%
•Introduction%/%Features%/%Load%Balancing%
•Use%Cases:%
•High%Availability%/%WAN%Replication%/%Read%Scaling%
•Limitations&
•Future
63

64. 64Limitations
•Supports%only%InnoDB%tables%
•MyISAM%support%will%most%likely%stay%in%alpha.%
•The%weakest%node%limits&write&performance&
•All%tables%must%have%a%Primary&Key!

65. 65Limitations
•Large%Transactions%are%not%recommended%if%
you%write%on%all%nodes%simultaneously%
•Long&Running&Transactions&
•If%the%workload%has%a%hotspot%then%
(frequently%writing%to%the%same%rows%across%
multiple%nodes)%
•Solution:%Write%to%only%1%node

66. 66Limitations
•WAN%Replication:%All%nodes%connect%to%all%
nodes,%causing%some%network%overhead%
•Mixing%Galera%with%asynchronous%replication%
is%hard%to%manage%(no%GTID%support)

67. Agenda
•Default%asynchronous%MySQL%Replication%
•Percona%XtraDB%Cluster:%
•Introduction%/%Features%/%Load%Balancing%
•Use%Cases:%
•High%Availability%/%WAN%Replication%/%Read%Scaling%
•Limitations%
•Future
67

68. 68Galera&3.0&P&Currently&BETA
•MySQL%5.6%Support%
•GTID:%solves%many%issues%with%mixing%
asynchronous%replication.%
•Improved%WAN%support%(cluster%segmentation)%
•Performance%improvements%
•Better%large%TRX%handling

69. 69Credits
•WSREP%patches%and%Galera%library%is%
developed%by%Codership%Oy

http://www.codership.com%
•Percona%&%Codership%will%present%on%Percona%
Live%UK%2013,%Nov%11c12%

http://www.percona.com/live/londonc2013/

70. Summary
•Default%asynchronous%MySQL%Replication%
•Percona%XtraDB%Cluster:%
•Introduction%/%Features%/%Load%Balancing%
•Use%Cases:%
•High%Availability%/%WAN%Replication%/%Read%Scaling%
•Limitations%
•Future
70

71. 71Resources
•Percona%XtraDB%Cluster%website:%

http://www.percona.com/software/perconacxtradbccluster/%
•Codership%website:%

http://www.codership.com/wiki/doku.php%
•PXC%articles%on%mysqlperformanceblog:%

http://www.mysqlperformanceblog.com/category/
perconacxtradbccluster/%
•Test%it%now%using%Vagrant%!%

https://github.com/grypyrg/vagrantcperconacplayground

https://github.com/lefred/perconaccluster

https://github.com/percona/xtradbcclusterctutorial/tree/v2
Questions?
Kenny%Gryp

<[email protected]>