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 Aurora
Search
FUJIWARA Shunichiro
November 02, 2016
Technology
9
5k
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
AWS CLIの起動が重くてつらいので aws-sdk-client-go を書いた / kamakura.go#6
fujiwara3
7
8.8k
コードを書く隙間を見つけて生きていく技術/Findy 思考の現在地
fujiwara3
28
6.6k
fujiwara-ware OSSをひたすら紹介する/ya8-2024
fujiwara3
7
550
Amazon ECSで好きなだけ検証環境を起動できるOSSの設計・実装・運用 / YAPC::Hiroshima 2024
fujiwara3
22
7.4k
リアル事例から読み解くWebパフォーマンスチューニングの勘所/Offers web performance tuning
fujiwara3
4
1.6k
隙間家具OSS開発で『自分の庭』をつくる / kayac-andpad-event
fujiwara3
0
750
ISUCON作問入門/ ISUCON Summer Fes 2023
fujiwara3
2
1.6k
隙間家具職人が考えること/ecspresso meetup
fujiwara3
5
4.6k
MackerelとGrafana OnCallを連携してみた
fujiwara3
0
1.7k
Other Decks in Technology
See All in Technology
リードタイム、コストを最適化しながら 回復性を求めるクラウドネイティブ戦略 #CNDS2024
sgrokym
1
240
成長し続けるTVerサービスを支える オブザーバビリティとカスタマーサポート
techtver
PRO
2
760
Web Intelligence and Visual Media Analytics
weblyzard
PRO
1
3.8k
From Fragile to Antifragile Internal Platforms
mfpais
PRO
0
120
Adopting ZGC in HBase for LINE Messaging
lycorptech_jp
PRO
2
290
[TimeTree] Aurora から Spanner への 移行の決断と背景
3utama
2
2.7k
Autify Company Deck
autifyhq
1
33k
三次元再構成(東京大学大学院 情報理工学系研究科『知能情報論』)
pfn
PRO
10
1.8k
定量データと定性評価を用いた技術戦略の組織的実践 / Systematic implementation of technology strategies using quantitative data and qualitative evaluation
chaspy
8
830
Claude3 on Bedrock with Converse API + Tool use でチャットアプリを作成してみた/Building-a-Chat-App-Using-Claude3-on-Bedrock-with-the-Converse-API+Tool-Use
ren8k
0
260
RubyKaigiのプロポーザルを通したい。 / rubykaigi-proposal
toshimaru
2
170
KCDSpain / DevBCN 2024: Serverless Platforms on Kubernetes
salaboy
1
160
Featured
See All Featured
Building a Modern Day E-commerce SEO Strategy
aleyda
22
6.5k
Building Applications with DynamoDB
mza
89
5.8k
What the flash - Photography Introduction
edds
64
11k
Building Your Own Lightsaber
phodgson
101
5.8k
Designing Experiences People Love
moore
136
23k
Designing for humans not robots
tammielis
247
25k
Six Lessons from altMBA
skipperchong
22
3.1k
Adopting Sorbet at Scale
ufuk
69
8.7k
Building Flexible Design Systems
yeseniaperezcruz
321
37k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
323
21k
Atom: Resistance is Futile
akmur
260
25k
Statistics for Hackers
jakevdp
791
220k
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ͷ҆ఆੑ ⭕ ϨϓϦέʔγϣϯΛ͑࠷খݶͷμϯλΠϜͰՄೳ ίετݮΒͤΔ(͔Εͳ͍)