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
監視のこれまでとこれから/sakura monitoring seminar 2025
fujiwara3
11
4k
k6による負荷試験 入門から日常的な実践まで/Re:TechTalk #01
fujiwara3
2
82
困難を「一般解」で解く
fujiwara3
10
3.7k
「隙間家具OSS」に至る道/Fujiwara Tech Conference 2025
fujiwara3
7
12k
alecthomas/kong はいいぞ / kamakura.go#7
fujiwara3
1
1k
ISUCONに強くなるかもしれない日々の過ごしかた/Findy ISUCON 2024-11-14
fujiwara3
11
1.4k
「最高のチューニング」をしないために / hack@delta 24.10
fujiwara3
21
4.4k
AWS Lambdaで実現するスケーラブルで低コストなWebサービス構築/YAPC::Hakodate2024
fujiwara3
10
6.4k
CEL(Common Expression Language)で書いた条件にマッチしたIAM Policyを見つける / iam-policy-finder
fujiwara3
2
1.9k
Other Decks in Technology
See All in Technology
AWS Organizations 新機能!マルチパーティ承認の紹介
yhana
1
190
低レイヤを知りたいPHPerのためのCコンパイラ作成入門 完全版 / Building a C Compiler for PHPers Who Want to Dive into Low-Level Programming - Expanded
tomzoh
4
3.3k
なぜ私はいま、ここにいるのか? #もがく中堅デザイナー #プロダクトデザイナー
bengo4com
0
1.2k
GeminiとNotebookLMによる金融実務の業務革新
abenben
0
240
標準技術と独自システムで作る「つらくない」SaaS アカウント管理 / Effortless SaaS Account Management with Standard Technologies & Custom Systems
yuyatakeyama
3
1.3k
AIの最新技術&テーマをつまんで紹介&フリートークするシリーズ #1 量子機械学習の入門
tkhresk
0
140
LangChain Interrupt & LangChain Ambassadors meetingレポート
os1ma
2
160
"サービスチーム" での技術選定 / Making Technology Decisions for the Service Team
kaminashi
1
200
強化されたAmazon Location Serviceによる新機能と開発者体験
dayjournal
3
230
250627 関西Ruby会議08 前夜祭 RejectKaigi「DJ on Ruby Ver.0.1」
msykd
PRO
2
350
WordPressから ヘッドレスCMSへ! Storyblokへの移行プロセス
nyata
0
230
急成長を支える基盤作り〜地道な改善からコツコツと〜 #cre_meetup
stefafafan
0
150
Featured
See All Featured
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
35
2.4k
Six Lessons from altMBA
skipperchong
28
3.9k
Build The Right Thing And Hit Your Dates
maggiecrowley
36
2.8k
The Invisible Side of Design
smashingmag
300
51k
RailsConf 2023
tenderlove
30
1.1k
Raft: Consensus for Rubyists
vanstee
140
7k
Mobile First: as difficult as doing things right
swwweet
223
9.7k
The Cult of Friendly URLs
andyhume
79
6.5k
Done Done
chrislema
184
16k
Music & Morning Musume
bryan
46
6.6k
Building Adaptive Systems
keathley
43
2.6k
For a Future-Friendly Web
brad_frost
179
9.8k
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ͷ҆ఆੑ ⭕ ϨϓϦέʔγϣϯΛ͑࠷খݶͷμϯλΠϜͰՄೳ ίετݮΒͤΔ(͔Εͳ͍)