Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQL - The Old Way
Search
Gea-Suan Lin
July 29, 2012
Technology
4
440
MySQL - The Old Way
Gea-Suan Lin
July 29, 2012
Tweet
Share
More Decks by Gea-Suan Lin
See All by Gea-Suan Lin
High Availability Vault Service on AWS Environment
gslin
0
6.9k
用 AWS CodeDeploy 解決程式佈署
gslin
0
350
MySQL to NoSQL & Search Engine
gslin
0
1.9k
用 Vagrant 與 Docker 拯救世界
gslin
1
230
Startup IT infrastructure: Developing and Working with AWS
gslin
8
3.4k
Talk about Percona XtraDB Cluster
gslin
0
170
API Design Optimized for Mobile Platform
gslin
9
8.3k
Use Facebook::Graph to write desktop application
gslin
2
470
COSCUP 2012 - MySQL System Stability
gslin
17
11k
Other Decks in Technology
See All in Technology
『GRANBLUE FANTASY: Relink』最高の「没入感」を実現するカットシーン制作手法とそれを支える技術
cygames
1
140
App Router を実プロダクトで採用して見えてきた勘所をちょっとだけ紹介
marokanatani
1
930
テスト”ケース”駆動開発 で手戻りをなくそう
ryohma0510
0
330
Classmethod AI Talks(CATs) #1 司会進行スライド(2024.09.19) / classmethod-ai-talks-aka-cats_moderator-slides_vol1_2024-09-19
shinyaa31
0
120
開発者の定量・定性データを組み合わせて開発者体験を把握するための取り組み
ham0215
1
140
Functional TypeScript
naoya
11
4.8k
AI活用したくてもできなかった不動産SaaSの今とこれから
nealle
0
340
Privacy Sandbox on Android / DroidKaigi 2024
7pairs
1
270
不動産売買取引におけるAIの可能性とプロダクトでのAI活用
zabio3
0
270
LINEヤフーのフロントエンド組織・体制の紹介
lycorp_recruit_jp
1
1.2k
サーバー管理しないサーバーサービスManaged DevOps Pool
kkamegawa
0
130
持続可能なソフトウェア開発を支える『GitHub CI/CD実践ガイド』
tmknom
8
1.4k
Featured
See All Featured
A designer walks into a library…
pauljervisheath
201
24k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
23
1.7k
Imperfection Machines: The Place of Print at Facebook
scottboms
263
13k
Build The Right Thing And Hit Your Dates
maggiecrowley
30
2.3k
Mobile First: as difficult as doing things right
swwweet
221
8.8k
The Mythical Team-Month
searls
218
43k
Unsuck your backbone
ammeep
667
57k
Building Applications with DynamoDB
mza
90
6k
Why Our Code Smells
bkeepers
PRO
334
56k
Music & Morning Musume
bryan
46
6k
Teambox: Starting and Learning
jrom
131
8.7k
Robots, Beer and Maslow
schacon
PRO
157
8.2k
Transcript
MySQL - The Old Way Gea-Suan Lin
Old != Bad
In this case, Old implies Reliable
Reliable
We are familiar with these issues,
We can workaround these issues
So...
First Solution
Built-in Replication
http://dev.mysql.com/ doc/refman/5.5/en/ replication.html
The Good Part
Simple
Dead Simple Setup
Dead Simple Concept
Master logs changes
Slave applies changes
The Bad Part
Async
Means...
Replication Lag
When you write data to master,
It’s possible unable to read from slave
Workaround
Always use master if you write data
The Worst Part
Application cannot write after master crash
This derives...
Second Solution
Master-Master
Two MySQL servers
Set each other as master server
The Good Part
Both MySQL servers can be written
The Bad Part
Data inconsistent
MySQL Server A TRUNCATE TABLE t; INSERT t SET i=1;
MySQL Server B TRUNCATE TABLE t; INSERT t SET i=2;
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;
Result
MySQL Server A i=2 MySQL Server B i=1
Workaround
Write one server in normal case
Write another one when primary node fails
You can write code to handle failover
But we suggest to use Heartbeat
Heartbeat can handle this failover case
When data is inconsistent,
We can use Percona’s pt-table-sync to sync data
Third Solution
DRBD + Heartbeat
DRBD is Network-based RAID-1
Block-level mirror
Heartbeat handles High Availability
http://dev.mysql.com/ doc/refman/5.5/en/ha- drbd.html
http://dev.mysql.com/ doc/refman/5.5/en/ha- heartbeat.html
The Good Part
Data consistent
You won’t need to worry about data inconsistent issue
The Bad Part
Utilization Rate
Only one server uses to serve applications
Warm up time
When I/O rate cannot catch query rate
Conclusion
DRBD has higher down-time than other twos,
And it costs higher in server hardware
But it costs lower for SA Operations
If down-time of DRBD + Heartbeat is acceptable,
You should choose it
How much down- time ?
It depends,
Usually < 30 secs to failover
With warm up time
Possible 3~5 mins for 10GB data size ?
Thanks !