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.5k
ecspressoの設計思想に至る道 / sekkeinight2025
fujiwara3
12
2.6k
さくらのIaaS基盤のモニタリングとOpenTelemetry/OSC Hokkaido 2025
fujiwara3
3
1.1k
監視のこれまでとこれから/sakura monitoring seminar 2025
fujiwara3
11
5.1k
k6による負荷試験 入門から日常的な実践まで/Re:TechTalk #01
fujiwara3
2
110
困難を「一般解」で解く
fujiwara3
10
3.7k
「隙間家具OSS」に至る道/Fujiwara Tech Conference 2025
fujiwara3
7
13k
alecthomas/kong はいいぞ / kamakura.go#7
fujiwara3
1
1.1k
ISUCONに強くなるかもしれない日々の過ごしかた/Findy ISUCON 2024-11-14
fujiwara3
11
1.4k
Other Decks in Technology
See All in Technology
마라톤 끝의 단거리 스퍼트: 2025년의 AI
inureyes
PRO
1
750
Amazon S3 Vectorsは大規模ベクトル検索を低コスト化するサーバーレスなベクトルデータベースだ #jawsugsaga / S3 Vectors As A Serverless Vector Database
quiver
1
510
相互運用可能な学修歴クレデンシャルに向けた標準技術と国際動向
fujie
0
250
AIエージェントを現場で使う / 2025.08.07 著者陣に聞く!現場で活用するためのAIエージェント実践入門(Findyランチセッション)
smiyawaki0820
6
1.1k
大規模イベントに向けた ABEMA アーキテクチャの遍歴 ~ Platform Strategy 詳細解説 ~
nagapad
0
230
AI関数が早くなったので試してみよう
kumakura
0
290
Cloud WANの基礎から応用~少しだけDeep Dive~
masakiokuda
3
100
o11yツールを乗り換えた話
tak0x00
2
1.4k
【OptimizationNight】数理最適化のラストワンマイルとしてのUIUX
brainpadpr
2
480
Claude CodeでKiroの仕様駆動開発を実現させるには...
gotalab555
3
1k
LLMをツールからプラットフォームへ〜Ai Workforceの戦略〜 #BetAIDay
layerx
PRO
1
980
猫でもわかるQ_CLI(CDK開発編)+ちょっとだけKiro
kentapapa
0
3.5k
Featured
See All Featured
I Don’t Have Time: Getting Over the Fear to Launch Your Podcast
jcasabona
33
2.4k
Why You Should Never Use an ORM
jnunemaker
PRO
58
9.5k
Being A Developer After 40
akosma
90
590k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
34
3.1k
Building Applications with DynamoDB
mza
96
6.5k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
50
5.5k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
131
19k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.8k
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
49
3k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
139
34k
Side Projects
sachag
455
43k
Making the Leap to Tech Lead
cromwellryan
134
9.5k
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ͷ҆ఆੑ ⭕ ϨϓϦέʔγϣϯΛ͑࠷খݶͷμϯλΠϜͰՄೳ ίετݮΒͤΔ(͔Εͳ͍)