Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
20221219_pNKayac_takatsuka.pdf
Hiroki Takatsuka
December 22, 2022
Technology
0
31
20221219_pNKayac_takatsuka.pdf
Hiroki Takatsuka
December 22, 2022
Tweet
Share
Other Decks in Technology
See All in Technology
CES_2023_FleetWise_demo.pdf
sparkgene
0
120
OCI技術資料 : ロード・バランサー 詳細 / Load Balancer 200
ocise
2
7.2k
初めてのデータ移行プロジェクトから得た学び
tjmtmmnk
0
350
Kaggleシミュレーションコンペの動向
nagiss
0
270
【Λ(らむだ)】WinActorから始めるいつのまにリスキリング / WinAtorライトニングトーク大会20230123
lambda
0
110
230125 モニターマウントLT ITガジェット翁(Ryu.Cyber)さん
comucal
PRO
0
4.7k
NGINXENG JP#2 - 3-NGINX Plus・プロダクトのアップデート
hiropo20
0
250
金属加工屋の営業マンがSTマイクロで・・・
usashirou
0
170
Deep dive in Reserved Instance ~脳死推奨量購入からの脱却~
kzkmaeda
0
540
AWS Cloud Forensics & Incident Response
e11i0t_4lders0n
0
380
2年で10→70人へ! スタートアップの 情報セキュリティ課題と施策
miekobayashi
1
630
ユーザーテストガイドライン VERSION 2.0
kouzoukaikaku
0
1.4k
Featured
See All Featured
Designing Experiences People Love
moore
130
22k
Atom: Resistance is Futile
akmur
256
24k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
44
14k
Designing with Data
zakiwarfel
91
4.2k
A Tale of Four Properties
chriscoyier
149
21k
Robots, Beer and Maslow
schacon
154
7.3k
Become a Pro
speakerdeck
PRO
6
3.2k
Unsuck your backbone
ammeep
659
56k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
270
12k
The MySQL Ecosystem @ GitHub 2015
samlambert
240
11k
In The Pink: A Labor of Love
frogandcode
132
21k
GitHub's CSS Performance
jonrohan
1020
430k
Transcript
trocco® ӡ༻ͰֶͼΛಘͨ Aurora MySQL ϋϚϦϙΠϯτͱରԠ primeNumber SRE ߴ௩و (@tk3fftk)
ձ໊ࣾ גࣜձࣾprimeNumber දऔకCEO ాᬑ ༤थ ۀ 201511݄ Office ౦ژ্۠େ࡚ࡾஸ1൪1߸ JR౦ٸࠇϏϧ5F
© primeNumber.Inc ࢲͨͪͷϏδϣϯ ͋ΒΏΔσʔλΛɺ Ϗδωεͷྗʹม͑Δɻ primeNumberɺσʔλςΫϊϩδʔΧϯύχʔɻ ͋ΒΏΔσʔλ͕രൃతʹ૿͍͑ͯ࣌͘ʹɺ ୭͕͘͢ɺ؆୯ʹσʔλΛ͑ΔڥΛߏங͠ɺ σʔλ׆༻·ͰͷϓϩηεΛ࠷దԽɻ ߴͳςΫϊϩδʔͱಠࣗͷΞΠσΞͰɺੈքதͷϏδωεΛࢧԉ͠·͢ɻ
3
© primeNumber.Inc 4
© primeNumber.Inc ߴ௩و (Hiroki Takatsuka, @tk3fftk) גࣜձࣾprimeNumber ΤϯδχΞϦϯάຊ෦ SRE 5
• େࡕ → ਆށ → ౦ژ ◦ ࡢژʹ͍ͨͷͰ౦ւಓ৽װઢͷӡస ݟ߹ΘͤͷӨڹ͕ͬͭΓड͚·ͨ͠😇 • ϠϑʔגࣜձࣾͰ։ൃऀ͚ϓϥοτϑΥʔϜ ͷ։ൃɾӡ༻෦ (2016~2022) ◦ ྗ͍ͯͨ͠ͷ OSS ͷ CI/CD πʔϧ Screwdriver.cd ͷ։ൃɾSRE ◦ εΫϥϜϚελʔͬͨΓEMͬͨΓ • trocco® ʹ2021/10 ͔Β෭ۀͰSREͱͯؔ͠Θ
© primeNumber.Inc • Aurora MySQL Ͱ Reader ͷෛՙ͕ Writer (Clusterશମ)
ʹӨڹ༩͑Δ͜ ͱ͕͋ΔΑ ◦ Aurora MySQL ಛ༗ͷͰͳ͍͚Ͳग़͍͢ΞʔΩςΫνϟʹͳ͍ͬͯΔΑ ◦ trocco® ͷ Aurora ͷࣾͰͷ͍ํͰ͕ͬͭΓҾ͔͔͍ͬͬͯͨΑ • ্هΛղܾͯ͠·͕͔ͩ͋ͬͨΒผͷରԠೖΕͨΑ ࠓ͢͜ͱ 6
trocco® ͕໘ͨ͠՝
© primeNumber.Inc ϝϞϦރׇʹΑΔDBΠϯελϯεμϯ → Failover ൃੜ • τυϝΛͨ͠ͷػೳϦϦʔε࣌ͷ ALTER TABLE
8
© primeNumber.Inc Failover ޙ࠶ͼԼ͕Γଓ͚Δ FreeableMemory • ϦϦʔεʹࢧো͕ग़Δঢ়ଶ • ϝϞϦރׇ͕ݪҼͰམͪͨͷʹɺ·ͨϝϞϦ͕ʹʹݮ͍ͬͯ͘ ◦
Ͳ͜·ͰԼ͕Δͷ͔ɺͳͥ͜ΜͳʹԼ͕Δͷ͔ɺਆ (AWS Support) ʹفΔʑ 9
© primeNumber.Inc Q. فͬͯͳ͍ͰରԠͨ͠ΒͲ͏Ͱ͔͢ʁ • A. ͪΖΜ͍߹Θͤͱಉ࣌ʹௐࠪɺରࡦͷݕ౼ਐΊ͍ͯ·ͨ͠ • ఆظతʹ࠶ىಈ͢Δʁ ◦
trocco® ࢭΊΒΕͳ͍ (ࢭΊͨ͘ͳ͍) ▪ 24͓࣌ؒ٬͞Μͷసૹδϣϒ͕ಈ͍͓ͯΓɺॠஅͰӨڹ͕ग़ΔՄೳੑ͕͋Δ • (͜ͷ͋ͨΓผ్վળ͍͖͍ͯͨ͠ϙΠϯτͰ͢) • ϝϞϦ૿͢ઃఆ͢Δ or Ϧιʔε૿͢ʁ ◦ ͲͪΒ࠶ىಈ͕ඞཁʹͳΔͨΊɺ্هͱಉ͡ཧ༝Ͱ࠷ऴखஈʹ͔ͨͬͨ͠ ▪ ࠶ىಈͳ͠ͷखஈ͕͋͠ΔͷͳΒͦͬͪΛબͼ͍ͨ ◦ 11݄ʹRDSΫϥελҠߦΛ༧ఆ͍ͯͨ͠ͷͰɺ2ϲ݄͑ΕઃఆมߋͰ͖Δ 10
© primeNumber.Inc ͦͯ͠ AWS Support ΑΓճ͕ • > ͋ΔλΠϛϯάΑΓʮRollbackSegmentHistoryListLengthʯʹେ͖ͳ͕ݟΒΕΔΑ͏ʹͳ͓ͬͯΓ·ͨ͠ҝɺͪ͜ ΒফඅϝϞϦʹӨڹΛ༩͍͑ͯͨͷͰͳ͍͔ͱఆக͓ͯ͠Γ·͢ɻ
• > MySQL DBΤϯδϯʹ͓͍ͯɺwriter/readerͷํͰɺඇৗʹ࣮ߦ࣌ؒͷ͍ΫΤϦ͕ଘࡏ͍ͯ͠Δ߹ɺಡΈऔΓҰ؏ ੑΛ࣮ݱ͢ΔͨΊʹϩʔϧόοΫηάϝϯτʹաڈͷใΛอ࣋͠ͳ͚Ε͍͚ͳ্͍ؔɺϩϯάΫΤϦ࣮ߦதͷߋ৽ใ Λશͯอ࣋͠ͳ͚ΕͳΒͳ͍ͱ͍͏ಈ࡞ʹىҼͨ͠ͷͱͳΓ·͢ɻ͜ͷͱ͖ɺߋ৽ʹΑͬͯอ࣋͠ͳ͚ΕͳΒͳ͍աڈ ͷใͷྔ͕ɺͪ͜ΒͷϝτϦΫεͱͳΓ·͢ɻ͜ͷͨΊɺߋ৽ྔ͕ଟ͍DBʹ͓͍ͯ࣌ؒͷಡΈऔΓΫΤϦ͕࣮ࢪ͞Εͨ ߹ɺDBͷෛՙ্͕ঢ͢ΔՄೳੑ͕͍͟͝·͢ɻ 11
© primeNumber.Inc Failover ޙͷ RollbackSegmentHistoryListLength (RSHLL) • ࢦఠΒͬͯΔ͠໌Β͔ʹةͳͦ͏ͳܗΛ͍ͯ͠Δ 12
© primeNumber.Inc ͳͥ RollbackSegmentHistoryListLength (RSHLL) ͕૿େ͢Δ͔ • τϥϯβΫγϣϯͷҰ؏ੑΛ֬อ͢ΔͨΊʹɺMySQL աڈͷߦͷཤྺͷΑ͏ͳͷ Λ͍࣋ͬͯΔ
◦ ྫ͑ɺ1લ͔ΒऴΘΒͳ͍ SELECT จ͕͋ͬͨͱͯ͠ɺͦͷ SELECT Ͱ৮͍ͬͯΔߦ͕ผͷτϥϯ βΫγϣϯͰߋ৽͞Εͨͱͯ͠ɺσʔλͷҰ؏ੑΛอͭͨΊʹ SELECT ͕ऴΘΔ·Ͱݹ͍ߦࣺͯΒ Εͳ͍ ▪ ࣺͯΒΕ͍ͯͳ͍ݹ͍ߦ͕ཷ·ͬͯ͘Δͱ৭Μͳͱ͜ΖͰύϑΥʔϚϯεͷ͕ग़Δ • ࢀߟ: Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back - Percona Database Performance Blog • Aurora ΞʔΩςΫνϟతʹ͜ͷ͕ग़͘͢ͳ͍ͬͯΔ ◦ Aurora MySQL DB ΫϥελʔͰͷ͍ SELECT ΫΤϦͷτϥϒϧγϡʔςΟϯά ▪ > Aurora MySQL Ͱɺڞ༗ετϨʔδͷϘϦϡʔϜͷੑ্࣭ɺཤྺϦετͷ͞ΫϥελʔϨ ϕϧͰ͋ΓɺݸʑͷΠϯελϯεϨϕϧͰ͋Γ·ͤΜɻ 13
© primeNumber.Inc trocco® ͷ Reader ͷ͍ํ͕ग़͔ͬͨ͢ • ࣌ؒτϥϯβΫγϣϯΛுΔΑ͏ͳΫΤϦΛఆظతʹྲྀ͍ͯͨ͠ 14
RollbackSegmentHistoryListLength ͷ૿େʹରͯ͠ ͲͷΑ͏ͳରԠҊ͕͋ΓɺԿΛߦ͔ͬͨ
© primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ • ظରԠ ◦ ࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ◦ ࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴԽ͢Δ •
தظରԠ ◦ MySQL ͷϨϕϧΛมߋ͢Δ ◦ Reader ʹ͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ͚Δ ◦ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿͢ (ཁ࠶ىಈ) ◦ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 16
© primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ • ظରԠ ◦ ࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ◦ ࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴԽ͢Δ •
தظରԠ ◦ MySQL ͷϨϕϧΛมߋ͢Δ ◦ Reader ʹ͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ͚Δ ◦ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿͢ (ཁ࠶ىಈ) ◦ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 17
© primeNumber.Inc ࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔɾߴԽ • Reader Ͱݕग़͞Ε͍ͯΔ slowquery Λௐࠪͯ͠Ұ࣌ఀࢭ 18
© primeNumber.Inc Reader ʹ͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ͚Δ • Aurora ͷΫϩʔϯػೳ + binlog ϨϓϦέʔγϣϯΛͬͯੳ༻
Aurora Ϋϥ ελΛ࡞ ◦ ظରԠͰࢭΊͨ Redash, trocco® ʹΑΔ trocco® ͷσʔλసૹΛ࠶։ͤ͞Δత ◦ ίετݮͷͨΊຊମΫϥελΑΓ1ͭԼͷΠϯελϯεαΠζͰՔಇ ▪ CPUUtilization, BufferCacheHitRatio, SwapUsageͳͲΛݟܾͯఆ ▪ ϦιʔεʹՃ͑ϨϓϦέʔγϣϯϥά (AuroraBinlogReplicaLag) Λࢹ • සൟʹϥάΔΑ͏ͳΒ্͛Δ͜ͱݕ౼ • ੳ༻Ͱ͋Εؾܰʹམͱͯ͠ΠϯελϯεαΠζมߋɺͱ͍͏͜ͱՄೳͳͣ 19
© primeNumber.Inc FreeableMemory ͷݮগ؇͔ʹͳͬͨ 20
© primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ (࠶ܝ) • ظରԠ ◦ ࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ◦ ࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴԽ͢Δ
• தظରԠ ◦ MySQL ͷϨϕϧΛมߋ͢Δ ◦ Reader ʹ͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ͚Δ ◦ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿͢ (ཁ࠶ىಈ) ◦ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 21
© primeNumber.Inc Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿͢ (ཁ࠶ىಈ) • innodb_buffer_pool_size Λ DBInstanceClassMemory*3/4 (default)
→ DBInstanceClassMemory*5/8 ◦ BufferCacheHitRatio, SwapUsage Λݟͯஅ ▪ BufferCacheHitRatio ΄΅100%ͩͬͨͨΊ (SwapUsage ͣͬͱθϩͩͬͨ) 22
© primeNumber.Inc Aurora engine ͷ minor version Λ্͛Δ (ཁ࠶ىಈ) •
ϝϞϦؔ࿈ͷ bugfix ͱ͔ imporovement ͕ೖ͍ͬͯΔՄೳੑ͕͋Δ ◦ Release notes for Amazon Aurora MySQL-Compatible Edition - Amazon Aurora • Zero Downtime Patching ·ͩར༻Ͱ͖ͳ͍ engine όʔδϣϯͩͬͨ ◦ ͦͦຊʹαʔϏεӨڹग़ͣ͞ʹ͑Δͷ͔ݕূ͠ͳ͍ͱ͏ͷ͜Θ͍ 23
© primeNumber.Inc ࠓޙͷల 24 • binlog ͷར༻ΛΊΔ ◦ binlog Λ༗ޮʹ͍ͯ͠ΔͱΫϥογϡޙͷ෮چ͕࣌ؒ͘ͳΔɺͱͷ͜ͱ
▪ ࢀߟ: Amazon Aurora ετϨʔδͱ৴པੑ - Amazon Aurora ◦ όοΫτϥοΫػೳ͕͑ͳ͍ • Aurora ΫϩʔϯػೳΛͬͨ߹ͷσʔλͷ࣋ͪํίετ໘Ͱݒ೦ ◦ ίϐʔΦϯϥΠτ ͰσʔλΛอ࣋ɾߋ৽͢Δ ▪ ޮѱ͍͔ʁΫϩʔϯ࡞࣌ͷσʔλΛແବʹอ࣋͠ଓ͚ͯ͠·͍ͬͯΔ͔ʁͳͲ • ސ٬૿ʹΑΓݟࠐ·ΕΔDBෛՙΛԼ͛Δ
© primeNumber.Inc ·ͱΊɾॴײ • Aurora Reader ʹ͛ͨΫΤϦ͕ΫϥελશମʹӨڹΛ༩͑ಘΔ ◦ RollbackSegmentHistoryListLength
ͷ૿େʹΑΔϦιʔεফඅɾύϑΥʔϚϯεԼͳͲ • ࣌ؒͷτϥϯβΫγϣϯ͕ආ͚ΒΕͳ͍߹ҎԼͷରԠΛೖΕͯΈΔ ◦ Ϋϥελ͝ͱͯ͠ Reader ʹ͛ͯͨΫΤϦΛͦͬͪʹ͛Δ ◦ MySQL ϨϕϧΛREAD COMMITTEDʹ͢Δ • AWS Support ͪΌΜͱ׆༻͖͢ ◦ ʮΒͳ͍͜ͱΛΒͳ͍ʯྖҬʹ͍ͭͯฦΛ͘ΕΔ 25
© primeNumber.Inc ࢀߟ • Amazon Aurora ϦʔυϨϓϦΧͷ࠶ىಈʹؔ͢ΔτϥϒϧγϡʔςΟϯά ◦ > ૿େԽ͕ਐΉ
History List Length (HLL) ΛௐΔ (Aurora MySQL - ޓ) • Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back - Percona Database Performance Blog • Aurora MySQL DB ΫϥελʔͰͷ͍ SELECT ΫΤϦͷτϥϒϧγϡʔςΟϯά 26