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 !