Using MySQL for Distributed Database Architectures



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