Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL - The Old Way

Avatar for Gea-Suan Lin Gea-Suan Lin
July 29, 2012
Technology
4
460

MySQL - The Old Way

Avatar for Gea-Suan Lin

Gea-Suan Lin

July 29, 2012
Tweet

More Decks by Gea-Suan Lin

See All by Gea-Suan Lin
High Availability Vault Service on AWS Environment
Avatar for Gea-Suan Lin gslin
0
7.1k
用 AWS CodeDeploy 解決程式佈署
Avatar for Gea-Suan Lin gslin
0
400
MySQL to NoSQL & Search Engine
Avatar for Gea-Suan Lin gslin
0
2k
用 Vagrant 與 Docker 拯救世界
Avatar for Gea-Suan Lin gslin
1
260
Startup IT infrastructure: Developing and Working with AWS
Avatar for Gea-Suan Lin gslin
8
3.6k
Talk about Percona XtraDB Cluster
Avatar for Gea-Suan Lin gslin
0
190
API Design Optimized for Mobile Platform
Avatar for Gea-Suan Lin gslin
9
8.5k
Use Facebook::Graph to write desktop application
Avatar for Gea-Suan Lin gslin
2
500
COSCUP 2012 - MySQL System Stability
Avatar for Gea-Suan Lin gslin
17
12k

Other Decks in Technology

See All in Technology
アウトプットから始めるOSSコントリビューション 〜eslint-plugin-vueの場合〜 #vuefes
Avatar for 弁護士ドットコム bengo4com
3
1.8k
知覚とデザイン
Avatar for Rinchoku rinchoku
1
580
Copilot Studio ハンズオン - 生成オーケストレーションモード
Avatar for tomoyasasaki tomoyasasakimskk
0
230
オブザーバビリティと育てた ID管理・認証認可基盤の歩み / The Journey of an ID Management, Authentication, and Authorization Platform Nurtured with Observability
Avatar for 株式会社カミナシ kaminashi
1
690
入院医療費算定業務をAIで支援する：包括医療費支払い制度とDPCコーディング (公開版)
Avatar for Takashi Nishibayashi hagino3000
0
110
AIプロダクトのプロンプト実践テクニック / Practical Techniques for AI Product Prompts
Avatar for Jumpei Sakatsu saka2jp
0
110
Dify on AWS 環境構築手順
Avatar for Hiroaki Yoshimura yosse95ai
0
130
クラウドとリアルの融合により、製造業はどう変わるのか？〜クラスメソッドの製造業への取組と共に〜
Avatar for 濱田孝治 hamadakoji
0
430
ヘンリー会社紹介資料（エンジニア向け） / company deck for engineer
Avatar for Henry, Inc. henryofficial
0
380
OSSで50の競合と戦うためにやったこと
Avatar for yamadashy / やまだし yamadashy
3
980
AIとともに歩んでいくデザイナーの役割の変化
Avatar for LINEヤフーTech (LY Corporation Tech) lycorptech_jp
PRO
0
880
ブラウザのAPIで Nintendo Switch用の特殊なゲーム用コントローラーを体験型コンテンツに / IoTLT @ストラタシス・ジャパン
Avatar for you（@youtoy） you
PRO
0
130

Featured

See All Featured
Performance Is Good for Brains [We Love Speed 2024]
Avatar for Tammy Everts tammyeverts
12
1.2k
Understanding Cognitive Biases in Performance Measurement
Avatar for Philip Tellis bluesmoon
31
2.7k
The World Runs on Bad Software
Avatar for Brandon Keepers bkeepers
PRO
72
11k
The Psychology of Web Performance [Beyond Tellerrand 2023]
Avatar for Tammy Everts tammyeverts
49
3.1k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
Avatar for Rebecca Miller-Webster rmw
35
3.2k
ReactJS: Keep Simple. Everything can be a component!
Avatar for Pedro Nauck pedronauck
667
130k
Sharpening the Axe: The Primacy of Toolmaking
Avatar for Bryan Cantrill bcantrill
46
2.5k
Navigating Team Friction
Avatar for Lara Hogan lara
190
15k
Put a Button on it: Removing Barriers to Going Fast.
Avatar for kastner kastner
60
4k
The Power of CSS Pseudo Elements
Avatar for Geoffrey Crofte geoffreycrofte
80
6k
For a Future-Friendly Web
Avatar for Brad Frost brad_frost
180
10k
Music & Morning Musume
Avatar for Bryan Veloso bryan
46
6.9k

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 !