Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PostgreSQL - maintenance -

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

PostgreSQL - maintenance -

Avatar for rm-rf-slant

rm-rf-slant

July 29, 2015
Tweet

More Decks by rm-rf-slant

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

Other Decks in Technology

See All in Technology
テストを実行してSorbetのsigを書こう！
Avatar for SansanTech sansantech
PRO
1
120
Agent Development Kitで始める生成 AI エージェント実践開発
Avatar for danishi danishi
0
150
はじめての転職講座/The Guide of First Career Change
Avatar for Hiromu Shioya kwappa
5
4.2k
Claude Codeは仕様駆動の夢を見ない
Avatar for Gota gotalab555
23
6.8k
Kiroでインフラ要件定義～テスト
 を実施してみた
Avatar for nagisa_53 nagisa53
3
370
ユーザー課題を愛し抜く――AI時代のPdM価値
Avatar for KAKEHASHI kakehashi
PRO
1
120
専門分化が進む分業下でもユーザーが本当に欲しかったものを追求するプロダクトマネジメント/Focus on real user needs despite deep specialization and division of labor
Avatar for moriyuya moriyuya
1
1.4k
S3 Glacier のデータを Athena からクエリしようとしたらどうなるのか/try-to-query-s3-glacier-from-athena
Avatar for emi emiki
0
230
JAWS AI/ML #30 AI コーディング IDE "Kiro" を触ってみよう
Avatar for Riku INADA inariku
3
380
Rubyの国のPerlMonger
Avatar for AnaTofuZ anatofuz
3
740
AIのグローバルトレンド 2025 / ai global trend 2025
Avatar for kyonmm kyonmm
PRO
1
150
工業高校で学習したとあるエンジニアのキャリアの話
Avatar for ShirayanagiRyuji shirayanagiryuji
0
110

Featured

See All Featured
Design and Strategy: How to Deal with People Who Don’t "Get" Design
Avatar for Morgane Peng morganepeng
131
19k
Designing Experiences People Love
Avatar for Jonathan Moore moore
142
24k
The Straight Up "How To Draw Better" Workshop
Avatar for Dennis Kardys denniskardys
235
140k
The MySQL Ecosystem @ GitHub 2015
Avatar for Sam Lambert samlambert
251
13k
Product Roadmaps are Hard
Avatar for C. Todd Lombardo iamctodd
PRO
54
11k
We Have a Design System, Now What?
Avatar for Morgane Peng morganepeng
53
7.7k
The Invisible Side of Design
Avatar for Vitaly Friedman smashingmag
301
51k
Put a Button on it: Removing Barriers to Going Fast.
Avatar for kastner kastner
60
4k
YesSQL, Process and Tooling at Scale
Avatar for Rocio Delgado rocio
173
14k
Java REST API Framework Comparison - PWX 2021
Avatar for Matt Raible mraible
33
8.8k
The Success of Rails: Ensuring Growth for the Next 100 Years
Avatar for Eileen M. Uchitelle eileencodes
46
7.6k
The Illustrated Children's Guide to Kubernetes
Avatar for Chris Short chrisshort
48
50k

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!