1. Using MySQL for Distributed Database Architectures Peter Zaitsev CEO, Percona Percona Technical Webinars July 31, 2018 © 2018 Percona. 1 

2.Presentation Cover Basics Why Going Distributed How to do it © 2018 Percona. 2 

3.Distributed ? MySQL Deployment on More than one System © 2018 Percona. 3 

4.Modern MySQL Scalability © 2018 Percona. 4 

5.Even more Modern MySQL Scalability © 2018 Percona. 5 

6.Single MySQL Instance Can Do Hundreds of Thousands of Queries/Sec Tends of Thousands of Updates/Sec Traverse Tens of Millions of Rows/Sec Comfortably Handle Several TB Database size © 2018 Percona. 6 

7.Lets Do Some Math 100.000 QPS 10 Queries per User Interaction 10.000 User Interactions/sec 864.000.000 User Interactions/Day 30 User Interactions/User Avg 28.000.000 Daily Active Users Possible 15M of Daily Active Users counting time of day skew © 2018 Percona. 7 

8.Distributed Systems Tend To be More Complicated to Develop Against More Complicated to Operate Have Additional Performance Bottlenecks Have Complicated Failure Modes © 2018 Percona. 8 

9.With All of this ? Why “Go Distributed” ? © 2018 Percona. 9 

10.Reasons to “Go Distributed” High Data Scalability Availability Distribution © 2018 Percona. 10 

11.High Availability with MySQL Cold Standby (ie DRBD) Failover (Classical Replication) Active-Active Clustering (PXC, MySQL Group Replication) © 2018 Percona. 11 

12.Q1:What Failure Modes Do you Consider ? Server Crash/Server Hardware Failure Software Bugs and Storage Corruption Network Failure “Datacenter” Failure Developer Mistakes/Intruder Actions © 2018 Percona. 12 

13.Q2:What Data Am I allowed to lose ? Limited Data Loss Allowed No Data Loss No Transactions In Flight Loss © 2018 Percona. 13 

14.Q3:How Quickly do you need to Recover ? “Immediate?” “Seconds?” “Minutes?” “Hours?” © 2018 Percona. 14 

15.Speed of Light Realities Data •Synchronous – Slow Propagation •Asynchronous – Can Be Data Loss Can Occur © 2018 Percona. 15 

16.Scalability Scaling Scaling Scaling Reads Writes Data Size © 2018 Percona. 16 

17.Data Distribution Some Data Must be in •User Latency Reasons Specific •Legal and Compliance Geographic Reasons Location © 2018 Percona. 17 

18.Distributed Architectures with MySQL © 2018 Percona. 18 

19.Main Concepts Replication Sharding Failover Management Traffic Management © 2018 Percona. 19 

20.Replication Having Multiple Copies of the data, updated with changes © 2018 Percona. 20 

21.Availability Service Stays up when component fails © 2018 Percona. 21 

22.Availability via Redundancy Have more than one system Works well for stateless systems Is not enough for databases © 2018 Percona. 22 

23.Availability via Replication Redundant Paired with Computing Replicated Resource Data © 2018 Percona. 23 

24.Where Replication Happens Storage Level Database Level Application Level © 2018 Percona. 24 

25.Storage Level Replication Replication in SAN/NAS, DRBD Typically provides cold standby Simple choice which works with many systems Amazon Aurora – Smart Storage © 2018 Percona. 25 

26.Database level Most Flexible Most Common Hot/Warm Spare Some can do Active-Active © 2018 Percona. 26 

27.Application Level Hard to get right Rarely used, especially “used right” Partial Replication/Syncronization Smart conflict resolution Cross Vendor Redundancy © 2018 Percona. 27 

28.Sharding Split data set by certain criteria and store such “shards” on separate “clusters” © 2018 Percona. 28 

29.Typical Sharding By User By Customer Account/Company © 2018 Percona. 29