DBͷো֐Λ ͳΔૣͰݕ஌͢Δٕज़ 2018-12-01 தࠃ஍ํDBษڧձ in ௗऔ(૔٢) JPUG தࠃࢧ෦௕ ߴڮ Ұٍ

ઌ೔ͷ PG Conference Japan 2018 ɻɻɻ ʮ͜Μͳख๏͕͋ͬͨͷ͔ʂʯ ʮ݄༵͔Βਅࣅ͢Δʂʯ ʮ͜͜ࠔͬͯͨΜͩʂʯ

ࣗ෼ͷ૝ఆҎ্ʹ൓ڹͷ͋ͬͨ RDSͷো֐ݕ஌ͷख๏Λ͝঺հ

͓͠ͳ͕͖ 1. DBো֐ݕ஌Λͳͥ΍Δͷ͔ 2. DBো֐ΛͲ͏΍ͬͯ؂ࢹ͢Δͷ͔ 3. ·ͱΊ

1. DBো֐ݕ஌Λͳͥ΍Δͷ͔

1. DBো֐ݕ஌ΛԿނ΍Δͷ͔. • ݱࡏΦϛΧϨ͸DBϦϑΝΫλϦϯάΛ ΍͍ͬͯΔɻ • DMSͰσʔλҠߦ => τϦΨʔͰσʔ λΛม׵ͯ͠৽εΩʔϚ΁ొ࿥ ͱ͍͏ ྲྀΕɻ ※ ৄ͘͠͸ PGconf2018ͷεϥΠυΛɻ h-ps:/ /speakerdeck.com/takahashiikki/ postgresqljapan2018

1. DBো֐ݕ஌ΛԿނ΍Δͷ͔. • ΞʔΩςΫνϟͷੑ্࣭ɺ τϦΨʔ͕ίέΔͱDBҠߦ͕ࢭ·Δɻ DBҠߦ͕ࢭ·ΔͱAPI͕ίέΔɻ API͕ίέΔͱɾɾαʔϏε͕ࢭ·Δɻ • τϦΨʔͷΤϥʔΛݕ஌ͯ͠ো֐ʹͳΔલʹରԠ͢Δࣄ͕ඞਢ • ݱࡏɺ਺ϲ݄ͷӡ༻ܦݧ্ɺ૝ఆ֎ͷσʔλ͕ೖΔ͜ͱʹΑΔ τϦΨʔ͸ͦΕͳΓʹ͋Δɻ

αʔϏεΛࢭΊͳ͍ҝʹ ΤϥʔΛݕ஌͢Δɻ

2. DBো֐ΛͲ͏΍ͬͯݕ஌͢Δͷ͔ɻ

AWS (RDS)ͷϩάͱ͍͑͹ɻɻɻ • AWS CloudWatch Logsʂ RDSͰ͸ɺDBϩάΛCloudWatch Logsʹు͖ग़͢͜ͱ͕ग़དྷΔɻ => ͜Ε͕͋Ε͹ SNSͱ͔৭ΜͳࣄͰ؂ࢹग़དྷͦ͏ʂ

AWS (RDS)ͷϩάͱ͍͑͹ɻɻɻ • AWS CloudWatch Logsʂ RDSͰ͸ɺDBϩάΛCloudWatch Logsʹు͖ग़͢͜ͱ͕ग़དྷΔɻ => ͜Ε͕͋Ε͹ SNSͱ͔৭ΜͳࣄͰ؂ࢹग़དྷͦ͏ʂ ※ RDS for MySQL ͰͷΈରԠ.

h"ps:/ /dev.classmethod.jp/cloud/aws/amazon-rds-logs-download-9ps/

ཁ໿͢Δͱ... • RDSͷϩά͸ AWS SDKͷؔ਺Λ࢖ͬͯશߦऔಘग़དྷΔ. • ؔ਺ʹ͸औಘ։࢝ҐஔΛࢦఆग़དྷΔ. ͭ·ΓɺطʹಡΜͩҐஔΛඈ͹ͨ͠ঢ়ଶͰॲཧग़དྷΔɻ

ඞཁͳཁ݅ • ఆظతʹSDKΛୟ͍ͯϩάΛϙʔϦϯά͢Δɻ • લճͷ෼ੳҐஔΛ͓͍֮͑ͯͯલճͷ෼ੳ෼͸ඈ͹͢ɻ • ΤϥʔΛݟ͚ͭͨ࣌ʹ௨஌͢Δɻ

ఆظతʹSDKΛୟ͘ ฐࣾʹͱͯ΋ৄ͍͠ํ͕ډͨͷͰMackerelΛར༻

ఆظతʹSDKΛୟ͘ -------------------------------------------------- [plugin.checks.೚ҙͷ໊લ] command = "php /path/to/check_rds_log.php" check_interval = 5 -------------------------------------------------- • MackerelͷઃఆʹΑͬͯɺ5෼ʹҰճࢦఆͨ͠ίϚϯυΛ࣮ߦ͠ ͯ͘ΕΔɻ

طʹಡΜͩ෼͸ඈ͹͢ɻ • Ґஔ͸ Marker ͱݺ͹Εɺ ਺஋Ͱ؅ཧ͞Ε͍ͯ·͢ɻ • JPUGͷཧࣄͳͷͰɺ.jsonϑΝΠϧͰ؅ཧ͢Δࣄʹ͠·ͨ͠ɻ

ΤϥʔΛݟ͚ͭͨ࣌ʹ௨஌Λඈ͹͢ɻ -------------------------------------------------- [plugin.checks.೚ҙͷ໊લ] command = "php /path/to/check_rds_log.php" check_interval = 5 -------------------------------------------------- • MackerelͷcheckϓϥάΠϯʹ࣮ͯݱ͍ͯ͠·͢ɻ • ίϚϯυ಺ͰҎԼͰऴྃͤ͞ΔࣄͰ checkϓϥάΠϯʹҟৗݕ஌ͤ͞Δࣄ͕ग़དྷ·͢ɻ exit(1) ... WARNING exit(2) ... CRITICAL • MackerelଆͷઃఆͰ WARNINGͷ࣌ͷৼΔ෣͍ͱͯ͠ Slackʹ௨஌͢Δ Λ࢓ࠐΜͰରԠɻ

Τϥʔͷ൑ఆϩδοΫ • :ERROR: ͱ͍͏จࣈྻ͕͋Δ͔Ͳ͏͔ɻ (ײ֒ਂ͍) • :ERROR: ͕͋Ε͹1~2ߦԼΛߋʹௐࠪͯ͠ :DETAIL: ͱ͔ :CONTEXT: ͕͋Ε͹Ұॹʹरͬͯ͘Δɻ

ΞʔΩςΫνϟ ·ͱΊ • PHPͰॻ͔ΕͨϓϩάϥϜΛMackerelͰ5෼͓͖ʹ࣮ߦ͢Δɻ • ϓϩάϥϜ಺Ͱ͸ɺΤϥʔߦΛ൑ఆͯ͠ ௐࠪʹ໾ཱͭ৘ใΛ௨஌͢Δɻ • طʹௐࠪͨ͠෦෼͸ .jsonϑΝΠϧʹు͖ग़ͯ͠ skip͢Δɻ

3. ·ͱΊ

͜ͷΞʔΩςΫνϟͰ5ϲ݄૸ͬͨ. • Τϥʔ͕͙͢ʹ௨஌͞ΕΔͷͰຊ౰ɺศརɻ • ΞʔΩςΫνϟ͸਺ճΞοϓσʔτͯͯ͠ɺ :DETAIL: ͳͲͷΤϥʔৄࡉΛऔΔΑ͏ʹͳͬͨͷ΋࠷ۙɻ • SQLྲྀͦ͏ͱͨ࣌͠ͷ syntax error ΋௨஌͞ΕΔͷͰΧδϡΞ ϧʹSQLΛ౤͛ΔͱౖΒΕΔɻ ʮ͢Έ·ͤΜɺ๻͕SQLؒҧ͑·ͨ͠ʯͬͯSlackʹ౤ߘ͢Δɻ

ศརͳ෦෼. • :ERROR: Ͱ௨஌͞ΕΔࣄ͕෼͔͍ͬͯΔͷͰɺ τϦΨʔ಺Ͱϩάʹ :ERROR: ͬͯॻ͍ͯσόοάϝοηʔδΛ ॻ͘ࣄͰ௨஌͍ͨ͠಺༰ΛϋοΫग़དྷΔɻ • ࢖༻ྫɿ :ERROR: delete_flag return 1 to 0 • MackerelͷػೳͰ CRITICAL ͔Β NORMAL ʹ໭ͬͨλΠϛϯά Ͱ΋ ௨஌͞ΕΔͷͰɺରԠ׬ྃΛϩάਫ਼ࠪʹΑͬͯ஌Δࣄ͕ग़དྷΔɻ

՝୊ͷ෦෼. • SQLྲྀͦ͏ͱͨ࣌͠ͷ syntax error ΋௨஌͞ΕΔͷͰ ΧδϡΞϧʹSQLΛ౤͛ΔͱౖΒΕΔɻ ※ʮ͢Έ·ͤΜɺ๻͕SQLؒҧ͑·ͨ͠ʯͬͯSlackʹ౤ߘ • (ઃఆͷ໰୊) ϝοηʔδͰ೔ຊޠΛॻ͘ͱจࣈԽ͚͢ΔͷͰɺ ࢖͑ͳ͍ɻ • ͦͷҝɺσόοάϝοηʔδΛӳޠͰॻ͔͟ΔΛಘͳͯ͘ ଐਓԽ͕ͪ͠ɻ

૯ׅ • ૯ׅͱͯ͠͸ΊͪΌͪ͘Όָʹͳͬͨɻ • ઈ͑ͣಈ͘ͷͰ ʮΞϥʔτ͕དྷͯͳ͍ʯ=> ʮτϦΨʔΤϥʔ ͕ແ͍ʯ͕୲อग़དྷΔͷͰ҆৺ײ͕͋Δɻ • ࢥͬͯΔΑΓ΋૝ఆ֎ͷσʔλ͕ೖΔɻ

͝੩ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ