Upgrade to Pro — share decks privately, control downloads, hide ads and more …
Sign up for free

PostgreSQL - maintenance -

Avatar for rm-rf-slant rm-rf-slant
July 29, 2015
Technology
140
0

PostgreSQL - maintenance -

Avatar for rm-rf-slant

rm-rf-slant

July 29, 2015

More Decks by rm-rf-slant

See All by rm-rf-slant
The story behind AbemaTV
Avatar for rm-rf-slant rmrfslant
10
11k
Akamai Training Day2
Avatar for rm-rf-slant rmrfslant
0
140
CloudでのMySQL
Avatar for rm-rf-slant rmrfslant
0
99

Other Decks in Technology

See All in Technology
生成AIと進める探索的データ分析（2026.10.03 第122回Tokyo.R勉強会）
Avatar for tatamiya tatamiya
2
1.4k
AI駆動開発、viviONの1年 ── うまくいったこと・いかなかったこと
Avatar for viviON vivion
0
260
転んだ話が背中を預ける理由になる / A_story_about_falling_down_gives_you_a_reason_to_watch_each_other_s_backs.pdf
Avatar for Pauli pauli
3
280
ファミコンでPHPを動かす / PHP on the Famicom side b
Avatar for HASEGAWA Tomoki tomzoh
0
140
ビジネスを止めない技術的負債の返済のための戦略とその手法 - 技術的負債と向き合う / Complexity and Simplicity
Avatar for soudai sone soudai
PRO
3
640
契約書画像からの情報抽出に特化した視覚接地モデルを育てる話
Avatar for SansanTech sansantech
PRO
1
140
検証フェーズはAIの回答を判断するための学習機会
Avatar for Toru_Kubota toru_kubota
2
500
いちAWSエンジニアのAI活用を振り返る #devio2026 / devio osaka 2026 kawahara
Avatar for MasahiroKawahara masahirokawahara
2
390
Claude起点の仕様駆動開発
Avatar for 田中 聖也 tanakaseiya
0
390
自律型 AI をセキュアに実装！Gemini と MIG で作る動的コード実行環境
Avatar for Recruit recruitengineers
PRO
1
210
生成AIを使って「人が」考える技術 ― AI時代の人機共想と実践ノウハウ｜UNITT AC2026
Avatar for 石井力重 ishiirikie
0
560
3つのアプローチで目指す チームを強くするAI駆動開発
Avatar for thkt thkt
0
120

Featured

See All Featured
The Power of CSS Pseudo Elements
Avatar for Geoffrey Crofte geoffreycrofte
82
6.6k
Sam Torres - BigQuery for SEOs
Avatar for Tech SEO Connect techseoconnect
PRO
0
570
Building an army of robots
Avatar for Kyle Neath kneath
307
47k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
Avatar for Addy Osmani addyosmani
10
1.4k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
Avatar for mongodb mongodb
50
10k
Why Mistakes Are the Best Teachers: Turning Failure into a Pathway for Growth
Avatar for Umar Saidu Auna auna
0
320
The Illustrated Guide to Node.js - THAT Conference 2024
Avatar for David Neal reverentgeek
1
550
Building Adaptive Systems
Avatar for Chris Keathley keathley
44
3.2k
Keith and Marios Guide to Fast Websites
Avatar for Keith Pitt keithpitt
413
23k
Become a Pro
Avatar for Speaker Deck speakerdeck
PRO
31
6.3k
Stop Working from a Prison Cell
Avatar for Chris Michel hatefulcrawdad
274
21k
Applied NLP in the Age of Generative AI
Avatar for Ines Montani inesmontani
PRO
4
2.5k

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!