Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
PostgreSQL - maintenance -
Search
rm-rf-slant
July 29, 2015
Technology
0
130
PostgreSQL - maintenance -
rm-rf-slant
July 29, 2015
Tweet
Share
More Decks by rm-rf-slant
See All by rm-rf-slant
The story behind AbemaTV
rmrfslant
10
10k
Akamai Training Day2
rmrfslant
0
110
CloudでのMySQL
rmrfslant
0
84
Other Decks in Technology
See All in Technology
[CV勉強会@関東 CVPR2025 読み会] MegaSaM: Accurate, Fast, and Robust Structure and Motion from Casual Dynamic Videos (Li+, CVPR2025)
abemii
0
180
生成AI活用のROI、どう測る? DMM.com 開発責任者から学ぶ「AI効果検証のノウハウ」 / ROI of AI
i35_267
4
140
プロダクトエンジニアリングで開発の楽しさを拡張する話
barometrica
0
220
新卒(ほぼ)専業Kagglerという選択肢
nocchi1
0
1.7k
Amazon Bedrock AgentCore でプロモーション用動画生成エージェントを開発する
nasuvitz
6
350
あなたの知らない OneDrive
murachiakira
0
190
我々は雰囲気で仕事をしている / How can we do vibe coding as well
naospon
2
180
第64回コンピュータビジョン勉強会@関東(後編)
tsukamotokenji
0
210
Observability for LLM Application lifecycle
ivry_presentationmaterials
1
190
マイクロモビリティシェアサービスを支える プラットフォームアーキテクチャ
grimoh
1
120
AWSの最新サービスでAIエージェント構築に楽しく入門しよう
minorun365
PRO
10
560
工業高校で学習したとあるエンジニアのキャリアの話
shirayanagiryuji
0
130
Featured
See All Featured
Large-scale JavaScript Application Architecture
addyosmani
512
110k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
36
2.5k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
367
26k
Side Projects
sachag
455
43k
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
32
1.4k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
30
9.6k
Balancing Empowerment & Direction
lara
2
580
Navigating Team Friction
lara
188
15k
KATA
mclloyd
32
14k
Connecting the Dots Between Site Speed, User Experience & Your Business [WebExpo 2025]
tammyeverts
8
470
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
10
1k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
139
34k
Transcript
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पճ͕ى͜Δέʔε XID32bit(=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!