Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

Hiroki Takatsuka

December 22, 2022
Tweet

More Decks by Hiroki Takatsuka

Other Decks in Technology

Transcript

  1. trocco® ӡ༻ͰֶͼΛಘͨ


    Aurora MySQL ϋϚϦϙΠϯτͱରԠ
    primeNumber SRE


    ߴ௩޿و (@tk3fftk)

    View full-size slide

  2. ձ໊ࣾ


    גࣜձࣾprimeNumber


    ୅දऔక໾CEO


    ాᬑ ༤थ


    ૑ۀ


    2015೥11݄


    Office


    ౦ژ౎඼઒্۠େ࡚ࡾஸ໨1൪1߸


    JR౦ٸ໨ࠇϏϧ5F

    View full-size slide

  3. © primeNumber.Inc
    ࢲͨͪͷϏδϣϯ
    ͋ΒΏΔσʔλΛɺ


    Ϗδωεͷྗʹม͑Δɻ
    primeNumber͸ɺσʔλςΫϊϩδʔΧϯύχʔɻ


    ͋ΒΏΔσʔλ͕രൃతʹ૿͍͑ͯ࣌͘୅ʹɺ


    ୭΋͕͢͹΍͘ɺ؆୯ʹσʔλΛ࢖͑Δ؀ڥΛߏங͠ɺ


    σʔλ׆༻·ͰͷϓϩηεΛ࠷దԽɻ


    ߴ౓ͳςΫϊϩδʔͱಠࣗͷΞΠσΞͰɺੈքதͷϏδωεΛࢧԉ͠·͢ɻ
    3

    View full-size slide

  4. © primeNumber.Inc
    4

    View full-size slide

  5. © primeNumber.Inc
    ߴ௩޿و (Hiroki Takatsuka, @tk3fftk)
    גࣜձࣾprimeNumber ΤϯδχΞϦϯάຊ෦ SRE
    5
    ● େࡕ → ਆށ → ౦ژ


    ○ ࡢ೔͸ژ౎ʹ͍ͨͷͰ౦ւಓ৽װઢͷӡస
    ݟ߹ΘͤͷӨڹ͕ͬͭΓड͚·ͨ͠😇


    ● ϠϑʔגࣜձࣾͰ։ൃऀ޲͚ϓϥοτϑΥʔϜ
    ͷ։ൃɾӡ༻෦໳ (2016~2022)


    ○ ஫ྗ͍ͯͨ͠ͷ͸ OSS ͷ CI/CD πʔϧ
    Screwdriver.cd ͷ։ൃɾSRE


    ○ εΫϥϜϚελʔ΍ͬͨΓEM΍ͬͨΓ


    ● trocco® ʹ͸2021/10 ͔Β෭ۀͰSREͱͯؔ͠Θ

    View full-size slide

  6. © primeNumber.Inc
    ● Aurora MySQL Ͱ͸ Reader ͷෛՙ͕ Writer (Clusterશମ) ʹ΋Өڹ༩͑Δ͜
    ͱ͕͋ΔΑ


    ○ Aurora MySQL ಛ༗ͷ໰୊Ͱ͸ͳ͍͚Ͳग़΍͍͢ΞʔΩςΫνϟʹͳ͍ͬͯΔΑ


    ○ trocco® ͷ Aurora ͷࣾ಺Ͱͷ࢖͍ํͰ͸͕ͬͭΓҾ͔͔͍ͬͬͯͨΑ


    ● ্هΛղܾͯ͠΋·ͩ໰୊͕͔͋ͬͨΒผͷରԠ΋ೖΕͨΑ
    ࠓ೔࿩͢͜ͱ 6

    View full-size slide

  7. trocco® ͕௚໘ͨ͠՝୊

    View full-size slide

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

    View full-size slide

  9. © primeNumber.Inc
    Failover ޙ΋࠶ͼԼ͕Γଓ͚Δ FreeableMemory
    ● ϦϦʔεʹࢧো͕ग़Δঢ়ଶ


    ● ϝϞϦރׇ͕ݪҼͰམͪͨͷʹɺ·ͨϝϞϦ͕೔ʹ೔ʹݮ͍ͬͯ͘


    ○ Ͳ͜·ͰԼ͕Δͷ͔ɺͳͥ͜ΜͳʹԼ͕Δͷ͔ɺਆ (AWS Support) ʹفΔ೔ʑ
    9

    View full-size slide

  10. © primeNumber.Inc
    Q. فͬͯͳ͍ͰରԠͨ͠ΒͲ͏Ͱ͔͢ʁ
    ● A. ΋ͪΖΜ໰͍߹Θͤͱಉ࣌ʹௐࠪɺରࡦͷݕ౼͸ਐΊ͍ͯ·ͨ͠


    ● ఆظతʹ࠶ىಈ͢Δʁ


    ○ trocco® ࢭΊΒΕͳ͍ (ࢭΊͨ͘ͳ͍) ໰୊


    ■ 24͓࣌ؒ٬͞Μͷసૹδϣϒ͕ಈ͍͓ͯΓɺॠஅͰ΋Өڹ͕ग़ΔՄೳੑ͕͋Δ


    ● (͜ͷ͋ͨΓ͸ผ్վળ͍͖͍ͯͨ͠ϙΠϯτͰ͢)


    ● ϝϞϦ૿΍͢ઃఆ͢Δ or Ϧιʔε૿΍͢ʁ


    ○ ͲͪΒ΋࠶ىಈ͕ඞཁʹͳΔͨΊɺ্هͱಉ͡ཧ༝Ͱ࠷ऴखஈʹ͔ͨͬͨ͠


    ■ ࠶ىಈͳ͠ͷखஈ͕΋͋͠ΔͷͳΒͦͬͪΛબͼ͍ͨ


    ○ 11݄ʹRDSΫϥελҠߦΛ༧ఆ͍ͯͨ͠ͷͰɺ໿2ϲ݄଱͑Ε͹ઃఆมߋͰ͖Δ
    10

    View full-size slide

  11. © primeNumber.Inc
    ͦͯ͠ AWS Support ΑΓճ౴͕
    ● > ͋ΔλΠϛϯάΑΓʮRollbackSegmentHistoryListLengthʯʹେ͖ͳ஋͕ݟΒΕΔΑ͏ʹͳ͓ͬͯΓ·ͨ͠ҝɺͪ͜
    Β͸ফඅϝϞϦʹӨڹΛ༩͍͑ͯͨͷͰ͸ͳ͍͔ͱ૝ఆக͓ͯ͠Γ·͢ɻ


    ● > MySQL DBΤϯδϯʹ͓͍ͯɺwriter/readerͷ૒ํͰɺඇৗʹ࣮ߦ࣌ؒͷ௕͍ΫΤϦ͕ଘࡏ͍ͯ͠Δ৔߹ɺಡΈऔΓҰ؏
    ੑΛ࣮ݱ͢ΔͨΊʹϩʔϧόοΫηάϝϯτʹաڈͷ৘ใΛอ࣋͠ͳ͚Ε͹͍͚ͳ͍ؔ܎্ɺϩϯάΫΤϦ࣮ߦதͷߋ৽৘ใ
    Λશͯอ࣋͠ͳ͚Ε͹ͳΒͳ͍ͱ͍͏ಈ࡞ʹىҼͨ͠΋ͷͱͳΓ·͢ɻ͜ͷͱ͖ɺߋ৽ʹΑͬͯอ࣋͠ͳ͚Ε͹ͳΒͳ͍աڈ
    ͷ৘ใͷྔ͕ɺͪ͜ΒͷϝτϦΫεͱͳΓ·͢ɻ͜ͷͨΊɺߋ৽ྔ͕ଟ͍DBʹ͓͍ͯ௕࣌ؒͷಡΈऔΓΫΤϦ͕࣮ࢪ͞Εͨ৔
    ߹ɺDBͷෛՙ্͕ঢ͢ΔՄೳੑ͕͍͟͝·͢ɻ
    11

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  15. RollbackSegmentHistoryListLength ͷ૿େʹରͯ͠


    ͲͷΑ͏ͳରԠҊ͕͋ΓɺԿΛߦ͔ͬͨ

    View full-size slide

  16. © primeNumber.Inc
    ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ
    ● ୹ظରԠ


    ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ


    ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ


    ● த௕ظରԠ


    ○ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ


    ○ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ


    ○ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ)


    ○ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ)
    16

    View full-size slide

  17. © primeNumber.Inc
    ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ
    ● ୹ظରԠ


    ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ


    ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ


    ● த௕ظରԠ


    ○ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ


    ○ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ


    ○ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ)


    ○ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ)
    17

    View full-size slide

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

    View full-size slide

  19. © primeNumber.Inc
    Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ
    ● Aurora ͷΫϩʔϯػೳ + binlog ϨϓϦέʔγϣϯΛ࢖ͬͯ෼ੳ༻ Aurora Ϋϥ
    ελΛ࡞੒


    ○ ୹ظରԠͰࢭΊͨ Redash, trocco® ʹΑΔ trocco® ͷσʔλసૹΛ࠶։ͤ͞Δ໨త


    ○ ίετ࡟ݮͷͨΊຊମΫϥελΑΓ1ͭԼͷΠϯελϯεαΠζͰՔಇ


    ■ CPUUtilization, BufferCacheHitRatio, SwapUsageͳͲΛݟܾͯఆ


    ■ ϦιʔεʹՃ͑ϨϓϦέʔγϣϯϥά (AuroraBinlogReplicaLag) Λ؂ࢹ


    ● සൟʹϥάΔΑ͏ͳΒ্͛Δ͜ͱ΋ݕ౼


    ● ෼ੳ༻Ͱ͋Ε͹ؾܰʹམͱͯ͠ΠϯελϯεαΠζมߋɺͱ͍͏͜ͱ΋Մೳͳ͸ͣ
    19

    View full-size slide

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

    View full-size slide

  21. © primeNumber.Inc
    ͲͷΑ͏ͳରԠҊ͕͔͋ͬͨʁ (࠶ܝ)
    ● ୹ظରԠ


    ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛࢭΊΔ


    ○ ௕࣮࣌ؒߦ͞Ε͍ͯΔΫΤϦΛߴ଎Խ͢Δ


    ● த௕ظରԠ


    ○ MySQL ͷ෼཭ϨϕϧΛมߋ͢Δ


    ○ Reader ʹ޲͚࣮ͯߦ͍ͯ͠ΔΫΤϦΛผΫϥελʹ޲͚Δ


    ○ Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ)


    ○ Aurora engine ͷ version Λ্͛Δ (ཁ࠶ىಈ)
    21

    View full-size slide

  22. © primeNumber.Inc
    Writer ͕ར༻Ͱ͖ΔϝϞϦΛ૿΍͢ (ཁ࠶ىಈ)
    ● innodb_buffer_pool_size Λ DBInstanceClassMemory*3/4 (default) →
    DBInstanceClassMemory*5/8


    ○ BufferCacheHitRatio, SwapUsage Λݟͯ൑அ


    ■ BufferCacheHitRatio ͸΄΅100%ͩͬͨͨΊ (SwapUsage ͸ͣͬͱθϩͩͬͨ)
    22

    View full-size slide

  23. © primeNumber.Inc
    Aurora engine ͷ minor version Λ্͛Δ (ཁ࠶ىಈ)
    ● ϝϞϦؔ࿈ͷ bugfix ͱ͔ imporovement ͕ೖ͍ͬͯΔՄೳੑ͕͋Δ


    ○ Release notes for Amazon Aurora MySQL-Compatible Edition - Amazon Aurora


    ● Zero Downtime Patching ͸·ͩར༻Ͱ͖ͳ͍ engine όʔδϣϯͩͬͨ


    ○ ͦ΋ͦ΋ຊ౰ʹαʔϏεӨڹग़ͣ͞ʹ࢖͑Δͷ͔ݕূ͠ͳ͍ͱ࢖͏ͷ͸͜Θ͍
    23

    View full-size slide

  24. © primeNumber.Inc
    ࠓޙͷల๬ 24
    ● binlog ͷར༻Λ΍ΊΔ


    ○ binlog Λ༗ޮʹ͍ͯ͠ΔͱΫϥογϡޙͷ෮چ͕࣌ؒ௕͘ͳΔɺͱͷ͜ͱ


    ■ ࢀߟ: Amazon Aurora ετϨʔδͱ৴པੑ - Amazon Aurora


    ○ όοΫτϥοΫػೳ͕࢖͑ͳ͍


    ● Aurora ΫϩʔϯػೳΛ࢖ͬͨ৔߹ͷσʔλͷ࣋ͪํ͸ίετ໘Ͱ΍΍ݒ೦


    ○ ίϐʔΦϯϥΠτ ͰσʔλΛอ࣋ɾߋ৽͢Δ


    ■ ޮ཰ѱ͍͔΋ʁΫϩʔϯ࡞੒࣌ͷσʔλΛແବʹอ࣋͠ଓ͚ͯ͠·͍ͬͯΔ͔΋ʁͳͲ


    ● ސ٬૿ʹΑΓݟࠐ·ΕΔDBෛՙΛԼ͛Δ

    View full-size slide

  25. © primeNumber.Inc
    ·ͱΊɾॴײ
    ● Aurora ͸ Reader ʹ౤͛ͨΫΤϦ͕ΫϥελશମʹӨڹΛ༩͑ಘΔ


    ○ RollbackSegmentHistoryListLength ͷ૿େʹΑΔϦιʔεফඅɾύϑΥʔϚϯε௿ԼͳͲ


    ● ௕࣌ؒͷτϥϯβΫγϣϯ͕ආ͚ΒΕͳ͍৔߹͸ҎԼͷରԠΛೖΕͯΈΔ


    ○ Ϋϥελ͝ͱ෼཭ͯ͠ Reader ʹ౤͛ͯͨΫΤϦΛͦͬͪʹ౤͛Δ


    ○ MySQL ෼཭ϨϕϧΛREAD COMMITTEDʹ͢Δ


    ● AWS Support ͸ͪΌΜͱ׆༻͢΂͖


    ○ ʮ஌Βͳ͍͜ͱΛ஌Βͳ͍ʯྖҬʹ͍ͭͯฦ౴Λ͘ΕΔ
    25

    View full-size slide

  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

    View full-size slide