Using MySQL for Distributed Database Architectures
在现代数据体系结构中,我们越来越多地从单节点设计系统转向使用多个节点的分布式体系结构——通常分布在多个数据库和多个大陆。这样的体系结构带来许多好处(如可扩展性和弹性),但如果架构不正确,也会带来很多痛苦。在本演示中,我们将研究如何使用MySQL来设计分布式多节点系统。
展开查看详情
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