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
6.9k
用 AWS CodeDeploy 解決程式佈署
gslin
0
380
MySQL to NoSQL & Search Engine
gslin
0
2k
用 Vagrant 與 Docker 拯救世界
gslin
1
240
Startup IT infrastructure: Developing and Working with AWS
gslin
8
3.5k
Talk about Percona XtraDB Cluster
gslin
0
170
API Design Optimized for Mobile Platform
gslin
9
8.4k
Use Facebook::Graph to write desktop application
gslin
2
490
COSCUP 2012 - MySQL System Stability
gslin
17
12k
Other Decks in Technology
See All in Technology
持続可能なドキュメント運用のリアル: 1年間の成果とこれから
akitok_
1
180
MCPを活用した検索システムの作り方/How to implement search systems with MCP #catalks
quiver
12
6.7k
AI AgentOps LT大会(2025/04/16) Algomatic伊藤発表資料
kosukeito
0
140
Рекомендации с нуля: как мы в Lamoda превратили главную страницу в ключевую точку входа для персонализированного шоппинга. Данил Комаров, Data Scientist, Lamoda Tech
lamodatech
0
740
勝手に!深堀り!Cloud Run worker pools / Deep dive Cloud Run worker pools
iselegant
2
320
Recap of Next - Google Cloud で実践する クラウドネイティブ最前線 / The Frontlines of Cloud-Native with Insights from Google Cloud
aoto
PRO
1
110
新卒エンジニアがCICDをモダナイズしてみた話
akashi_sn
2
240
Goの組織でバックエンドTypeScriptを採用してどうだったか / How was adopting backend TypeScript in a Golang company
kaminashi
6
6k
サーバレス、コンテナ、データベース特化型機能をご紹介。CloudWatch をもっと使いこなそう!
o11yfes2023
0
180
技術者はかっこいいものだ!!~キルラキルから学んだエンジニアの生き方~
masakiokuda
2
270
ワールドカフェI /チューターを改良する / World Café I and Improving the Tutors
ks91
PRO
0
120
DETR手法の変遷と最新動向(CVPR2025)
tenten0727
2
1.4k
Featured
See All Featured
Testing 201, or: Great Expectations
jmmastey
42
7.5k
Art, The Web, and Tiny UX
lynnandtonic
298
20k
Building Applications with DynamoDB
mza
94
6.3k
The Pragmatic Product Professional
lauravandoore
33
6.5k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
31
1.1k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
30
2.3k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
5
520
Adopting Sorbet at Scale
ufuk
76
9.3k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
251
21k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
2.9k
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
3.8k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
30
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 !