MySQL - The Old Way

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 !