PostgreSQL - maintenance -

PostgreSQL ~ςʔϒϧϝϯςφϯε~

Situa&on

@ςʔϒϧʹෆཁྖҬ͕େྔʹ࢒͍ͬͯΔέʔε PostgreSQLͷσʔλߏ଄͸௥هܕɻ ඇৗʹγϯϓϧʹMVCCϞσϧΛ࣮ݱ͍ͯ͠Δ͕ɺෆཁྖҬΛఆ ظతʹ࡟আ͢ΔͨΊͷॲཧΛ࣮ߦ͢Δඞཁ͕͋Δɻ ͦΕ͕ɺVACUUMॲཧ...!

@͓͞Β͍(MySQL(+(InnoDBͷ৔߹)(1/2 ෳ਺ςʔϒϧͷϨίʔυσʔλฒͼʹΠϯσοΫεσʔλΛ·ͱ ΊͯɺςʔϒϧεϖʔεͱݺͿσʔλϑΝΠϧ಺ʹ֨ೲ͢Δߏ ੒ɻ Αͬͯςʔϒϧ಺ͷϨίʔυ݅਺͕૿Ճ͢Δͱɺςʔϒϧεϖʔε ༻ͷσʔλϑΝΠϧͷ༰ྔ͕૿Ճɻ ͨͩ͠ɺϨίʔυ݅਺͕૿Ճ͢ΔͨͼʹϑΝΠϧαΠζ͕ঃʑʹ େ͖͘ͳΔ࢓૊ΈͰ͸ͳ͍ɻ

@͓͞Β͍(MySQL(+(InnoDBͷ৔߹)(2/2 ઃఆͨ͠ҰఆαΠζͷϑΝΠϧΛ࡞੒͓͖ͯ͠ɺ༰ྔ͕ෆ଍ͨ͠ ࣌ʹ͸͡ΊͯϑΝΠϧαΠζΛେ͖͘͢Δ࢓૊Έʹͳ͍ͬͯΔɻ ͨͩ͠ɺϨίʔυ݅਺͕૿Ճ͢ΔͨͼʹϑΝΠϧαΠζ͕ঃʑʹ େ͖͘ͳΔ࢓૊ΈͰ͸ͳ͘ɺ ઃఆͨ͠ҰఆαΠζͷϑΝΠϧΛ࡞੒͓͖ͯ͠ɺ༰ྔ͕ෆ଍ͨ͠ ࣌ʹ͸͡ΊͯϑΝΠϧαΠζΛେ͖͘͢Δ࢓૊Έɻ (MyISAM͸ςʔϒϧ୯ҐͷϑΝΠϧͱ͍͏ҙຯͰগ͠ࣅͯΔ)

VACCUMॲཧ

@What's(that? • ෆཁྖҬͷ࠶ར༻ • τϥϯβΫγϣϯIDपճ໰୊ͷճආ

ෆཁྖҬͷ࠶ར༻

@VACCUMͷ಺෦ॲཧɹ1/2 1. ֤ςʔϒϧͷϖʔδΛઌ಄͔Β૸ࠪ 2. VMΛνΣοΫͯ͠ෆཁߦΛؚΉϖʔδͳΒ3.ʹɺෆཁߦ͕ͳ͚ Ε͹࣍ͷϖʔδΛ૸ࠪ ※VM(Visibility*Map)*.*ςʔϒϧͷՄࢹੑͷ൑அʹར༻͢Δิॿ σʔλɻςʔϒϧ̍ϖʔδͷঢ়ଶΛ1bitͰ؅ཧ 3. ର৅ϖʔδͷશߦΛ૸ࠪ͠ɺෆཁߦͷ৘ใΛநग़

@VACCUMͷ಺෦ॲཧɹ2/2 4"શϖʔδ૸ࠪޙɺෆཁߦ͕நग़͞Ε͍ͯΕ͹ର৅ςʔϒϧͷΠ ϯσοΫεϝϯςφϯεΛߦ͍ɻෆཁߦͷ࡟আ 5"࡟আͨ͠ߦͷ৘ใΛ΋ͱʹFSMΛߋ৽

@VMͱFSMʹΑΔԸܙ • VMͷ৘ใΛ΋ͱʹॲཧΛߦ͏΂͖ϖʔδΛߜΔͷͰɺෛՙͷ ܰݮ͕ظ଴͞ΕΔ • FSMʹΑͬͯɺߦ͕ޮ཰Α͘࠶ར༻͞ΕΔΑ͏ʹͳΔɻ͜Εʹ Αͬͯςʔϒϧͷ෺ཧతͳංେԽΛ཈͑ɺσʔλϕʔεͷύϑ ΥʔϚϯεΛҡ࣋Ͱ͖Δ

XIDपճ໰୊ͷճආ

@What's(that? • ςʔϒϧʹσʔλΛ֨ೲ͢Δࡍɺ࣮ߦ͞ΕͨτϥϯβΫγϣϯ Λ۠ผͰ͖ΔΑ͏ʹɺXID͕ߦϔομ(xmin)ͱͯ֨͠ೲ͞ΕΔ • ࣮ߦதͷτϥϯβΫγϣϯ͸ɺࣗ਎͕࣋ͭXIDͱߦϔομͷ XID(xmin)ͷൺֱΛ͠ɺՄࢹorෆՄࢹͷ൑அΛ͢Δ࢓૊Έ

@XIDपճ໰୊͕ى͜Δέʔε XID͸32bit(=໿40ԯ)Ͱ؅ཧ͞Ε͍ͯΔͷͰɺपճΛ܁Γฦ͢ɻ पճͨ͠τϥϯβΫγϣϯIDΛ࣋ͭτϥϯβΫγϣϯ͔Βطଘͷ σʔλΛݟΔͱɺՄࢹ൑ఆʹΑΓશͯͷσʔλ͕ݟ͑ͳ͘ͳΔݱ ৅͕ى͜Δɻ

ҙਤతʹϝϯςφϯεʁ ͳΜ͔ɺ໘౗ͳΠϝʔδ...

͔͠͠ʂ

PostgreSQL+8.3Ҏ͔߱ΒɺVACCUM ϝϯςφϯε͸ࣗಈԽʂͨͩ͠...

@Demerit • ࣗಈόΩϡʔϜͷઃఆ͚ͩͰ͸׬શʹ੍ޚͰ͖ͳ͍ɻςʔϒϧ ͕ංେԽ͠ɺੑೳʹӨڹΛٴ΅͢Մೳੑ͋Γ ※ࣗಈόΩϡʔϜ͸ɺALTER&TABLEͰର৅֎ʹͰ͖Δɻ ʢςʔϒϧΦϓγϣϯมߋʣ

VACCUM&FULL

@What's(that? VACCUMʹΑΔϝϯςφϯε͕૝ఆ௨Γʹػೳ͠ͳ͔ͬͨ ৔߹ͷରॲࡦɻ (VACCUMͱVACCUM'FULL͸ผ෺ͳͷͰ஫ҙ) LongTransac*on͕ଘࡏ͍ͯ͠Δ৔߹ɺVACCUM͸ػೳ͠ͳ͍ͷ Ͱɺͦ͏͍͏࣌ʹ͜ΕΛ࢖༻ɻ ※LongTransac*onʹ͍ͭͯ͸ɺp211ࢀর

@important*point • VACUUM'FULL͸ɺ࣮ߦத͸ഉଞϩοΫɻ

@Summary • VACCUM͸େࣄ • VACCUM͕༗ޮʹͳΒͳ͍έʔε͕͋ΔࣄΛཧղ͠ɺͦͷ্Ͱ ରॲࡦΛݕ౼͢Δඞཁ͋Γ • جຊతʹ͸ɺࣗಈόΩϡʔϜorఆظతͳखಈόΩϡʔϜΛਖ਼ৗ ʹߦ͑͹ɺ໰୊͸ͳ͍

PostgreSQL ~indexϝϯςφϯε~

@ΠϯσοΫε΁ͷΞΫηεੑೳ͕௿Լ͢Δ৔߹ͷݪҼ • ංେԽ • அยԽ • Ϋϥελੑͷܽམ

@ංେԽʹ͍ͭͯ • indexϑΝΠϧ͕ංେԽ͢ΔͱɺςʔϒϧͷංେԽͱൺྫͯ͠༗ ޮσʔλ͕গྔͰ΋ଟ͘ͷϖʔδ͕ར༻͞ΕΔ • ͦΕʹΑͬͯແବͳI/O͕ൃߦ͞ΕΔͷͰੑೳͷ௿Լʹͭͳ͕ Δ • ܏޲͸ɺpg_classͷrelpagesྻɺreltuplesྻͰ֬ೝ͢Δ͜ͱ͕Ͱ ͖Δ

@ංେԽͷ༧๷ࡦ ΍͸ΓVACCUMͷग़൪ɻ VACCUMʹΑΓ׬શʹۭͱͳΓɺ࠶ར༻Մೳͱͳͬͨindexϖʔ δ͸࠶ར༻͞ΕɺංେԽΛ๷͙ɻ ఆظతʹVACCUN͕ػೳ͍ͯ͠Ε͹ɺ͋·Γҙࣝ͢Δඞཁ͸ແ͍

@அยԽʹ͍ͭͯ • B#treeΠϯσοΫεݻ༗ͷࣄ৅ • Ωϟογϡώοτޮ཰͕ѱԽ͠ɺੑೳʹӨڹΛٴ΅͢Մೳੑ͕ ͋Δ • ܏޲͸ɺcontribϞδϡʔϧͷpgsta0upleʹؚ·ΕΔpgstaindex ؔ਺Ͱ֬ೝ͢Δɻʢleaf_fragmenta8onΛݟΔʣ ※͜ͷௐࠪํ๏͸ɺൺֱతαʔό΁ͷෛՙ͕ߴ͍ͷͰɺߟ͑ͯ
࢖༻͢Δ͜ͱ

@அยԽͷ༧๷ࡦ ࣗಈόΩϡʔϜͰ΋ɺશͯͷindex͕͖Ε͍ʹ࠶ར༻Ͱ͖Δঢ়گ͕ ଓ͘ͱ͸ݶΒͳ͍ɻ அยԽʹΑΓindexϑΝΠϧͷαΠζ͕૿Ճ͢Δ৔߹͸ɺ REINDEXʹΑΔindexͷ࠶ఆٛΛߦ͏ɻ

@Ϋϥελੑͷܽམʹ͍ͭͯ • ӡ༻͍ͯ͠Δؒʹςʔϒϧσʔλͷ෺ཧతͳ഑ஔॱং͕ɺසൟ ʹར༻͞ΕΔindexͷฒͼॱͱဃ཭͍ͯ͠Δঢ়ଶʹͳΔ͜ͱ • ΠϯσοΫεεΩϟϯΛߦͬͯ΋ɺඞཁͳσʔλΛऔಘ͢Δͨ Ίʹෳ਺ͷϖʔδΛࢀর͠ͳ͚Ε͹ͳΒͳ͍ͷͰɺI/O͕૿Ճ ͠ੑೳʹӨڹΛٴ΅͢ • ܏޲͸ɺpg_statsϏϡʔͷcorrela1onΛ֬ೝ͢Δ
ʢp2185ද1471ࢀরʣ

@Ϋϥελੑͷܽམͷ༧๷ࡦ CLUSTERΛߦ͏ɻ ࣮ߦ࣌ʹ͸ɺVACCUM+FULLͱಉ༷ʹ࣍ͷ఺ʹ஫ҙ • Ұ࣌తʹର৅ςʔϒϧ/indexͱಉఔ౓ͷ༰ྔ͕ඞཁʹͳΔ • CLUSTER࣮ߦ࣌ʹ΋ഉଞϩοΫΛऔಘ͢Δ ※CLUSTER࣮ߦ࣌ʹ͸REINDEX΋࣮ࢪ͞ΕΔͷͰɺஅยԽͱΫϥ ελੑͷվળΛಉ࣌ʹ࣮ࢪ͍ͨ͠৔߹͸CLUSTERͷΈ࣮ࢪ͢Ε͹ ໰୊ແ͍

Index&Only&Scan

@What's(that? • Index'Only'Scan͸ɺindexͷΈΛݕࡧͯ݁͠ՌΛฦ٫͢Δ࢓૊Έ • ςʔϒϧσʔλͰ͸ͳ͘ɺVMΛ֬ೝ͢ΔܗͰ࣮ݱ͞Ε͍ͯΔ • PostgreSQL9.2͔Βಋೖ͞Εͨ

@important*point • ಛఆͷ৚݅Ͱ͔͠ޮՌ͕ൃشͰ͖ͳ͍ͷͰɺ࢖༻͢Δ͜ͱࣗମ ೉͍͠ʢৄࡉ͸p223ࢀরʣ • VMΛߋ৽ͨ͠ΓɺՄೳͳݶΓVACCUM͕࣮ߦ͞ΕΔΑ͏ͳ޻ ෉Λ͢Δඞཁ͕͋Δ

@Summary • ӡ༻Λଓ͚Δ͜ͱͰindexʹى͜Δ໰୊Λཧղ͢Δ • Ұ࿈ͷྲྀΕͱͯ͠͸ɺVACUUMʹΑΔ࠶ར༻ΛޮՌతʹߦ͍ɺ ੑೳ΁ͷӨڹ͕ஶ͍࣌͠ʹϝϯςφϯεظؒΛઃ͚ͯɺ REINDEXͱCLUSTERͰindexΛϝϯςφϯε͢Δ

Thank&you!

PostgreSQL - maintenance -

PostgreSQL - maintenance -

rm-rf-slant

More Decks by rm-rf-slant

Other Decks in Technology

Featured

Transcript

PostgreSQL ~ςʔϒϧϝϯςφϯε~

Situa&on

@ςʔϒϧʹෆཁྖҬ͕େྔʹ࢒͍ͬͯΔέʔε PostgreSQLͷσʔλߏ଄͸௥هܕɻ ඇৗʹγϯϓϧʹMVCCϞσϧΛ࣮ݱ͍ͯ͠Δ͕ɺෆཁྖҬΛఆ ظతʹ࡟আ͢ΔͨΊͷॲཧΛ࣮ߦ͢Δඞཁ͕͋Δɻ ͦΕ͕ɺVACUUMॲཧ...!

VACCUMॲཧ

@What's(that? • ෆཁྖҬͷ࠶ར༻ • τϥϯβΫγϣϯIDपճ໰୊ͷճආ

ෆཁྖҬͷ࠶ར༻

@VACCUMͷ಺෦ॲཧɹ2/2 4"શϖʔδ૸ࠪޙɺෆཁߦ͕நग़͞Ε͍ͯΕ͹ର৅ςʔϒϧͷΠ ϯσοΫεϝϯςφϯεΛߦ͍ɻෆཁߦͷ࡟আ 5"࡟আͨ͠ߦͷ৘ใΛ΋ͱʹFSMΛߋ৽

@VMͱFSMʹΑΔԸܙ • VMͷ৘ใΛ΋ͱʹॲཧΛߦ͏΂͖ϖʔδΛߜΔͷͰɺෛՙͷ ܰݮ͕ظ଴͞ΕΔ • FSMʹΑͬͯɺߦ͕ޮ཰Α͘࠶ར༻͞ΕΔΑ͏ʹͳΔɻ͜Εʹ Αͬͯςʔϒϧͷ෺ཧతͳංେԽΛ཈͑ɺσʔλϕʔεͷύϑ ΥʔϚϯεΛҡ࣋Ͱ͖Δ

XIDपճ໰୊ͷճආ

@What's(that? • ςʔϒϧʹσʔλΛ֨ೲ͢Δࡍɺ࣮ߦ͞ΕͨτϥϯβΫγϣϯ Λ۠ผͰ͖ΔΑ͏ʹɺXID͕ߦϔομ(xmin)ͱͯ֨͠ೲ͞ΕΔ • ࣮ߦதͷτϥϯβΫγϣϯ͸ɺࣗ਎͕࣋ͭXIDͱߦϔομͷ XID(xmin)ͷൺֱΛ͠ɺՄࢹorෆՄࢹͷ൑அΛ͢Δ࢓૊Έ

@XIDपճ໰୊͕ى͜Δέʔε XID͸32bit(=໿40ԯ)Ͱ؅ཧ͞Ε͍ͯΔͷͰɺपճΛ܁Γฦ͢ɻ पճͨ͠τϥϯβΫγϣϯIDΛ࣋ͭτϥϯβΫγϣϯ͔Βطଘͷ σʔλΛݟΔͱɺՄࢹ൑ఆʹΑΓશͯͷσʔλ͕ݟ͑ͳ͘ͳΔݱ ৅͕ى͜Δɻ

ҙਤతʹϝϯςφϯεʁ ͳΜ͔ɺ໘౗ͳΠϝʔδ...

͔͠͠ʂ

PostgreSQL+8.3Ҏ͔߱ΒɺVACCUM ϝϯςφϯε͸ࣗಈԽʂͨͩ͠...

@Demerit • ࣗಈόΩϡʔϜͷઃఆ͚ͩͰ͸׬શʹ੍ޚͰ͖ͳ͍ɻςʔϒϧ ͕ංେԽ͠ɺੑೳʹӨڹΛٴ΅͢Մೳੑ͋Γ ※ࣗಈόΩϡʔϜ͸ɺALTER&TABLEͰର৅֎ʹͰ͖Δɻ ʢςʔϒϧΦϓγϣϯมߋʣ

VACCUM&FULL

@What's(that? VACCUMʹΑΔϝϯςφϯε͕૝ఆ௨Γʹػೳ͠ͳ͔ͬͨ ৔߹ͷରॲࡦɻ (VACCUMͱVACCUM'FULL͸ผ෺ͳͷͰ஫ҙ) LongTransacon͕ଘࡏ͍ͯ͠Δ৔߹ɺVACCUM͸ػೳ͠ͳ͍ͷ Ͱɺͦ͏͍͏࣌ʹ͜ΕΛ࢖༻ɻ ※LongTransaconʹ͍ͭͯ͸ɺp211ࢀর

@important*point • VACUUM'FULL͸ɺ࣮ߦத͸ഉଞϩοΫɻ

@Summary • VACCUM͸େࣄ • VACCUM͕༗ޮʹͳΒͳ͍έʔε͕͋ΔࣄΛཧղ͠ɺͦͷ্Ͱ ରॲࡦΛݕ౼͢Δඞཁ͋Γ • جຊతʹ͸ɺࣗಈόΩϡʔϜorఆظతͳखಈόΩϡʔϜΛਖ਼ৗ ʹߦ͑͹ɺ໰୊͸ͳ͍

PostgreSQL ~indexϝϯςφϯε~

@ΠϯσοΫε΁ͷΞΫηεੑೳ͕௿Լ͢Δ৔߹ͷݪҼ • ංେԽ • அยԽ • Ϋϥελੑͷܽམ

@ංେԽʹ͍ͭͯ • indexϑΝΠϧ͕ංେԽ͢ΔͱɺςʔϒϧͷංେԽͱൺྫͯ͠༗ ޮσʔλ͕গྔͰ΋ଟ͘ͷϖʔδ͕ར༻͞ΕΔ • ͦΕʹΑͬͯແବͳI/O͕ൃߦ͞ΕΔͷͰੑೳͷ௿Լʹͭͳ͕ Δ • ܏޲͸ɺpg_classͷrelpagesྻɺreltuplesྻͰ֬ೝ͢Δ͜ͱ͕Ͱ ͖Δ

@ංେԽͷ༧๷ࡦ ΍͸ΓVACCUMͷग़൪ɻ VACCUMʹΑΓ׬શʹۭͱͳΓɺ࠶ར༻Մೳͱͳͬͨindexϖʔ δ͸࠶ར༻͞ΕɺංେԽΛ๷͙ɻ ఆظతʹVACCUN͕ػೳ͍ͯ͠Ε͹ɺ͋·Γҙࣝ͢Δඞཁ͸ແ͍

@அยԽʹ͍ͭͯ • B#treeΠϯσοΫεݻ༗ͷࣄ৅ • Ωϟογϡώοτޮ཰͕ѱԽ͠ɺੑೳʹӨڹΛٴ΅͢Մೳੑ͕ ͋Δ • ܏޲͸ɺcontribϞδϡʔϧͷpgsta0upleʹؚ·ΕΔpgstaindex ؔ਺Ͱ֬ೝ͢Δɻʢleaf_fragmenta8onΛݟΔʣ ※͜ͷௐࠪํ๏͸ɺൺֱతαʔό΁ͷෛՙ͕ߴ͍ͷͰɺߟ͑ͯ

@அยԽͷ༧๷ࡦ ࣗಈόΩϡʔϜͰ΋ɺશͯͷindex͕͖Ε͍ʹ࠶ར༻Ͱ͖Δঢ়گ͕ ଓ͘ͱ͸ݶΒͳ͍ɻ அยԽʹΑΓindexϑΝΠϧͷαΠζ͕૿Ճ͢Δ৔߹͸ɺ REINDEXʹΑΔindexͷ࠶ఆٛΛߦ͏ɻ

Index&Only&Scan

@What's(that? • Index'Only'Scan͸ɺindexͷΈΛݕࡧͯ݁͠ՌΛฦ٫͢Δ࢓૊Έ • ςʔϒϧσʔλͰ͸ͳ͘ɺVMΛ֬ೝ͢ΔܗͰ࣮ݱ͞Ε͍ͯΔ • PostgreSQL9.2͔Βಋೖ͞Εͨ

@important*point • ಛఆͷ৚݅Ͱ͔͠ޮՌ͕ൃشͰ͖ͳ͍ͷͰɺ࢖༻͢Δ͜ͱࣗମ ೉͍͠ʢৄࡉ͸p223ࢀরʣ • VMΛߋ৽ͨ͠ΓɺՄೳͳݶΓVACCUM͕࣮ߦ͞ΕΔΑ͏ͳ޻ ෉Λ͢Δඞཁ͕͋Δ

@Summary • ӡ༻Λଓ͚Δ͜ͱͰindexʹى͜Δ໰୊Λཧղ͢Δ • Ұ࿈ͷྲྀΕͱͯ͠͸ɺVACUUMʹΑΔ࠶ར༻ΛޮՌతʹߦ͍ɺ ੑೳ΁ͷӨڹ͕ஶ͍࣌͠ʹϝϯςφϯεظؒΛઃ͚ͯɺ REINDEXͱCLUSTERͰindexΛϝϯςφϯε͢Δ

Thank&you!