PXC-101

1 . Percona XtraDB Cluster 101 Krunal Bauskar PXC Product Lead @ Percona 1 © 2017 Percona 

2 .Agenda ▪ Why we need synchronous replication ? ▪ How PXC helps achieve it ▪ Key features of PXC ▪ 5.7 exclusive features ▪ PXC as a complete HA solution 2 © 2017 Percona 

3 . Traditional MySQL replication ● Replication delay (loss of transactions). developer ● Limited read scalability. ● No option for GROWTH write-scalability. user ● Switchover interval. ● No protection against network failure. MASTER SLAVE 3 © 2017 Percona 

4 .In search of better solution…. ● What if all nodes can act as active master ● No single point failure ● Can protect against network outages ● Read/Write-scalability ● Easy to maintain/deploy ????? 4 © 2017 Percona 

5 .PXC ▪ PXC is multi-master solution implemented using • Percona-Server (drop-in replacement to MySQL) with replication plugin. • Galera (Replication Plugin from Codership) ▪ Independent product with complete life-cycle 5 © 2017 Percona 

6 .PXC - Multi-Master ▪ Write to any node of the cluster and write will be replicated transparently to all other nodes of cluster ▪ Read from any node of the PXC cluster and get consistent view of the data. 6 © 2017 Percona 

7 .But what makes PXC enterprise ready? ▪ Automatic Node Provisioning ▪ Cluster-safe-mode ▪ Ability to handle conflicting ▪ Security workload ▪ Performance ▪ Flow-control ▪ Trackability ▪ Parallel processing ▪ Protection against network failure ▪ Geo-distributed 7 © 2017 Percona 

8 .Automatic Node Provisioning ▪ Bootstrapping cluster N1 ▪ SST (State Snapshot Transfer) ▪ rsync/mysqldump/xtrabackup* ▪ IST (Incremental Snapshot Transfer) PXC N2 N3 ▪ auto-catchup cluster state SST/IST link 8 © 2017 Percona 

9 .Handling workload conflicts ▪ Optimistic Locking. (No N1 distributed locks) update t set i = 100 ▪ Brute force abort: • Forceful abort of conflicting FIRST COMMITTER transaction N2 PXC WIN N3 ▪ Certification failure • 2 conflicting transactions update t set i = 100 replicate at same time 9 © 2017 Percona 

10 .Flow Control ▪ Dynamic control the workload N1 processing to adjust nodes processing ability. ▪ Trx are queued. Queue full can cause flow-control. N2 PXC N3 10 © 2017 Percona 

11 .Parallel Processing ▪ Multiple parallel worker N1 threads to match-up with user-level workload threads ▪ They all beautifully co-ordinate to let the “FIRST N2 PXC N3 COMMITTER WIN” (Commit/Local/Apply Monitor) 11 © 2017 Percona 

12 .Network failure ▪ Quorum lost N1 • Network outage, Machine failure ▪ Auto-recovery: Restart the cluster once all the nodes are back. N2 PXC N3 ▪ Configurable timeout to adjust different network. ▪ Protection against Split-Brain 12 © 2017 Percona 

13 .Geo-distributed ▪ Advance Availability with N 1 Geo-distribution of cluster nodes N N 2 3 One Big PXC Cluster N 4 N N 5 6 13 © 2017 Percona 

14 .Lot more flexible topology ▪ PXC cluster can act ASYNC Independent MASTER SLAVE, ASYNC MASTER. ▪ 2 PXC cluster could be connected through a ASYNC N1 replication link. N2 N3 Independent SLAVE 14 © 2017 Percona 

15 .Cluster-Safe-Mode [5.7] ▪ Workload that is not cluster safe. ▪ pxc_strict_mode • ENFORCING • MASTER PXC • PERMISSIVE • DISABLED 15 © 2017 Percona 

16 .Security ▪ Data secure: • In-flight/during transit (SST, IST, replication traffic through SSL sockets) [Single option configuration supported pxc-encrypt-cluster-traffic=ON/OFF] [5.7] PXC • at-rest (through encrypted tablespace) [5.7] 16 © 2017 Percona 

17 .Performance [5.7] ▪ Industry Leading Performance Solution (5.7.17+ onwards) ▪ For all workload: OLTP/POINT UPDATE/etc… ▪ Quicker node rejoins PXC 17 © 2017 Percona 

18 .Trackability ▪ Can track lot of important aspects including • show status (Flow-control, IST progress, queue size, replication latency, applied/committed upto, cache size) • performance_schema.pxc_cluster_view • Various other internal objects exposed through PFS • Error/information logs. • Improved debugging messages for SST • Improved visibility of thread state through show processlist 18 © 2017 Percona 

19 .PXC as a complete HA solution ▪ Complete HA solution should have: • Stable cluster • Load Balance • Simplified and Unified view of the system 19 © 2017 Percona 

20 .Load Balancer ▪ PXC can operate with multiple load balancers like HAProxy, ProxySQL, etc... ▪ PXC suggest use of ProxySQL • Integrated and closed development. • Feature rich load balancer (lot more features getting added) • Custom PXC script aids simplified PXC configuration (auto-discovery of PXC PXC nodes). 20 © 2017 Percona 

21 .Load Balancer ▪ PXC maintenance mode pxc_maint_mode • Abrupt graceful shutdown can cause disruption in DISABLED workload till Load-Balancer readjust the load. SHUTDOWN • maintenance mode is like advance warning helping load balancer (ProxySQL only) to make a note of this MAINTENANCE and pre-adjust workload. pxc_maint_transition_period • Also applicable if node needs to stop active traffic for maintenance purpose. 21 © 2017 Percona 

22 .Simplified and Unified view ▪ PMM (Percona Monitoring and Management) • Complete integrated with PXC • Simple way to take control of complete HA system https://pmmdemo.percona.com/ 22 © 2017 Percona 

23 .PXC as a complete HA solution ▪ Complete HA solution should have: • Stable cluster • Load Balance • Simplified and Unified view of the system 23 © 2017 Percona 

24 .Connect back….. ▪ PXC • mail me: krunal.bauskar@percona.com • PXC forum https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster • PXC @ Launchpad/JIRA QUESTIONS 24 © 2017 Percona 

25 . DATABASE PERFORMANCE Database Performance Matters MATTERS