Share
Embed
Transcript
81 slides
-
ࣗݾհ ໊લ: ࣉ େً(ͯΒ͏ͪ ͍͖ͨ) ॴଐ: ຊPostgreSQLϢʔβձ ؔࢧ෦ Twitter/GitHub: @ester41
ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ શͯͷϓϩηεΛ औΓ·ͱΊΔϓϩηεɻ ͜Ε͕ఀࢭ͢Δͱ PostgreSQLશମ͕ఀࢭ͠·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ڞ༗ϝϞϦʔ্ͷWALόοϑΝʔΛ WALϑΝΠϧʹॻ͖ग़͢ϓϩηεɻ ※WAL: τϥϯβΫγϣϯϩά(Write Ahead Logging) -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ڞ༗ϝϞϦʔ্ͷڞ༗όοϑΝʔͷ ߋ৽͞ΕͨϖʔδΛରԠ͢Δ σʔλϑΝΠϧͷϒϩοΫʹ ॻ͖ग़͢ϓϩηεɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ શͯͷμʔςΟϖʔδ(ϑΝΠϧγεςϜʹ ॻ͖͢ඞཁͷ͋ΔσʔλΛ࣋ͬͨϖʔδ) ΛσʔλϑΝΠϧʹө͢Δϓϩηεɻ νΣοΫϙΠϯτͱݺΕΔɺ Ϋϥογϡ࣌ʹϦΧόϦॲཧΛߦ͏ Օॴ୯ҐͰࣗಈతʹ࣮ߦ͠·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ ใΛҰఆִؒͰऩू͢Δϓϩηεɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛ ੍ޚɾ࣮ߦ͢Δϓϩηεɻ όΩϡʔϜʹ͍ͭͯ ޙ΄Ͳղઆ͠·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϓϩηεߏ) postgres (όοΫΤϯυϓϩηε) postgres (ϥΠλʔ) postgres (νΣοΫϙΠϯλʔ) postgres (౷ܭใίϨΫλʔ)
postgres (ࣗಈόΩϡʔϜϥϯνϟʔ) postgres (ࣗಈόΩϡʔϜϫʔΧʔ) postgres (WALϥΠλʔ) postgres (Ϛελʔαʔόʔϓϩηε) WALόοϑΝʔ WALϑΝΠϧ σʔλϑΝΠϧ ڞ༗όοϑΝʔ ౷ܭใ ىಈ࣌ʹϓϩηεΛϑΥʔΫ ଓཁٻ͝ͱʹϓϩηεΛϑΥʔΫ ࣗಈόΩϡʔϜΛܖػʹϓϩηεΛϑΥʔΫ ΫϥΠΞϯτ͔ΒଓཁٻΛ ड͚ͨ࣌ʹੜ͞ΕΔϓϩηεɻ SQLͷ࣮ߦ͜ͷϓϩηεͰ ߦΘΕ·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε Ұ࣌ςʔϒϧʹΞΫηε͢Δ߹ʹ ༻͞ΕΔྖҬɻ ઃఆ͞ΕͨྖҬΛ্ݶͱ͠ɺ ඞཁʹԠͯ͡ϝϞϦΛ֬อ͠·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ฒͼସ͑ૢ࡞(ORDER BY, DISTINCT, Ϛʔδ݁߹)ͱ ϋογϡςʔϒϧ(ΠϯσοΫεͳͲ)ૢ࡞ʹ ༻͞ΕΔྖҬɻ ෳࡶͳΫΤϦʹͳΔͱɺ࡞ۀϝϞϦʔ͕ ෳੜ͞Ε·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε อकૢ࡞(VACUUM, CREATE INDEXͳͲ)Ͱ ༻͞ΕΔྖҬɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍ WALΛΩϟογϡ͢ΔྖҬɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧΠϯσοΫεσʔλΛ Ωϟογϡ͢ΔྖҬɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧ্ͷར༻ՄೳͳྖҬΛ ཧ͢ΔྖҬɻ όΩϡʔϜॲཧɺ σʔλͷՃɾߋ৽࣌ʹ ༻͞Ε·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ϝϞϦʔߏ) ϓϩηεϝϞϦʔ όοΫΤϯυϓϩηε Ұ࣌όοϑΝʔ (temp_buffers) ࡞ۀϝϞϦʔ (work_mem) ϝϯςφϯε༻࡞ۀϝϞϦʔ (maintenance_work_mem)
ڞ༗ϝϞϦʔ WALόοϑΝʔ (wal_buffers) ڞ༗όοϑΝʔ (shard_buffers) σʔλϑΝΠϧ WALϑΝΠϧ WALϥΠλʔϓϩηε όΩϡʔϜϫʔΧʔϓϩηε ۭ͖ྖҬϚοϓ ՄࢹੑϚοϓ ϥΠλʔ/ νΣοΫϙΠϯλʔϓϩηε ςʔϒϧσʔλͷՄࢹঢ়ଶΛ ཧ͢ΔྖҬɻ όΩϡʔϜॲཧɺ INDEX ONLY SCAN࣌ʹ ༻͞Ε·͢ɻ -
σΟεΫྖҬ ΞʔΧΠϒWALྖҬ WALྖҬ PostgreSQLͷ෦ߏʹ͍ͭͯ (σʔλߏ) σʔλϕʔεྖҬ ςʔϒϧϑΝΠϧ WALϑΝΠϧ ΞʔΧΠϒϑΝΠϧ TOASTϑΝΠϧ
ΠϯσοΫεϑΝΠϧ ۭ͖ྖҬϚοϓϑΝΠϧ ՄࢹੑϚοϓϑΝΠϧ ௨ৗ16MBͷWALϑΝΠϧΛ ॥ར༻͠·͢ɻ ΞʔΧΠϒϞʔυΛ༗ޮʹ ͢Δ͜ͱͰɺ॥ར༻͞Ε ΔWALϑΝΠϧΛอଘ͠· ͢ɻϙΠϯτΠϯλΠϜϦΧ όϦʔͰ༻͠·͢ɻ ςʔϒϧϑΝΠϧɾTOAST ϑΝΠϧɾΠϯσοΫεϑ ΝΠϧʹ͍ͭͯ࣍ϖʔδ͔ Βղઆ͠·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (σʔλߏ ʔ ςʔϒϧϑΝΠϧ) ςʔϒϧϑΝΠϧ ϖʔδ(8KB) ΞΠςϜIDσʔλ1 (4όΠτ) ΞΠςϜIDσʔλ2 (4όΠτ)
… ۭ͖ྖҬ ΞΠςϜIDσʔλn (4όΠτ) ΞΠςϜσʔλn(Մม) … ϖʔδϔομʔ (24όΠτ) ΞΠςϜσʔλ1(Մม) ΞΠςϜσʔλ2(Մม) ϖʔδ (8KB) … ςʔϒϧϑΝΠϧɺ௨ৗ8KBͷϖʔδ͔Βߏ͞Ε·͢ɻ ϖʔδʹσʔλ͕ऩ·Βͳ͘ͳΔͱɺϖʔδ͕৽͘͠ੜ͞Ε·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (σʔλߏ ʔ TOASTϑΝΠϧ) TOASTͱɺʮաେଐੑ֨ೲٕ๏(The Oversized-Attribute Storage Technique)ʯͷུশͰɺ ඇৗʹେ͖ͳྻͷσʔλ(ΞΠςϜσʔλ)Λ֨ೲ͢ΔͨΊͷٕ๏Ͱ͢ɻ ΞΠςϜσʔλʹTOASTྖҬͷΞΫηεϙΠϯλ͕֨ೲ͞Ε·͢ɻ
ςʔϒϧϑΝΠϧ ϖʔδ ΞΠςϜIDσʔλ ϖʔδϔομʔ ۭ͖ྖҬ ΞΠςϜσʔλ TOASTϑΝΠϧ ڊେͳσʔλ1 ڊେͳσʔλ2 ڊେͳσʔλ3 … ΞΠςϜσʔλ͕ϖʔδ αΠζͷ1/4αΠζΛ ͑Δ߹ɺTOAST͕ ༻͞Ε·͢ɻ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (σʔλߏ ʔ ΠϯσοΫεϑΝΠϧ) ΠϯσοΫεɺϖʔδΛϊʔυͱ͢ΔཧతͳπϦʔߏΛ࣋ͪ·͢ɻ ΠϯσοΫεϑΝΠϧςʔϒϧϑΝΠϧͱ΄΅ಉ͡ߏͱͳ͍ͬͯ·͕͢ɺ ֤ϖʔδͷඌʹΠϯσοΫεΞΫηεϝιουಛ༗ͷσʔλΛอ࣋͢ΔྖҬ͕ଘࡏ͠·͢ɻ ΠϯσοΫεϝλϖʔδɺϧʔτϖʔδɺΠϯλʔφϧϖʔδɺϦʔϑϖʔδͷ4ͭͰߏ͠·͢ɻ ϦʔϑϖʔδʹɺΧϥϜͷͱςʔϒϧϖʔδͷΞυϨε͕֨ೲ͞Ε͍ͯ·͢ɻ ΠϯσοΫεϑΝΠϧ
ϧʔτ ϖʔδ ϝλ ϖʔδ Πϯλʔφϧ ϖʔδ Ϧʔϑʔ ϖʔδ Πϯλʔφϧ ϖʔδ Πϯλʔφϧ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ Ϧʔϑʔ ϖʔδ -
PostgreSQLͷ෦ߏʹ͍ͭͯ (ΫΤϦͷ࣮ߦॱং) σʔλϕʔεͷϞχλϦϯάΛߦ͏ͨΊʹΫΤϦͷ࣮ߦॱংʹ͍ͭͯཧղ͢Δඞཁ͕͋Γ·͢ɻ ॲཧ͕͍ݪҼͷΓ͚ʹ༻Ͱ͖·͢ɻ ౷ܭใ ϧʔϧఆٛ CREATE RULE۟ Ͱఆٛ SQLจ
͍߹ΘͤπϦʔ ͍߹ΘͤπϦʔ ࣮ߦܭը ౷ܭใίϨΫλ Ͱੜ ύʔαʔ (ࣈ۟ղੳ / ߏจղੳ) ϦϥΠλʔ (VIEWϧʔϧͰॻ͖͑) ϓϥϯφʔ / ΦϓςΟϚΠβ (࣮ߦܭըͷੜ / ࠷దԽ) ΤάθΩϡʔλʔ (ΫΤϦͷ࣮ߦ) -
PostgreSQLͷ෦ߏʹ͍ͭͯ (σʔλͷߋ৽) PostgreSQLهܕΞʔΩςΫνϟΛ࠾༻͍ͯ͠·͢ɻ هܕΞʔΩςΫνϟɺچϨίʔυΛআͤͣʹ৽ϨίʔυΛՃ͢ΔํࣜͰ͢ɻ ςʔϒϧ Ϩίʔυ1 Ϩίʔυ2 Ϩίʔυ3 ςʔϒϧ Ϩίʔυ1
Ϩίʔυ2 Ϩίʔυ3 Ϩίʔυ2 Ϩίʔυ2Λߋ৽ چϨίʔυʹআ ϑϥάΛཱͯΔ ৽ϨίʔυΛ Ճ͢Δ -
ςʔϒϧ PostgreSQLͷ෦ߏʹ͍ͭͯ (HOTͱFILLFACTOR) ߋ৽Λߦ͏ͱɺΠϯσοΫε͕֨ೲ͍ͯ͠ΔΞυϨεมΘͬͯ͠·͍·͢ɻ ߋ৽ͷͨͼʹΠϯσοΫεมߋΛߦ͏ͱɺஶ͍͠ੑೳྼԽ͕ى͜Γ·͢ɻ HOT(Heap Only Tuple)ɺΠϯσοΫε͕ுΒΕ͍ͯͳ͍ΧϥϜΛߋ৽͢Δ࣌ͷߴԽٕज़ͱͳΓ·͢ɻ FILLFACTORɺϖʔδͷۭ͖ྖҬΛཧ͢ΔػೳٴͼύϥϝʔλʔͰ͢ɻ σϑΥϧτɺςʔϒϧͷ߹"100%"(ۭ͖ແ͠)ɺΠϯσοΫεͷ߹"90%"ͱͳ͍ͬͯ·͢ɻ
ςʔϒϧ Ϩίʔυ1 Ϩίʔυ2 ΠϯσοΫε Ϩίʔυ2 ΠϯσοΫε ߋ৽ • HOT͕ແޮͳ߹ Ϩίʔυ1 Ϩίʔυ2 ΠϯσοΫε Ϩίʔυ2 ΠϯσοΫεߋ৽ ͤͣɺ৽Ϩίʔυͷ ΞυϨεΛՃ • HOT͕༗ޮͳ߹ -
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ίϚϯυɺͦͷ΄͔ͷπʔϧͰ֬ೝΛߦ͏ඞཁ͕͋Γ·͢ɻ -
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ʹؔ͢Δ౷ܭใΛࣔ͠·͢ɻ -
PostgreSQLͷ౷ܭใ (ੑೳྼԽΛࣄલʹݕ͢ΔͨΊͷ౷ܭใ) લड़ͷVIEWΛΈ߹ΘͤΔ͜ͱͰɺϞχλϦϯάʹඞཁͳใΛऔಘ͢Δ͜ͱ͕Ͱ͖·͢ɻ ҎԼͷใΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ ʲࢀߟαΠτʳLet’s Postgres Քಈ౷ܭใΛ׆༻͠Α͏ https://lets.postgresql.jp/documents/technical/statistics DB୯ҐͷίϛοτϩʔϧόοΫ DBςʔϒϧɺΠϯσοΫε୯ҐͷI/Oൃੜঢ়گ
DBςʔϒϧʹରͯ͠ૠೖ/ߋ৽/আ͞Εͨߦ ςʔϒϧ୯ҐͰ࣮ࢪ͞ΕͨදεΩϟϯճ DBςʔϒϧɺΠϯσοΫε୯ҐͰεΩϟϯ͞Εͨߦ ςʔϒϧ୯ҐͷߦɺΨϕʔδྔ ݱࡏ࣮ࢪதͷSQLϝϯςφϯεॲཧ ౷ܭใίϨΫλɺ”ݱࡏͷঢ়ଶ”͔͠ݟΔ͜ͱͰ͖·ͤΜɻ ϞχλϦϯάΛਖ਼͘͠ߦ͏ͨΊʹɺաڈͷঢ়ଶΛऔΓଓ͚ɺൺֱͰ͖Δڥ͕ඞཁͱͳΓ·͢ɻ -
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); -
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͕ෆ͍ͯ͠·͢ɻ -
PostgreSQLͷ౷ܭใ (σʔλϕʔεঢ়ଶ) σʔλϕʔεঢ়ଶɺpg_stat_databaseϏϡʔͰ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ ओʹݟΔ͖ΧϥϜҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ datname όοΫΤϯυϓϩηε͕ଓͨ͠σʔλϕʔε໊Ͱ͢ɻ xact_commit ίϛοτ͞ΕͨྦྷܭͷτϥϯβΫγϣϯͰ͢ɻ
xact_rollback ϩʔϧόοΫ͞ΕͨྦྷܭͷτϥϯβΫγϣϯͰ͢ɻ temp_files ͍߹ΘͤʹΑͬͯॻ͖ग़͞ΕͨྦྷܭͷҰ࣌ϑΝΠϧͰ͢ɻ temp_bytes ͍߹ΘͤʹΑͬͯॻ͖ग़͞ΕͨྦྷܭͷҰ࣌ϑΝΠϧαΠζͰ͢ɻ deadlocks ݕ͞ΕͨྦྷܭͷσουϩοΫͰ͢ɻ pg_stat_databaseϏϡʔɺϦηοτ͕͔͔Δ·ͰྦྷܭΛฦ͠·͢ɻ ্هΧϥϜΛ֬ೝ͢Δ͜ͱͰɺҎԼͷใ͕Θ͔Γ·͢ɻ • σʔλϕʔεͷ༻ঢ়گ • ੑೳτϥϒϧͷ༗ແ (Ұ࣌ϑΝΠϧͷେྔੜɾେنͳҰ࣌ϑΝΠϧͷੜ) • σʔλϕʔεΛ༻͍ͯ͠ΔγεςϜͷෆ۩߹ ͪͳΈʹɺҎԼͷΫΤϦͰσʔλϕʔεͷαΠζΛऔಘ͢Δ͜ͱ͕ՄೳͰ͢ɻ =# SELECT pg_size_pretty(pg_database_size(‘σʔλϕʔε໊')); -
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 -
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 ςʔϒϧͷόοϑΝώοτͰ͢ɻ -
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 -
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ͷ߹ɺΠϯσοΫε͕શ͘༻͞Ε͍ͯ·ͤΜɻ আରͱͯ͠ݕ౼͢Δ͜ͱ͕Ͱ͖·͢ɻ -
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 -
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 -
ϞχλϦϯάͰศརͳ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Λࢹ͢ΔͨΊͷϓϥάΠϯͰ͢ɻ -
ϞχλϦϯάͰศརͳ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; Λ࣮ߦ͠·͢ɻ -
ϞχλϦϯάͰศརͳ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 -
ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stat_statements) pg_stat_statementsϏϡʔͰओʹݟΔ͖ΧϥϜҎԼͷ௨ΓͰ͢ɻ ΧϥϜ໊ ղઆ query ࣮ߦ͞ΕͨΫΤϦͰ͢ɻ calls ࣮ߦճͰ͢ɻ total_time
ΫΤϦ࣮ߦʹඅͨ͠૯࣌ؒͰ͢ɻ mean_time ΫΤϦ࣮ߦʹඅͨ͠ฏۉ࣌ؒͰ͢ɻ rows ΫΤϦ࣮ߦʹΑͬͯऔಘ͞ΕͨɾӨڹΛड͚ͨߦͷ૯Ͱ͢ɻ shared_blks_hit ΫΤϦ࣮ߦʹΑͬͯώοτͨ͠ڞ༗ϒϩοΫΩϟογϡͷ૯Ͱ͢ɻ shared_blks_read ΫΤϦ࣮ߦʹΑͬͯಡΈࠐ·Εͨڞ༗ϒϩοΫͷ૯Ͱ͢ɻ -
ϞχλϦϯάͰศརͳ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; -
ϞχλϦϯάͰศརͳ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; Λ࣮ߦ͠·͢ɻ -
ϞχλϦϯάͰศརͳ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/ -
ϞχλϦϯάͰศརͳExtensionͨͪ (pg_dbms_stats) pg_dbms_statsͱ pg_dbms_statsANALYZE͕ऩू͢Δ౷ܭใΛཧ͢Δ֦ுػೳͰ͢ɻ ౷ܭใͷ࠷৽ԽݻఆԽΛߦ͏͜ͱ͕Ͱ͖ΔͨΊɺ࣮ߦϓϥϯͷ੍ޚ͕ՄೳͱͳΓ·͢ɻ NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ pg_dbms_statsͷΠϯετʔϧɾ༗ޮԽ ެࣜαΠτͰrpm·ͨιʔείʔυ͕ެ։͞Ε͍ͯ·͢ɻ ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ༻͢Δ͜ͱ͕Ͱ͖·͢ɻ ༗ޮԽҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
1. postgresql.confϑΝΠϧͷshared_preload_librariesύϥϝʔλʔʹ ’pg_dbms_stats’ ΛՃ͠·͢ɻ 2. ޙPostgreSQLΛ࠶ىಈ͠·͢ɻ 3. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ CREATE EXTENSION pg_dbms_stats; Λ࣮ߦ͠·͢ɻ -
ϞχλϦϯάͰศརͳ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('εΩʔϚ໊', 'ςʔϒϧ໊'); • ౷ܭใͷݻఆղআ -
ϞχλϦϯάͰศརͳ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; Λ࣮ߦ͠·͢ɻ -
ϞχλϦϯάͰศརͳExtensionͨͪ (pg_hint_plan) pg_hint_planͷ͍ํ ώϯτ۟ΛదԠ͍ͨ͠ΫΤϦͷઌ಄·్ͨதͷSQLϒϩοΫίϝϯτͷதʹهड़͠·͢ɻ ώϯτ۟༻ͷϒϩοΫίϝϯτɺ/*+ͱͳΓ·͢ɻ ࣍ϖʔδʹώϯτ۟ΛదԠͨ͠ྫΛهࡌ͠·͢ɻ pg_hint_planͷҙ pg_hint_planɺΦϓςΟϚΠβʔ͕౷ܭใ͔Βܾఆ࣮ͨ͠ߦܭըΛมߋ͢ΔͨΊɺσʔλͷಛੑ͕ มΘͬͨࡍʹదͳ࣮ߦܭըͰ࣮ߦ͞Εͳ͍ڪΕ͕͋Γ·͢ɻ ώϯτ۟Λ༻͢Δ߹ɺमਖ਼ํ๏ͷݕ౼Λߦ͔ͬͯΒ༻͢ΔΑ͏ʹ͠·͠ΐ͏ɻ
ৄࡉެࣜαΠτΛࢀর͍ͯͩ͘͠͞ɻ https://ja.osdn.net/projects/pghintplan/ -
ϞχλϦϯάͰศརͳ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ςʔϒϧΛɺ ΠϯσοΫεΛ༻ͤͣʹ͍߹Θͤ͢Δɻ -
ϞχλϦϯάͰศརͳ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ςʔϒϧͷ݁߹Λϋογϡ݁ ߹ͱ͢Δɻ -
ϞχλϦϯάͰศརͳ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ʹΜ͜͏͢Δ ͜ͱͰɺΠϯσοΫεΛ༻͍͘͢͢Δɻ -
ϞχλϦϯάͰศརͳ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; Λ࣮ߦ͠·͢ɻ -
ϞχλϦϯάͰศརͳExtensionͨͪ (pg_statsinfo) pg_statsinfoͷ͍ํ pg_stasinfoPostgreSQLΛىಈ͢Δͱಉ࣌ʹಈ࡞͠·͢ɻ 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 -
ϞχλϦϯάͰศརͳExtensionͨͪ (pg_repack) pg_repackͱ pg_repackංେԽͨ͠ςʔϒϧΠϯσοΫεΛ࠶ฤ͢Δ֦ுػೳͰ͢ɻ ࣮ߦύϥϝʔλʔʹΑΓɺσʔλͷฒͼସ͑Λมߋ͢Δ͜ͱՄೳͰ͢ɻ NTT͕։ൃͨ͠ϞδϡʔϧͰɺαʔυύʔςΟͷ֦ுػೳͱͳΓ·͢ɻ pg_repackͷΠϯετʔϧɾ༗ޮԽ PGDGϨϙδτϦͰrpm͕ఏڙ͞Ε͍ͯ·͢ɻ ڥʹ͋ͬͨΠϯετʔϧΛͨ͠ޙɺExtensionͷ༗ޮԽΛߦ͏͜ͱͰ༻͢Δ͜ͱ͕Ͱ͖·͢ɻ ༗ޮԽҎԼͷखॱΛ࣮ߦ͢Δඞཁ͕͋Γ·͢ɻ
1. ༗ޮԽ͍ͨ͠σʔλϕʔεͰ CREATE EXTENSION pg_repack; Λ࣮ߦ͠·͢ɻ -
ϞχλϦϯάͰศརͳ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 σʔλϕʔε໊ -
ϞχλϦϯάͰศརͳ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' . -
ϞχλϦϯάͰศརͳ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 -
ϞχλϦϯάͰศརͳExtensionͨͪ (pg_stats_reporter) pg_stats_reporterͱ pg_stats_reporterpg_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 -
ϞχλϦϯάͰศརͳ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 ಈ࡞֬ೝ༻ɻ ਖ਼͕͘͠औಘͰ͖͍ͯΔ͔֬ೝ͢Δɻ ಈ࡞֬ೝͨ͠Λೖྗ͢Δɻ