PostgreSQL - maintenance -

Transcript

1. PostgreSQL ~ςʔϒϧϝϯςφϯε~
2. Situa&on
3. @ςʔϒϧʹෆཁྖҬ͕େྔʹ࢒͍ͬͯΔέʔε PostgreSQLͷσʔλߏ଄͸௥هܕɻ ඇৗʹγϯϓϧʹMVCCϞσϧΛ࣮ݱ͍ͯ͠Δ͕ɺෆཁྖҬΛఆ ظతʹ࡟আ͢ΔͨΊͷॲཧΛ࣮ߦ͢Δඞཁ͕͋Δɻ ͦΕ͕ɺVACUUMॲཧ...!
4. @͓͞Β͍(MySQL(+(InnoDBͷ৔߹)(1/2 ෳ਺ςʔϒϧͷϨίʔυσʔλฒͼʹΠϯσοΫεσʔλΛ·ͱ ΊͯɺςʔϒϧεϖʔεͱݺͿσʔλϑΝΠϧ಺ʹ֨ೲ͢Δߏ ੒ɻ Αͬͯςʔϒϧ಺ͷϨίʔυ݅਺͕૿Ճ͢Δͱɺςʔϒϧεϖʔε ༻ͷσʔλϑΝΠϧͷ༰ྔ͕૿Ճɻ ͨͩ͠ɺϨίʔυ݅਺͕૿Ճ͢ΔͨͼʹϑΝΠϧαΠζ͕ঃʑʹ େ͖͘ͳΔ࢓૊ΈͰ͸ͳ͍ɻ
5. @͓͞Β͍(MySQL(+(InnoDBͷ৔߹)(2/2 ઃఆͨ͠ҰఆαΠζͷϑΝΠϧΛ࡞੒͓͖ͯ͠ɺ༰ྔ͕ෆ଍ͨ͠ ࣌ʹ͸͡ΊͯϑΝΠϧαΠζΛେ͖͘͢Δ࢓૊Έʹͳ͍ͬͯΔɻ ͨͩ͠ɺϨίʔυ݅਺͕૿Ճ͢ΔͨͼʹϑΝΠϧαΠζ͕ঃʑʹ େ͖͘ͳΔ࢓૊ΈͰ͸ͳ͘ɺ ઃఆͨ͠ҰఆαΠζͷϑΝΠϧΛ࡞੒͓͖ͯ͠ɺ༰ྔ͕ෆ଍ͨ͠ ࣌ʹ͸͡ΊͯϑΝΠϧαΠζΛେ͖͘͢Δ࢓૊Έɻ (MyISAM͸ςʔϒϧ୯ҐͷϑΝΠϧͱ͍͏ҙຯͰগ͠ࣅͯΔ)
6. VACCUMॲཧ
7. @What's(that? • ෆཁྖҬͷ࠶ར༻ • τϥϯβΫγϣϯIDपճ໰୊ͷճආ
8. ෆཁྖҬͷ࠶ར༻
9. @VACCUMͷ಺෦ॲཧɹ1/2 1. ֤ςʔϒϧͷϖʔδΛઌ಄͔Β૸ࠪ 2. VMΛνΣοΫͯ͠ෆཁߦΛؚΉϖʔδͳΒ3.ʹɺෆཁߦ͕ͳ͚ Ε͹࣍ͷϖʔδΛ૸ࠪ ※VM(Visibility*Map)*.*ςʔϒϧͷՄࢹੑͷ൑அʹར༻͢Δิॿ σʔλɻςʔϒϧ̍ϖʔδͷঢ়ଶΛ1bitͰ؅ཧ 3. ର৅ϖʔδͷશߦΛ૸ࠪ͠ɺෆཁߦͷ৘ใΛநग़
10. @VACCUMͷ಺෦ॲཧɹ2/2 4"શϖʔδ૸ࠪޙɺෆཁߦ͕நग़͞Ε͍ͯΕ͹ର৅ςʔϒϧͷΠ ϯσοΫεϝϯςφϯεΛߦ͍ɻෆཁߦͷ࡟আ 5"࡟আͨ͠ߦͷ৘ใΛ΋ͱʹFSMΛߋ৽
11. @VMͱFSMʹΑΔԸܙ • VMͷ৘ใΛ΋ͱʹॲཧΛߦ͏΂͖ϖʔδΛߜΔͷͰɺෛՙͷ ܰݮ͕ظ଴͞ΕΔ • FSMʹΑͬͯɺߦ͕ޮ཰Α͘࠶ར༻͞ΕΔΑ͏ʹͳΔɻ͜Εʹ Αͬͯςʔϒϧͷ෺ཧతͳංେԽΛ཈͑ɺσʔλϕʔεͷύϑ ΥʔϚϯεΛҡ࣋Ͱ͖Δ
12. XIDपճ໰୊ͷճආ
13. @What's(that? • ςʔϒϧʹσʔλΛ֨ೲ͢Δࡍɺ࣮ߦ͞ΕͨτϥϯβΫγϣϯ Λ۠ผͰ͖ΔΑ͏ʹɺXID͕ߦϔομ(xmin)ͱͯ֨͠ೲ͞ΕΔ • ࣮ߦதͷτϥϯβΫγϣϯ͸ɺࣗ਎͕࣋ͭXIDͱߦϔομͷ XID(xmin)ͷൺֱΛ͠ɺՄࢹorෆՄࢹͷ൑அΛ͢Δ࢓૊Έ
14. @XIDपճ໰୊͕ى͜Δέʔε XID͸32bit(=໿40ԯ)Ͱ؅ཧ͞Ε͍ͯΔͷͰɺपճΛ܁Γฦ͢ɻ पճͨ͠τϥϯβΫγϣϯIDΛ࣋ͭτϥϯβΫγϣϯ͔Βطଘͷ σʔλΛݟΔͱɺՄࢹ൑ఆʹΑΓશͯͷσʔλ͕ݟ͑ͳ͘ͳΔݱ ৅͕ى͜Δɻ
15. ҙਤతʹϝϯςφϯεʁ ͳΜ͔ɺ໘౗ͳΠϝʔδ...
16. ͔͠͠ʂ
17. PostgreSQL+8.3Ҏ͔߱ΒɺVACCUM ϝϯςφϯε͸ࣗಈԽʂͨͩ͠...
18. @Demerit • ࣗಈόΩϡʔϜͷઃఆ͚ͩͰ͸׬શʹ੍ޚͰ͖ͳ͍ɻςʔϒϧ ͕ංେԽ͠ɺੑೳʹӨڹΛٴ΅͢Մೳੑ͋Γ ※ࣗಈόΩϡʔϜ͸ɺALTER&TABLEͰର৅֎ʹͰ͖Δɻ ʢςʔϒϧΦϓγϣϯมߋʣ
19. VACCUM&FULL
20. @What's(that? VACCUMʹΑΔϝϯςφϯε͕૝ఆ௨Γʹػೳ͠ͳ͔ͬͨ ৔߹ͷରॲࡦɻ (VACCUMͱVACCUM'FULL͸ผ෺ͳͷͰ஫ҙ) LongTransac*on͕ଘࡏ͍ͯ͠Δ৔߹ɺVACCUM͸ػೳ͠ͳ͍ͷ Ͱɺͦ͏͍͏࣌ʹ͜ΕΛ࢖༻ɻ ※LongTransac*onʹ͍ͭͯ͸ɺp211ࢀর
21. @important*point • VACUUM'FULL͸ɺ࣮ߦத͸ഉଞϩοΫɻ
22. @Summary • VACCUM͸େࣄ • VACCUM͕༗ޮʹͳΒͳ͍έʔε͕͋ΔࣄΛཧղ͠ɺͦͷ্Ͱ ରॲࡦΛݕ౼͢Δඞཁ͋Γ • جຊతʹ͸ɺࣗಈόΩϡʔϜorఆظతͳखಈόΩϡʔϜΛਖ਼ৗ ʹߦ͑͹ɺ໰୊͸ͳ͍
23. PostgreSQL ~indexϝϯςφϯε~
24. @ΠϯσοΫε΁ͷΞΫηεੑೳ͕௿Լ͢Δ৔߹ͷݪҼ • ංେԽ • அยԽ • Ϋϥελੑͷܽམ
25. @ංେԽʹ͍ͭͯ • indexϑΝΠϧ͕ංେԽ͢ΔͱɺςʔϒϧͷංେԽͱൺྫͯ͠༗ ޮσʔλ͕গྔͰ΋ଟ͘ͷϖʔδ͕ར༻͞ΕΔ • ͦΕʹΑͬͯແବͳI/O͕ൃߦ͞ΕΔͷͰੑೳͷ௿Լʹͭͳ͕ Δ • ܏޲͸ɺpg_classͷrelpagesྻɺreltuplesྻͰ֬ೝ͢Δ͜ͱ͕Ͱ ͖Δ
26. @ංେԽͷ༧๷ࡦ ΍͸ΓVACCUMͷग़൪ɻ VACCUMʹΑΓ׬શʹۭͱͳΓɺ࠶ར༻Մೳͱͳͬͨindexϖʔ δ͸࠶ར༻͞ΕɺංେԽΛ๷͙ɻ ఆظతʹVACCUN͕ػೳ͍ͯ͠Ε͹ɺ͋·Γҙࣝ͢Δඞཁ͸ແ͍
27. @அยԽʹ͍ͭͯ • B#treeΠϯσοΫεݻ༗ͷࣄ৅ • Ωϟογϡώοτޮ཰͕ѱԽ͠ɺੑೳʹӨڹΛٴ΅͢Մೳੑ͕ ͋Δ • ܏޲͸ɺcontribϞδϡʔϧͷpgsta0upleʹؚ·ΕΔpgstaindex ؔ਺Ͱ֬ೝ͢Δɻʢleaf_fragmenta8onΛݟΔʣ ※͜ͷௐࠪํ๏͸ɺൺֱతαʔό΁ͷෛՙ͕ߴ͍ͷͰɺߟ͑ͯ ࢖༻͢Δ͜ͱ
28. @அยԽͷ༧๷ࡦ ࣗಈόΩϡʔϜͰ΋ɺશͯͷindex͕͖Ε͍ʹ࠶ར༻Ͱ͖Δঢ়گ͕ ଓ͘ͱ͸ݶΒͳ͍ɻ அยԽʹΑΓindexϑΝΠϧͷαΠζ͕૿Ճ͢Δ৔߹͸ɺ REINDEXʹΑΔindexͷ࠶ఆٛΛߦ͏ɻ
29. @Ϋϥελੑͷܽམʹ͍ͭͯ • ӡ༻͍ͯ͠Δؒʹςʔϒϧσʔλͷ෺ཧతͳ഑ஔॱং͕ɺසൟ ʹར༻͞ΕΔindexͷฒͼॱͱဃ཭͍ͯ͠Δঢ়ଶʹͳΔ͜ͱ • ΠϯσοΫεεΩϟϯΛߦͬͯ΋ɺඞཁͳσʔλΛऔಘ͢Δͨ Ίʹෳ਺ͷϖʔδΛࢀর͠ͳ͚Ε͹ͳΒͳ͍ͷͰɺI/O͕૿Ճ ͠ੑೳʹӨڹΛٴ΅͢ • ܏޲͸ɺpg_statsϏϡʔͷcorrela1onΛ֬ೝ͢Δ ʢp2185ද1471ࢀরʣ
30. @Ϋϥελੑͷܽམͷ༧๷ࡦ CLUSTERΛߦ͏ɻ ࣮ߦ࣌ʹ͸ɺVACCUM+FULLͱಉ༷ʹ࣍ͷ఺ʹ஫ҙ • Ұ࣌తʹର৅ςʔϒϧ/indexͱಉఔ౓ͷ༰ྔ͕ඞཁʹͳΔ • CLUSTER࣮ߦ࣌ʹ΋ഉଞϩοΫΛऔಘ͢Δ ※CLUSTER࣮ߦ࣌ʹ͸REINDEX΋࣮ࢪ͞ΕΔͷͰɺஅยԽͱΫϥ ελੑͷվળΛಉ࣌ʹ࣮ࢪ͍ͨ͠৔߹͸CLUSTERͷΈ࣮ࢪ͢Ε͹ ໰୊ແ͍
31. Index&Only&Scan
32. @What's(that? • Index'Only'Scan͸ɺindexͷΈΛݕࡧͯ݁͠ՌΛฦ٫͢Δ࢓૊Έ • ςʔϒϧσʔλͰ͸ͳ͘ɺVMΛ֬ೝ͢ΔܗͰ࣮ݱ͞Ε͍ͯΔ • PostgreSQL9.2͔Βಋೖ͞Εͨ
33. @important*point • ಛఆͷ৚݅Ͱ͔͠ޮՌ͕ൃشͰ͖ͳ͍ͷͰɺ࢖༻͢Δ͜ͱࣗମ ೉͍͠ʢৄࡉ͸p223ࢀরʣ • VMΛߋ৽ͨ͠ΓɺՄೳͳݶΓVACCUM͕࣮ߦ͞ΕΔΑ͏ͳ޻ ෉Λ͢Δඞཁ͕͋Δ
34. @Summary • ӡ༻Λଓ͚Δ͜ͱͰindexʹى͜Δ໰୊Λཧղ͢Δ • Ұ࿈ͷྲྀΕͱͯ͠͸ɺVACUUMʹΑΔ࠶ར༻ΛޮՌతʹߦ͍ɺ ੑೳ΁ͷӨڹ͕ஶ͍࣌͠ʹϝϯςφϯεظؒΛઃ͚ͯɺ REINDEXͱCLUSTERͰindexΛϝϯςφϯε͢Δ
35. Thank&you!