MySQL - The Old Way

8327d09b0bd5ac97409aa10f927b3c30?s=47 Gea-Suan Lin
July 29, 2012
Technology
4
420

MySQL - The Old Way

8327d09b0bd5ac97409aa10f927b3c30?s=128

Gea-Suan Lin

July 29, 2012
Tweet

More Decks by Gea-Suan Lin

See All by Gea-Suan Lin
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
0
160
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
0
1.1k
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
1
170
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
8
2.7k
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
0
140
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
9
7.3k
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
2
390
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
17
11k
8327d09b0bd5ac97409aa10f927b3c30?s=48 gslin
4
5.4k

Other Decks in Technology

See All in Technology
1bfc6e2ed04a895bb36f36b86828b689?s=48 110y
1
160
80a3a3857a55f154d23acb705eff72cc?s=48 star_zero
4
340
457ada693e168bd1102fec3914040b5d?s=48 uipath_friends
0
130
Add4ea030be9e6aa7a319fc8c1b587c6?s=48 hashhub
2
2k
95aaab3d693889550fd2e7ae02f2f789?s=48 takaking22
13
5.1k
9e7a653ed313b3da4b263a772bfc5026?s=48 clustervr
0
150
93fc2d713bc4d0e9c594bf8c9e6bcc62?s=48 oztick139
0
110
0102ef8594b9d3a74f45dfd5daa5ede4?s=48 yskmjp
0
260
8a84268593355816432ceaf78777d585?s=48 dena_tech
0
910
966e29b84ae7dbde170f66b0bc75b7a6?s=48 ishiayaya
1
200
7c4b1ae16723b56facc7a8a8f95aa6ce?s=48 jmortega
1
140
D4b3dfc68675b3564c58e75cef7c9689?s=48 ryoichi_obara
0
530

Featured

See All Featured
433acaea1012b25d97ae66da9b998dad?s=48 malarkey
392
60k
C9b18d9dff88a9dd2393364c2b3b21bd?s=48 colly
185
14k
5b9fe87ec1faa67a4599782930f45ec9?s=48 sstephenson
144
12k
A9704266587836f7e784235e5073b93e?s=48 jmmastey
5
360
3d6ace9554821d552146413bcdf874f6?s=48 trishagee
14
960
B3d6434763caa0ef5dc4b792662c49f7?s=48 smashingmag
281
46k
E76911cbe088e5b850d966de3fc7435b?s=48 robhawkes
52
2.7k
Aebc2d07a50011e2a30d38435fde564a?s=48 jrom
113
7k
170b760e0147792d0140e59ec78e9893?s=48 eitanlees
108
9.6k
B47b288784fda77a94aeb234ca743c24?s=48 keavy
105
14k
168ccec72eee0530b818d44f3fedaacf?s=48 shlominoach
175
7.2k
3d4519fd34b76fb265fc0237f3792bd4?s=48 danielanewman
1
340

Transcript

  1. MySQL - The Old Way Gea-Suan Lin

  2. Old != Bad

  3. In this case, Old implies Reliable

  4. Reliable

  5. We are familiar with these issues,

  6. We can workaround these issues

  7. So...

  8. First Solution

  9. Built-in Replication

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

  11. The Good Part

  12. Simple

  13. Dead Simple Setup

  14. Dead Simple Concept

  15. Master logs changes

  16. Slave applies changes

  17. The Bad Part

  18. Async

  19. Means...

  20. Replication Lag

  21. When you write data to master,

  22. It’s possible unable to read from slave

  23. Workaround

  24. Always use master if you write data

  25. The Worst Part

  26. Application cannot write after master crash

  27. This derives...

  28. Second Solution

  29. Master-Master

  30. Two MySQL servers

  31. Set each other as master server

  32. The Good Part

  33. Both MySQL servers can be written

  34. The Bad Part

  35. Data inconsistent

  36. MySQL Server A TRUNCATE TABLE t; INSERT t SET i=1;

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

  37. 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;

  38. Result

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

  40. Workaround

  41. Write one server in normal case

  42. Write another one when primary node fails

  43. You can write code to handle failover

  44. But we suggest to use Heartbeat

  45. Heartbeat can handle this failover case

  46. When data is inconsistent,

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

  48. Third Solution

  49. DRBD + Heartbeat

  50. DRBD is Network-based RAID-1

  51. Block-level mirror

  52. Heartbeat handles High Availability

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

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

  55. The Good Part

  56. Data consistent

  57. You won’t need to worry about data inconsistent issue

  58. The Bad Part

  59. Utilization Rate

  60. Only one server uses to serve applications

  61. Warm up time

  62. When I/O rate cannot catch query rate

  63. Conclusion

  64. DRBD has higher down-time than other twos,

  65. And it costs higher in server hardware

  66. But it costs lower for SA Operations

  67. If down-time of DRBD + Heartbeat is acceptable,

  68. You should choose it

  69. How much down- time ?

  70. It depends,

  71. Usually < 30 secs to failover

  72. With warm up time

  73. Possible 3~5 mins for 10GB data size ?

  74. Thanks !