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

Transcript

1. ͸͡ΊͯͷPostgreSQL
 ϞχλϦϯάೖ໳ PostgreSQLΧϯϑΝϨϯε2019
2. ͸͡Ίʹ ࣭໰Ͱ͢ʂPostgreSQLΛಋೖͨ͠ޙ
 ϞχλϦϯάΛߦͳ͍ͬͯ·͔͢ʁ
3. ͸͡Ίʹ σʔλϕʔε͸ಋೖ͢Δ͚ͩͰऴΘΓͰ ͸͋Γ·ͤΜɻ
4. ͸͡Ίʹ ো֐ݕ஌΍ো֐ൃੜՕॴͷಛఆ౳Λ
 ߦ͏ͨΊɺϞχλϦϯά͕ඞཁͰ͢ɻ
5. ͸͡Ίʹ ຊηογϣϯͰ͸ɺϞχλϦϯάΛ
 ߦ͏ͨΊͷ৘ใΛ͝঺հ͠·͢ɻ
6. ͸͡Ίʹ ຊηογϣϯ࡞੒ʹ͋ͨΓɺҎԼͷॻ੶͓ΑͼεϥΠυΛࢀߟʹ͍ͯ͠·͢ɻ ીࠜ ૖େཧࣄ: PostgreSQLͷ಺෦ߏ଄ͱPostgreSQLͷ؂ࢹͷجຊ
 https://speakerdeck.com/soudai/postgresql-architecture-and- performance-monitoring
7. ͓͜ͱΘΓ ࢲ͸ΦϯϓϨϛε؀ڥͰ͔ͭΫϩʔζυωοτϫʔΫͰͷ PostgreSQLߏஙͷΈΛܦݧ͍ͯ͠ΔͨΊɺΫϥ΢υ؀ڥ౳ͷ ϞχλϦϯάπʔϧʹ͍ͭͯ஌ݟ͕ෆ଍͍ͯ͠·͢ɻ ͦͷͨΊɺ౰ηογϣϯͰ͸͍ܰ঺հͷΈ࣮ࢪͱ͍ͤͯͨ͞ ͖ͩ·͢ɻ ·ͨɺίϚϯυ͸શͯRHEL7(CentOS7)Ͱ
 ֬ೝ͍ͯ͠·͢ɻ
8. ΞδΣϯμ ࣗݾ঺հ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ PostgreSQLͷ౷ܭ৘ใ ϞχλϦϯάͰศརͳExtensionͨͪ ऴΘΓʹ
9. ΞδΣϯμ ࣗݾ঺հ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ PostgreSQLͷ౷ܭ৘ใ ϞχλϦϯάͰศརͳExtensionͨͪ ऴΘΓʹ
10. ࣗݾ঺հ ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ) ॴଐ: ೔ຊPostgreSQLϢʔβձ
 ؔ੢ࢧ෦௕ Twitter/GitHub: @ester41 ࢓ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ
11. ΞδΣϯμ ࣗݾ঺հ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ PostgreSQLͷ౷ܭ৘ใ ϞχλϦϯάͰศརͳExtensionͨͪ ऴΘΓʹ
12. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ • ϞχλϦϯάΛߦ͏ʹ͸ɺ·ͣॳΊʹPostgreSQL ͷ಺෦ߏ଄ʹ͍ͭͯཧղ͕ඞཁͱͳΓ·͢ɻ • σʔλϕʔεͷϓϩηεߏ੒ɺϝϞϦʔߏ੒ɺσ ʔλߏ੒ɺΫΤϦղੳॱংʹ͍ͭͯ
 ཧղ͠·͠ΐ͏ɻ
13. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ
14. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ શͯͷϓϩηεΛ औΓ·ͱΊΔϓϩηεɻ ͜Ε͕ఀࢭ͢Δͱ PostgreSQLશମ͕ఀࢭ͠·͢ɻ
15. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ڞ༗ϝϞϦʔ্ͷWALόοϑΝʔΛ WALϑΝΠϧʹॻ͖ग़͢ϓϩηεɻ ※WAL: τϥϯβΫγϣϯϩά(Write Ahead Logging)
16. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ڞ༗ϝϞϦʔ্ͷڞ༗όοϑΝʔ಺ͷ ߋ৽͞ΕͨϖʔδΛରԠ͢Δ σʔλϑΝΠϧͷϒϩοΫʹ ॻ͖ग़͢ϓϩηεɻ
17. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ શͯͷμʔςΟϖʔδ(ϑΝΠϧγεςϜʹ ॻ͖໭͢ඞཁͷ͋ΔσʔλΛ࣋ͬͨϖʔδ) ΛσʔλϑΝΠϧʹ൓ө͢Δϓϩηεɻ νΣοΫϙΠϯτͱݺ͹ΕΔɺ Ϋϥογϡ࣌ʹϦΧόϦॲཧΛߦ͏ Օॴ୯ҐͰࣗಈతʹ࣮ߦ͠·͢ɻ
18. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ ৘ใΛҰఆִؒͰऩू͢Δϓϩηεɻ
19. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛ ੍ޚɾ࣮ߦ͢Δϓϩηεɻ όΩϡʔϜʹ͍ͭͯ͸ ޙ΄Ͳղઆ͠·͢ɻ
20. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϓϩηεߏ੒) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭ৘ใίϨΫλʔ) postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭ৘ใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ઀ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ΫϥΠΞϯτ͔Β઀ଓཁٻΛ ड͚ͨ࣌ʹੜ੒͞ΕΔϓϩηεɻ SQLͷ࣮ߦ͸͜ͷϓϩηε಺Ͱ ߦΘΕ·͢ɻ
21. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε
22. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε Ұ࣌ςʔϒϧʹΞΫηε͢Δ৔߹ʹ ࢖༻͞ΕΔྖҬɻ ઃఆ͞ΕͨྖҬΛ্ݶͱ͠ɺ ඞཁʹԠͯ͡ϝϞϦΛ֬อ͠·͢ɻ
23. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ฒͼସ͑ૢ࡞(ORDER BY, DISTINCT, Ϛʔδ݁߹)ͱ ϋογϡςʔϒϧ(ΠϯσοΫεͳͲ)ૢ࡞ʹ ࢖༻͞ΕΔྖҬɻ ෳࡶͳΫΤϦʹͳΔͱɺ࡞ۀϝϞϦʔ͕ ෳ਺ੜ੒͞Ε·͢ɻ
24. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε อकૢ࡞(VACUUM, CREATE INDEXͳͲ)Ͱ ࢖༻͞ΕΔྖҬɻ
25. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍ WALΛΩϟογϡ͢ΔྖҬɻ
26. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧ΍ΠϯσοΫεσʔλΛ Ωϟογϡ͢ΔྖҬɻ
27. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧ্ͷར༻ՄೳͳྖҬΛ ؅ཧ͢ΔྖҬɻ όΩϡʔϜॲཧ΍ɺ σʔλͷ௥Ճɾߋ৽࣌ʹ ࢖༻͞Ε·͢ɻ
28. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ϝϞϦʔߏ੒) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem) ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧσʔλͷՄࢹঢ়ଶΛ ؅ཧ͢ΔྖҬɻ όΩϡʔϜॲཧ΍ɺ INDEX ONLY SCAN࣌ʹ ࢖༻͞Ε·͢ɻ
29. σΟεΫྖҬ ΞʔΧΠϒWALྖҬ WALྖҬ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλߏ੒) σʔλϕʔεྖҬ ςʔϒϧϑΝΠϧ WALϑΝΠϧ ΞʔΧΠϒϑΝΠϧ TOASTϑΝΠϧ ΠϯσοΫεϑΝΠϧ ۭ͖ྖҬϚοϓϑΝΠϧ ՄࢹੑϚοϓϑΝΠϧ ௨ৗ16MBͷWALϑΝΠϧΛ ॥؀ར༻͠·͢ɻ ΞʔΧΠϒϞʔυΛ༗ޮʹ ͢Δ͜ͱͰɺ॥؀ར༻͞Ε ΔWALϑΝΠϧΛอଘ͠· ͢ɻϙΠϯτΠϯλΠϜϦΧ όϦʔͰ࢖༻͠·͢ɻ ςʔϒϧϑΝΠϧɾTOAST ϑΝΠϧɾΠϯσοΫεϑ ΝΠϧʹ͍ͭͯ͸࣍ϖʔδ͔ Βղઆ͠·͢ɻ
30. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλߏ੒ ʔ ςʔϒϧϑΝΠϧ) ςʔϒϧϑΝΠϧ ϖʔδ(8KB) ΞΠςϜIDσʔλ1 (4όΠτ) ΞΠςϜIDσʔλ2 (4όΠτ) … ۭ͖ྖҬ ΞΠςϜIDσʔλn (4όΠτ) ΞΠςϜσʔλn(Մม) … ϖʔδϔομʔ (24όΠτ) ΞΠςϜσʔλ1(Մม) ΞΠςϜσʔλ2(Մม) ϖʔδ (8KB) … ςʔϒϧϑΝΠϧ͸ɺ௨ৗ8KBͷϖʔδ͔Βߏ੒͞Ε·͢ɻ ϖʔδ಺ʹσʔλ͕ऩ·Βͳ͘ͳΔͱɺϖʔδ͕৽͘͠ੜ੒͞Ε·͢ɻ
31. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλߏ੒ ʔ TOASTϑΝΠϧ) TOASTͱ͸ɺʮաେଐੑ֨ೲٕ๏(The Oversized-Attribute Storage Technique)ʯͷུশͰɺ ඇৗʹେ͖ͳྻͷσʔλ(ΞΠςϜσʔλ)Λ֨ೲ͢ΔͨΊͷٕ๏Ͱ͢ɻ ΞΠςϜσʔλʹ͸TOASTྖҬͷΞΫηεϙΠϯλ͕֨ೲ͞Ε·͢ɻ ςʔϒϧϑΝΠϧ ϖʔδ ΞΠςϜIDσʔλ ϖʔδϔομʔ ۭ͖ྖҬ ΞΠςϜσʔλ TOASTϑΝΠϧ ڊେͳσʔλ1 ڊେͳσʔλ2 ڊେͳσʔλ3 … ΞΠςϜσʔλ͕ϖʔδ αΠζͷ1/4αΠζΛ௒ ͑Δ৔߹ɺTOAST͕࢖ ༻͞Ε·͢ɻ
32. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλߏ੒ ʔ ΠϯσοΫεϑΝΠϧ) ΠϯσοΫε͸ɺϖʔδΛϊʔυͱ͢Δ࿦ཧతͳπϦʔߏ଄Λ࣋ͪ·͢ɻ ΠϯσοΫεϑΝΠϧ͸ςʔϒϧϑΝΠϧͱ΄΅ಉ͡ߏ੒ͱͳ͍ͬͯ·͕͢ɺ ֤ϖʔδͷ຤ඌʹΠϯσοΫεΞΫηεϝιουಛ༗ͷσʔλΛอ࣋͢ΔྖҬ͕ଘࡏ͠·͢ɻ ΠϯσοΫε͸ϝλϖʔδɺϧʔτϖʔδɺΠϯλʔφϧϖʔδɺϦʔϑϖʔδͷ4ͭͰߏ੒͠·͢ɻ Ϧʔϑϖʔδʹ͸ɺΧϥϜͷ஋ͱςʔϒϧϖʔδͷΞυϨε͕֨ೲ͞Ε͍ͯ·͢ɻ ΠϯσοΫεϑΝΠϧ ϧʔτ ϖʔδ ϝλ ϖʔδ Πϯλʔφϧ ϖʔδ Ϧʔϑʔ ϖʔδ Πϯλʔφϧ ϖʔδ Πϯλʔφϧ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ
33. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (ΫΤϦͷ࣮ߦॱং) σʔλϕʔεͷϞχλϦϯάΛߦ͏ͨΊʹ͸ΫΤϦͷ࣮ߦॱংʹ͍ͭͯཧղ͢Δඞཁ͕͋Γ·͢ɻ ॲཧ଎౓͕஗͍ݪҼͷ੾Γ෼͚౳ʹ࢖༻Ͱ͖·͢ɻ ౷ܭ৘ใ ϧʔϧఆٛ CREATE RULE۟ Ͱఆٛ SQLจ ໰͍߹ΘͤπϦʔ ໰͍߹ΘͤπϦʔ ࣮ߦܭը ౷ܭ৘ใίϨΫλ Ͱੜ੒ ύʔαʔ (ࣈ۟ղੳ / ߏจղੳ) ϦϥΠλʔ (VIEW΍ϧʔϧͰॻ͖׵͑) ϓϥϯφʔ / ΦϓςΟϚΠβ (࣮ߦܭըͷੜ੒ / ࠷దԽ) ΤάθΩϡʔλʔ (ΫΤϦͷ࣮ߦ)
34. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (σʔλͷߋ৽) PostgreSQL͸௥هܕΞʔΩςΫνϟΛ࠾༻͍ͯ͠·͢ɻ ௥هܕΞʔΩςΫνϟ͸ɺچϨίʔυΛ࡟আͤͣʹ৽ϨίʔυΛ௥Ճ͢ΔํࣜͰ͢ɻ ςʔϒϧ Ϩίʔυ1 Ϩίʔυ2 Ϩίʔυ3 ςʔϒϧ Ϩίʔυ1 Ϩίʔυ2 Ϩίʔυ3 Ϩίʔυ2 Ϩίʔυ2Λߋ৽ چϨίʔυʹ࡟আ ϑϥάΛཱͯΔ ৽ϨίʔυΛ ௥Ճ͢Δ
35. PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (VACUUM) ௥هܕΞʔΩςΫνϟ͸ߋ৽Λߦ͑͹ߦ͏΄ͲɺෆཁϨίʔυ͕૿Ճ͍͖ͯ͠·͢ɻ PostgreSQL͸ɺچϨίʔυʹࢀর͍ͯ͠ΔผτϥϯβΫγϣϯ͕ແ͘ͳΔͱɺ ࡟আϑϥάཱ͕ͯΒΕͨϨίʔυͷྖҬΛճऩ͢Δػೳ(VACUUM)Λ࣮ߦ͠·͢ɻ ςʔϒϧ Ϩίʔυ1 Ϩίʔυ2 Ϩίʔυ3 ςʔϒϧ Ϩίʔυ1 ۭ͖ྖҬ Ϩίʔυ3 Ϩίʔυ2 VACUUM࣮ߦ Ϩίʔυ2
36. ςʔϒϧ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ (HOTͱFILLFACTOR) ߋ৽Λߦ͏ͱɺΠϯσοΫε͕֨ೲ͍ͯ͠ΔΞυϨε΋มΘͬͯ͠·͍·͢ɻ ߋ৽ͷͨͼʹΠϯσοΫε΋มߋΛߦ͏ͱɺஶ͍͠ੑೳྼԽ͕ى͜Γ·͢ɻ HOT(Heap Only Tuple)͸ɺΠϯσοΫε͕ுΒΕ͍ͯͳ͍ΧϥϜΛߋ৽͢Δ࣌ͷߴ଎Խٕज़ͱͳΓ·͢ɻ FILLFACTOR͸ɺϖʔδͷۭ͖ྖҬΛ؅ཧ͢ΔػೳٴͼύϥϝʔλʔͰ͢ɻ σϑΥϧτ஋͸ɺςʔϒϧͷ৔߹͸"100%"(ۭ͖ແ͠)ɺΠϯσοΫεͷ৔߹͸"90%"ͱͳ͍ͬͯ·͢ɻ ςʔϒϧ Ϩίʔυ1 Ϩίʔυ2 ΠϯσοΫε Ϩίʔυ2 ΠϯσοΫε΋ ߋ৽ • HOT͕ແޮͳ৔߹ Ϩίʔυ1 Ϩίʔυ2 ΠϯσοΫε Ϩίʔυ2 ΠϯσοΫε͸ߋ৽ ͤͣɺ৽Ϩίʔυͷ ΞυϨεΛ௥Ճ • HOT͕༗ޮͳ৔߹
37. ΞδΣϯμ ࣗݾ঺հ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ PostgreSQLͷ౷ܭ৘ใ ϞχλϦϯάͰศརͳExtensionͨͪ ऴΘΓʹ
38. ϞχλϦϯά͸ԿΛߦ͏ͷ͔ σʔλϕʔε͸ɺΞϓϦέʔγϣϯΛࢧ͑Δେ੾ͳϛυϧ΢ΣΞͰ͢ɻ ਖ਼͘͠ಈ࡞͠ͳ͔ͬͨΓੑೳྼԽͰϨεϙϯε͕஗͘ͳΔ͜ͱͰར༻ऀͷෆຬ͕ൃੜ͢ΔͨΊɺ ༧ஹΛൃݟ͠໰୊͕ൃੜ͠ͳ͍Α͏ʹϞχλϦϯάΛߦ͏ඞཁ͕͋Γ·͢ɻ PostgreSQLͷϞχλϦϯά͸2ͭͷ؍఺͕ඞཁͱͳΓ·͢ɻ ϓϩηεىಈঢ়ଶ΍σΟεΫ༰ྔͱ͍ͬͨɺσʔλϕʔεશମʹ͔͔ΘΔϞχλϦϯά ੑೳྼԽΛࣄલʹݕ஌͢ΔͨΊͷϞχλϦϯά(ϩάɾύϑΥʔϚϯε) ϓϩηε͕μϝ ੑೳ͕μϝ ෆຬʂʂʂ
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ίϚϯυɺͦͷ΄͔ͷπʔϧͰ֬ೝΛߦ͏ඞཁ͕͋Γ·͢ɻ
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ʹؔ͢Δ౷ܭ৘ใΛࣔ͠·͢ɻ
41. PostgreSQLͷ౷ܭ৘ใ (ੑೳྼԽΛࣄલʹݕ஌͢ΔͨΊͷ౷ܭ৘ใ) લड़ͷVIEWΛ૊Έ߹ΘͤΔ͜ͱͰɺϞχλϦϯάʹඞཁͳ৘ใΛऔಘ͢Δ͜ͱ͕Ͱ͖·͢ɻ ҎԼͷ৘ใΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ ʲࢀߟαΠτʳLet’s Postgres Քಈ౷ܭ৘ใΛ׆༻͠Α͏ https://lets.postgresql.jp/documents/technical/statistics DB୯Ґͷίϛοτ਺΍ϩʔϧόοΫ਺ DB΍ςʔϒϧɺΠϯσοΫε୯ҐͷI/Oൃੜঢ়گ DB΍ςʔϒϧʹରͯ͠ૠೖ/ߋ৽/࡟আ͞Εͨߦ਺ ςʔϒϧ୯ҐͰ࣮ࢪ͞ΕͨදεΩϟϯճ਺ DB΍ςʔϒϧɺΠϯσοΫε୯ҐͰεΩϟϯ͞Εͨߦ਺ ςʔϒϧ୯Ґͷߦ਺ɺΨϕʔδྔ ݱࡏ࣮ࢪதͷSQL΍ϝϯςφϯεॲཧ ౷ܭ৘ใίϨΫλ͸ɺ”ݱࡏͷঢ়ଶ”͔͠ݟΔ͜ͱ͸Ͱ͖·ͤΜɻ ϞχλϦϯάΛਖ਼͘͠ߦ͏ͨΊʹ͸ɺաڈͷঢ়ଶΛऔΓଓ͚ɺൺֱͰ͖Δ؀ڥ͕ඞཁͱͳΓ·͢ɻ
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);
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͕ෆ଍͍ͯ͠·͢ɻ
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(‘σʔλϕʔε໊'));
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
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 ςʔϒϧ಺ͷόοϑΝώοτ਺Ͱ͢ɻ
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
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ͷ৔߹ɺΠϯσοΫε͕શ͘࢖༻͞Ε͍ͯ·ͤΜɻ ࡟আର৅ͱͯ͠ݕ౼͢Δ͜ͱ͕Ͱ͖·͢ɻ
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
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
51. ΞδΣϯμ ࣗݾ঺հ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ PostgreSQLͷ౷ܭ৘ใ ϞχλϦϯάͰศརͳExtensionͨͪ ऴΘΓʹ
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Λ؂ࢹ͢ΔͨΊͷϓϥάΠϯͰ͢ɻ
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;
 Λ࣮ߦ͠·͢ɻ
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
55. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stat_statements) pg_stat_statementsϏϡʔͰओʹݟΔ΂͖ΧϥϜ͸ҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ query ࣮ߦ͞ΕͨΫΤϦͰ͢ɻ calls ࣮ߦճ਺Ͱ͢ɻ total_time ΫΤϦ࣮ߦʹඅ΍ͨ͠૯࣌ؒͰ͢ɻ mean_time ΫΤϦ࣮ߦʹඅ΍ͨ͠ฏۉ࣌ؒͰ͢ɻ rows ΫΤϦ࣮ߦʹΑͬͯऔಘ͞ΕͨɾӨڹΛड͚ͨߦͷ૯਺Ͱ͢ɻ shared_blks_hit ΫΤϦ࣮ߦʹΑͬͯώοτͨ͠ڞ༗ϒϩοΫΩϟογϡͷ૯਺Ͱ͢ɻ shared_blks_read ΫΤϦ࣮ߦʹΑͬͯಡΈࠐ·Εͨڞ༗ϒϩοΫͷ૯਺Ͱ͢ɻ
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;
57. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stat_statements)
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;
 Λ࣮ߦ͠·͢ɻ
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/
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;
 Λ࣮ߦ͠·͢ɻ
61. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_dbms_stats) pg_dbms_statsͷ࢖͍ํ ֦ுػೳͰఏڙ͞ΕΔؔ਺Λ࣮ߦ͢Δ͜ͱͰɺ ౷ܭ৘ใͷόοΫΞοϓɾϦετΞɾݻఆԽΛ࣮ߦ͢Δ͜ͱ͕Ͱ͖·͢ɻ ࣍ϖʔδʹ࣮ߦίϚϯυΛهࡌ͠·͢ɻ pg_dbms_statsͷ஫ҙ఺ ςʔϒϧαΠζ΍ภΓͳͲͰσʔλͷಛੑ͕มΘͬͯ΋౷ܭ৘ใ͸ݻఆ͞Εͨ··ͱͳΓ·͢ɻ มߋ͕ൃੜͨ͠৔߹͸౷ܭ৘ใͷ࠶ݻఆԽΛݕ౼͍ͯͩ͘͠͞ɻ ৄࡉ͸ެࣜαΠτΛࢀর͍ͯͩ͘͠͞ɻ https://ja.osdn.net/projects/pgdbmsstats/
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('εΩʔϚ໊', 'ςʔϒϧ໊'); • ౷ܭ৘ใͷݻఆղআ
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;
 Λ࣮ߦ͠·͢ɻ
64. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_hint_plan) pg_hint_planͷ࢖͍ํ ώϯτ۟ΛదԠ͍ͨ͠ΫΤϦͷઌ಄·ͨ͸్தͷSQLϒϩοΫίϝϯτͷதʹهड़͠·͢ɻ ώϯτ۟༻ͷϒϩοΫίϝϯτ͸ɺ/*+ͱͳΓ·͢ɻ ࣍ϖʔδʹώϯτ۟ΛదԠͨ͠ྫΛهࡌ͠·͢ɻ pg_hint_planͷ஫ҙ఺ pg_hint_plan͸ɺΦϓςΟϚΠβʔ͕౷ܭ৘ใ౳͔Βܾఆ࣮ͨ͠ߦܭըΛมߋ͢ΔͨΊɺσʔλͷಛੑ͕ มΘͬͨࡍʹద੾ͳ࣮ߦܭըͰ࣮ߦ͞Εͳ͍ڪΕ͕͋Γ·͢ɻ ώϯτ۟Λ࢖༻͢Δ৔߹͸ɺमਖ਼ํ๏౳ͷݕ౼Λߦ͔ͬͯΒ࢖༻͢ΔΑ͏ʹ͠·͠ΐ͏ɻ ৄࡉ͸ެࣜαΠτΛࢀর͍ͯͩ͘͠͞ɻ https://ja.osdn.net/projects/pghintplan/
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ςʔϒϧΛɺ ΠϯσοΫεΛ࢖༻ͤͣʹ໰͍߹Θͤ͢Δɻ
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ςʔϒϧͷ݁߹Λϋογϡ݁ ߹ͱ͢Δɻ
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ʹ΁Μ͜͏͢Δ ͜ͱͰɺΠϯσοΫεΛ༻͍΍͘͢͢Δɻ
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;
 Λ࣮ߦ͠·͢ɻ
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
70. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_repack) pg_repackͱ͸ pg_repack͸ංେԽͨ͠ςʔϒϧ΍ΠϯσοΫεΛ࠶ฤ੒͢Δ֦ுػೳͰ͢ɻ ࣮ߦύϥϝʔλʔʹΑΓɺσʔλͷฒͼସ͑Λมߋ͢Δ͜ͱ΋ՄೳͰ͢ɻ NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ pg_repackͷΠϯετʔϧɾ༗ޮԽ PGDGϨϙδτϦͰrpm͕ఏڙ͞Ε͍ͯ·͢ɻ ؀ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ ༗ޮԽ͸ҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ 1. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ
 CREATE EXTENSION pg_repack;
 Λ࣮ߦ͠·͢ɻ
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 σʔλϕʔε໊
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' .
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
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
75. ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stats_reporter)
76. ϞχλϦϯάͰศརͳExtensionͨͪ (mackerel-plugin-postgres) mackerel-plugin-postgresͱ͸ αʔόʔ؂ࢹαʔϏεMackerelͰPostgreSQLΛ؂ࢹ͢ΔͨΊͷϓϥάΠϯͰ͢ɻ ΤʔδΣϯτΛ؂ࢹର৅ͷαʔόʔʹΠϯετʔϧ͢Δ͜ͱͰ؂ࢹΛ։࢝͢Δ͜ͱ͕ՄೳͰɺ αʔόʔΛ؂ࢹͯ͘͠Ε·͢ɻ ·ͨɺऔಘͨ͠৘ใΛάϥϑԽ΍ΞϥʔτΛه࿥ɾϝʔϧ΍Slack౳ʹ௨஌͢Δ͜ͱ͕ՄೳͰ͢ɻ
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 ಈ࡞֬ೝ༻ɻ ਖ਼͘͠஋͕औಘͰ͖͍ͯΔ͔֬ೝ͢Δɻ ಈ࡞֬ೝͨ͠஋Λೖྗ͢Δɻ
78. ϞχλϦϯάͰศརͳExtensionͨͪ (mackerel-plugin-postgres) mackerel-plugin-postgresͷ࢖͍ํ ΤʔδΣϯτʹਖ਼͘͠ϓϥάΠϯ͕ೝࣝ͞Ε͍ͯΕ͹ɺMackerelͷϚΠϖʔδͰPostgreSQLͷ ৘ใ͕දࣔ͞Ε͍ͯΔ͸ͣͰ͢ɻ
79. ΞδΣϯμ ࣗݾ঺հ PostgreSQLͷ಺෦ߏ଄ʹ͍ͭͯ PostgreSQLͷ౷ܭ৘ใ ϞχλϦϯάͰศརͳExtensionͨͪ ऴΘΓʹ
80. ऴΘΓʹ ຊηογϣϯͰ͸ϞχλϦϯάํ๏ͷ͋͘·ͰҰྫͷ঺հΛ ߦ͍·ͨ͠ɻ ࣮ࡍʹ͸Ҋ݅͝ͱʹ؀ڥ͕มΘΔͱࢥ͍·͢ͷͰɺͦͷ࣌ͦ ͷ࣌ʹ߹ΘͤͯϞχλϦϯάํ๏Λมߋ͍ͯͩ͘͠͞ɻ ·ͨɺPostgreSQLͷઃఆมߋ΍ศརͳExtensionͷಋೖΛߦ ͏͜ͱ΋ߟྀ͢ΔΑ͏ʹ͠·͠ΐ͏ɻ ͜·ΊʹPostgreSQLͷঢ়ଶΛ֬ೝ͠ɺ ҆ఆՔಇΛ໨ࢦ͍ͯͩ͘͠͞ɻ
81. ͝੩ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ