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

はじめてのPostgreSQLモニタリング入門 / PostgreSQL 11 Monit...

ester41
November 15, 2019

はじめてのPostgreSQLモニタリング入門 / PostgreSQL 11 Monitoring

ester41

November 15, 2019
Tweet

More Decks by ester41

Other Decks in Technology

Transcript

  1. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ
  2. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ શͯͷϓϩηεΛ औΓ·ͱΊΔϓϩηεɻ ͜Ε͕ఀࢭ͢Δͱ PostgreSQLશମ͕ఀࢭ͠·͢ɻ
  3. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ڞ༗ϝϞϦʔ্ͷWALόοϑΝʔΛ WALϑΝΠϧʹॻ͖ग़͢ϓϩηεɻ ※WAL: τϥϯβΫγϣϯϩά(Write Ahead Logging)
  4. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ڞ༗ϝϞϦʔ্ͷڞ༗όοϑΝʔ಺ͷ ߋ৽͞ΕͨϖʔδΛରԠ͢Δ σʔλϑΝΠϧͷϒϩοΫʹ ॻ͖ग़͢ϓϩηεɻ
  5. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ શͯͷμʔςΟϖʔδ(ϑΝΠϧγεςϜʹ ॻ͖໭͢ඞཁͷ͋ΔσʔλΛ࣋ͬͨϖʔδ) ΛσʔλϑΝΠϧʹ൓ө͢Δϓϩηεɻ νΣοΫϙΠϯτͱݺ͹ΕΔɺ Ϋϥογϡ࣌ʹϦΧόϦॲཧΛߦ͏ Օॴ୯ҐͰࣗಈతʹ࣮ߦ͠·͢ɻ
  6. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ ৘ใΛҰఆִؒͰऩू͢Δϓϩηεɻ
  7. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛ ੍ޚɾ࣮ߦ͢Δϓϩηεɻ όΩϡʔϜʹ͍ͭͯ͸ ޙ΄Ͳղઆ͠·͢ɻ
  8. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ)

    postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ΫϥΠΞϯτ͔Β઀ଓཁٻΛ ड͚ͨ࣌ʹੜ੒͞ΕΔϓϩηεɻ SQLͷ࣮ߦ͸͜ͷϓϩηε಺Ͱ ߦΘΕ·͢ɻ
  9. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε
  10. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε Ұ࣌ςʔϒϧʹΞΫηε͢Δ৔߹ʹ ࢖༻͞ΕΔྖҬɻ ઃఆ͞ΕͨྖҬΛ্ݶͱ͠ɺ ඞཁʹԠͯ͡ϝϞϦΛ֬อ͠·͢ɻ
  11. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ฒͼସ͑ૢ࡞(ORDER BY, DISTINCT, Ϛʔδ݁߹)ͱ ϋογϡςʔϒϧ(ΠϯσοΫεͳͲ)ૢ࡞ʹ ࢖༻͞ΕΔྖҬɻ ෳࡶͳΫΤϦʹͳΔͱɺ࡞ۀϝϞϦʔ͕ ෳ਺ੜ੒͞Ε·͢ɻ
  12. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε อकૢ࡞(VACUUM, CREATE INDEXͳͲ)Ͱ ࢖༻͞ΕΔྖҬɻ
  13. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍ WALΛΩϟογϡ͢ΔྖҬɻ
  14. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧ΍ΠϯσοΫεσʔλΛ Ωϟογϡ͢ΔྖҬɻ
  15. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧ্ͷར༻ՄೳͳྖҬΛ ؅ཧ͢ΔྖҬɻ όΩϡʔϜॲཧ΍ɺ σʔλͷ௥Ճɾߋ৽࣌ʹ ࢖༻͞Ε·͢ɻ
  16. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)

    ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧσʔλͷՄࢹঢ়ଶΛ ؅ཧ͢ΔྖҬɻ όΩϡʔϜॲཧ΍ɺ INDEX ONLY SCAN࣌ʹ ࢖༻͞Ε·͢ɻ
  17. σΟεΫྖҬ ΞʔΧΠϒWALྖҬ WALྖҬ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλߏ੒) σʔλϕʔεྖҬ ςʔϒϧϑΝΠϧ WALϑΝΠϧ ΞʔΧΠϒϑΝΠϧ TOASTϑΝΠϧ

    ΠϯσοΫεϑΝΠϧ ۭ͖ྖҬϚοϓϑΝΠϧ ՄࢹੑϚοϓϑΝΠϧ ௨ৗ16MBͷWALϑΝΠϧΛ ॥؀ར༻͠·͢ɻ ΞʔΧΠϒϞʔυΛ༗ޮʹ ͢Δ͜ͱͰɺ॥؀ར༻͞Ε ΔWALϑΝΠϧΛอଘ͠· ͢ɻϙΠϯτΠϯλΠϜϦΧ όϦʔͰ࢖༻͠·͢ɻ ςʔϒϧϑΝΠϧɾTOAST ϑΝΠϧɾΠϯσοΫεϑ ΝΠϧʹ͍ͭͯ͸࣍ϖʔδ͔ Βղઆ͠·͢ɻ
  18. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλߏ੒ ʔ ςʔϒϧϑΝΠϧ) ςʔϒϧϑΝΠϧ ϖʔδ(8KB) ΞΠςϜIDσʔλ1 (4όΠτ) ΞΠςϜIDσʔλ2 (4όΠτ)

    … ۭ͖ྖҬ ΞΠςϜIDσʔλn (4όΠτ) ΞΠςϜσʔλn(Մม) … ϖʔδϔομʔ (24όΠτ) ΞΠςϜσʔλ1(Մม) ΞΠςϜσʔλ2(Մม) ϖʔδ (8KB) … ςʔϒϧϑΝΠϧ͸ɺ௨ৗ8KBͷϖʔδ͔Βߏ੒͞Ε·͢ɻ ϖʔδ಺ʹσʔλ͕ऩ·Βͳ͘ͳΔͱɺϖʔδ͕৽͘͠ੜ੒͞Ε·͢ɻ
  19. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλߏ੒ ʔ TOASTϑΝΠϧ) TOASTͱ͸ɺʮաେଐੑ֨ೲٕ๏(The Oversized-Attribute Storage Technique)ʯͷུশͰɺ ඇৗʹେ͖ͳྻͷσʔλ(ΞΠςϜσʔλ)Λ֨ೲ͢ΔͨΊͷٕ๏Ͱ͢ɻ ΞΠςϜσʔλʹ͸TOASTྖҬͷΞΫηεϙΠϯλ͕֨ೲ͞Ε·͢ɻ

    ςʔϒϧϑΝΠϧ ϖʔδ ΞΠςϜIDσʔλ ϖʔδϔομʔ ۭ͖ྖҬ ΞΠςϜσʔλ TOASTϑΝΠϧ ڊେͳσʔλ1 ڊେͳσʔλ2 ڊେͳσʔλ3 … ΞΠςϜσʔλ͕ϖʔδ αΠζͷ1/4αΠζΛ௒ ͑Δ৔߹ɺTOAST͕࢖ ༻͞Ε·͢ɻ
  20. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ΫΤϦͷ࣮ߦॱং) σʔλϕʔεͷϞχλϦϯάΛߦ͏ͨΊʹ͸ΫΤϦͷ࣮ߦॱংʹ͍ͭͯཧղ͢Δඞཁ͕͋Γ·͢ɻ ॲཧ଎౓͕஗͍ݪҼͷ੾Γ෼͚౳ʹ࢖༻Ͱ͖·͢ɻ ౷ܭ৘ใ ϧʔϧఆٛ CREATE RULE۟ Ͱఆٛ SQLจ

    ໰͍߹ΘͤπϦʔ ໰͍߹ΘͤπϦʔ ࣮ߦܭը ౷ܭ৘ใίϨΫλ Ͱੜ੒ ύʔαʔ (ࣈ۟ղੳ / ߏจղੳ) ϦϥΠλʔ (VIEW΍ϧʔϧͰॻ͖׵͑) ϓϥϯφʔ / ΦϓςΟϚΠβ (࣮ߦܭըͷੜ੒ / ࠷దԽ) ΤάθΩϡʔλʔ (ΫΤϦͷ࣮ߦ)
  21. PostgreSQLͷ౷ܭ৘ใ (σʔλϕʔεશମʹ͔͔ΘΔ৘ใ) ϓϩηεىಈঢ়ଶͷϞχλϦϯάํ๏ ϓϩηεىಈঢ়ଶͷϞχλϦϯάํ๏͸ɺೋ௨Γͷํ๏͕ଘࡏ͠·͢ɻ 1. psίϚϯυͰ֬ೝΛߦ͏ํ๏
 ҎԼͷίϚϯυͷ݁Ռ͕1Ҏ্ͷ৔߹ɺਖ਼ৗͱΈͳ͠·͢ɻ
 
 2. pg_isreadyίϚϯυͰ֬ೝΛߦ͏ํ๏


    ҎԼͷίϚϯυͷऴྃεςʔλε͕0ͷ৔߹ɺਖ਼ৗͱΈͳ͠·͢ɻ $ ps aux | grep postmaster | wc -l $ /usr/pgsql-11/bin/pg_isready -h localhost -p 15432 -d noa <ϗετ໊>:<ϙʔτ൪߸> - ઀ଓΛड͚෇͚͍ͯ·͢ $ echo $? 0 σΟεΫ࢖༻཰ͷϞχλϦϯάํ๏ ඪ४ͷػೳͰϞχλϦϯά͢Δํ๏͸͋Γ·ͤΜɻ dfίϚϯυ΍duίϚϯυɺͦͷ΄͔ͷπʔϧͰ֬ೝΛߦ͏ඞཁ͕͋Γ·͢ɻ
  22. PostgreSQLͷ౷ܭ৘ใ (ੑೳྼԽΛࣄલʹݕ஌͢ΔͨΊͷ౷ܭ৘ใ) PostgreSQLͷ౷ܭ৘ใίϨΫλΛ༻͍ͯϞχλϦϯάΛߦ͍·͢ɻ ౷ܭ৘ใίϨΫλ͸༷ʑͳ৘ใʹΞΫηεͰ͖ΔVIEWΛఏڙ͍ͯ͠·͢ɻ ౷ܭ৘ใίϨΫλʹ͍ͭͯ͸ɺެࣜυΩϡϝϯτΛࢀর͍ͯͩ͘͠͞ɻ https://www.postgresql.jp/document/current/html/monitoring-stats.html VIEW໊ ղઆ pg_stat_activity ঢ়ଶ΍ݱࡏͷ໰͍߹Θͤ౳ͷϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใΛදࣔ͠·͢ɻ

    ෛՙͷߴ͍ɾΫΤϦͷ࣮ߦ͕࣌ؒ௕͍ϓϩηε͕Θ͔Γ·͢ɻ pg_stat_bgwriter όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใΛදࣔ͠·͢ɻ ͜Ε·Ͱʹ࣮ߦ͞ΕͨνΣοΫϙΠϯτͷճ਺ɾॲཧ࣌ؒ౳͕Θ͔Γ·͢ɻ pg_stat_database σʔλϕʔεશମͷ৘ใΛදࣔ͠·͢ɻ σʔλϕʔε͝ͱͷ૯τϥϯβΫγϣϯ਺ɾϒϩοΫ਺΍ࢀরɾߋ৽ɾ࡟আճ਺౳͕Θ͔Γ·͢ɻ pg_stat_user_tables Ϣʔβʔςʔϒϧ΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใΛࣔ͠·͢ɻ pg_stat_databaseΛςʔϒϧ୯ҐͰݟΔ͜ͱ͕Ͱ͖ΔΑ͏ʹͳͬͨ΋ͷͰ͢ɻ pg_statio_user_tables Ϣʔβʔςʔϒϧʹର͢ΔI/Oʹؔ͢Δ౷ܭ৘ใΛࣔ͠·͢ɻ pg_stat_user_indexes ϢʔβʔςʔϒϧͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใΛࣔ͠·͢ɻ pg_statio_user_indexes ϢʔβʔςʔϒϧͷΠϯσοΫεʹର͢ΔI/Oʹؔ͢Δ౷ܭ৘ใΛࣔ͠·͢ɻ
  23. PostgreSQLͷ౷ܭ৘ใ (ੑೳྼԽΛࣄલʹݕ஌͢ΔͨΊͷ౷ܭ৘ใ) લड़ͷVIEWΛ૊Έ߹ΘͤΔ͜ͱͰɺϞχλϦϯάʹඞཁͳ৘ใΛऔಘ͢Δ͜ͱ͕Ͱ͖·͢ɻ ҎԼͷ৘ใΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ ʲࢀߟαΠτʳLet’s Postgres Քಈ౷ܭ৘ใΛ׆༻͠Α͏ https://lets.postgresql.jp/documents/technical/statistics DB୯Ґͷίϛοτ਺΍ϩʔϧόοΫ਺ DB΍ςʔϒϧɺΠϯσοΫε୯ҐͷI/Oൃੜঢ়گ

    DB΍ςʔϒϧʹରͯ͠ૠೖ/ߋ৽/࡟আ͞Εͨߦ਺ ςʔϒϧ୯ҐͰ࣮ࢪ͞ΕͨදεΩϟϯճ਺ DB΍ςʔϒϧɺΠϯσοΫε୯ҐͰεΩϟϯ͞Εͨߦ਺ ςʔϒϧ୯Ґͷߦ਺ɺΨϕʔδྔ ݱࡏ࣮ࢪதͷSQL΍ϝϯςφϯεॲཧ ౷ܭ৘ใίϨΫλ͸ɺ”ݱࡏͷঢ়ଶ”͔͠ݟΔ͜ͱ͸Ͱ͖·ͤΜɻ ϞχλϦϯάΛਖ਼͘͠ߦ͏ͨΊʹ͸ɺաڈͷঢ়ଶΛऔΓଓ͚ɺൺֱͰ͖Δ؀ڥ͕ඞཁͱͳΓ·͢ɻ
  24. PostgreSQLͷ౷ܭ৘ใ (ϓϩηεͷՔಇঢ়ଶ֬ೝ) ϓϩηεͷՔಇঢ়ଶ͸ɺpg_stat_activityϏϡʔͰ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ ओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ ※pg_stat_activityϏϡʔ΁໰͍߹Θͤͨ͠ϢʔβʔݖݶʹΑΓɺσʔλ͕ܽམ͢Δ৔߹͕͋Γ·͢ɻ ΧϥϜ໊ ղઆ pid όοΫΤϯυϓϩηεͷϓϩηεIDͰ͢ɻ datname

    όοΫΤϯυϓϩηε͕઀ଓͨ͠σʔλϕʔε໊Ͱ͢ɻ usename όοΫΤϯυϓϩηε͕࢖༻͍ͯ͠ΔϢʔβʔ໊Ͱ͢ɻ client_addr όοΫΤϯυϓϩηεʹ઀ଓ͍ͯ͠ΔΫϥΠΞϯτͷIPΞυϨεͰ͢ɻ NULLͷ৔߹ɺUNIXιέοτܦ༝ͷ઀ଓ͔಺෦ॲཧͷͲͪΒ͔ͱͳΓ·͢ɻ state όοΫΤϯυϓϩηεͷݱࡏͷঢ়ଶͰ͢ɻ activeͷ৔߹͸࣮ߦதͱͳΓ·͢ɻ query_start ໰͍߹Θͤͷ࣮ߦΛ։࢝ͨ࣌ؒ͠Ͱ͢ɻ query ௚ۙͷΫΤϦͷҰ෦͕֨ೲ͞Ε·͢ɻ ໰͍߹ΘͤΛఀࢭ͍ͨ͠৔߹͸ɺpg_stat_activityϏϡʔͱ֬ೝ͠ɺ֘౰໰͍߹ΘͤΛ࣮ߦ͍ͯ͠ΔϓϩηεIDΛ֬ೝ͠·͢ɻ σʔλϕʔεʹ઀ଓ͠ɺԼهͷ1ͭ໨ͷΫΤϦΛ࣮ߦ͠·͢ɻ ͦΕͰ΋ࢭ·Βͳ͍৔߹͸ɺԼهͷ2ͭ໨ͷΫΤϦΛ࣮ߦ͠·͢ɻ 2ͭ໨ͷΫΤϦ͸ϓϩηεͷڧ੍ఀࢭͱͳΔͨΊɺσʔλͷอূ͸Ͱ͖·ͤΜʂ postgres => SELECT pg_cancel_backend(ϓϩηεID); postgres => SELECT pg_terminate_backend(ϓϩηεID);
  25. PostgreSQLͷ౷ܭ৘ใ (σʔλϕʔεΫϥελঢ়ଶ) σʔλϕʔεΫϥελঢ়ଶ͸ɺpg_stat_bgwriterϏϡʔͰ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ ओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ checkpoints_timed εέδϡʔϧ͞ΕͨνΣοΫϙΠϯτ਺Ͱ͢ɻ checkpoints_req ཁٻ͞ΕͨνΣοΫϙΠϯτ਺Ͱ͢ɻ

    buffers_backend όοΫΤϯυʹΑΓ௚઀ॻ͖ग़͞ΕͨόοϑΝ਺Ͱ͢ɻ buffers_alloc ׂ౰ΒΕͨόοϑΝ਺Ͱ͢ɻ pg_stat_bgwriterϏϡʔΛ֬ೝ͢Δ͜ͱͰɺσʔλϕʔεͷϘτϧωοΫΛݕ஌͢Δ͜ͱ͕Ͱ͖·͢ɻ • ʲcheckpoint_reqͷ஋͕େ͖͍৔߹ʳ
 WALαΠζ͕খ͍͞ҝɺνΣοΫϙΠϯτ͕සൃ͍ͯ͠·͢ɻWALαΠζ(max_wal_size)ͷมߋ͕ඞཁͰ͢ɻ • ʲbuffers_backend͕buffers_allocΑΓେ͖͍ʳ
 shared_buffers͕ෆ଍͍ͯ͠·͢ɻ
  26. PostgreSQLͷ౷ܭ৘ใ (σʔλϕʔεঢ়ଶ) σʔλϕʔεঢ়ଶ͸ɺpg_stat_databaseϏϡʔͰ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ ओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ datname όοΫΤϯυϓϩηε͕઀ଓͨ͠σʔλϕʔε໊Ͱ͢ɻ xact_commit ίϛοτ͞ΕͨྦྷܭͷτϥϯβΫγϣϯ਺Ͱ͢ɻ

    xact_rollback ϩʔϧόοΫ͞ΕͨྦྷܭͷτϥϯβΫγϣϯ਺Ͱ͢ɻ temp_files ໰͍߹ΘͤʹΑͬͯॻ͖ग़͞ΕͨྦྷܭͷҰ࣌ϑΝΠϧ਺Ͱ͢ɻ temp_bytes ໰͍߹ΘͤʹΑͬͯॻ͖ग़͞ΕͨྦྷܭͷҰ࣌ϑΝΠϧαΠζͰ͢ɻ deadlocks ݕ஌͞ΕͨྦྷܭͷσουϩοΫ਺Ͱ͢ɻ pg_stat_databaseϏϡʔ͸ɺϦηοτ͕͔͔Δ·Ͱྦྷܭ஋Λฦ͠·͢ɻ ্هΧϥϜΛ֬ೝ͢Δ͜ͱͰɺҎԼͷ৘ใ͕Θ͔Γ·͢ɻ • σʔλϕʔεͷ࢖༻ঢ়گ • ੑೳτϥϒϧͷ༗ແ
 (Ұ࣌ϑΝΠϧͷେྔੜ੒ɾେن໛ͳҰ࣌ϑΝΠϧͷੜ੒) • σʔλϕʔεΛ࢖༻͍ͯ͠ΔγεςϜͷෆ۩߹ ͪͳΈʹɺҎԼͷΫΤϦͰσʔλϕʔεͷαΠζΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ =# SELECT pg_size_pretty(pg_database_size(‘σʔλϕʔε໊'));
  27. PostgreSQLͷ౷ܭ৘ใ (σʔλϕʔεঢ়ଶ) Ωϟογϡώοτ཰ΛٻΊΔ৔߹ɺҎԼͷΫΤϦͰऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ =# SELECT datname , round(heap_blks_hit * 100

    / (heap_blks_hit + heap_blks_read), 2) as cache_hit_ratio FROM pg_stat_database WHERE blks_read > 0; datname | cache_hit_ratio ——————————+————————————————— postgres | 99.00
  28. PostgreSQLͷ౷ܭ৘ใ (Ϣʔβʔςʔϒϧঢ়ଶ) Ϣʔβʔςʔϒϧঢ়ଶ͸ɺpg_stat_user_tablesϏϡʔٴͼpg_statio_user_tablesϏϡʔͰ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ ओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ schemaname ςʔϒϧ͕ଘࡏ͢ΔεΩʔϚ໊Ͱ͢ɻ relname ςʔϒϧ໊Ͱ͢ɻ

    n_tup_ins ૠೖ͞Εͨߦ਺Ͱ͢ɻ n_tup_upd ߋ৽͞ΕͨHOTߋ৽ΛؚΉߦ਺Ͱ͢ɻ n_tup_del ࡟আ͞Εͨߦ਺Ͱ͢ɻ n_live_tup ༗ޮߦͷਪఆ஋Ͱ͢ɻ n_dead_tup ෆཁߦͷਪఆ஋Ͱ͢ɻ last_autovacuum ࣗಈόΩϡʔϜσʔϞϯʹΑΓςʔϒϧ͕όΩϡʔϜ͞Εͨ࠷ऴ࣌ࠁͰ͢ɻ • pg_stat_user_tablesϏϡʔ • pg_stat_user_tablesϏϡʔ ΧϥϜ໊ ղઆ schemaname ςʔϒϧ͕ଘࡏ͢ΔεΩʔϚ໊Ͱ͢ɻ relname ςʔϒϧ໊Ͱ͢ɻ heap_blks_read ςʔϒϧ͔ΒಡΈऔΒΕͨσΟεΫϒϩοΫ਺Ͱ͢ɻ heap_blks_hit ςʔϒϧ಺ͷόοϑΝώοτ਺Ͱ͢ɻ
  29. PostgreSQLͷ౷ܭ৘ใ (ϢʔβʔΠϯσοΫεঢ়ଶ) ϢʔβʔΠϯσοΫεঢ়ଶ͸ɺpg_stat_user_indexesϏϡʔٴͼpg_statio_user_indexesϏϡʔͰ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ ओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ schemaname ΠϯσοΫε͕ଘࡏ͢ΔεΩʔϚ໊Ͱ͢ɻ relname ΠϯσοΫεର৅ͷςʔϒϧ໊Ͱ͢ɻ

    indexrelname ΠϯσοΫε໊Ͱ͢ɻ idx_scan ΠϯσοΫεʹରͯ͠։࢝͞ΕͨΠϯσοΫεεΩϟϯͷ࣮ߦճ਺Ͱ͢ɻ • pg_stat_user_indexesϏϡʔ • pg_statio_user_indexesϏϡʔ ΧϥϜ໊ ղઆ schemaname ςʔϒϧ͕ଘࡏ͢ΔεΩʔϚ໊Ͱ͢ɻ relname ςʔϒϧ໊Ͱ͢ɻ idx_blks_read ΠϯσοΫε͔ΒಡΈऔΒΕͨσΟεΫϒϩοΫ਺Ͱ͢ɻ idx_blks_hit ΠϯσοΫεʹ͓͚ΔόοϑΝώοτ਺Ͱ͢ɻ pg_stat_user_indexesϏϡʔΛ֬ೝ͢Δ͜ͱͰɺΠϯσοΫεͷ࢖༻ঢ়ଶΛ֬ೝ͢Δ͜ͱ͕Ͱ͖ɺ pg_statio_user_indexesϏϡʔΛ֬ೝ͢Δ͜ͱͰΠϯσοΫεͷΩϟογϡώοτ཰Λࢉग़͢Δ͜ͱ͕Ͱ͖·͢ɻ pg_stat_user_indexesϏϡʔͷidx_scan͕0ͷ৔߹ɺΠϯσοΫε͕શ͘࢖༻͞Ε͍ͯ·ͤΜɻ ࡟আର৅ͱͯ͠ݕ౼͢Δ͜ͱ͕Ͱ͖·͢ɻ
  30. PostgreSQLͷ౷ܭ৘ใ (ϢʔβʔΠϯσοΫεঢ়ଶ) ΠϯσοΫεͷΩϟογϡώοτ཰ΛٻΊΔ৔߹ɺҎԼͷΫΤϦͰऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ =# SELECT relname , indexrelname , round(idx_blks_hit

    * 100 / (idx_blks_hit + idx_blks_read), 2) as cache_hit_ratio FROM pg_statio_user_indexes WHERE idx_blks_read > 0; relname | indexrelname | cache_hit_ratio ————————————+————————————————-+————————————————— test_table | test_table_pkey | 98.00
  31. ϞχλϦϯάͰศརͳExtensionͨͪ PostgreSQL͸ɺ֦ுػೳ(Extension)Λಋೖ͢Δ͜ͱͰ جຊߏ੒Ͱ࣮ݱͰ͖ͳ͍͜ͱΛ࣮ݱͰ͖ΔΑ͏ʹͳΓ·͢ɻ ੈքதͰExtension͕։ൃ͞Εɺศརͳ΋ͷ͔Βχονͳ΋ͷ·Ͱ༷ʑଘࡏ͠·͢ɻ ͦͷதͰ΋ɺݸਓతʹϞχλϦϯάͰศརͩͱײ͍ͯ͡ΔExtensionΛ঺հ͠·͢ɻ (Ұ෦ϞχλϦϯάͱؔ܎ͳ͍΋ͷɾExtionsionͰͳ͍΋ͷ͕ଘࡏ͠·͢ɻ) Extension/πʔϧ໊ छྨ ղઆ pg_stat_statements

    Extension ΫΤϦͱ౷ܭܭըΛه࿥ɾ؅ཧ͠·͢ɻ pg_store_plans Extension ΫΤϦͱ࣮ߦܭըͱ౷ܭ৘ใΛه࿥ɾ؅ཧ͠·͢ɻ pg_dbms_stats Extension ౷ܭ৘ใΛه࿥ɾ؅ཧ͠·͢ɻ pg_hint_plan Extension ౷ܭ৘ใΛώϯτ۟Ͱ੍ޚͰ͖ΔΑ͏ʹ͠·͢ɻ pg_statsinfo Extension ౷ܭ৘ใͷه࿥ɾ؅ཧͱ௨஌Λ؅ཧ͠·͢ɻ ্هExtensionͱ૊Έ߹Θͤͯ࢖༻Ͱ͖·͢ɻ pg_repack Extensionɾπʔϧ ߋ৽ॲཧ౳ͰංେԽͨ͠ςʔϒϧ΍ΠϯσοΫεΛɺ࠶ੜ੒ʹΑΓ͖Ε͍ʹ͠·͢ɻ pg_flame πʔϧ ໰߹ͤܭը(EXPLAIN ANALYZE)ΛϏδϡΞϧԽ͢ΔπʔϧͰ͢ɻ pg_stats_reporter πʔϧ pg_statsinfoͷ৘ใΛάϥϑΟΧϧʹදࣔ͢ΔπʔϧͰ͢ɻ mackerel-plugin-postgres ϓϥάΠϯ αʔόʔ؂ࢹαʔϏεMackerelͰPostgreSQLΛ؂ࢹ͢ΔͨΊͷϓϥάΠϯͰ͢ɻ
  32. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stat_statements) pg_stat_statementsϏϡʔͰओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ query ࣮ߦ͞ΕͨΫΤϦͰ͢ɻ calls ࣮ߦճ਺Ͱ͢ɻ total_time

    ΫΤϦ࣮ߦʹඅ΍ͨ͠૯࣌ؒͰ͢ɻ mean_time ΫΤϦ࣮ߦʹඅ΍ͨ͠ฏۉ࣌ؒͰ͢ɻ rows ΫΤϦ࣮ߦʹΑͬͯऔಘ͞ΕͨɾӨڹΛड͚ͨߦͷ૯਺Ͱ͢ɻ shared_blks_hit ΫΤϦ࣮ߦʹΑͬͯώοτͨ͠ڞ༗ϒϩοΫΩϟογϡͷ૯਺Ͱ͢ɻ shared_blks_read ΫΤϦ࣮ߦʹΑͬͯಡΈࠐ·Εͨڞ༗ϒϩοΫͷ૯਺Ͱ͢ɻ
  33. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stat_statements) ҎԼͷΫΤϦͰ10݅ͷεϩʔΫΤϦΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ =# SELECT query , calls , total_time

    , mean_time , rows , round(100 * shared_blks_hit / (shared_blks_hit + shared_blks_read), 2) AS hit_ratio FROM pg_stat_statements WHERE shared_blks_hit > 0 ORDER BY total_time DESC LIMIT 10;
  34. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_dbms_stats) =# SELECT dbms_stats.backup_database_stats('ίϝϯτ'); • όοΫΞοϓ =# SELECT dbms_stats.restore_stats(όοΫΞοϓID஋);

    • ϦετΞ =# SELECT * FROM dbms_stats.backup_history; id | time | unit | comment ————+——————————————————————————————-+—————-+—————————— 1 | 2019-11-15 00:00:00.000000+09 | d | ίϝϯτ • όοΫΞοϓҰཡͷ֬ೝ =# SELECT dbms_stats.lock_column_stats('εΩʔϚ໊', 'ςʔϒϧ໊', 'ΧϥϜ໊'); • ౷ܭ৘ใͷݻఆԽ =# SELECT dbms_stats.unlock_table_stats('εΩʔϚ໊', 'ςʔϒϧ໊'); • ౷ܭ৘ใͷݻఆղআ
  35. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_hint_plan) =# EXPLAIN (VERBOSE, COSTS) /*+ SeqScan(a) HashJoin(a b)

    Set(random_page_cost 2.0) */ SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON a.bid = b.bid ORDER BY a.aid LIMIT 10; pgbench_accountsςʔϒϧΛɺ ΠϯσοΫεΛ࢖༻ͤͣʹ໰͍߹Θͤ͢Δɻ
  36. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_hint_plan) =# EXPLAIN (VERBOSE, COSTS) /*+ SeqScan(a) HashJoin(a b)

    Set(random_page_cost 2.0) */ SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON a.bid = b.bid ORDER BY a.aid LIMIT 10; pgbench_accountsςʔϒϧͱ pgbench_branchesςʔϒϧͷ݁߹Λϋογϡ݁ ߹ͱ͢Δɻ
  37. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_hint_plan) =# EXPLAIN (VERBOSE, COSTS) /*+ SeqScan(a) HashJoin(a b)

    Set(random_page_cost 2.0) */ SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON a.bid = b.bid ORDER BY a.aid LIMIT 10; random_page_costΛ4.0͔Β2.0ʹ΁Μ͜͏͢Δ ͜ͱͰɺΠϯσοΫεΛ༻͍΍͘͢͢Δɻ
  38. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_statsinfo) pg_statsinfoͷ࢖͍ํ pg_stasinfo͸PostgreSQLΛىಈ͢Δͱಉ࣌ʹಈ࡞͠·͢ɻ
 postgresql.confϑΝΠϧʹpg_stasinfoͷઃఆΛ௥Ճ͢Δ͜ͱͰ౷ܭ৘ใऩू౳ͷઃఆΛߦ͏͜ͱ͕Ͱ͖ ·͢ɻ ·ͨɺલड़Ͱ঺հ֦ͨ͠ுػೳ(pg_stat_statementsɺpg_store_plans)Λಋೖ͢Δ͜ͱͰɺ ΫΤϦͷ౷ܭ৘ใΛ௥ՃͰऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ pg_statsinfoͷ஫ҙ఺ •

    ϩάϑΝΠϧ͸pg_statsinfoͰղੳͰ͖ΔܗࣜͰ͋ΔɺCSVϑΝΠϧͰڧ੍తʹग़ྗ͞Ε·͢ɻ • ϩάϑΝΠϧͷϩάϩʔςʔτ͕ଘࡏ͠ͳ͍ͨΊɺผ్ઃఆ͢Δඞཁ͕͋Γ·͢ɻ • PostgreSQLͷγϟοτμ΢ϯ͸ऩूͰ͖·ͤΜɻ ৄࡉ͸ެࣜͷղઆαΠτΛࢀর͍ͯͩ͘͠͞ɻ http://pgstatsinfo.sourceforge.net/documents/statsinfo10/pg_statsinfo-ja.html
  39. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_repack) pg_repackͷ࢖͍ํ pg_repackίϚϯυΛ࣮ߦ͢Δ͜ͱͰ࠶ੜ੒Λߦ͏͜ͱ͕Ͱ͖·͢ɻ ΦϓγϣϯΛࢦఆ͢Δ͜ͱͰɺࡉ͔͍ઃఆ͕ՄೳͱͳΓ·͢ɻ σʔλϕʔεͷશςʔϒϧΛฒͼସ͑ͳ͠Ͱ࠶ฤ੒Λߦ͏৔߹͸ҎԼͷίϚϯυͱͳΓ·͢ɻ pg_repackͷ஫ҙ఺ • pg_repackίϚϯυ͸ɺDBͷεʔύʔϢʔβʔ(postgresϢʔβʔ)ͰͷΈ࣮ߦ͕ՄೳͰ͢ɻ •

    ର৅ςʔϒϧ͸ओΩʔ·ͨ͸ϢχʔΫΠϯσοΫε͕ଘࡏ͍ͯ͠Δඞཁ͕͋Γ·͢ɻ • pg_repackίϚϯυ࣮ߦத͸DDLͷ࣮ߦ͸Ͱ͖·ͤΜɻ ৄ͘͠͸ɺެࣜͷղઆαΠτΛ͝ཡ͍ͩ͘͞ɻ https://github.com/reorg/pg_repack/blob/master/doc/pg_repack_jp.rst Φϓγϣϯ ղઆ -n VACUUM FULL૬౰ͷॲཧΛ࣮ߦ͢Δɻ -a શςʔϒϧΛ࠶ฤ੒͢Δɻ -d σʔλϕʔε໊ ࢦఆͨ͠σʔλϕʔεͷΈ࠶ฤ੒͢Δɻ -c εΩʔϚ໊ ࢦఆͨ͠εΩʔϚͷΈ࠶ฤ੒͢Δɻ -t ςʔϒϧ໊ ࢦఆͨ͠ςʔϒϧͷΈ࠶ฤ੒͢Δɻ -o ΧϥϜ໊ ࢦఆͨ͠ΧϥϜ໊Ͱฒͼସ͑Λߦ͏ɻ $ pg_repack -n -d σʔλϕʔε໊
  40. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_flame) pg_flameͷ࢖͍ํ 1. ղੳ͍ͨ͠໰͍߹ΘͤܭըΛɺJSONܗࣜͰग़ྗ͠·͢ɻ
 ΫΤϦͷઌ಄ʹɺEXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)Λ෇༩͠·͢ɻ


    
 2. JSONϑΝΠϧΛpg_flameʹղੳͤ͞·͢ɻ
 
 3. ग़ྗͨ͠HTMLϑΝΠϧΛ։͖·͢ɻ ৄࡉ͸࡞ऀͷGitHubΛࢀর͍ͯͩ͘͠͞ɻ https://github.com/mgartner/pg_flame # psql <σʔλϕʔε઀ଓ৘ใ> -qAtf query.sql > plan.json # cat plan.json | docker run -i pg_flame > flamegraph.html
  41. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stats_reporter) pg_stats_reporterͱ͸ pg_stats_reporter͸pg_statsinfoͰऩूɾ஝ੵͨ͠σʔλΛάϥϑΟΧϧʹදࣔ͢ΔWebΞϓϦέʔγϣϯͰ͢ɻ NTT͕։ൃͨ͠ΞϓϦέʔγϣϯͰ͢ɻ pg_stats_reporterͷΠϯετʔϧ ެࣜαΠτͰrpm·ͨ͸ιʔείʔυ͕ެ։͞Ε͍ͯ·͢ɻ httpdٴͼphpͷΠϯετʔϧޙʹpg_stats_reporterͷΠϯετʔϧΛ࣮ࢪ͍ͯͩ͘͠͞ɻ pg_stats_reporterΠϯετʔϧޙ͸ɺઃఆΛߦ͍httpdͷىಈΛ࣮ߦ͍ͯͩ͘͠͞ɻ pg_stats_reporterͷઃఆɾදࣔ

    ઃఆϑΝΠϧ͸/etc/pg_stats_reporter.iniͱͳΓ·͢ɻ database connectionʹpg_statsinfoΛಋೖͨ͠σʔλϕʔεͷ઀ଓ৘ใΛઃఆ͠·͢ɻ ͳ͓ɺpg_stasinfoͷϨϙδτϦDBʹΞΫηε͢ΔͨΊʹ͸ɺಛݖϢʔβʔͰ͋Δඞཁ͕͋Γ·͢ɻ pg_stas_reporter༻ͷϢʔβʔΛ࡞੒͓ͯ͘͠ͱྑ͍Ͱ͠ΐ͏ɻ ը໘Λදࣔ͢Δʹ͸ɺҎԼͷURLʹΞΫηε͠·͢ɻ http://<αʔόʔϗετ໊>/pg_stats_reporter/pg_stats_reporter.php
  42. ϞχλϦϯάͰศརͳExtensionͨͪ (mackerel-plugin-postgres) mackerel-plugin-postgresͷΠϯετʔϧ MackerelͷϓϥάΠϯΛαʔόʔʹΠϯετʔϧ͠·͢ɻ ϚΠϖʔδ౳ͷࢦࣔʹै͏͜ͱͰɺ؆୯ʹΠϯετʔϧ͕ՄೳͰ͢ɻ ΤʔδΣϯτͷΠϯετʔϧޙɺҎԼͷखॱͰϓϥάΠϯΛΠϯετʔϧ͢Δ͜ͱ͕ՄೳͰ͢ɻ # yum install -y

    mackerel-agent-plugins # mackerel-plugin-postgres -hostname=ϗετ໊ ¥ -database=σʔλϕʔε໊ -user=Ϣʔβʔ໊ ¥ -password=ύεϫʔυ # sed -i -e 's/^# \(.*postgres.*\)/\1/g' /etc/mackerel-agent/mackerel-agent.conf # sed -i -e 's/mackerel-plugin-postgres/mackerel-plugin-postgres -hostname=ϗετ໊ -database=σʔ λϕʔε໊ -user=Ϣʔβʔ໊ -password=ύεϫʔυ/g' /etc/mackerel-agent/mackerel-agent.conf # systemctl restart mackerel-agent ಈ࡞֬ೝ༻ɻ ਖ਼͘͠஋͕औಘͰ͖͍ͯΔ͔֬ೝ͢Δɻ ಈ࡞֬ೝͨ͠஋Λೖྗ͢Δɻ