20120426 High Availability MySQL

Transcript

1. High Availability MySQL Jui-Nan Lin (؍๿Ӳ)! Apr. 2012!
2. Agenda About Me! Typical Architecture for Websites! Why High Availability for databases?! Degree of High Availability! Approaches to high availability MySQL! Our Choices! Conclusion!
3. About Me Jui-Nan Lin (؍๿Ӳ)! Section Manager of R&D Team, R&D Dept.! Operate MySQL for 4 year in PIXNET! Twitter: @jnlin! LinkedIn: jnlin! Email: jnlin@pixnet.tw! !
4. Typical Architecture (for websites)
5. Why High Availability Hardware can fail, something can break ! – We have broken hardware every week in PIXNET! We need maintenance window! – Upgrade Operating System/MySQL server! We don’t want to be called by my boss at 4am.
6. Our Target Fault Tolerant! No SPOF (Single Point of Failure)! Easy to add repaired node back! Data Correctness! Reduce Cost! – Human Resource! – Hardware!
7. Degree of High Availability Down Time! – 90% uptime => 35 days down per year! – 99% uptime => 4 days! – 99.9% uptime => 8 hours! – 99.99% uptime => 50 mins! – 99.999% uptime => 5 mins
8. Approaches to high availability MySQL Replication – Master and Slave! DRBD! Master-to-Master Replication! MySQL Cluster
9. Replication Idea: If master fails, promote one of slaves to master
10. Replication – Concept Master Slave Slave Slave Master Fail Promoted to master Replication Replication Replication
11. Replication – Pros and Cons Pros! – Simple - MySQL feature! Cons! – Data inconsistency! • Incomplete transaction! • Replication delay!
12. DRBD DRBD - Distributed Replicated Block Device! – RAID 1 over the network! Relies on crash safe storage engine and file system! – InnoDB and XFS! http://www.drbd.org/!
13. DRBD – Concept Heartbeat Active Standby Heartbeat Active Fail
14. DRBD – Pros and Cons Pros! – Reliable! – Data Consistency! Cros! – Warm up problem!
15. Master-to-Master Replication Databases to set master to each other.! http://mysql-mmm.org/
16. Master-to-Master Replication – Concept Writer Reader Writer/Reader Fail Replication Monitor Healthy
 Check Healthy
 Check Monitor Healthy
 Check Healthy
 Check
17. Master-to-Master Replication – Pros and Cons Pros! – No “Warm up” Problem! – Better Resource Usage! Cons! – Same as MySQL replication! – Race condition! • Solved by fixed writer node
18. Master-to-Master Replication – Pros and Cons Cons! – Duplicated Key when use auto-increment! • Solved by auto_increment_increment=2 and auto_increment_offset!
19. MySQL Cluster http://www.mysql.com/products/cluster/
20. Failover time Approach Time Replication Fast DRBD Slow Master-to-Master Fast
21. Our Choices DRBD for applications which require more consistency! Master-to-Master Replication for applications which require high performance! Will evaluate cluster configuration in Q4
22. Conclusion Evaluate a suitable solution ! – What degree of high availability do you need?! – What applications do you run?! • Web 2.0 Applications? Billing Data? Mobile Apps?! – How much budget do you have?! Aim for what is reasonable for your needs!