Percona XtraDB Cluster ( Ensure high Availability )

It was presented on OSI days 2017 at Bangalore. This talk focus on XtraDB Cluster architecture and its improvements in latest MySQL 5.7.

1. www.mydbops.com [email protected]
Presenter

Karthik P R
CEO Mydbops
Percona XtraDB Cluster
Ensure High Availability

2. Mydbops is into MySQL/MongoDB Support and Consulting. It is founded by experts
who have scaled database at Yahoo! ,Percona and Datavail. We are providing an
expert level support and 24*7 monitoring for MySQL databases and its related
technologies like MariaDB , Percona ( also clustering ) . We support modern
database technologies in MySQL which includes Galera ( Clustering ), Group
Replication , SQL aware Load balancers like Maxscale / ProxySQL.



Mydbops

3. www.mydbops.com [email protected]
Our Clients

4. CEO / DB Architect


About Me

5. 1.Native MySQL Replication
2.Percona XtraDB Cluster
3.What is new in XtraDB Cluster ?
4.Load Balancing
5.Use Cases
6.Limitations




Table of Contents

6. 1.Native MySQL Replication
2.Percona XtraDB Cluster
3.What is new in XtraDB Cluster ?
4.Load Balancing
5.Use Cases
6.Limitations




Table of Contents

7. •One of Best Feature in MySQL from start.
•Asynchronous in nature.
•Binary log based replication.
•Data is streamed between nodes.
•Works irrespective of MySQL Engine used.





Native MySQL Replication

8. •Can have any kind of topology and any levels






Native MySQL Replication

9. •Slaves helps in the read scalability.
○Asynchronous Replication ( lag )
○Data loss and data inconsistency
○ Parallelism.
■5.6 = Database level
■5.7 = Time based group
■8.0 = Writeset ( RC )






Native MySQL Replication

10. •Replication Switchover and Failover
○Externals scripts for failover.
○GTID 5.6 and Orchestrator makes things better.
•Adding a slave (node)
○Backup and restore.
○Manual config of slave coordinates.






Native MySQL Replication

11. 1.Native MySQL Replication
2.Percona XtraDB Cluster
3.What is new in XtraDB Cluster ?
4.Load Balancing
5.Use Cases
6.Limitations




Table of Contents

12. Percona XtraDB Cluster is an active/active
high availability and high scalability open source
solution for MySQL.Percona XtraDB Cluster 5.7
is MySQL 5.7 compatible Galera-based high
availability (HA) solution.


Tightly integrated with Xtrabackup and ProxySQL for scalability. Heavily tested for
production workload and has a huge share in MySQL clustering solution & Openstack for
RDBMS.







Percona XtraDB Cluster

13. What is Galera ?
Galera is a replication plugin for the synchronous replication and multi master replication to
achieve High Availability. The read and writes can be made on any node and there is no
complex failover solutions. An open source from codership team.

What is wsrep ?

WSREP (Write Set REPlication) is an API to connect the Galera library and control the
characteristics of Galera.It helps to implement the Certificate based replication and multi
master replication.
Percona XtraDB Cluster

14. Features

●Synchronous Replication ( Virtually )
●Parallel applier threads
●Quorum based
●Automatic node provisioning
●Multi master writes
●PXC Specific features






Percona XtraDB Cluster

15. Transaction in Galera







Percona XtraDB Cluster

16. Transaction in Galera






Percona XtraDB Cluster

17. Percona XtraDB Cluster - Setup






Group Communication

Percona XtraDB Cluster

18. Parallel Appliers
●Write set consists only rows to replicate.
●Any transaction which writes different tuples can be parallelized.
●wsrep_slave_threads controls parallel threads









Percona XtraDB Cluster
Application Writes

Replication Writes

19. Percona XtraDB Cluster
Recovery
IST
(Incremental State
Transfer)
SST
(State Snapshot
Transfer)
●Disconnected for
maintenance

●Node crashed
●Joining a new node

●Node disconnected
for a long time

20. 1.Native MySQL Replication
2.Percona XtraDB Cluster
3.What is new in XtraDB Cluster ?
4.Load Balancing
5.Use Cases
6.Limitations




Table of Contents

21. ●Improved scalability by 10x.
●Tracking IST Progress
●Gcache Recovery
●PXC_Strict_mode
●Performance Schema for Galera
●Securing PXC
What is new in XtraDB Cluster ?

22. What is new in XtraDB Cluster ?
Improved XtraDB Cluster Scalability

23. What is new in XtraDB Cluster ?
IST Improvements

24. ●Gcache Recovery ( Codership )
Gcache can be made to disk with gcache.recover=yes it helps in restoring cache on
restore.It saves pains in SST.

●PXC_strict_mode
PXC Strict Mode is designed to avoid the use of experimental and unsupported features in
Percona XtraDB Cluster. It includes storage engine checks, Primary key checks, binlog format ,
Autoinc lock mode and others.
What is new in XtraDB Cluster ?

25. 1.Native MySQL Replication
2.Percona XtraDB Cluster
3.What is new in XtraDB Cluster ?
4.Load Balancing
5.Use Cases
6.Limitations




Table of Contents

26. ●Load balancer for MySQL
○HAProxy ( TCP Layer )
○Maxscale ( Application Layer )
○ProxySQL ( Application Layer )

●SQL aware load balancer (application layer) , forward the queries/statements by
understanding the underlying state of PXC.
○Read-write split up
○Automatic Switchover




Load Balancing

27. ●ProxySQL is light weight
●Build by René Cannaò
●GPL V3 License
●SQL based administration
●Custom cluster health check script
●Proxysql-admin for easy configuration.
●ProxySQL 1.4 comes with inbuilt clustering
Load Balancing ( ProxySQL )

28. ●Maxscale Build by MariaDB
●File based config
●Pluggable Router
●GPL and BSL model
○Maxscale 1.4 (GPL)
○Maxscale 2.X (BSL)
Load Balancing ( Maxscale )

29. 1.Native MySQL Replication
2.Percona XtraDB Cluster
3.What is new in XtraDB Cluster ?
4.Load Balancing
5.Use Cases
6.Limitations




Table of Contents

30. ●High Availability
○Quorum
○Automated Failover
●Read Scaling ( No write scaling )
○No read-write split required
○Read throughput increased with nodes
●WAN Based Replication.
○Optimized WAN segments
Use cases

31. Sample Architecture ( 3 node + 2 node ProxySQL )

Use cases

32. 1.Native MySQL Replication
2.Percona XtraDB Cluster
3.What is new in XtraDB Cluster ?
4.Load Balancing
5.Use Cases
6.Limitations




Table of Contents

33. ●Supports Only InnoDB Engine.
●Tables should have Primary Key ( else created internally )
●All nodes should have same capacity.
●Avoid heavier and longer transaction ( OLTP workload )
●Schema changes are costly ( use pt-osc )
●Needs a Stable and strong network.
Limitations

34. ●Percona XtraDB Cluster Official website
○https://www.percona.com/doc/percona-xtradb-cluster/
●Percona XtraDB Blogs
○https://www.percona.com/blog/category/percona-xtradb-cluster/
Resources

35. www.mydbops.com [email protected]
Contact US:
Write to me at [email protected]

DB Consulting Contact : [email protected]

Phone : [phone]