Using MySQL Distributed Database Architecture

1 . Using MySQL for Distributed Database Architectures Peter Zaitsev CEO, Percona PingCAP Infra Meetup, Shanghai, China, May 26, 2018 © 2018 Percona. 1 

2 .About Percona Solutions for your success with MySQL,MariaDB, MongoDB and PostgreSQL Support, Managed Services, Consulting, Training, Software Our Software is 100% Free and Open Source Support Broad Ecosystem – MySQL, MariaDB, Amazon RDS, Google CloudSQL In Business for 11 years More than 3000 customers, including top Internet companies and enterprises © 2018 Percona. 2 

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

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

5 .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