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
460
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
7.1k
用 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.6k
Talk about Percona XtraDB Cluster
gslin
0
190
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
ラスベガスの歩き方 2025年版(re:Invent 事前勉強会)
junjikoide
0
180
From Natural Language to K8s Operations: The MCP Architecture and Practice of kubectl-ai
appleboy
0
230
ブラウザのAPIで Nintendo Switch用の特殊なゲーム用コントローラーを体験型コンテンツに / IoTLT @ストラタシス・ジャパン
you
PRO
0
140
Behind Postgres 18: The People, the Code, & the Invisible Work | Claire Giordano | PGConfEU 2025
clairegiordano
0
140
Building a cloud native business on open source
lizrice
0
180
.NET 10のBlazorの期待の新機能
htkym
0
110
ハノーファーメッセ2025で見た生成AI活用ユースケース.pdf
hamadakoji
1
470
Oracle Database@Google Cloud:サービス概要のご紹介
oracle4engineer
PRO
0
360
個人でデジタル庁の デザインシステムをVue.jsで 作っている話
nishiharatsubasa
3
5.1k
ソースを読む時の思考プロセスの例-MkDocs
sat
PRO
1
210
SRE × マネジメントレイヤーが挑戦した組織・会社のオブザーバビリティ改革 ― ビジネス価値と信頼性を両立するリアルな挑戦
coconala_engineer
0
260
AI機能プロジェクト炎上の 3つのしくじりと学び
nakawai
0
120
Featured
See All Featured
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
162
15k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
116
20k
Agile that works and the tools we love
rasmusluckow
331
21k
Making Projects Easy
brettharned
120
6.4k
Git: the NoSQL Database
bkeepers
PRO
431
66k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
10
890
Documentation Writing (for coders)
carmenintech
75
5.1k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
127
54k
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
4k
Rails Girls Zürich Keynote
gr2m
95
14k
Docker and Python
trallard
46
3.6k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
32
1.7k
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 !