Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Sign up for free
Menu
Search
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Pricing
Search
Sign in
Sign up for free
PostgreSQL - maintenance -
Search
Sponsored
·
Your Podcast. Everywhere. Effortlessly.
Share. Educate. Inspire. Entertain. You do you. We'll handle the rest.
→
rm-rf-slant
July 29, 2015
Technology
150
0
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
PostgreSQL - maintenance -
rm-rf-slant
July 29, 2015
Transcript
35 slides
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!
More Decks by rm-rf-slant
See All by rm-rf-slant
The story behind AbemaTV
rmrfslant
10
11k
Akamai Training Day2
rmrfslant
0
150
CloudでのMySQL
rmrfslant
0
100
Other Decks in Technology
See All in Technology
2026-11-11 Tamachi.sre#7 PaCにインフラリソースの命を救われました。 / PaC saves the infra resource life
masasuzu
1
210
gradle-best-practices 使ってみた
mikanichinose
0
160
Context parameters で作る!はじめての型クラス
kworkdev
PRO
1
150
AIネイティブ組織の作り方。1人のPOとAIから始める、自社での実験
ama_ch
2
1.2k
高品質と高スピードを支える品質のシフトレフトのアプローチ
goyoki
4
1.7k
契約書SaaSにAIエージェントを組み込む
sansantech
PRO
0
190
攻撃と守りは何が変わり、何が変わらないのか~最近の不正アクセスから考える、リスク評価と標準の使いどころ
oidfj
2
200
人間+AIを「チーム」としてプロセスを再設計する
littlehands
4
3.5k
SORACOM × AWS IoT Core でSREする
butadora
0
110
人の努力に頼らないセキュリティをエンジニアリングする在り方 / An approach to engineering security that does not rely on human effort
carta_engineering
0
220
Claude Codeで新規事業を伸ばせるか? Zennの試行錯誤の記録
wadayusuke
2
240
Oracle Base Database Service 技術詳細
oracle4engineer
PRO
17
120k
Featured
See All Featured
Marketing Yourself as an Engineer | Alaka | Gurzu
gurzu
0
380
Collaborative Software Design: How to facilitate domain modelling decisions
baasie
1
380
ReactJS: Keep Simple. Everything can be a component!
pedronauck
666
130k
Git: the NoSQL Database
bkeepers
PRO
433
67k
Bridging the Design Gap: How Collaborative Modelling removes blockers to flow between stakeholders and teams @FastFlow conf
baasie
0
770
SEOcharity - Dark patterns in SEO and UX: How to avoid them and build a more ethical web
sarafernandez
0
330
The Power of CSS Pseudo Elements
geoffreycrofte
82
6.6k
How to Grow Your eCommerce with AI & Automation
katarinadahlin
PRO
2
350
Build The Right Thing And Hit Your Dates
maggiecrowley
39
3.5k
We Are The Robots
honzajavorek
0
450
The Impact of AI in SEO - AI Overviews June 2024 Edition
aleyda
6
1.3k
Reality Check: Gamification 10 Years Later
codingconduct
0
2.4k