MySQL - The Old Way

MySQL - The Old Way

8327d09b0bd5ac97409aa10f927b3c30?s=128

Gea-Suan Lin

July 29, 2012
Tweet

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 !