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

Transcript

1. ͸͡ΊͯͷPostgreSQL

   ϞχλϦϯάೖ໳
   PostgreSQLΧϯϑΝϨϯε2019
   

   View Slide

2. ͸͡Ίʹ
   ࣭໰Ͱ͢ʂPostgreSQLΛಋೖͨ͠ޙ

   ϞχλϦϯάΛߦͳ͍ͬͯ·͔͢ʁ
   

   View Slide

3. ͸͡Ίʹ
   σʔλϕʔε͸ಋೖ͢Δ͚ͩͰऴΘΓͰ
   ͸͋Γ·ͤΜɻ
   

   View Slide

4. ͸͡Ίʹ
   ো֐ݕ஌΍ো֐ൃੜՕॴͷಛఆ౳Λ

   ߦ͏ͨΊɺϞχλϦϯά͕ඞཁͰ͢ɻ
   

   View Slide

5. ͸͡Ίʹ
   ຊηογϣϯͰ͸ɺϞχλϦϯάΛ

   ߦ͏ͨΊͷ৘ใΛ͝঺հ͠·͢ɻ
   

   View Slide

6. ͸͡Ίʹ
   ຊηογϣϯ࡞੒ʹ͋ͨΓɺҎԼͷॻ੶͓ΑͼεϥΠυΛࢀߟʹ͍ͯ͠·͢ɻ
   ીࠜ ૖େཧࣄ: PostgreSQLͷ಺෦ߏ଄ͱPostgreSQLͷ؂ࢹͷجຊ

   https://speakerdeck.com/soudai/postgresql-architecture-and-
   performance-monitoring
   

   View Slide

7. ͓͜ͱΘΓ
   ࢲ͸ΦϯϓϨϛε؀ڥͰ͔ͭΫϩʔζυωοτϫʔΫͰͷ
   PostgreSQLߏஙͷΈΛܦݧ͍ͯ͠ΔͨΊɺΫϥ΢υ؀ڥ౳ͷ
   ϞχλϦϯάπʔϧʹ͍ͭͯ஌ݟ͕ෆ଍͍ͯ͠·͢ɻ
   ͦͷͨΊɺ౰ηογϣϯͰ͸͍ܰ঺հͷΈ࣮ࢪͱ͍ͤͯͨ͞
   ͖ͩ·͢ɻ
   ·ͨɺίϚϯυ͸શͯRHEL7(CentOS7)Ͱ

   ֬ೝ͍ͯ͠·͢ɻ
   

   View Slide

8. ΞδΣϯμ
   ࣗݾ঺հ
   PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
   PostgreSQLͷ౷ܭ৘ใ
   ϞχλϦϯάͰศརͳExtensionͨͪ
   ऴΘΓʹ
   

   View Slide

9. ΞδΣϯμ
   ࣗݾ঺հ
   PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
   PostgreSQLͷ౷ܭ৘ใ
   ϞχλϦϯάͰศརͳExtensionͨͪ
   ऴΘΓʹ
   

   View Slide

10. ࣗݾ঺հ
    ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ)
    ॴଐ: ೔ຊPostgreSQLϢʔβձ

    ؔ੢ࢧ෦௕
    Twitter/GitHub: @ester41
    ࢓ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ
    

    View Slide

11. ΞδΣϯμ
    ࣗݾ঺հ
    PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    PostgreSQLͷ౷ܭ৘ใ
    ϞχλϦϯάͰศརͳExtensionͨͪ
    ऴΘΓʹ
    

    View Slide

12. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    • ϞχλϦϯάΛߦ͏ʹ͸ɺ·ͣॳΊʹPostgreSQL
    ͷ಺෦ߏ଄ʹ͍ͭͯཧղ͕ඞཁͱͳΓ·͢ɻ
    • σʔλϕʔεͷϓϩηεߏ੒ɺϝϞϦʔߏ੒ɺσ
    ʔλߏ੒ɺΫΤϦղੳॱংʹ͍ͭͯ

    ཧղ͠·͠ΐ͏ɻ
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

30. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    (σʔλߏ੒ ʔ ςʔϒϧϑΝΠϧ)
    ςʔϒϧϑΝΠϧ
    ϖʔδ(8KB)
    ΞΠςϜIDσʔλ1
    (4όΠτ)
    ΞΠςϜIDσʔλ2
    (4όΠτ)
    …
    ۭ͖ྖҬ
    ΞΠςϜIDσʔλn
    (4όΠτ)
    ΞΠςϜσʔλn(Մม) …
    ϖʔδϔομʔ
    (24όΠτ)
    ΞΠςϜσʔλ1(Մม)
    ΞΠςϜσʔλ2(Մม)
    ϖʔδ
    (8KB)
    …
    ςʔϒϧϑΝΠϧ͸ɺ௨ৗ8KBͷϖʔδ͔Βߏ੒͞Ε·͢ɻ
    ϖʔδ಺ʹσʔλ͕ऩ·Βͳ͘ͳΔͱɺϖʔδ͕৽͘͠ੜ੒͞Ε·͢ɻ
    

    View Slide

31. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    (σʔλߏ੒ ʔ TOASTϑΝΠϧ)
    TOASTͱ͸ɺʮաେଐੑ֨ೲٕ๏(The Oversized-Attribute Storage Technique)ʯͷུশͰɺ
    ඇৗʹେ͖ͳྻͷσʔλ(ΞΠςϜσʔλ)Λ֨ೲ͢ΔͨΊͷٕ๏Ͱ͢ɻ
    ΞΠςϜσʔλʹ͸TOASTྖҬͷΞΫηεϙΠϯλ͕֨ೲ͞Ε·͢ɻ
    ςʔϒϧϑΝΠϧ
    ϖʔδ
    ΞΠςϜIDσʔλ
    ϖʔδϔομʔ
    ۭ͖ྖҬ
    ΞΠςϜσʔλ
    TOASTϑΝΠϧ
    ڊେͳσʔλ1
    ڊେͳσʔλ2
    ڊେͳσʔλ3
    …
    ΞΠςϜσʔλ͕ϖʔδ
    αΠζͷ1/4αΠζΛ௒
    ͑Δ৔߹ɺTOAST͕࢖
    ༻͞Ε·͢ɻ
    

    View Slide

32. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    (σʔλߏ੒ ʔ ΠϯσοΫεϑΝΠϧ)
    ΠϯσοΫε͸ɺϖʔδΛϊʔυͱ͢Δ࿦ཧతͳπϦʔߏ଄Λ࣋ͪ·͢ɻ
    ΠϯσοΫεϑΝΠϧ͸ςʔϒϧϑΝΠϧͱ΄΅ಉ͡ߏ੒ͱͳ͍ͬͯ·͕͢ɺ
    ֤ϖʔδͷ຤ඌʹΠϯσοΫεΞΫηεϝιουಛ༗ͷσʔλΛอ࣋͢ΔྖҬ͕ଘࡏ͠·͢ɻ
    ΠϯσοΫε͸ϝλϖʔδɺϧʔτϖʔδɺΠϯλʔφϧϖʔδɺϦʔϑϖʔδͷ4ͭͰߏ੒͠·͢ɻ
    Ϧʔϑϖʔδʹ͸ɺΧϥϜͷ஋ͱςʔϒϧϖʔδͷΞυϨε͕֨ೲ͞Ε͍ͯ·͢ɻ
    ΠϯσοΫεϑΝΠϧ
    ϧʔτ
    ϖʔδ
    ϝλ
    ϖʔδ
    Πϯλʔφϧ
    ϖʔδ
    Ϧʔϑʔ
    ϖʔδ
    Πϯλʔφϧ
    ϖʔδ
    Πϯλʔφϧ
    ϖʔδ
    Ϧʔϑʔ
    ϖʔδ
    Ϧʔϑʔ
    ϖʔδ
    Ϧʔϑʔ
    ϖʔδ
    Ϧʔϑʔ
    ϖʔδ
    Ϧʔϑʔ
    ϖʔδ
    

    View Slide

33. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    (ΫΤϦͷ࣮ߦॱং)
    σʔλϕʔεͷϞχλϦϯάΛߦ͏ͨΊʹ͸ΫΤϦͷ࣮ߦॱংʹ͍ͭͯཧղ͢Δඞཁ͕͋Γ·͢ɻ
    ॲཧ଎౓͕஗͍ݪҼͷ੾Γ෼͚౳ʹ࢖༻Ͱ͖·͢ɻ
    ౷ܭ৘ใ
    ϧʔϧఆٛ
    CREATE RULE۟
    Ͱఆٛ
    SQLจ
    ໰͍߹ΘͤπϦʔ
    ໰͍߹ΘͤπϦʔ
    ࣮ߦܭը
    ౷ܭ৘ใίϨΫλ
    Ͱੜ੒
    ύʔαʔ
    (ࣈ۟ղੳ / ߏจղੳ)
    ϦϥΠλʔ
    (VIEW΍ϧʔϧͰॻ͖׵͑)
    ϓϥϯφʔ / ΦϓςΟϚΠβ
    (࣮ߦܭըͷੜ੒ / ࠷దԽ)
    ΤάθΩϡʔλʔ
    (ΫΤϦͷ࣮ߦ)
    

    View Slide

34. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    (σʔλͷߋ৽)
    PostgreSQL͸௥هܕΞʔΩςΫνϟΛ࠾༻͍ͯ͠·͢ɻ
    ௥هܕΞʔΩςΫνϟ͸ɺچϨίʔυΛ࡟আͤͣʹ৽ϨίʔυΛ௥Ճ͢ΔํࣜͰ͢ɻ
    ςʔϒϧ
    Ϩίʔυ1
    Ϩίʔυ2
    Ϩίʔυ3
    ςʔϒϧ
    Ϩίʔυ1
    Ϩίʔυ2
    Ϩίʔυ3
    Ϩίʔυ2
    Ϩίʔυ2Λߋ৽
    چϨίʔυʹ࡟আ
    ϑϥάΛཱͯΔ
    ৽ϨίʔυΛ
    ௥Ճ͢Δ
    

    View Slide

35. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    (VACUUM)
    ௥هܕΞʔΩςΫνϟ͸ߋ৽Λߦ͑͹ߦ͏΄ͲɺෆཁϨίʔυ͕૿Ճ͍͖ͯ͠·͢ɻ
    PostgreSQL͸ɺچϨίʔυʹࢀর͍ͯ͠ΔผτϥϯβΫγϣϯ͕ແ͘ͳΔͱɺ
    ࡟আϑϥάཱ͕ͯΒΕͨϨίʔυͷྖҬΛճऩ͢Δػೳ(VACUUM)Λ࣮ߦ͠·͢ɻ
    ςʔϒϧ
    Ϩίʔυ1
    Ϩίʔυ2
    Ϩίʔυ3
    ςʔϒϧ
    Ϩίʔυ1
    ۭ͖ྖҬ
    Ϩίʔυ3
    Ϩίʔυ2
    VACUUM࣮ߦ
    Ϩίʔυ2
    

    View Slide

36. ςʔϒϧ
    PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    (HOTͱFILLFACTOR)
    ߋ৽Λߦ͏ͱɺΠϯσοΫε͕֨ೲ͍ͯ͠ΔΞυϨε΋มΘͬͯ͠·͍·͢ɻ
    ߋ৽ͷͨͼʹΠϯσοΫε΋มߋΛߦ͏ͱɺஶ͍͠ੑೳྼԽ͕ى͜Γ·͢ɻ
    HOT(Heap Only Tuple)͸ɺΠϯσοΫε͕ுΒΕ͍ͯͳ͍ΧϥϜΛߋ৽͢Δ࣌ͷߴ଎Խٕज़ͱͳΓ·͢ɻ
    FILLFACTOR͸ɺϖʔδͷۭ͖ྖҬΛ؅ཧ͢ΔػೳٴͼύϥϝʔλʔͰ͢ɻ
    σϑΥϧτ஋͸ɺςʔϒϧͷ৔߹͸"100%"(ۭ͖ແ͠)ɺΠϯσοΫεͷ৔߹͸"90%"ͱͳ͍ͬͯ·͢ɻ
    ςʔϒϧ
    Ϩίʔυ1
    Ϩίʔυ2
    ΠϯσοΫε
    Ϩίʔυ2
    ΠϯσοΫε΋
    ߋ৽
    • HOT͕ແޮͳ৔߹
    Ϩίʔυ1
    Ϩίʔυ2
    ΠϯσοΫε
    Ϩίʔυ2
    ΠϯσοΫε͸ߋ৽
    ͤͣɺ৽Ϩίʔυͷ
    ΞυϨεΛ௥Ճ
    • HOT͕༗ޮͳ৔߹
    

    View Slide

37. ΞδΣϯμ
    ࣗݾ঺հ
    PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    PostgreSQLͷ౷ܭ৘ใ
    ϞχλϦϯάͰศརͳExtensionͨͪ
    ऴΘΓʹ
    

    View Slide

38. ϞχλϦϯά͸ԿΛߦ͏ͷ͔
    σʔλϕʔε͸ɺΞϓϦέʔγϣϯΛࢧ͑Δେ੾ͳϛυϧ΢ΣΞͰ͢ɻ
    ਖ਼͘͠ಈ࡞͠ͳ͔ͬͨΓੑೳྼԽͰϨεϙϯε͕஗͘ͳΔ͜ͱͰར༻ऀͷෆຬ͕ൃੜ͢ΔͨΊɺ
    ༧ஹΛൃݟ͠໰୊͕ൃੜ͠ͳ͍Α͏ʹϞχλϦϯάΛߦ͏ඞཁ͕͋Γ·͢ɻ
    PostgreSQLͷϞχλϦϯά͸2ͭͷ؍఺͕ඞཁͱͳΓ·͢ɻ
    ϓϩηεىಈঢ়ଶ΍σΟεΫ༰ྔͱ͍ͬͨɺσʔλϕʔεશମʹ͔͔ΘΔϞχλϦϯά
    ੑೳྼԽΛࣄલʹݕ஌͢ΔͨΊͷϞχλϦϯά(ϩάɾύϑΥʔϚϯε)
    ϓϩηε͕μϝ
    ੑೳ͕μϝ
    ෆຬʂʂʂ
    

    View Slide

39. 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ίϚϯυɺͦͷ΄͔ͷπʔϧͰ֬ೝΛߦ͏ඞཁ͕͋Γ·͢ɻ
    

    View Slide

40. 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ʹؔ͢Δ౷ܭ৘ใΛࣔ͠·͢ɻ
    

    View Slide

41. PostgreSQLͷ౷ܭ৘ใ
    (ੑೳྼԽΛࣄલʹݕ஌͢ΔͨΊͷ౷ܭ৘ใ)
    લड़ͷVIEWΛ૊Έ߹ΘͤΔ͜ͱͰɺϞχλϦϯάʹඞཁͳ৘ใΛऔಘ͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ҎԼͷ৘ใΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ
    ʲࢀߟαΠτʳLet’s Postgres Քಈ౷ܭ৘ใΛ׆༻͠Α͏
    https://lets.postgresql.jp/documents/technical/statistics
    DB୯Ґͷίϛοτ਺΍ϩʔϧόοΫ਺
    DB΍ςʔϒϧɺΠϯσοΫε୯ҐͷI/Oൃੜঢ়گ
    DB΍ςʔϒϧʹରͯ͠ૠೖ/ߋ৽/࡟আ͞Εͨߦ਺
    ςʔϒϧ୯ҐͰ࣮ࢪ͞ΕͨදεΩϟϯճ਺
    DB΍ςʔϒϧɺΠϯσοΫε୯ҐͰεΩϟϯ͞Εͨߦ਺
    ςʔϒϧ୯Ґͷߦ਺ɺΨϕʔδྔ
    ݱࡏ࣮ࢪதͷSQL΍ϝϯςφϯεॲཧ
    ౷ܭ৘ใίϨΫλ͸ɺ”ݱࡏͷঢ়ଶ”͔͠ݟΔ͜ͱ͸Ͱ͖·ͤΜɻ
    ϞχλϦϯάΛਖ਼͘͠ߦ͏ͨΊʹ͸ɺաڈͷঢ়ଶΛऔΓଓ͚ɺൺֱͰ͖Δ؀ڥ͕ඞཁͱͳΓ·͢ɻ
    

    View Slide

42. 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);
    

    View Slide

43. 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͕ෆ଍͍ͯ͠·͢ɻ
    

    View Slide

44. PostgreSQLͷ౷ܭ৘ใ
    (σʔλϕʔεঢ়ଶ)
    σʔλϕʔεঢ়ଶ͸ɺpg_stat_databaseϏϡʔͰ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ
    ΧϥϜ໊ ղઆ
    datname όοΫΤϯυϓϩηε͕઀ଓͨ͠σʔλϕʔε໊Ͱ͢ɻ
    xact_commit ίϛοτ͞ΕͨྦྷܭͷτϥϯβΫγϣϯ਺Ͱ͢ɻ
    xact_rollback ϩʔϧόοΫ͞ΕͨྦྷܭͷτϥϯβΫγϣϯ਺Ͱ͢ɻ
    temp_files ໰͍߹ΘͤʹΑͬͯॻ͖ग़͞ΕͨྦྷܭͷҰ࣌ϑΝΠϧ਺Ͱ͢ɻ
    temp_bytes ໰͍߹ΘͤʹΑͬͯॻ͖ग़͞ΕͨྦྷܭͷҰ࣌ϑΝΠϧαΠζͰ͢ɻ
    deadlocks ݕ஌͞ΕͨྦྷܭͷσουϩοΫ਺Ͱ͢ɻ
    pg_stat_databaseϏϡʔ͸ɺϦηοτ͕͔͔Δ·Ͱྦྷܭ஋Λฦ͠·͢ɻ
    ্هΧϥϜΛ֬ೝ͢Δ͜ͱͰɺҎԼͷ৘ใ͕Θ͔Γ·͢ɻ
    • σʔλϕʔεͷ࢖༻ঢ়گ
    • ੑೳτϥϒϧͷ༗ແ

    (Ұ࣌ϑΝΠϧͷେྔੜ੒ɾେن໛ͳҰ࣌ϑΝΠϧͷੜ੒)
    • σʔλϕʔεΛ࢖༻͍ͯ͠ΔγεςϜͷෆ۩߹
    ͪͳΈʹɺҎԼͷΫΤϦͰσʔλϕʔεͷαΠζΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ
    =# SELECT pg_size_pretty(pg_database_size(‘σʔλϕʔε໊'));
    

    View Slide

45. 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
    

    View Slide

46. 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 ςʔϒϧ಺ͷόοϑΝώοτ਺Ͱ͢ɻ
    

    View Slide

47. PostgreSQLͷ౷ܭ৘ใ
    (Ϣʔβʔςʔϒϧঢ়ଶ)
    pg_stat_user_tablesϏϡʔΛ֬ೝ͢Δ͜ͱͰɺϨίʔυͷঢ়ଶΛ֬ೝ͢Δ͜ͱ͕Ͱ͖ɺ
    pg_statio_user_tablesϏϡʔΛ֬ೝ͢Δ͜ͱͰςʔϒϧͷΩϟογϡώοτ཰Λࢉग़͢Δ͜ͱ͕Ͱ͖·͢ɻ
    pg_stat_user_tablesϏϡʔͷn_dead_tup͕ଟ͘ͳΓ͍͗ͯ͢Δ৔߹͸ɺਖ਼͘͠όΩϡʔϜ͞Ε͍ͯͳ͍Մೳੑ͕͋Γ·͢ɻ
    last_autovacuumͷ࣌ࠁΛ֬ೝ͍ͯͩ͘͠͞ɻ
    ςʔϒϧͷΩϟογϡώοτ཰ΛٻΊΔ৔߹ɺҎԼͷΫΤϦͰऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ
    =# SELECT
    relname
    , round( heap_blks_hit * 100 / (heap_blks_hit + heap_blks_read), 2) as cache_hit_ratio
    FROM
    pg_statio_user_tables
    WHERE
    blks_read > 0;
    relname | cache_hit_ratio
    ————————————+—————————————————
    test_table | 99.00
    

    View Slide

48. 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ͷ৔߹ɺΠϯσοΫε͕શ͘࢖༻͞Ε͍ͯ·ͤΜɻ
    ࡟আର৅ͱͯ͠ݕ౼͢Δ͜ͱ͕Ͱ͖·͢ɻ
    

    View Slide

49. 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
    

    View Slide

50. PostgreSQLͷϩά
    PostgreSQLͷϩά͸ɺσʔλϕʔεͷՔಇϩά΍઀ଓϩάɺΫΤϦ৘ใͳͲ༷ʑͳ৘ใΛ࢒͠·͢ɻ
    PostgreSQLΛ҆ఆՔಇͤ͞ΔͨΊʹ͸ɺ౷ܭ৘ใΛఆظతʹ֬ೝ͢Δ͚ͩͰͳ͘
    ϩά΋֬ೝ͢Δඞཁ͕͋Γ·͢ɻ
    PostgreSQLͷϩά͸ɺpostgresql.confϑΝΠϧͷlog_min_messagesύϥϝʔλʔͰ
    ग़ྗ͢ΔϩάྔΛௐ੔͠·͢ɻ
    σϑΥϧτͰ͸warningͱͳ͍ͬͯ·͢ɻ
    (warning͸τϥϯβΫγϣϯ֎ͷίϛοτॲཧͳͲɺϢʔβʔ΁ͷܯࠂ·Ͱग़ྗ͠·͢ɻ)
    ·ͨɺΤϥʔ͕ൃੜͨ͠ΫΤϦΛϩάʹग़ྗ੍ޚ͢Δlog_min_error_statementύϥϝʔλʔ΍ɺ
    ࣮ߦ͕஗͍ΫΤϦΛϩάʹग़ྗ੍ޚ͢Δlog_min_duration_statementύϥϝʔλʔͳͲ͕ଘࡏ͠·͢ɻ
    ৄ͘͠͸ɺެࣜυΩϡϝϯτΛࢀর͍ͯͩ͘͠͞ɻ
    https://www.postgresql.jp/document/current/html/runtime-config-logging.html
    

    View Slide

51. ΞδΣϯμ
    ࣗݾ঺հ
    PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    PostgreSQLͷ౷ܭ৘ใ
    ϞχλϦϯάͰศརͳExtensionͨͪ
    ऴΘΓʹ
    

    View Slide

52. ϞχλϦϯάͰศརͳ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Λ؂ࢹ͢ΔͨΊͷϓϥάΠϯͰ͢ɻ
    

    View Slide

53. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_stat_statements)
    pg_stat_statementsͱ͸
    pg_stat_statements͸શͯͷશͯͷΫΤϦͱ࣮ߦ࣌ͷ౷ܭ৘ใΛه࿥͠·͢ɻ
    PostgreSQLެࣜͷExtension܈Ͱ͋Δcontribʹଘࡏ͠·͢ɻ
    pg_stat_statementsͷΠϯετʔϧɾ༗ޮԽ
    contribύοέʔδΛΠϯετʔϧͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏ͱ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ༗ޮԽ͸ҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
    1. postgresql.confϑΝΠϧͷshared_preload_librariesύϥϝʔλʔʹ

    ’pg_stat_statements’

    Λ௥Ճ͠·͢ɻ
    2. PostgreSQLΛ࠶ىಈ͠·͢ɻ
    3. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ

    CREATE EXTENSION pg_stat_statements;

    Λ࣮ߦ͠·͢ɻ
    

    View Slide

54. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_stat_statements)
    pg_stat_statementsͷ࢖͍ํ
    pg_stat_statementsϏϡʔΛࢀর͢Δ͜ͱͰɺ1ΫΤϦ୯ҐͰ࣮ߦճ਺ɾΫΤϦͷ࣮ߦ࣌ؒɾ
    ໰߹ͤͨ͠ϒϩοΫ਺ͳͲΛௐ΂Δ͜ͱ͕ՄೳͰ͢ɻ
    ݟΔ΂͖߲໨͸࣍ϖʔδʹهࡌ͠·͢ɻ
    pg_stat_statementsͷ஫ҙ఺
    pg_stat_statements͸ɺσʔλϕʔεͱ࢖༻ऀ(ΞϓϦέʔγϣϯ)ͷؒʹೖ͍ͬͯΔঢ়ଶͷͨΊ
    ύϑΥʔϚϯεʹӨڹ͠·͢ɻ
    ։ൃ؀ڥͰ͸༗ޮԽ͓͖ͯ͠ɺຊ൪؀ڥͰ͸ແޮԽ͓ͯ͘͠ͱ͍ͬͨӡ༻Λ͓קΊ͠·͢ɻ
    ৄ͘͠͸ɺެࣜυΩϡϝϯτΛࢀর͍ͯͩ͘͠͞ɻ
    https://www.postgresql.jp/document/current/html/pgstatstatements.html
    

    View Slide

55. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_stat_statements)
    pg_stat_statementsϏϡʔͰओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ
    ΧϥϜ໊ ղઆ
    query ࣮ߦ͞ΕͨΫΤϦͰ͢ɻ
    calls ࣮ߦճ਺Ͱ͢ɻ
    total_time ΫΤϦ࣮ߦʹඅ΍ͨ͠૯࣌ؒͰ͢ɻ
    mean_time ΫΤϦ࣮ߦʹඅ΍ͨ͠ฏۉ࣌ؒͰ͢ɻ
    rows ΫΤϦ࣮ߦʹΑͬͯऔಘ͞ΕͨɾӨڹΛड͚ͨߦͷ૯਺Ͱ͢ɻ
    shared_blks_hit ΫΤϦ࣮ߦʹΑͬͯώοτͨ͠ڞ༗ϒϩοΫΩϟογϡͷ૯਺Ͱ͢ɻ
    shared_blks_read ΫΤϦ࣮ߦʹΑͬͯಡΈࠐ·Εͨڞ༗ϒϩοΫͷ૯਺Ͱ͢ɻ
    

    View Slide

56. ϞχλϦϯάͰศརͳ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;
    

    View Slide

57. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_stat_statements)
    

    View Slide

58. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_store_plans)
    pg_store_plansͱ͸
    pg_store_plans͸࣮ߦܭը͝ͱʹ࣮ߦ౷ܭΛऔಘ͢Δ֦ுػೳͰ͢ɻ
    NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ
    pg_store_plansͷΠϯετʔϧɾ༗ޮԽ
    ެࣜαΠτͰrpm·ͨ͸ιʔείʔυ͕ެ։͞Ε͍ͯ·͢ɻ
    ؀ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ༗ޮԽ͸ҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
    1. postgresql.confϑΝΠϧͷshared_preload_librariesύϥϝʔλʔʹ

    ’pg_store_plans’

    Λ௥Ճ͠·͢ɻ
    2. ޙPostgreSQLΛ࠶ىಈ͠·͢ɻ
    3. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ

    CREATE EXTENSION pg_store_plans;

    Λ࣮ߦ͠·͢ɻ
    

    View Slide

59. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_store_plans)
    pg_store_plansͷ࢖͍ํ
    pg_store_plansϏϡʔΛࢀর͢Δ͜ͱͰɺ1ΫΤϦ୯ҐͰ࣮ߦճ਺ɾΫΤϦͷ࣮ߦ࣌ؒɾ
    ໰߹ͤͨ͠ϒϩοΫ਺ɾ࣮ߦܭըͷςΩετɾߏจ໦৘ใͳͲΛௐ΂Δ͜ͱ͕ՄೳͰ͢ɻ
    pg_store_plans͸୯ମͰ࢖༻͢Δ͜ͱ͸গͳ͘ɺجຊతʹଞͷ֦ுػೳͷิॿ༻Ͱ༻͍ΒΕ·͢ɻ
    pg_store_plansͷ஫ҙ఺
    pg_store_plans͸ɺσʔλϕʔεͱ࢖༻ऀ(ΞϓϦέʔγϣϯ)ͷؒʹೖ͍ͬͯΔঢ়ଶͷͨΊ
    ύϑΥʔϚϯεʹӨڹ͠·͢ɻ
    ։ൃ؀ڥͰ͸༗ޮԽ͓͖ͯ͠ɺຊ൪؀ڥͰ͸ແޮԽ͓ͯ͘͠ͱ͍ͬͨӡ༻Λ͓קΊ͠·͢ɻ
    ৄࡉ͸ެࣜαΠτΛࢀর͍ͯͩ͘͠͞ɻ
    https://ja.osdn.net/projects/pgstoreplans/
    

    View Slide

60. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_dbms_stats)
    pg_dbms_statsͱ͸
    pg_dbms_stats͸ANALYZE͕ऩू͢Δ౷ܭ৘ใΛ؅ཧ͢Δ֦ுػೳͰ͢ɻ
    ౷ܭ৘ใͷ࠷৽Խ΍ݻఆԽΛߦ͏͜ͱ͕Ͱ͖ΔͨΊɺ࣮ߦϓϥϯͷ੍ޚ͕ՄೳͱͳΓ·͢ɻ
    NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ
    pg_dbms_statsͷΠϯετʔϧɾ༗ޮԽ
    ެࣜαΠτͰrpm·ͨ͸ιʔείʔυ͕ެ։͞Ε͍ͯ·͢ɻ
    ؀ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ༗ޮԽ͸ҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
    1. postgresql.confϑΝΠϧͷshared_preload_librariesύϥϝʔλʔʹ

    ’pg_dbms_stats’

    Λ௥Ճ͠·͢ɻ
    2. ޙPostgreSQLΛ࠶ىಈ͠·͢ɻ
    3. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ

    CREATE EXTENSION pg_dbms_stats;

    Λ࣮ߦ͠·͢ɻ
    

    View Slide

61. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_dbms_stats)
    pg_dbms_statsͷ࢖͍ํ
    ֦ுػೳͰఏڙ͞ΕΔؔ਺Λ࣮ߦ͢Δ͜ͱͰɺ
    ౷ܭ৘ใͷόοΫΞοϓɾϦετΞɾݻఆԽΛ࣮ߦ͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ࣍ϖʔδʹ࣮ߦίϚϯυΛهࡌ͠·͢ɻ
    pg_dbms_statsͷ஫ҙ఺
    ςʔϒϧαΠζ΍ภΓͳͲͰσʔλͷಛੑ͕มΘͬͯ΋౷ܭ৘ใ͸ݻఆ͞Εͨ··ͱͳΓ·͢ɻ
    มߋ͕ൃੜͨ͠৔߹͸౷ܭ৘ใͷ࠶ݻఆԽΛݕ౼͍ͯͩ͘͠͞ɻ
    ৄࡉ͸ެࣜαΠτΛࢀর͍ͯͩ͘͠͞ɻ
    https://ja.osdn.net/projects/pgdbmsstats/
    

    View Slide

62. ϞχλϦϯάͰศརͳ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('εΩʔϚ໊', 'ςʔϒϧ໊');
    • ౷ܭ৘ใͷݻఆղআ
    

    View Slide

63. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_hint_plan)
    pg_hint_planͱ͸
    pg_hint_plan͸ΫΤϦͷ࣮ߦܭըΛࣗ༝ʹૢ࡞͢Δ؀ڥΛఏڙ͢Δ֦ுػೳͰ͢ɻ
    ΫΤϦதͷίϝϯτʹॴఆͷϑΥʔϚοτͰهࡌΛߦ͍·͢ɻ

    (Oracle౳ͷଞσʔλϕʔεͰ࢖༻Ͱ͖Δώϯτ۟ͱಉ౳ͷػೳͰ͢ɻ)
    NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ
    pg_hint_planͷΠϯετʔϧɾ༗ޮԽ
    ެࣜαΠτͰrpm·ͨ͸ιʔείʔυ͕ެ։͞Ε͍ͯ·͢ɻ
    ؀ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ༗ޮԽ͸ҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
    1. postgresql.confϑΝΠϧͷshared_preload_librariesύϥϝʔλʔʹ

    ’pg_hint_plan’

    Λ௥Ճ͠·͢ɻ
    2. ޙPostgreSQLΛ࠶ىಈ͠·͢ɻ
    3. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ

    CREATE EXTENSION pg_hint_plan;

    Λ࣮ߦ͠·͢ɻ
    

    View Slide

64. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_hint_plan)
    pg_hint_planͷ࢖͍ํ
    ώϯτ۟ΛదԠ͍ͨ͠ΫΤϦͷઌ಄·ͨ͸్தͷSQLϒϩοΫίϝϯτͷதʹهड़͠·͢ɻ
    ώϯτ۟༻ͷϒϩοΫίϝϯτ͸ɺ/*+ͱͳΓ·͢ɻ
    ࣍ϖʔδʹώϯτ۟ΛదԠͨ͠ྫΛهࡌ͠·͢ɻ
    pg_hint_planͷ஫ҙ఺
    pg_hint_plan͸ɺΦϓςΟϚΠβʔ͕౷ܭ৘ใ౳͔Βܾఆ࣮ͨ͠ߦܭըΛมߋ͢ΔͨΊɺσʔλͷಛੑ͕
    มΘͬͨࡍʹద੾ͳ࣮ߦܭըͰ࣮ߦ͞Εͳ͍ڪΕ͕͋Γ·͢ɻ
    ώϯτ۟Λ࢖༻͢Δ৔߹͸ɺमਖ਼ํ๏౳ͷݕ౼Λߦ͔ͬͯΒ࢖༻͢ΔΑ͏ʹ͠·͠ΐ͏ɻ
    ৄࡉ͸ެࣜαΠτΛࢀর͍ͯͩ͘͠͞ɻ
    https://ja.osdn.net/projects/pghintplan/
    

    View Slide

65. ϞχλϦϯάͰศརͳ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ςʔϒϧΛɺ
    ΠϯσοΫεΛ࢖༻ͤͣʹ໰͍߹Θͤ͢Δɻ
    

    View Slide

66. ϞχλϦϯάͰศརͳ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ςʔϒϧͷ݁߹Λϋογϡ݁
    ߹ͱ͢Δɻ
    

    View Slide

67. ϞχλϦϯάͰศརͳ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ʹ΁Μ͜͏͢Δ
    ͜ͱͰɺΠϯσοΫεΛ༻͍΍͘͢͢Δɻ
    

    View Slide

68. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_statsinfo)
    pg_statsinfoͱ͸
    pg_statsinfo͸αʔόʔͷ౷ܭ৘ใΛఆظతʹऩूɾ஝ੵɾ؅ཧΛ࣮ࢪ͢Δ֦ுػೳͰ͢ɻ
    ·ͨɺᮢ஋Λ௒͑ͨ৔߹ΞϥʔτΛग़ྗ͢Δػೳ͕͋Γ·͢ɻ
    NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ
    pg_statsinfoͷΠϯετʔϧɾ༗ޮԽ
    ެࣜαΠτͰrpm·ͨ͸ιʔείʔυ͕ެ։͞Ε͍ͯ·͢ɻ
    ؀ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ༗ޮԽ͸ҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
    1. postgresql.confϑΝΠϧͷshared_preload_librariesύϥϝʔλʔʹ

    ’pg_statsinfo’

    Λ௥Ճ͠·͢ɻ
    2. ޙPostgreSQLΛ࠶ىಈ͠·͢ɻ
    3. postgresσʔλϕʔεͰ

    CREATE EXTENSION pg_statsinfo;

    Λ࣮ߦ͠·͢ɻ
    

    View Slide

69. ϞχλϦϯάͰศརͳ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
    

    View Slide

70. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_repack)
    pg_repackͱ͸
    pg_repack͸ංେԽͨ͠ςʔϒϧ΍ΠϯσοΫεΛ࠶ฤ੒͢Δ֦ுػೳͰ͢ɻ
    ࣮ߦύϥϝʔλʔʹΑΓɺσʔλͷฒͼସ͑Λมߋ͢Δ͜ͱ΋ՄೳͰ͢ɻ
    NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ
    pg_repackͷΠϯετʔϧɾ༗ޮԽ
    PGDGϨϙδτϦͰrpm͕ఏڙ͞Ε͍ͯ·͢ɻ
    ؀ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ
    ༗ޮԽ͸ҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
    1. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ

    CREATE EXTENSION pg_repack;

    Λ࣮ߦ͠·͢ɻ
    

    View Slide

71. ϞχλϦϯάͰศརͳ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 σʔλϕʔε໊
    

    View Slide

72. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_flame)
    pg_flameͱ͸
    pg_flame͸ΫΤϦͷ໰͍߹ΘͤܭըΛϏδϡΞϧԽ͢ΔπʔϧͰ͢ɻ
    PostgreSQLͷ໰͍߹Θͤܭը͸ɺཧղ͢ΔͨΊʹ஌͕ࣝඞཁͱͳΓ·͕͢ɺ
    ͜ͷπʔϧΛ༻͍Δ͜ͱͰ෼͔Γ΍͘͢ͳΓ·͢ɻ
    ݸਓ(mgartner༷)͕։ൃͨ͠πʔϧͱͳΓ·͢ɻ
    pg_flameͷΠϯετʔϧ
    GitHubʹιʔείʔυ͕ެ։͞Ε͍ͯ·͢ɻ
    ҎԼͷखॱͰΠϯετʔϧ͢Δ͜ͱ͕ՄೳͰ͢ɻ
    # yum install -y git docker
    # systemctl start docker
    # git clone https://github.com/mgartner/pg_flame.git
    # cd pg_flame/
    # docker build --tag 'pg_flame' .
    

    View Slide

73. ϞχλϦϯάͰศརͳ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
    

    View Slide

74. ϞχλϦϯάͰศརͳ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
    

    View Slide

75. ϞχλϦϯάͰศརͳExtensionͨͪ
    (pg_stats_reporter)
    

    View Slide

76. ϞχλϦϯάͰศརͳExtensionͨͪ
    (mackerel-plugin-postgres)
    mackerel-plugin-postgresͱ͸
    αʔόʔ؂ࢹαʔϏεMackerelͰPostgreSQLΛ؂ࢹ͢ΔͨΊͷϓϥάΠϯͰ͢ɻ
    ΤʔδΣϯτΛ؂ࢹର৅ͷαʔόʔʹΠϯετʔϧ͢Δ͜ͱͰ؂ࢹΛ։࢝͢Δ͜ͱ͕ՄೳͰɺ
    αʔόʔΛ؂ࢹͯ͘͠Ε·͢ɻ
    ·ͨɺऔಘͨ͠৘ใΛάϥϑԽ΍ΞϥʔτΛه࿥ɾϝʔϧ΍Slack౳ʹ௨஌͢Δ͜ͱ͕ՄೳͰ͢ɻ
    

    View Slide

77. ϞχλϦϯάͰศརͳ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
    ಈ࡞֬ೝ༻ɻ
    ਖ਼͘͠஋͕औಘͰ͖͍ͯΔ͔֬ೝ͢Δɻ
    ಈ࡞֬ೝͨ͠஋Λೖྗ͢Δɻ
    

    View Slide

78. ϞχλϦϯάͰศརͳExtensionͨͪ
    (mackerel-plugin-postgres)
    mackerel-plugin-postgresͷ࢖͍ํ
    ΤʔδΣϯτʹਖ਼͘͠ϓϥάΠϯ͕ೝࣝ͞Ε͍ͯΕ͹ɺMackerelͷϚΠϖʔδͰPostgreSQLͷ
    ৘ใ͕දࣔ͞Ε͍ͯΔ͸ͣͰ͢ɻ
    

    View Slide

79. ΞδΣϯμ
    ࣗݾ঺հ
    PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ
    PostgreSQLͷ౷ܭ৘ใ
    ϞχλϦϯάͰศརͳExtensionͨͪ
    ऴΘΓʹ
    

    View Slide

80. ऴΘΓʹ
    ຊηογϣϯͰ͸ϞχλϦϯάํ๏ͷ͋͘·ͰҰྫͷ঺հΛ
    ߦ͍·ͨ͠ɻ
    ࣮ࡍʹ͸Ҋ݅͝ͱʹ؀ڥ͕มΘΔͱࢥ͍·͢ͷͰɺͦͷ࣌ͦ
    ͷ࣌ʹ߹ΘͤͯϞχλϦϯάํ๏Λมߋ͍ͯͩ͘͠͞ɻ
    ·ͨɺPostgreSQLͷઃఆมߋ΍ศརͳExtensionͷಋೖΛߦ
    ͏͜ͱ΋ߟྀ͢ΔΑ͏ʹ͠·͠ΐ͏ɻ
    ͜·ΊʹPostgreSQLͷঢ়ଶΛ֬ೝ͠ɺ
    ҆ఆՔಇΛ໨ࢦ͍ͯͩ͘͠͞ɻ
    

    View Slide

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

    View Slide