High Availability MySQL Jui-Nan Lin (؍๿Ӳ)! Apr. 2012!

Agenda About Me! Typical Architecture for Websites! Why High Availability for databases?! Degree of High Availability! Approaches to high availability MySQL! Our Choices! Conclusion!

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: [email protected]! !

Typical Architecture (for websites)

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.

Our Target Fault Tolerant! No SPOF (Single Point of Failure)! Easy to add repaired node back! Data Correctness! Reduce Cost! – Human Resource! – Hardware!

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

Approaches to high availability MySQL Replication – Master and Slave! DRBD! Master-to-Master Replication! MySQL Cluster

Replication Idea: If master fails, promote one of slaves to master

Replication – Concept Master Slave Slave Slave Master Fail Promoted to master Replication Replication Replication

Replication – Pros and Cons Pros! – Simple - MySQL feature! Cons! – Data inconsistency! • Incomplete transaction! • Replication delay!

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/!

DRBD – Concept Heartbeat Active Standby Heartbeat Active Fail

DRBD – Pros and Cons Pros! – Reliable! – Data Consistency! Cros! – Warm up problem!

Master-to-Master Replication Databases to set master to each other.! http://mysql-mmm.org/

Master-to-Master Replication – Concept Writer Reader Writer/Reader Fail Replication Monitor Healthy
 Check Healthy
 Check Monitor Healthy
 Check Healthy
 Check

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

Master-to-Master Replication – Pros and Cons Cons! – Duplicated Key when use auto-increment! • Solved by auto_increment_increment=2 and auto_increment_offset!

MySQL Cluster http://www.mysql.com/products/cluster/

Failover time Approach Time Replication Fast DRBD Slow Master-to-Master Fast

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

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!