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
450
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
7k
用 AWS CodeDeploy 解決程式佈署
gslin
0
400
MySQL to NoSQL & Search Engine
gslin
0
2k
用 Vagrant 與 Docker 拯救世界
gslin
1
260
Startup IT infrastructure: Developing and Working with AWS
gslin
8
3.5k
Talk about Percona XtraDB Cluster
gslin
0
180
API Design Optimized for Mobile Platform
gslin
9
8.5k
Use Facebook::Graph to write desktop application
gslin
2
500
COSCUP 2012 - MySQL System Stability
gslin
17
12k
Other Decks in Technology
See All in Technology
新規案件の立ち上げ専門チームから見たAI駆動開発の始め方
shuyakinjo
0
660
【5分でわかる】セーフィー エンジニア向け会社紹介
safie_recruit
0
30k
Kiroと学ぶコンテキストエンジニアリング
oikon48
6
7.8k
なぜSaaSがMCPサーバーをサービス提供するのか?
sansantech
PRO
6
2.1k
TypeScript入門
recruitengineers
PRO
35
12k
Webブラウザ向け動画配信プレイヤーの 大規模リプレイスから得た知見と学び
yud0uhu
0
190
RSCの時代にReactとフレームワークの境界を探る
uhyo
9
2.6k
AI時代に非連続な成長を実現するエンジニアリング戦略
sansantech
PRO
3
970
実践AIガバナンス
asei
3
310
allow_retry と Arel.sql / allow_retry and Arel.sql
euglena1215
1
150
Nstockの一人目エンジニアが 3年間かけて向き合ってきた セキュリティのこととこれから〜あれから半年〜
yo41sawada
0
190
AI時代にPdMとPMMはどう連携すべきか / PdM–PMM-collaboration-in-AI-era
rakus_dev
0
270
Featured
See All Featured
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
Fantastic passwords and where to find them - at NoRuKo
philnash
52
3.4k
jQuery: Nuts, Bolts and Bling
dougneiner
64
7.9k
Java REST API Framework Comparison - PWX 2021
mraible
33
8.8k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
addyosmani
7
840
Stop Working from a Prison Cell
hatefulcrawdad
271
21k
Imperfection Machines: The Place of Print at Facebook
scottboms
268
13k
Building Better People: How to give real-time feedback that sticks.
wjessup
368
19k
Faster Mobile Websites
deanohume
309
31k
Speed Design
sergeychernyshev
32
1.1k
Intergalactic Javascript Robots from Outer Space
tanoku
272
27k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.9k
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 !