Aurora MySQL ハマリポイントと対応

1. trocco® ӡ༻ͰֶͼΛಘͨ Aurora MySQL ϋϚϦϙΠϯτͱରԠ primeNumber SRE ߴ௩޿و (@tk3fftk)
2. ձ໊ࣾ גࣜձࣾprimeNumber ୅දऔక໾CEO ాᬑ ༤थ ૑ۀ 2015೥11݄ Office ౦ژ౎඼઒্۠େ࡚ࡾஸ໨1൪1߸ JR౦ٸ໨ࠇϏϧ5F 
3. © primeNumber.Inc ࢲͨͪͷϏδϣϯ ͋ΒΏΔσʔλΛɺ Ϗδωεͷྗʹม͑Δɻ primeNumber͸ɺσʔλςΫϊϩδʔΧϯύχʔɻ ͋ΒΏΔσʔλ͕രൃతʹ૿͍͑ͯ࣌͘୅ʹɺ ୭΋͕͢͹΍͘ɺ؆୯ʹσʔλΛ࢖͑Δ؀ڥΛߏங͠ɺ σʔλ׆༻·ͰͷϓϩηεΛ࠷దԽɻ ߴ౓ͳςΫϊϩδʔͱಠࣗͷΞΠσΞͰɺੈքதͷϏδωεΛࢧԉ͠·͢ɻ 3
4. © primeNumber.Inc 4
5. © primeNumber.Inc ߴ௩޿و (Hiroki Takatsuka, @tk3fftk) גࣜձࣾprimeNumber ΤϯδχΞϦϯάຊ෦ SRE 5 • େࡕ → ਆށ → ౦ژ ◦ ࡢ೔͸ژ౎ʹ͍ͨͷͰ౦ւಓ৽װઢͷӡస ݟ߹ΘͤͷӨڹ͕ͬͭΓड͚·ͨ͠😇 • ϠϑʔגࣜձࣾͰ։ൃऀ޲͚ϓϥοτϑΥʔϜ ͷ։ൃɾӡ༻෦໳ (2016~2022) ◦ ஫ྗ͍ͯͨ͠ͷ͸ OSS ͷ CI/CD πʔϧ Screwdriver.cd ͷ։ൃɾSRE ◦ εΫϥϜϚελʔ΍ͬͨΓEM΍ͬͨΓ • trocco® ʹ͸2021/10 ͔Β෭ۀͰSREͱͯؔ͠Θ
6. © primeNumber.Inc • Aurora MySQL Ͱ͸ Reader ͷෛՙ͕ Writer (Clusterશମ) ʹ΋Өڹ༩͑Δ͜ ͱ͕͋ΔΑ ◦ Aurora MySQL ಛ༗ͷ໰୊Ͱ͸ͳ͍͚Ͳग़΍͍͢ΞʔΩςΫνϟʹͳ͍ͬͯΔΑ ◦ trocco® ͷ Aurora ͷࣾ಺Ͱͷ࢖͍ํͰ͸͕ͬͭΓҾ͔͔͍ͬͬͯͨΑ • ্هΛղܾͯ͠΋·ͩ໰୊͕͔͋ͬͨΒผͷରԠ΋ೖΕͨΑ ࠓ೔࿩͢͜ͱ 6
7. trocco® ͕௚໘ͨ͠՝୊
8. © primeNumber.Inc ϝϞϦރׇʹΑΔDBΠϯελϯεμ΢ϯ → Failover ൃੜ • τυϝΛࢗͨ͠ͷ͸ػೳϦϦʔε࣌ͷ ALTER TABLE 8
9. © primeNumber.Inc Failover ޙ΋࠶ͼԼ͕Γଓ͚Δ FreeableMemory • ϦϦʔεʹࢧো͕ग़Δঢ়ଶ • ϝϞϦރׇ͕ݪҼͰམͪͨͷʹɺ·ͨϝϞϦ͕೔ʹ೔ʹݮ͍ͬͯ͘ ◦ Ͳ͜·ͰԼ͕Δͷ͔ɺͳͥ͜ΜͳʹԼ͕Δͷ͔ɺਆ (AWS Support) ʹفΔ೔ʑ 9
10. © primeNumber.Inc Q. فͬͯͳ͍ͰରԠͨ͠ΒͲ͏Ͱ͔͢ʁ • A. ΋ͪΖΜ໰͍߹Θͤͱಉ࣌ʹௐࠪɺରࡦͷݕ౼͸ਐΊ͍ͯ·ͨ͠ • ఆظతʹ࠶ىಈ͢Δʁ ◦ trocco® ࢭΊΒΕͳ͍ (ࢭΊͨ͘ͳ͍) ໰୊ ▪ 24͓࣌ؒ٬͞Μͷసૹδϣϒ͕ಈ͍͓ͯΓɺॠஅͰ΋Өڹ͕ग़ΔՄೳੑ͕͋Δ • (͜ͷ͋ͨΓ͸ผ్վળ͍͖͍ͯͨ͠ϙΠϯτͰ͢) • ϝϞϦ૿΍͢ઃఆ͢Δ or Ϧιʔε૿΍͢ʁ ◦ ͲͪΒ΋࠶ىಈ͕ඞཁʹͳΔͨΊɺ্هͱಉ͡ཧ༝Ͱ࠷ऴखஈʹ͔ͨͬͨ͠ ▪ ࠶ىಈͳ͠ͷखஈ͕΋͋͠ΔͷͳΒͦͬͪΛબͼ͍ͨ ◦ 11݄ʹRDSΫϥελҠߦΛ༧ఆ͍ͯͨ͠ͷͰɺ໿2ϲ݄଱͑Ε͹ઃఆมߋͰ͖Δ 10
11. © primeNumber.Inc ͦͯ͠ AWS Support ΑΓճ౴͕ • > ͋ΔλΠϛϯάΑΓʮRollbackSegmentHistoryListLengthʯʹେ͖ͳ஋͕ݟΒΕΔΑ͏ʹͳ͓ͬͯΓ·ͨ͠ҝɺͪ͜ Β͸ফඅϝϞϦʹӨڹΛ༩͍͑ͯͨͷͰ͸ͳ͍͔ͱ૝ఆக͓ͯ͠Γ·͢ɻ • > MySQL DBΤϯδϯʹ͓͍ͯɺwriter/readerͷ૒ํͰɺඇৗʹ࣮ߦ࣌ؒͷ௕͍ΫΤϦ͕ଘࡏ͍ͯ͠Δ৔߹ɺಡΈऔΓҰ؏ ੑΛ࣮ݱ͢ΔͨΊʹϩʔϧόοΫηάϝϯτʹաڈͷ৘ใΛอ࣋͠ͳ͚Ε͹͍͚ͳ͍ؔ܎্ɺϩϯάΫΤϦ࣮ߦதͷߋ৽৘ใ Λશͯอ࣋͠ͳ͚Ε͹ͳΒͳ͍ͱ͍͏ಈ࡞ʹىҼͨ͠΋ͷͱͳΓ·͢ɻ͜ͷͱ͖ɺߋ৽ʹΑͬͯอ࣋͠ͳ͚Ε͹ͳΒͳ͍աڈ ͷ৘ใͷྔ͕ɺͪ͜ΒͷϝτϦΫεͱͳΓ·͢ɻ͜ͷͨΊɺߋ৽ྔ͕ଟ͍DBʹ͓͍ͯ௕࣌ؒͷಡΈऔΓΫΤϦ͕࣮ࢪ͞Εͨ৔ ߹ɺDBͷෛՙ্͕ঢ͢ΔՄೳੑ͕͍͟͝·͢ɻ 11
12. © primeNumber.Inc Failover ޙͷ RollbackSegmentHistoryListLength (RSHLL) • ࢦఠ΋ΒͬͯΔ͠໌Β͔ʹةͳͦ͏ͳܗΛ͍ͯ͠Δ 12
13. © 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
14. © primeNumber.Inc trocco® ͷ Reader ͷ࢖͍ํ΋໰୊͕ग़΍͔ͬͨ͢ • ௕࣌ؒτϥϯβΫγϣϯΛுΔΑ͏ͳΫΤϦΛఆظతʹྲྀ͍ͯͨ͠ 14
15. RollbackSegmentHistoryListLength ͷ૿େʹରͯ͠ ͲͷΑ͏ͳରԠҊ͕͋ΓɺԿΛߦ͔ͬͨ
16. © primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ • ୹ظରԠ ◦ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ◦ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ • த௕ظରԠ ◦ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ ◦ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ ◦ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) ◦ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 16
17. © primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ • ୹ظରԠ ◦ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ◦ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ • த௕ظରԠ ◦ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ ◦ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ ◦ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) ◦ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 17
18. © primeNumber.Inc ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔɾߴ଎Խ • Reader Ͱݕग़͞Ε͍ͯΔ slowquery Λௐࠪͯ͠Ұ࣌ఀࢭ 18
19. © primeNumber.Inc Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ • Aurora ͷΫϩʔϯػೳ + binlog ϨϓϦέʔγϣϯΛ࢖ͬͯ෼ੳ༻ Aurora Ϋϥ ελΛ࡞੒ ◦ ୹ظରԠͰࢭΊͨ Redash, trocco® ʹΑΔ trocco® ͷσʔλసૹΛ࠶։ͤ͞Δ໨త ◦ ίετ࡟ݮͷͨΊຊମΫϥελΑΓ1ͭԼͷΠϯελϯεαΠζͰՔಇ ▪ CPUUtilization, BufferCacheHitRatio, SwapUsageͳͲΛݟܾͯఆ ▪ ϦιʔεʹՃ͑ϨϓϦέʔγϣϯϥά (AuroraBinlogReplicaLag) Λ؂ࢹ • සൟʹϥάΔΑ͏ͳΒ্͛Δ͜ͱ΋ݕ౼ • ෼ੳ༻Ͱ͋Ε͹ؾܰʹམͱͯ͠ΠϯελϯεαΠζมߋɺͱ͍͏͜ͱ΋Մೳͳ͸ͣ 19
20. © primeNumber.Inc FreeableMemory ͷݮগ଎౓͸؇΍͔ʹͳͬͨ 20
21. © primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ (࠶ܝ) • ୹ظରԠ ◦ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ◦ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ • த௕ظରԠ ◦ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ ◦ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ ◦ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) ◦ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 21
22. © primeNumber.Inc Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) • innodb_buffer_pool_size Λ DBInstanceClassMemory*3/4 (default) → DBInstanceClassMemory*5/8 ◦ BufferCacheHitRatio, SwapUsage Λݟͯ൑அ ▪ BufferCacheHitRatio ͸΄΅100%ͩͬͨͨΊ (SwapUsage ͸ͣͬͱθϩͩͬͨ) 22
23. © primeNumber.Inc Aurora engine ͷ minor version Λ্͛Δ (ཁ࠶ىಈ) • ϝϞϦؔ࿈ͷ bugfix ͱ͔ imporovement ͕ೖ͍ͬͯΔՄೳੑ͕͋Δ ◦ Release notes for Amazon Aurora MySQL-Compatible Edition - Amazon Aurora • Zero Downtime Patching ͸·ͩར༻Ͱ͖ͳ͍ engine όʔδϣϯͩͬͨ ◦ ͦ΋ͦ΋ຊ౰ʹαʔϏεӨڹग़ͣ͞ʹ࢖͑Δͷ͔ݕূ͠ͳ͍ͱ࢖͏ͷ͸͜Θ͍ 23
24. © primeNumber.Inc ࠓޙͷల๬ 24 • binlog ͷར༻Λ΍ΊΔ ◦ binlog Λ༗ޮʹ͍ͯ͠ΔͱΫϥογϡޙͷ෮چ͕࣌ؒ௕͘ͳΔɺͱͷ͜ͱ ▪ ࢀߟ: Amazon Aurora ετϨʔδͱ৴པੑ - Amazon Aurora ◦ όοΫτϥοΫػೳ͕࢖͑ͳ͍ • Aurora ΫϩʔϯػೳΛ࢖ͬͨ৔߹ͷσʔλͷ࣋ͪํ͸ίετ໘Ͱ΍΍ݒ೦ ◦ ίϐʔΦϯϥΠτ ͰσʔλΛอ࣋ɾߋ৽͢Δ ▪ ޮ཰ѱ͍͔΋ʁΫϩʔϯ࡞੒࣌ͷσʔλΛແବʹอ࣋͠ଓ͚ͯ͠·͍ͬͯΔ͔΋ʁͳͲ • ސ٬૿ʹΑΓݟࠐ·ΕΔDBෛՙΛԼ͛Δ
25. © primeNumber.Inc ·ͱΊɾॴײ • Aurora ͸ Reader ʹ౤͛ͨΫΤϦ͕ΫϥελશମʹӨڹΛ༩͑ಘΔ ◦ RollbackSegmentHistoryListLength ͷ૿େʹΑΔϦιʔεফඅɾύϑΥʔϚϯε௿ԼͳͲ • ௕࣌ؒͷτϥϯβΫγϣϯ͕ආ͚ΒΕͳ͍৔߹͸ҎԼͷରԠΛೖΕͯΈΔ ◦ Ϋϥελ͝ͱ෼཭ͯ͠ Reader ʹ౤͛ͯͨΫΤϦΛͦͬͪʹ౤͛Δ ◦ MySQL ෼཭ϨϕϧΛREAD COMMITTEDʹ͢Δ • AWS Support ͸ͪΌΜͱ׆༻͢΂͖ ◦ ʮ஌Βͳ͍͜ͱΛ஌Βͳ͍ʯྖҬʹ͍ͭͯฦ౴Λ͘ΕΔ 25
26. © 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