Replication for Availability & Durability with MySQL and Amazon RDS

1. Replication for Availability & Durability with MySQL and Amazon RDS Grant McAlister

3. Overview • Why replicate? • What are the options? • What are the issues? • What do we do.

4. Why Replicate? • Durability • Availability • Scaling

5. Durability Replicate Location 1 Location 2

6. Availability • When disaster or failure happens • Read availability from readable replicas • Read/Write availability by promoting a replica • Can be faster than waiting for a reboot • Online DDL operations (create index, alter table) • Make changes on a replica • Failover to it • Requires Logical Replication

7. Read Scaling R/W Client R/W Client Read Client Read Client Read Client Read Client Replica1 Replica2 Master Replica3

8. Replication Options • Logical vs. Physical • Synchronous vs. Asynchronous

9. Logical vs. Physical • Logical • Standard MySQL Replication • Logical statement or transaction is shipped • Non-deterministic? (Statement vs. Mixed vs. Row) • Physical • Shipping the physical block changes • Oracle Dataguard • Filesystem or block layer replication • Physical SAN device replication • Deterministic (physically exactly the same)

10. Logical Replication insert into person values(‘grant’); commit; Primary Replica Single Threaded Parse Parse Buffer Replication Log Buffer Replication Log Relay Log Recovery Log Recovery Log Data Data

11. Physical Replication insert into person values(‘grant’); commit; Primary Replica Parse Parse Buffer Replication Log Buffer Replication Log Relay Log Recovery Log Recovery Log Data Data

12. Synchronous vs. Asynchronous Replication • Synchronous Replication • Write is not committed until it is written on both replicas • Guarantees high durability (almost no data loss) • Higher transaction latency • Pay penalty now (maybe) • Asynchronous Replication • Acknowledged as soon as written to the local storage • Some level of durability (possible data loss) • Can be far behind on shipping • About LOG SHIPPING – NOT APPLY

13. Asynchronous insert into person values (‘grant’); DURABLE in ONE LOCATION DURABLE in TWO LOCATION commit; ACK ACK Primary Secondary Relay Log Replication Log grant

14. Synchronous insert into person values (‘grant’); commit; DURABLE in TWO LOCATION ACK ACK Primary Secondary Relay Log Replication Log grant

15. Performance Max Apply Rate

16. Read Scaling Challenges • Eventual Consistency • Read to Write Ratio

17. Eventual Consistency = Replication Lag Writer Reader Primary Replica

18. Large Commit = Replication Delay 500,000 rows 100,000 rows

19. Reasons for Replication Lag • Large transactions • Single apply thread on replica • Network problems • Overloaded replica • DON’T ASSUME THE BEST CASE

20. Read to Write Ratio 2X 1X 3X SCALE Primary Replica1 Replica2 Replica3 Replica4 Reads 90% Reads10% Reads10% Reads10% Reads10% Reads10% Reads10% Reads10% Reads10% Writes10% Writes10% Writes10% Writes10% Reads10% Reads10% Reads10% Reads10% Reads10% Writes10% Reads10% Reads10% Reads10% Reads10% Writes10% Writes10% Reads10% Writes10% Writes10% Reads10% Reads10% Writes10% Reads10% Reads10% Reads10% Reads10% Reads10% Reads10% Reads10% Writes10% Writes10% Writes10% Writes10% Writes10%

21. Read to Write Ratio - More Writes 1X 2X SCALE Primary Replica1 Replica2 Replica3 Replica4 Reads 80% Reads 20% Reads 20% Reads 20% Reads 20% Writes 20% Writes 20% Writes 20% Writes 20% Reads 20% Reads 20% Reads 20% Writes 20% Reads 20% Writes 20% Writes 20% Writes 20% Writes 20% Writes20%

23. What is RDS MySQL • Managed Relational Database Service (RDS) • Automated Backups and Point in Time Recovery • Can Scale CPU & Memory up and down • Online storage scaling • User controlled patching • Multi-AZ replication • Read Replicas

24. RDS MySQL - Replication • Multi-AZ • Synchronous Replication • Across Availability Zones (AZ) for increased durability • Standby is not usable for reads • Automated rebuilding of failed replica • Read Replica • Standard Asynchronous MySQL replication • Readable by default but also writeable • Can have multiple per master

25. Read Replicas Multi-AZ Deployments Highly Available, Durable, & Scalable MySQL Deployments

26. Demo of RDS Multi-AZ failover

27. THANK YOU