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
RDS for MySQLからAuroraに移行した話 / RDS for MySQL to ...
Search
FUJIWARA Shunichiro
November 02, 2016
Technology
9
5.3k
RDS for MySQLからAuroraに移行した話 / RDS for MySQL to Aurora
2016.11.02 JAWS-UG横浜 - Reboot!!
http://jawsug-yokohama.connpass.com/event/40497/
FUJIWARA Shunichiro
November 02, 2016
Tweet
Share
More Decks by FUJIWARA Shunichiro
See All by FUJIWARA Shunichiro
alecthomas/kong はいいぞ
fujiwara3
6
1.8k
ecspressoの設計思想に至る道 / sekkeinight2025
fujiwara3
12
2.9k
さくらのIaaS基盤のモニタリングとOpenTelemetry/OSC Hokkaido 2025
fujiwara3
3
1.2k
監視のこれまでとこれから/sakura monitoring seminar 2025
fujiwara3
11
5.3k
k6による負荷試験 入門から日常的な実践まで/Re:TechTalk #01
fujiwara3
2
160
困難を「一般解」で解く
fujiwara3
10
3.8k
「隙間家具OSS」に至る道/Fujiwara Tech Conference 2025
fujiwara3
7
13k
alecthomas/kong はいいぞ / kamakura.go#7
fujiwara3
1
1.2k
ISUCONに強くなるかもしれない日々の過ごしかた/Findy ISUCON 2024-11-14
fujiwara3
11
1.4k
Other Decks in Technology
See All in Technology
E2Eテスト設計_自動化のリアル___Playwrightでの実践とMCPの試み__AIによるテスト観点作成_.pdf
findy_eventslides
1
490
職種別ミートアップで社内から盛り上げる アウトプット文化の醸成と関係強化/ #DevRelKaigi
nishiuma
2
140
VCC 2025 Write-up
bata_24
0
180
Access-what? why and how, A11Y for All - Nordic.js 2025
gdomiciano
1
120
研究開発部メンバーの働き⽅ / Sansan R&D Profile
sansan33
PRO
3
20k
20201008_ファインディ_品質意識を育てる役目は人かAIか___2_.pdf
findy_eventslides
1
520
PLaMoの事後学習を支える技術 / PFN LLMセミナー
pfn
PRO
9
3.9k
Modern_Data_Stack最新動向クイズ_買収_AI_激動の2025年_.pdf
sagara
0
220
生成AIとM5Stack / M5 Japan Tour 2025 Autumn 東京
you
PRO
0
230
LLM時代にデータエンジニアの役割はどう変わるか?
ikkimiyazaki
4
920
Escaping_the_Kraken_-_October_2025.pdf
mdalmijn
0
150
定期的な価値提供だけじゃない、スクラムが導くチームの共創化 / 20251004 Naoki Takahashi
shift_evolve
PRO
3
330
Featured
See All Featured
Why You Should Never Use an ORM
jnunemaker
PRO
59
9.6k
KATA
mclloyd
32
15k
4 Signs Your Business is Dying
shpigford
185
22k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
2.6k
Agile that works and the tools we love
rasmusluckow
331
21k
Bootstrapping a Software Product
garrettdimon
PRO
307
110k
Build your cross-platform service in a week with App Engine
jlugia
232
18k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
48
9.7k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
45
2.5k
The Power of CSS Pseudo Elements
geoffreycrofte
79
6k
Learning to Love Humans: Emotional Interface Design
aarron
274
40k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
30
2.9k
Transcript
RDS for MySQL ͔Β Aurora ʹҠߦͨ͠ 2016.11.02 JAWS-UGԣ - Reboot!!
@fujiwara
౻ݪ ढ़Ұ @fujiwara github.com/fujiwara sfujiwara.hatenablog.com ٕज़෦
20149݄ϦϦʔε શ໘తʹAWSΛར༻ • ELB + EC2 • RDS for MySQL
• ElastiCache Redis • CloudFront • S3 • Redshift
ετϨʔδ MySQL ήʔϜͷಛੑ • 9ਓ x 2 νʔϜ͕ಉ࣌ʹҰͭͷࢼ߹Λૢ࡞͢Δ • ͦΕͱผʹࢼ߹ਐߦͰόονॲཧ͕Δ
• → τϥϯβΫγϣϯ͕ඞਢ
MySQL → Aurora
MySQL → Aurora ͳͥҠߦ͔͔ͨͬͨ͠ (1) ࣌ʑى͖ΔEBS٧·Γ͕ͭΒ͍ • ผAZͷॻ͖ࠐΈ͕ඵʙेඵ͞Δݱ • Aurora
3 AZ த 2 AZ ͷॻ͖ࠐΈྃͰฦͬͯ͘ΔͷͰ (ݪཧతʹ) ൃੜ͠ʹ͍ͣ͘
MySQL → Aurora ͳͥҠߦ͔͔ͨͬͨ͠ (2) ϦʔυϨϓϦΧͷஆؾ͕ͭΒ͍ • ϦʔυϨϓϦΧEBS snapshot͔Β෮ݩ͞ΕΔ •
EBSͷ First Touch Penalty • EC2ͳΒ dd ίϚϯυͰͰ͖Δ͕RDS… • primary key + secondary indexͯ͢ᢞΊΔඞཁ • ΠϕϯτલʹΠϯελϯεαΠζมߋ͕ΓͮΒ͍
MySQL → Aurora • 2016-08 ৽ϫʔϧυ্ཱͪ͛࠷ॳ͔ΒAuroraͰ • 2016-10 چϫʔϧυ MySQL
→ AuroraҠߦ
ࣄલͷݕূ • ։ൃαʔόΛAuroraʹͯ͠ςετΛճ͢ • ΞϓϦέʔγϣϯϨϕϧͷޓੑͳ͠ • ΞϓϦέʔγϣϯܦ༝ͰͷϕϯνϚʔΫ • ಛʹੑೳɺݶք্͕͕ΔΘ͚Ͱͳ͔ͬͨ !
Amazon AuroraΛਅʹཧղ͢ΔͨΊͷੑೳݕূ blog.father.gedow.net/2015/11/05/amazon-aurora- benchmark/
Ҡߦ࡞ۀ
Ҡߦ࡞ۀ ॳظঢ়ଶ
Ҡߦ࡞ۀ 1. MySQLͷϦʔυϨϓϦΧΛ࡞ͬͯϨϓϦΛࢭΊΔ
Ҡߦ࡞ۀ 2. ࢭΊͨMySQLϦʔυϨϓϦΧ͔ΒAuroraΛͭ͘Δ
Ҡߦ࡞ۀ 3. AuroraΛMySQL masterͱϨϓϦ͢Δ
Ҡߦ࡞ۀ 4. Ҡߦ༻ͷMySQLϦʔυϨϓϦΧআ
Ҡߦ࡞ۀ 5. AuroraͷϦʔυϨϓϦΧΛ࡞Δ
Ҡߦ࡞ۀ 6. AuroraΛঢ֨ɺΞϓϦέʔγϣϯͷଓઌมߋ
Ҡߦ࡞ۀ 7. Ҡߦྃ!
MySQL࣌ͱͷมߋ
MySQL࣌ͱͷมߋ HAProxyΛࢀরࢄʹ͍ͬͯΔ (֤ϗετͰىಈͯ͠localhost:3307ʹଓ)
MySQL࣌ͱͷมߋ Before: HAProxyΈࠐΈͷmysqlϔϧενΣοΫ • Πϯελϯε࡞Γ͠ͰͷIPΞυϨεมߋʹैͰ͖ͳ͍ • master͔slave͔͕͔Βͳ͍ ↓ After: HAProxy1.6ͷಈత໊લղܾ
+ ֎෦scriptʹ
MySQL࣌ͱͷมߋ tkuchiki.hatenablog.com/entry/2016/07/14/180046 HAProxy1.6ͷಈత໊લղܾ + ֎෦script listen mysql-slave bind 127.0.0.1:3307 mode
tcp option external-check external-check path "/usr/bin:/bin:/usr/local/bin" external-check command /usr/local/bin/check_aurora_slave.sh balance roundrobin server master db01.xxx.rds.amazonaws.com:3306 check backup resolvers mydns server slave1 db02.xxx.rds.amazonaws.com:3306 check resolvers mydns server slave2 db03.xxx.rds.amazonaws.com:3306 check resolvers mydns
MySQL࣌ͱͷมߋ HAProxyͷ֎෦script innodb_read_only͕ON → ReadReplica #!/bin/bash READ_ONLY=$(mysql -u haproxy -h
${HAPROXY_SERVER_ADDR} \ -P ${HAPROXY_SERVER_PORT} -BN\ -e "SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';"\ --connect-timeout=5 | awk '{print $2}') [ "${READ_ONLY}" = "ON" ]
MySQL࣌ͱͷมߋ ReadReplicaͰ show engine innodb status ͕ݟ͑ͳ͍?? ༷ͱͷ͜ͱ (by αϙʔτ)
ࢹͰ ROW OPERATIONS Λूܭ͍ͯͨ͠ͷͰࠔΔ
MySQL࣌ͱͷมߋ show status like '%Innodb_rows%' ʹมߋ +----------------------+----------+ | Variable_name |
Value | +----------------------+----------+ | Innodb_rows_deleted | 1 | | Innodb_rows_inserted | 111002 | | Innodb_rows_read | 15682572 | | Innodb_rows_updated | 1020 | +----------------------+----------+
MySQL࣌ͱͷมߋ ΫΤϦͷϨΠςϯγ͕औΕΔΑ͏ʹͳͬͨ
Ҡߦͯ͠Έͯ
Ҡߦͯ͠Έͯ ࣌ʑى͖ΔEBS٧·Γ͕ͭΒ͍ → 2ʙ3िִؒͰൃੜ͍ͯͨ͠ͷ͕0ʹ ! Α͘ΕΔ
Ҡߦͯ͠Έͯ ϦʔυϨϓϦΧͷஆؾ͕ͭΒ͍ → ෆཁʹͳͬͨͷͰ৺ཧతʹָ ! (·ཱͩͯͯͳ͍͚Ͳ)
Ҡߦͯ͠Έͯ ! snapshotͷෛՙΛؾʹ͠ͳͯ͘Α͘ͳͬͨ • (ΞϓϦͷ)োରԠͷͨΊʹ͜·ΊʹsnapshotΛऔ͍ͬͯΔ • EBS snapshotଟগͳΓͱੑೳʹӨڹ͢Δ • AuroraͰӨڹͳ͠ͱͷ͜ͱ
(by αϙʔτ)
Ҡߦͯ͠Έͯ ! ίετେࠩͳ͠(͔Լ͕ͬͨ) • Provisioned IOPS (ߴ͍) • GP2ͰੑೳͷͨΊʹ༰ྔΛ֬อ͍ͯͨ͠ͷ͕ෆཁʹ •
Πϯελϯεඅ༻ ↑ • ετϨʔδɺIOඅ༻ ↓
·ͱΊ MySQL ͔Β Aurora Ҡߦ͢ΔՁ͋Γ ಛʹIOͷ҆ఆੑ ⭕ ϨϓϦέʔγϣϯΛ͑࠷খݶͷμϯλΠϜͰՄೳ ίετݮΒͤΔ(͔Εͳ͍)