Tweet
Tweet

Slide 1

Slide 1 text

trocco® ӡ༻ͰֶͼΛಘͨ Aurora MySQL ϋϚϦϙΠϯτͱରԠ primeNumber SRE ߴ௩޿و (@tk3fftk)

Slide 2

Slide 2 text

ձ໊ࣾ גࣜձࣾprimeNumber ୅දऔక໾CEO ాᬑ ༤थ ૑ۀ 2015೥11݄ Office ౦ژ౎඼઒্۠େ࡚ࡾஸ໨1൪1߸ JR౦ٸ໨ࠇϏϧ5F

Slide 3

Slide 3 text

© primeNumber.Inc ࢲͨͪͷϏδϣϯ ͋ΒΏΔσʔλΛɺ Ϗδωεͷྗʹม͑Δɻ primeNumber͸ɺσʔλςΫϊϩδʔΧϯύχʔɻ ͋ΒΏΔσʔλ͕രൃతʹ૿͍͑ͯ࣌͘୅ʹɺ ୭΋͕͢͹΍͘ɺ؆୯ʹσʔλΛ࢖͑Δ؀ڥΛߏங͠ɺ σʔλ׆༻·ͰͷϓϩηεΛ࠷దԽɻ ߴ౓ͳςΫϊϩδʔͱಠࣗͷΞΠσΞͰɺੈքதͷϏδωεΛࢧԉ͠·͢ɻ 3

Slide 4

Slide 4 text

© primeNumber.Inc 4

Slide 5

Slide 5 text

© primeNumber.Inc ߴ௩޿و (Hiroki Takatsuka, @tk3fftk) גࣜձࣾprimeNumber ΤϯδχΞϦϯάຊ෦ SRE 5 ● େࡕ → ਆށ → ౦ژ ○ ࡢ೔͸ژ౎ʹ͍ͨͷͰ౦ւಓ৽װઢͷӡస ݟ߹ΘͤͷӨڹ͕ͬͭΓड͚·ͨ͠😇 ● ϠϑʔגࣜձࣾͰ։ൃऀ޲͚ϓϥοτϑΥʔϜ ͷ։ൃɾӡ༻෦໳ (2016~2022) ○ ஫ྗ͍ͯͨ͠ͷ͸ OSS ͷ CI/CD πʔϧ Screwdriver.cd ͷ։ൃɾSRE ○ εΫϥϜϚελʔ΍ͬͨΓEM΍ͬͨΓ ● trocco® ʹ͸2021/10 ͔Β෭ۀͰSREͱͯؔ͠Θ

Slide 6

Slide 6 text

© primeNumber.Inc ● Aurora MySQL Ͱ͸ Reader ͷෛՙ͕ Writer (Clusterશମ) ʹ΋Өڹ༩͑Δ͜ ͱ͕͋ΔΑ ○ Aurora MySQL ಛ༗ͷ໰୊Ͱ͸ͳ͍͚Ͳग़΍͍͢ΞʔΩςΫνϟʹͳ͍ͬͯΔΑ ○ trocco® ͷ Aurora ͷࣾ಺Ͱͷ࢖͍ํͰ͸͕ͬͭΓҾ͔͔͍ͬͬͯͨΑ ● ্هΛղܾͯ͠΋·ͩ໰୊͕͔͋ͬͨΒผͷରԠ΋ೖΕͨΑ ࠓ೔࿩͢͜ͱ 6

Slide 7

Slide 7 text

trocco® ͕௚໘ͨ͠՝୊

Slide 8

Slide 8 text

© primeNumber.Inc ϝϞϦރׇʹΑΔDBΠϯελϯεμ΢ϯ → Failover ൃੜ ● τυϝΛࢗͨ͠ͷ͸ػೳϦϦʔε࣌ͷ ALTER TABLE 8

Slide 9

Slide 9 text

© primeNumber.Inc Failover ޙ΋࠶ͼԼ͕Γଓ͚Δ FreeableMemory ● ϦϦʔεʹࢧো͕ग़Δঢ়ଶ ● ϝϞϦރׇ͕ݪҼͰམͪͨͷʹɺ·ͨϝϞϦ͕೔ʹ೔ʹݮ͍ͬͯ͘ ○ Ͳ͜·ͰԼ͕Δͷ͔ɺͳͥ͜ΜͳʹԼ͕Δͷ͔ɺਆ (AWS Support) ʹفΔ೔ʑ 9

Slide 10

Slide 10 text

© primeNumber.Inc Q. فͬͯͳ͍ͰରԠͨ͠ΒͲ͏Ͱ͔͢ʁ ● A. ΋ͪΖΜ໰͍߹Θͤͱಉ࣌ʹௐࠪɺରࡦͷݕ౼͸ਐΊ͍ͯ·ͨ͠ ● ఆظతʹ࠶ىಈ͢Δʁ ○ trocco® ࢭΊΒΕͳ͍ (ࢭΊͨ͘ͳ͍) ໰୊ ■ 24͓࣌ؒ٬͞Μͷసૹδϣϒ͕ಈ͍͓ͯΓɺॠஅͰ΋Өڹ͕ग़ΔՄೳੑ͕͋Δ ● (͜ͷ͋ͨΓ͸ผ్վળ͍͖͍ͯͨ͠ϙΠϯτͰ͢) ● ϝϞϦ૿΍͢ઃఆ͢Δ or Ϧιʔε૿΍͢ʁ ○ ͲͪΒ΋࠶ىಈ͕ඞཁʹͳΔͨΊɺ্هͱಉ͡ཧ༝Ͱ࠷ऴखஈʹ͔ͨͬͨ͠ ■ ࠶ىಈͳ͠ͷखஈ͕΋͋͠ΔͷͳΒͦͬͪΛબͼ͍ͨ ○ 11݄ʹRDSΫϥελҠߦΛ༧ఆ͍ͯͨ͠ͷͰɺ໿2ϲ݄଱͑Ε͹ઃఆมߋͰ͖Δ 10

Slide 11

Slide 11 text

© primeNumber.Inc ͦͯ͠ AWS Support ΑΓճ౴͕ ● > ͋ΔλΠϛϯάΑΓʮRollbackSegmentHistoryListLengthʯʹେ͖ͳ஋͕ݟΒΕΔΑ͏ʹͳ͓ͬͯΓ·ͨ͠ҝɺͪ͜ Β͸ফඅϝϞϦʹӨڹΛ༩͍͑ͯͨͷͰ͸ͳ͍͔ͱ૝ఆக͓ͯ͠Γ·͢ɻ ● > MySQL DBΤϯδϯʹ͓͍ͯɺwriter/readerͷ૒ํͰɺඇৗʹ࣮ߦ࣌ؒͷ௕͍ΫΤϦ͕ଘࡏ͍ͯ͠Δ৔߹ɺಡΈऔΓҰ؏ ੑΛ࣮ݱ͢ΔͨΊʹϩʔϧόοΫηάϝϯτʹաڈͷ৘ใΛอ࣋͠ͳ͚Ε͹͍͚ͳ͍ؔ܎্ɺϩϯάΫΤϦ࣮ߦதͷߋ৽৘ใ Λશͯอ࣋͠ͳ͚Ε͹ͳΒͳ͍ͱ͍͏ಈ࡞ʹىҼͨ͠΋ͷͱͳΓ·͢ɻ͜ͷͱ͖ɺߋ৽ʹΑͬͯอ࣋͠ͳ͚Ε͹ͳΒͳ͍աڈ ͷ৘ใͷྔ͕ɺͪ͜ΒͷϝτϦΫεͱͳΓ·͢ɻ͜ͷͨΊɺߋ৽ྔ͕ଟ͍DBʹ͓͍ͯ௕࣌ؒͷಡΈऔΓΫΤϦ͕࣮ࢪ͞Εͨ৔ ߹ɺDBͷෛՙ্͕ঢ͢ΔՄೳੑ͕͍͟͝·͢ɻ 11

Slide 12

Slide 12 text

© primeNumber.Inc Failover ޙͷ RollbackSegmentHistoryListLength (RSHLL) ● ࢦఠ΋ΒͬͯΔ͠໌Β͔ʹةͳͦ͏ͳܗΛ͍ͯ͠Δ 12

Slide 13

Slide 13 text

© 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

Slide 14

Slide 14 text

© primeNumber.Inc trocco® ͷ Reader ͷ࢖͍ํ΋໰୊͕ग़΍͔ͬͨ͢ ● ௕࣌ؒτϥϯβΫγϣϯΛுΔΑ͏ͳΫΤϦΛఆظతʹྲྀ͍ͯͨ͠ 14

Slide 15

Slide 15 text

RollbackSegmentHistoryListLength ͷ૿େʹରͯ͠ ͲͷΑ͏ͳରԠҊ͕͋ΓɺԿΛߦ͔ͬͨ

Slide 16

Slide 16 text

© primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ ● ୹ظରԠ ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ ● த௕ظରԠ ○ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ ○ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ ○ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) ○ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 16

Slide 17

Slide 17 text

© primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ ● ୹ظରԠ ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ ● த௕ظରԠ ○ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ ○ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ ○ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) ○ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 17

Slide 18

Slide 18 text

© primeNumber.Inc ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔɾߴ଎Խ ● Reader Ͱݕग़͞Ε͍ͯΔ slowquery Λௐࠪͯ͠Ұ࣌ఀࢭ 18

Slide 19

Slide 19 text

© primeNumber.Inc Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ ● Aurora ͷΫϩʔϯػೳ + binlog ϨϓϦέʔγϣϯΛ࢖ͬͯ෼ੳ༻ Aurora Ϋϥ ελΛ࡞੒ ○ ୹ظରԠͰࢭΊͨ Redash, trocco® ʹΑΔ trocco® ͷσʔλసૹΛ࠶։ͤ͞Δ໨త ○ ίετ࡟ݮͷͨΊຊମΫϥελΑΓ1ͭԼͷΠϯελϯεαΠζͰՔಇ ■ CPUUtilization, BufferCacheHitRatio, SwapUsageͳͲΛݟܾͯఆ ■ ϦιʔεʹՃ͑ϨϓϦέʔγϣϯϥά (AuroraBinlogReplicaLag) Λ؂ࢹ ● සൟʹϥάΔΑ͏ͳΒ্͛Δ͜ͱ΋ݕ౼ ● ෼ੳ༻Ͱ͋Ε͹ؾܰʹམͱͯ͠ΠϯελϯεαΠζมߋɺͱ͍͏͜ͱ΋Մೳͳ͸ͣ 19

Slide 20

Slide 20 text

© primeNumber.Inc FreeableMemory ͷݮগ଎౓͸؇΍͔ʹͳͬͨ 20

Slide 21

Slide 21 text

© primeNumber.Inc ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ (࠶ܝ) ● ୹ظରԠ ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ ● த௕ظରԠ ○ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ ○ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ ○ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) ○ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ) 21

Slide 22

Slide 22 text

© primeNumber.Inc Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ) ● innodb_buffer_pool_size Λ DBInstanceClassMemory*3/4 (default) → DBInstanceClassMemory*5/8 ○ BufferCacheHitRatio, SwapUsage Λݟͯ൑அ ■ BufferCacheHitRatio ͸΄΅100%ͩͬͨͨΊ (SwapUsage ͸ͣͬͱθϩͩͬͨ) 22

Slide 23

Slide 23 text

© primeNumber.Inc Aurora engine ͷ minor version Λ্͛Δ (ཁ࠶ىಈ) ● ϝϞϦؔ࿈ͷ bugfix ͱ͔ imporovement ͕ೖ͍ͬͯΔՄೳੑ͕͋Δ ○ Release notes for Amazon Aurora MySQL-Compatible Edition - Amazon Aurora ● Zero Downtime Patching ͸·ͩར༻Ͱ͖ͳ͍ engine όʔδϣϯͩͬͨ ○ ͦ΋ͦ΋ຊ౰ʹαʔϏεӨڹग़ͣ͞ʹ࢖͑Δͷ͔ݕূ͠ͳ͍ͱ࢖͏ͷ͸͜Θ͍ 23

Slide 24

Slide 24 text

© primeNumber.Inc ࠓޙͷల๬ 24 ● binlog ͷར༻Λ΍ΊΔ ○ binlog Λ༗ޮʹ͍ͯ͠ΔͱΫϥογϡޙͷ෮چ͕࣌ؒ௕͘ͳΔɺͱͷ͜ͱ ■ ࢀߟ: Amazon Aurora ετϨʔδͱ৴པੑ - Amazon Aurora ○ όοΫτϥοΫػೳ͕࢖͑ͳ͍ ● Aurora ΫϩʔϯػೳΛ࢖ͬͨ৔߹ͷσʔλͷ࣋ͪํ͸ίετ໘Ͱ΍΍ݒ೦ ○ ίϐʔΦϯϥΠτ ͰσʔλΛอ࣋ɾߋ৽͢Δ ■ ޮ཰ѱ͍͔΋ʁΫϩʔϯ࡞੒࣌ͷσʔλΛແବʹอ࣋͠ଓ͚ͯ͠·͍ͬͯΔ͔΋ʁͳͲ ● ސ٬૿ʹΑΓݟࠐ·ΕΔDBෛՙΛԼ͛Δ

Slide 25

Slide 25 text

© primeNumber.Inc ·ͱΊɾॴײ ● Aurora ͸ Reader ʹ౤͛ͨΫΤϦ͕ΫϥελશମʹӨڹΛ༩͑ಘΔ ○ RollbackSegmentHistoryListLength ͷ૿େʹΑΔϦιʔεফඅɾύϑΥʔϚϯε௿ԼͳͲ ● ௕࣌ؒͷτϥϯβΫγϣϯ͕ආ͚ΒΕͳ͍৔߹͸ҎԼͷରԠΛೖΕͯΈΔ ○ Ϋϥελ͝ͱ෼཭ͯ͠ Reader ʹ౤͛ͯͨΫΤϦΛͦͬͪʹ౤͛Δ ○ MySQL ෼཭ϨϕϧΛREAD COMMITTEDʹ͢Δ ● AWS Support ͸ͪΌΜͱ׆༻͢΂͖ ○ ʮ஌Βͳ͍͜ͱΛ஌Βͳ͍ʯྖҬʹ͍ͭͯฦ౴Λ͘ΕΔ 25

Slide 26

Slide 26 text

© 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