Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL Replication Smackdown

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

Ef8a4161c1e7ce34ea50c491ad99a67e?s=128

Ronald Bradford

September 29, 2016
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

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