A Tour Of Mysql High Availability

A tour of MySQL High Availability PHP Quebec, 2009 Morgan Tocker, firstname at percona dot com Consultant, Percona Inc.

Introduction • We’re going to be talking about various technologies and topologies we can use to improve availability. • High Availability != High Performance – Often we want both, but beware some solutions come without the other!

• Yes, at the end we’ll cover backups!

Audience Poll! • Who needs 100% availability?

Trick Question. • Sorry, it doesn’t exist.

Second question... • Lets just say in a magical world it did exist...

Yep, another trick question. • You can’t afford it.

It’s Expensive. • HA is like the Noah’s Ark of computing. • You need at least two of everything. – Anything less less than two and you have a single point of failure (“SPOF”).

It’s also a sliding scale

You?

How do you measure availability? • Availability is measured in “nines”; – Two, three, four, five and six nines.

• Is it the time your application is running minus unpredicted outages? • Does scheduled maintenance count? • How about network downtime?

Comparing you options Availability %

Downtime per year

Downtime per month*

90%

36.5 days

72 hours

95%

18.25 days

36 hours

98%

7.30 days

14.4 hours

99%

3.65 days

7.20 hours

99.9%

8.76 hours

43.2 min

99.99%

52.6 min

4.32 min

99.999%

5.26 min

25.9s

99.9999%

31.5 s

2.59s

* For monthly calculations, a 30-day month is used. Source: http://en.wikipedia.org/wiki/High_availability

How do you do this? • You likely have a boss that demands 100% availability. – Part of Implementing HA is a social problem, here’s how to deal with this....

1. Identify all failure scenarios • Example Problem: RAID controller thinks it’s April fools day and starts writing 1s instead of 0s and 0s instead of 1s. • The Master Database server is down!

2. Estimate the recovery... • 02:05 AM - Crash occurs. • 02:12 AM - Someone notices a problem with the website - calls the Sales Manager, no answer. • 02:17 AM - Sales Manager answers phone, figures he can’t fix the problem. • 02:20 AM - Sales Manager calls you, you log in to diagnose the problem. • 02:26 AM - You find out you can’t log into the system remotely and have to lodge a ticket with your hosting company. • .....

Estimate (cont...) • ... • 02:52 AM - You get a ticket response - “yes, your server is down!”. You reply with some words that can not be repeated. • 03:10 AM - You call up, and are lucky to get someone helpful. Success! • 03:12 AM - They tell you that you have a colocation server, so warranty is your responsibility. It turns out the machine is also over 3 years old, so you know it’s not covered..... • 03:18 AM - You call your boss. Tell him the problem and say “I think we need this part.. or maybe that part, but I really have no idea”, and ask for his credit card number.

Estimate (cont...) • Fast forward... You’re lucky you can get back online within the day. You probably have to abandon that server and restore the backup on a new one. And these estimates are me at my most optimistic!

3. Pass the responsibility on • Now put this in a report in size 12 font, double line spacing and give it to your manager: Failure Scenarios i. Database Server Failure Current ETA is 16 hours for full recovery from backup (explain here ...)

Potential Improvement

Associated Costs

New Recovery ETA

Use Servers under Warranty

+ $3000

8 hours

Rent Servers

+ $50/month

4 hours

Have Hot Spare

+ $300/month

10 minutes

Some additional notes • The story I gave is very common. – There are a few warning lights that come up straight away, and they’re not expensive things to fix. • Pingdom.com is $10/month, can do quite advanced checks, and will Send you SMS.

Having said all that... • Let’s get back to technical challenges. • What should you care about with regards to availability?

Simple Answer: • Availability is what your customer thinks it is. • If you can still provide them their required functionality - then you’re still running. – Even if you’ve just had a critical database server crash!

Example
Example (cont.) .. /*  Check if we have a database link  before rendering optional functionality. */ if (!degrade_gracefully()) {     show_add_comment_box(); } else {     print "Comments is under maintenance, check back soon!"; } ?>

A simple example, but.. • There are probably many cases like this in your application.

High Availability Tools mysql-mastermaster

MySQL Replication • Asynchronous – I’ll explain that in a second.

• Statement-Based (until 5.1 at least) • Spreads read load. • Does not spread writes.

How replication works...

How replication works...

How replication works...

How replication works...

How replication works...

DRBD • A Linux Kernel Module • Think of it as “Network Level RAID1”. • Not just for MySQL! – Can be used to mirror other files.

• Doesn’t spread reads. • Doesn’t spread writes. • Can be Synchronous.

How DRBD works

How DRBD works

How DRBD works

How DRBD works

How DRBD works

How DRBD works

How DRBD works

How DRBD works

How DRBD works

Comparing Replication to DRBD Feature

Replication

DRBD

Fast Resync on inconsistency

No

Yes

Online Resync

Yes if InnoDB

Yes

Syncronous

No

Yes

Network Traffic

Low to Medium

High

Corruption on primary spreads

Unlikely

Certain

Secondary can be time delayed from primary

Yes

No

Secondary can be utilized

Yes (reads)

Yes (backups)

OS Support

All

Linux

Comparing (cont.) Feature

Replication

DRBD

Consistency Check

Not Built in

Yes

Failover Capability

Fairly Easy

Easy

Multiple Spares

Easy

No

Remote Spares

Yes

Unsuitable

myslqdump backup from spare

Yes

No*

Secondary hardware requirements

Either faster/slower than master

At least as fast as master

Allows replicating partial dataset

Yes

No

Replication Event Checksums

No

Yes

Heartbeat • A Linux cluster manager. • Can monitor just about anything – Including DRBD and MySQL Replication.

• Common setup is to use shared IP address between two nodes. – Can script whatever you like happens on node failure.

Heartbeat

192.168.1.1

192.168.1.2

Database Servers

#1

192.168.1.3

#2

Webservers

Heartbeat

192.168.1.1

192.168.1.2

Database Servers

#1

192.168.1.3

#2

Webservers

Heartbeat

192.168.1.1

192.168.1.2

Database Servers

192.168.1.3

#2

Webservers

Heartbeat

192.168.1.1

192.168.1.2

192.168.1.3

Database Servers

Webservers

Heartbeat

192.168.1.1

192.168.1.2

Database Servers

192.168.1.3

#1

Webservers

MMM • Similar concept to Heartbeat with a floating “application facing” IP address. – But more MySQL Aware than heartbeat.

• Under active development by Percona.

Memcached • Dead simple front-end caching. • Access data from cache, alleviate database load server.

Cacti • A graphing tool. • Monitoring health important:

Cacti (cont.)

• When we combine our powers together, we have topologies.....

Starting small... Web Servers

Database Server

Database Slave

A little larger... Web Servers

Database Server

Database Slave

“May work” Web Servers

Database Server

Database Slave

Active-Passive Master-Master Web Servers

Database Server

Database Slave

Larger again Web Servers

Front End Caching (Memcached)

Database Server

Database Slave

Clusters aren’t all fun and games • Split Brains – A situation where both nodes think that they are the primary. I’m Primary

I’m Primary

Another issue... • Fencing and STONITHs – Force a take down of a failed node. I won’t die.

I kill you!

Backups • No HA talk would be complete without plugging the importance of backups!

What makes a good backup? • What are you insulating against? – Require protection from failures as well as accidental/ Malicious deletes.

• Quick to Backup • Quick to Recovery • Flexible recovery granularity – Can recover just one row, or an entire table.

• Consistent

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

3

2

2

3

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

3

2

2

3

An “inconsistent” backup Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

3

2

2

3

An “inconsistent” backup Users

What’s wrong here?

id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

3

2

2

3

What’s wrong (cont.) Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

3

Jim

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

3

2

2

3

A “consistent backup” Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

A “consistent backup” Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

A “consistent backup” Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

A “consistent backup” Users id

name

email

1

Fred

[email protected]

2

Mary

[email protected]

user_blog_posts id

user_id

post_contents

1

1

I hate my parents.

2

2

Ponnies! lol. :D

user_friendship user_1

user_2

1

2

2

1

InnoDB with MVCC Users id

name email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

4

Jim

[email protected]

InnoDB with MVCC Users id

name email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

4

Jim

[email protected]

InnoDB with MVCC Users id

name email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

4

Jim

[email protected]

InnoDB with MVCC Users id

name email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

4

Jim

[email protected]

InnoDB with MVCC Users id

name email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

4

Jim

[email protected]

3 Barry [email protected]

InnoDB with MVCC Users id

name email

1

Fred

[email protected]

2

Mary

[email protected]

3

Barry

[email protected]

4

Jim

[email protected]

3 Barry [email protected]

MySQL Backup Overview • • • • • • • •

mysqldump Text based backups Easier to recover individual rows Raw Backup Copy the data files Filesystem snapshot InnoDB Hot Backup (commercial) Backup from a Slave

Comparisons mysqldump

Raw backups

Snapshots

InnoDB Hot Backup*

No (if all InnoDB)

Yes

Partially

No (if all InnoDB)

Backup Speed Medium

Fast

Fast

Fast

Recovery Speed

Slow

Fast

Medium

Fast

Recovery Granularity

Best

Depends

Not Good

Good

Blocking

Point in Time Recovery • Really slick, just enable binary logging. • An example backup: mysqldump --flush-logs --all-databases --master-data=2 > mybackup.sql Recovery: mysql < mybackup.sql mysqlbinlog mylog.000005 | mysql

Need more info? • We have a blog on everything about MySQL Performance: www.mysqlperformanceblog.com

Pictures used with CC Attribution • Hot Rod: http://www.flickr.com/photos/januszbc/1353696160/ • Hyundai: http://www.flickr.com/photos/daveseven/ 1538984615/

The End • Questions?