Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

Situa&on

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

VACCUMॲཧ

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

ෆཁྖҬͷ࠶ར༻

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

XIDपճ໰୊ͷճආ

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

͔͠͠ʂ

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

VACCUM&FULL

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

PostgreSQL ~indexϝϯςφϯε~

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

Index&Only&Scan

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

Thank&you!