MySQL Replication Smackdown

What is the best replication approach to use?
Why use replication?
What are production requirements?
What does MySQL offer for replication?
What are the barriers to usage?
What are improvements to replication?
How do we apply replication to other systems?
The new mindset in architecture

Transcript

1. Replication Smackdown EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Ronald Bradford http://ronaldbradford.com 2016.10 Tuesday, October 4, 16
2. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Agenda Replication What, Why, How, MySQL Types Production Systems Requirements, Needs, MTBF A New Mindset Availability, Classification, Pipeline Tuesday, October 4, 16
3. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Which is the best replication approach to use? Answer: It depends Tuesday, October 4, 16
4. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Which is the best replication approach to use? Answer: It depends Tuesday, October 4, 16
5. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What is replication? Tuesday, October 4, 16
6. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What? Tuesday, October 4, 16
7. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What? Tuesday, October 4, 16
8. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What? Copy of data Full or partial Tuesday, October 4, 16
9. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What? Copy of data Full or partial Translation of data e.g. Oracle to MySQL Tuesday, October 4, 16
10. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What? Copy of data Full or partial Translation of data e.g. Oracle to MySQL Transformation of data e.g. MySQL to DW e.g. MySQL to Hadoop Tuesday, October 4, 16
11. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive ONE WAY FORM Tuesday, October 4, 16
12. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive TWO WAY FORM Tuesday, October 4, 16
13. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Why use replication? Tuesday, October 4, 16
14. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Why? Redundancy (copies) Availability (distribution) Failover Scalability (read/write) Performance (optimizations) Backups (locking, load) Consolidation Tuesday, October 4, 16
15. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What are production requirements? Tuesday, October 4, 16
16. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive PROD REQS Acceptable latency Acceptable throughput Performance under load High availability Failover Disaster recovery Security Backups Load testing Monitoring Alerting Sizing How does replication help achieve production requirements? Tuesday, October 4, 16
17. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What does MySQL offer for replication? Tuesday, October 4, 16
18. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Types [Traditional] MySQL Replication Asynchronous / Semi-synchronous MySQL Cluster Galera (MySQL/Percona/MariaDB) MySQL Group Replication (RC) / InnoDB Cluster (TBD) Amazon RDS MAZ & Aurora Others (e.g. Google Cloud SQL, Clustrix, DRBD) Tuesday, October 4, 16
19. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What are barriers to usage? Tuesday, October 4, 16
20. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Traditional async - since 3.23 lag drift consistency throughput semi-sync - since 5.5 lag drift consistency throughput Tuesday, October 4, 16
21. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive MySQL CLuster Additional installation complexity Data/SQL/Admin nodes Different admin interface Different backup strategy LAN based Same SQL syntax Limited large join options Tuesday, October 4, 16
22. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Galera Syntax limitations Feature limitation (e.g. MEMORY table), primary key OS Limitations (Linux Only) Hot data spots /Large transactions in multi-master write Timeouts Schema upgrades LAN v WAN Tuesday, October 4, 16
23. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Group replication Syntax Limitations, Feature Limitation Hot Spots/Large transactions in multi-master write Supported on Linux, Windows, Solaris, FreeBSD, OSX Requirements MySQL 5.7, GTID, binlog_format=ROW Other configuration settings RC only Tuesday, October 4, 16
24. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive INNODB CLUSTER Only in labs Based on group replication limitations Helps solve the routing problem Simplified orchestration in JS Tuesday, October 4, 16
25. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What are improvements to replication? Tuesday, October 4, 16
26. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive MySQL Versions 5.6 crash save slaves, GTID, group commit, multi-threaded 5.7 semi-sync improvements, multi-threaded V2, multi-source, XA support, group replication Tuesday, October 4, 16
27. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive GTID MySQL Percona MariaDB Custom Others http://code.openark.org/blog/mysql/refactoring-replication-topology-with-pseudo-gtid https://mariadb.com/kb/en/mariadb/gtid/ https://www.facebook.com/notes/mysql-at-facebook/lessons- from-deploying-mysql-gtid-at-scale/10152252699590933/ Does not improve async/semi sync replication? Improves [faster] failover Tuesday, October 4, 16
28. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive multi-threaded parallel schema applier (5.6) parallel query applier (5.7) Does improve async/semi sync replication? Improves performance (i.e. lag) Does not eliminate lag Tuesday, October 4, 16
29. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive How do we apply replication to our systems? Tuesday, October 4, 16
30. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Tuesday, October 4, 16
31. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What are your business needs? Tuesday, October 4, 16
32. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What are your business needs? What are [ideal] business needs? Tuesday, October 4, 16
33. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What are your business needs? What are [ideal] business needs? What are [acceptable] business needs? Tuesday, October 4, 16
34. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Objectives Mean Time Between Failure (MTBF) Mean Time To Detect (MTTD) Mean Time To Recover (MTTR) Recovery Point Objective (RPO) Recovery Time Objective (RTO) Tuesday, October 4, 16
35. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive The new mindset in architecture Tuesday, October 4, 16
36. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive 1. Availability 2. Classification 3. Pipeline Tuesday, October 4, 16
37. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Data Availability Tuesday, October 4, 16
38. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive data availability Ability to write data Ability to read data Ability to [read|write] cached data Ability to operate with no data Tuesday, October 4, 16
39. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Tuesday, October 4, 16
40. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What do I mean? Tuesday, October 4, 16
41. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What do I mean? What is your definition of downtime? Tuesday, October 4, 16
42. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Availability Not database availability e.g. those maintenance windows Not data availability e.g. Write/Read/Cache/None It is all about service availability i.e. endpoints Tuesday, October 4, 16
43. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Data Class Tuesday, October 4, 16
44. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Data Class Tuesday, October 4, 16
45. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Data Class All data in a RDBMS schema (or even table) is not equal Users data (register, modify) Change password Last login date Add content Comment/Rate/Score Tuesday, October 4, 16
46. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Data Class All data in a RDBMS schema (or even table) is not equal Users data (register, modify) Change password Last login date Add content Comment/Rate/Score Current Order Last Order Historical Orders Credit Card Details Tuesday, October 4, 16
47. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive DATA Class Some data needs to be more highly available than other data Some data access requires more responsiveness than others Some data has acceptable data loss Some data can be unavailable some of the time Some data visibility can vary between users All data should be secure, some more secure Tuesday, October 4, 16
48. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive DATA Class Some data needs to be more highly available than other data Some data access requires more responsiveness than others Some data has acceptable data loss Some data can be unavailable some of the time Some data visibility can vary between users All data should be secure, some more secure Reclassification of data changes replication requirements Tuesday, October 4, 16
49. Acceptable latency Acceptable throughput Performance under load High availability Failover Disaster recovery Security EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive PROD systems Requirements MTBF MTTD MTTR RPO RTO Responsibilities Tuesday, October 4, 16
50. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Example (Financial) Tuesday, October 4, 16
51. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Data Class Single greatest feature loss Referential Integrity A & C of ACID Tuesday, October 4, 16
52. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Data Pipeline Tuesday, October 4, 16
53. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive data pipeline A single request does not produce one synchronous response Data is not stored in one RDBMS or product type Data locality for responsiveness Use product strengths for data manipulation Tuesday, October 4, 16
54. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Rethinking how to choose MySQL Replication Tuesday, October 4, 16
55. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive UTF-FTV you-twit-face-flix-talk-vr The next great social experience Tuesday, October 4, 16
56. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Old way Found in many “traditional” frameworks/OSS products Users table in monolithic schema Synchronous web requests for information Polling for new/streaming information Data for application is available or not available Replication enables read scalability only Tuesday, October 4, 16
57. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive New Way stores Highly available synchronous store Columnar store Messaging (PUB/SUB) Graph Queue Search Tuesday, October 4, 16
58. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive User path Microservices Login/Logout Register/Maintain Log actions (login good/bad, click, mouse movement) Friends Friends interactions Availability: What type of data access is available Tuesday, October 4, 16
59. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive New WAy Graph Store Friends Customized for relevancy/strength algorithm Queue Password changes, User profile changes Lost password Tuesday, October 4, 16
60. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive notificationS User actions (success, failure) are published (PUB) (SUB) Subscriber logs information (SUB) Subscriber audits for unexpected behavior (SUB) Subscriber notifies friends user is online/offline Pipeline of multiple asynchronous actions Tuesday, October 4, 16
61. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive RATE ME Rate a comment Client only feedback Action held on client batched, overloaded, timed transmission Supports rate/unrate (client side only) Class: Optimized for payload Tuesday, October 4, 16
62. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive Conclusion Tuesday, October 4, 16
63. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive CONCLUSION Do you choose a replication approach to match your [ideal] business needs and data store(s) OR Do you architect a data infrastructure to meet your [ideal] business needs and target specific replication (aka availability) approaches where applicable Tuesday, October 4, 16
64. EffectiveMySQL.com - Performance, Scalability, Site Reliability @RonaldBradford #PerconaLive What does this have to do with replication? Answer: Everything Tuesday, October 4, 16