Tweet
Tweet

Slide 1

Slide 1 text

MySQL - The Old Way Gea-Suan Lin

Slide 2

Slide 2 text

Old != Bad

Slide 3

Slide 3 text

In this case, Old implies Reliable

Slide 4

Slide 4 text

Reliable

Slide 5

Slide 5 text

We are familiar with these issues,

Slide 6

Slide 6 text

We can workaround these issues

Slide 7

Slide 7 text

So...

Slide 8

Slide 8 text

First Solution

Slide 9

Slide 9 text

Built-in Replication

Slide 10

Slide 10 text

http://dev.mysql.com/ doc/refman/5.5/en/ replication.html

Slide 11

Slide 11 text

The Good Part

Slide 12

Slide 12 text

Simple

Slide 13

Slide 13 text

Dead Simple Setup

Slide 14

Slide 14 text

Dead Simple Concept

Slide 15

Slide 15 text

Master logs changes

Slide 16

Slide 16 text

Slave applies changes

Slide 17

Slide 17 text

The Bad Part

Slide 18

Slide 18 text

Async

Slide 19

Slide 19 text

Means...

Slide 20

Slide 20 text

Replication Lag

Slide 21

Slide 21 text

When you write data to master,

Slide 22

Slide 22 text

It’s possible unable to read from slave

Slide 23

Slide 23 text

Workaround

Slide 24

Slide 24 text

Always use master if you write data

Slide 25

Slide 25 text

The Worst Part

Slide 26

Slide 26 text

Application cannot write after master crash

Slide 27

Slide 27 text

This derives...

Slide 28

Slide 28 text

Second Solution

Slide 29

Slide 29 text

Master-Master

Slide 30

Slide 30 text

Two MySQL servers

Slide 31

Slide 31 text

Set each other as master server

Slide 32

Slide 32 text

The Good Part

Slide 33

Slide 33 text

Both MySQL servers can be written

Slide 34

Slide 34 text

The Bad Part

Slide 35

Slide 35 text

Data inconsistent

Slide 36

Slide 36 text

MySQL Server A TRUNCATE TABLE t; INSERT t SET i=1; MySQL Server B TRUNCATE TABLE t; INSERT t SET i=2;

Slide 37

Slide 37 text

MySQL Server A TRUNCATE TABLE t; INSERT t SET i=1; TRUNCATE TABLE t; INSERT t SET i=2; MySQL Server B TRUNCATE TABLE t; INSERT t SET i=2; TRUNCATE TABLE t; INSERT t SET i=1;

Slide 38

Slide 38 text

Result

Slide 39

Slide 39 text

MySQL Server A i=2 MySQL Server B i=1

Slide 40

Slide 40 text

Workaround

Slide 41

Slide 41 text

Write one server in normal case

Slide 42

Slide 42 text

Write another one when primary node fails

Slide 43

Slide 43 text

You can write code to handle failover

Slide 44

Slide 44 text

But we suggest to use Heartbeat

Slide 45

Slide 45 text

Heartbeat can handle this failover case

Slide 46

Slide 46 text

When data is inconsistent,

Slide 47

Slide 47 text

We can use Percona’s pt-table-sync to sync data

Slide 48

Slide 48 text

Third Solution

Slide 49

Slide 49 text

DRBD + Heartbeat

Slide 50

Slide 50 text

DRBD is Network-based RAID-1

Slide 51

Slide 51 text

Block-level mirror

Slide 52

Slide 52 text

Heartbeat handles High Availability

Slide 53

Slide 53 text

http://dev.mysql.com/ doc/refman/5.5/en/ha- drbd.html

Slide 54

Slide 54 text

http://dev.mysql.com/ doc/refman/5.5/en/ha- heartbeat.html

Slide 55

Slide 55 text

The Good Part

Slide 56

Slide 56 text

Data consistent

Slide 57

Slide 57 text

You won’t need to worry about data inconsistent issue

Slide 58

Slide 58 text

The Bad Part

Slide 59

Slide 59 text

Utilization Rate

Slide 60

Slide 60 text

Only one server uses to serve applications

Slide 61

Slide 61 text

Warm up time

Slide 62

Slide 62 text

When I/O rate cannot catch query rate

Slide 63

Slide 63 text

Conclusion

Slide 64

Slide 64 text

DRBD has higher down-time than other twos,

Slide 65

Slide 65 text

And it costs higher in server hardware

Slide 66

Slide 66 text

But it costs lower for SA Operations

Slide 67

Slide 67 text

If down-time of DRBD + Heartbeat is acceptable,

Slide 68

Slide 68 text

You should choose it

Slide 69

Slide 69 text

How much down- time ?

Slide 70

Slide 70 text

It depends,

Slide 71

Slide 71 text

Usually < 30 secs to failover

Slide 72

Slide 72 text

With warm up time

Slide 73

Slide 73 text

Possible 3~5 mins for 10GB data size ?

Slide 74

Slide 74 text

Thanks !