PostgreSQL Architecture And Performance Monitoring

Geeks Who DrinkとPostgreSQL Conference Japan 2017での資料です。

■Geeks Who Drink
https://nulab.connpass.com/event/68737/

■PostgreSQL Conference Japan 2017
https://www.postgresql.jp/events/jpug-pgcon2017

Transcript

1. PostgreSQLͷ಺෦ߏ଄ ͱ PostgreSQLͷ؂ࢹͷجຊ 1PTUHSF42-ΧϯϑΝϨϯε
   
2. PostgreSQLͷ಺෦ߏ଄ Φεεϝຊʂ
   ͚ͩͲͷ࿩
3. PostgreSQLͷ಺෦ߏ଄ ɹQHͷ
   ೖ໳هࣄ͕͋Δ
4. What is it? ͳͥ؂ࢹʢϞχλϦϯάʣ͕ඞཁ͔
5. What is it? • ͍ͪૣ͘ো֐ൃੜΛ֬ೝͰ͖ΔΑ͏ʹ͢ΔͨΊ • ෮چʹ͍ͪૣ͘औΓֻ͔ΕΔΑ͏ʹ͢ΔͨΊ • ঢ়گͷมԽΛ࣌ܥྻͰ؅ཧ͢Δ͜ͱͰɺΩϟύγςΟ ϓϥϯχϯά΍ো֐ͷ༧ஹͷ೺Ѳʹ໾ཱͯΔͨΊ
6. What is it? αʔϏεΛਖ਼͘͠ӡ༻͢ΔͨΊʹ͸ ඞཁෆՄܽͳࣄʂ
7. What is it? PostgreSQLΛݟΔ
8. What is it? PostgreSQLΛݟΔ ↓ ͦͯ͠஌Δ
9. What is it? ਖ਼͘͠ӡ༻͢ΔͨΊͷҰา໨
10. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ಺෦ߏ଄ ̏ɹPostgreSQLͷ౷ܭ৘ใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
11. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ಺෦ߏ଄ ̏ɹPostgreSQLͷ౷ܭ৘ใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
12. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ33ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿCustomer Reliability Engineering ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹɹ ษڧձ୲౰ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
13. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ33ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿCustomer Reliability Engineering ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹɹ ษڧձ୲౰ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
14. Mackerel
15. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ಺෦ߏ଄ ̏ɹPostgreSQLͷ౷ܭ৘ใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
16. PostgreSQLͷ಺෦ߏ଄ ԿΛϞχλϦϯά͢Δ͔ʁ
17. PostgreSQLͷ಺෦ߏ଄ ԿΛϞχλϦϯά͢Δ͔ʁ
    ˣ
    ಺෦ߏ଄Λ஌Βͳ͍ͱ
    ஋͕ཧղͰ͖ͳ͍
18. PostgreSQLͷϓϩηε L L E E L X E I N D AE
    E
    W
    
    W E E
19. ϓϩηε໊ આ໌ Ϛελʔαʔό ࠷ॳʹىಈ͞ΕΔ਌ϓϩηε ϥΠλ ڞ༗όοϑΝͷ಺༰ΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ WALϥΠλ WALόοϑΝͷ಺༰ΛWALϑΝΠϧʹॻ͖ग़͢ɻ νΣοΫϙΠϯλ શͯͷμʔςΟʔϖʔδΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ ࣗಈVACUUMϥϯνϟ ઃఆʹ͕ͨͬͯࣗ͠ಈVACUUMϫʔΧΛىಈ͢Δɻ ࣗಈVACUUMϫʔΧ ࣗಈVACUUMΛ࣮ߦ͢Δɻෳ਺ىಈ͢Δ͜ͱ͕͋Δɻ ౷ܭ৘ใίϨΫλ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใΛऩू͢Δɻ όοΫΤϯυϓϩηε ΫϥΠΞϯτͷ઀ଓཁٻຖʹىಈ͠ɺཁٻʹରͯ͠ॲཧ͢Δɻ ϩΨʔ PostgreSQLͷϩάΛϑΝΠϧ΁ॻ͖ग़͢ɻ ΞʔΧΠό WALϩάΛΞʔΧΠϒ͢Δɻ WALηϯμ ϨϓϦέʔγϣϯ࣌ʹWALΛεϨʔϒαʔόʹసૹ͢Δɻ WALϨγʔό ϨϓϦέʔγϣϯ࣌ʹWALΛϚελʔαʔό͔Βड৴͢Δɻ ओͳϓϩηε܈
20. PostgreSQLͷ಺෦ߏ଄ ओͳϝϞϦ܈ ໊લ આ໌ ڞ༗όοϑΝ (shared_buffers) ςʔϒϧ΍ΠϯσοΫεͷσʔλΛΩϟογϡ͢ΔྖҬͰ͢ɻ WALόοϑΝ (wal_buffers) σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍τϥϯβΫγϣϯϩάΛΩϟογϡ͢ΔྖҬͰ͢ɻ ՄࢹੑϚοϓ (Visibility Map) ςʔϒϧͷσʔλ͕ࢀরग़དྷΔ͔൱͔؅ཧ͢Δ৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹॲཧର৅ͷϖʔ δ͔൑அ͢Δࡍʹར༻͞Ε·͢ɻ·ͨՄࢹੑϚοϓ͸VACUUMॲཧ΍֤ߋ৽ॲཧͷࡍʹߋ৽͞Ε·͢ɻ PostgreSQL 9.2Ҏ߱Ͱ͸ΠϯσοΫεɾΦϯϦʔɾεΩϟϯͱݴ͏ͱͯ΋ߴ଎ͳݕࡧํࣜͷࡍʹ΋ར༻͞ Ε·͢ɻ ۭ͖ྖҬϚοϓ (Free Scan Map) ςʔϒϧ্ͷར༻ՄೳͳྖҬΛࢦࣔ͢͠৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹશ͘ࢀর͞Ε͍ͯͳ͍ ߦΛ୳ۭ͖ͯ͠ྖҬͱͯ͠࠶ར༻ग़དྷΔঢ়ଶʹ͠·͢ɻͦͷޙɺ௥Ճ΍ߋ৽࣌ʹۭ͖ྖҬϚοϓΛ୳ࡧ ͠ɺۭ͖ྖҬΛ࠶ར༻͠·͢ɻ
21. ໊લ આ໌ σʔλϑΝΠϧ ςʔϒϧσʔλͷ࣮ମ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧ͸ෳ਺ͷ8192όΠτͷϖʔδ (OracleDBͰ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ ̍Fileͷ࠷େ͸1GBͰ͢ɻ INDEXϑΝΠϧ INDEX৘ใ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧͱಉ༷ʹෳ਺ͷ8192όΠτͷϖʔδ(OracleDB Ͱ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ WALϑΝΠϧ Write Ahead LoggingͷུͰτϥϯβΫγϣϯϩάΛPostgreSQLͰ͸WALͱݺͼ·͢ɻߋ৽ʹؔΘΔ৘ใ ΛهԱ͢Δ͜ͱͰσʔλϕʔεͷӬଓੑͷอূΛߦ͍ͬͯ·͢ɻpg_xlogʢPostgreSQL 10͔Β͸pg_walʣ σΟϨΫτϦ഑Լʹอଘ͞Εɺ16MBͷݻఆαΠζͰ࡞੒͞Ε·͢ɻ PostgreSQLͷ಺෦ߏ଄ ओͳϑΝΠϧ܈
22. όοΫΤϯυϓϩηε
    PostgreSQLͷϝϞϦ όοΫΤϯυϓϩηε
    ϫʔΫϝϞϦ ϫʔΫϝϞϦ ڞ༗όοϑΝ 8"-όοϑΝ 04ͷόοϑΝΩϟογϡ σʔλϑΝΠϧ 8"- %# σΟεΫྖҬ ϝϞϦྖҬ ϓϩηεྖҬ
23. PostgreSQLͷσʔλ %#ྖҬ
    8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ ςʔϒϧϑΝΠϧ */%&9ϑΝΠϧ 8"- ΞʔΧΠϒ
    ϑΝΠϧ 7.
    '4.
    
24. PostgreSQLͷσʔλߏ଄ (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7. '4. ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ộ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ộ ߦσʔλ ߦσʔλ ςʔϒϧϑΝΠϧ
    (#
    
25. PostgreSQLͷσʔλߏ଄ (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7. ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ộ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ộ ߦσʔλ ߦσʔλ ςʔϒϧϑΝΠϧ
    (#
    ςʔϒϧͷϖʔδϔομ͸ 
    CZUFݻఆ '4.
26. PostgreSQLͷσʔλߏ଄ (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7. ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ộ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ộ ߦσʔλ ߦσʔλ ΞΠςϜϙΠϯλ ߦϔομ ͸
    CZUF ςʔϒϧϑΝΠϧ
    (#
    '4.
27. PostgreSQLͷσʔλߏ଄ (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7. ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ,#ϖʔδ ộ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ộ ߦσʔλ ߦσʔλ ςʔϒϧϑΝΠϧ
    (#
    ࣮ࡍͷϨίʔυͷαΠζ '4.
28. PostgreSQLͷߦσʔλߏ଄ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ộ ߦσʔλ ߦσʔλ JE ໊લ ࡞੒೔࣌  IPHF 
      'VHB 
      'PP 
      #BS
    
     JOU 5FYU UJNFTUBNQ 
    CZUF CZUF
    
    จࣈྻͷCZUF 
    CZUF
29. PostgreSQLͷINDEXσʔλߏ଄ ϖʔδϔομ ϊʔυϔομ ϊʔυϔομ ộ */%&9ΤϯτϦ */%&9ΤϯτϦ JE ໊લ ࡞੒೔࣌  IPHF 
      'VHB 
      'PP 
      #BS
    
     JOU 
    CZUF
30. PostgreSQLͷINDEXσʔλߏ଄ ϖʔδϔομ ϊʔυϔομ ϊʔυϔομ ộ */%&9ΤϯτϦ */%&9ΤϯτϦ JE ໊લ ࡞੒೔࣌  IPHF 
      'VHB 
      'PP 
      #BS
    
     JOU 
    CZUF ओΩʔͷ*/%&9ͷ৔߹ JEྻͷ஋Λ֤*/%&9ΤϯτϦ ʹอ࣋͢Δ
31. PostgreSQLͷINDEXσʔλߏ଄ ϖʔδϔομ ϊʔυϔομ ϊʔυϔομ ộ */%&9ΤϯτϦ */%&9ΤϯτϦ JE ໊લ ࡞੒೔࣌  IPHF 
      'VHB 
      'PP 
      #BS
    
     JOU 
    CZUF ϊʔυϔομ͸
    CZUF ϖʔδϔομͷݻఆ͸
    CZUF
32. PostgreSQLͷσʔλ %#ྖҬ
    8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ ςʔϒϧϑΝΠϧ */%&9ϑΝΠϧ 8"- ΞʔΧΠϒ
    ϑΝΠϧ 7.
    '4.
    ௨ৗ͸ͭ.#ͷ8"-ηάϝϯτ ϑΝΠϧΛ॥؀ར༻
    ෆཁͳ8"-͸ࣗಈతʹॳظԽ͞ΕΔ
    ʢNBY@XBM@TJ[Fͷઃఆˡ
    Ҏ߱ʣ
33. PostgreSQLͷϓϩηε L L E E L X E I N D AE
    E
    W
    
    W E E
34. PostgreSQLͷσʔλ %#ྖҬ
    8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ ςʔϒϧϑΝΠϧ */%&9ϑΝΠϧ 8"- ΞʔΧΠϒ
    ϑΝΠϧ 7.
    '4.
    ΞʔΧΠϒϞʔυΛ0/ʹ͢Δͱ8"- ͷফࣦΛ๷͙ͨΊʹຊདྷ॥؀͢Δ8"- Λ࢒͓ͯ͘͠
35. PostgreSQLͷ಺෦ߏ଄ ԿΛϞχλϦϯά͢Δ͔ʁ
    
36. PostgreSQLͷ಺෦ߏ଄ ԿΛϞχλϦϯά͢Δ͔ʁ
    ˣ
    42-ͷྲྀΕΛ஌Βͳ͍ͱ
    Կ͕ى͍ͬͯ͜Δ͔Θ͔Βͳ͍
37. PostgreSQLͷ಺෦ߏ଄ 2VFSZͷड৴ ߏจղੳ ॻ͖׵͑ ࣮ߦܭըੜ੒࠷దԽ ࣮ߦ ݁Ռૹ৴ 1BSTFS
    42-ͷߏจղੳɾจ๏Τϥʔݕग़ɾߏจ໦ͷੜ੒ 3FXSJUFS
    7JFXɾ3VMFʹجͮ͘ߏจ໦ͷॻ͖׵͑ 1MBOOFS0QUJNBJ[FS
    ࣮ߦܭըͷੜ੒౷ܭ৘ใͳͲΛར༻ͨ͠࠷దԽ &YFDVUPS
    ࣮ߦܭըͷج͍ͮͨ2VFSZͷ࣮ߦɾ8"-ͷ௥هͳͲ 42-จͷॲཧ͞ΕΔྲྀΕ
38. PostgreSQLͷ಺෦ߏ଄ '30.۟ 0/۟ +0*/۟ 8)&3&۟ (3061
    #:۟ )"7*/(۟ 4&-&$5۟ %*45*/$5۟ 03%&3
    #:۟ -*.*5۟ 42-จͷධՁ͞ΕΔॱ IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM
39. PostgreSQLͷ಺෦ߏ଄ '30.۟ 0/۟ +0*/۟ 8)&3&۟ (3061
    #:۟ )"7*/(۟ 4&-&$5۟ %*45*/$5۟ 03%&3
    #:۟ -*.*5۟ 42-จͷධՁ͞ΕΔॱ IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM ͲΕ͘Β͍ͷσʔλΛѻ͏͔
    ೗ԿʹσʔλΛߜΓࠐΉ͔
40. PostgreSQLͷ಺෦ߏ଄ 1PTUHSF42-͸௥هܕ
41. PostgreSQLͷ಺෦ߏ଄  1  2  3   1  3   2 PostgreSQL(
     
        
42. PostgreSQLͷ಺෦ߏ଄  1  2  3   1  3  2   2 PostgreSQL(
     
        
43. PostgreSQLͷ಺෦ߏ଄  1  2  3   1  3  2   2 PostgreSQL(
     
         ͜ͷ࢒ͬͨچϨίʔυ Λ៉ྷ͢Δͷ͕7"$66.
44. PostgreSQLͷ಺෦ߏ଄ 1PTUHSF42-͸௥هܕ
45. PostgreSQLͷ಺෦ߏ଄ 1PTUHSF42-͸௥هܕ
    ˣ
    )05 )FBQ
    0OMZ
    5VQMF ͱ '*--'"$503
46. HOTͱFILLFACTOR ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ߦσʔλ ߦσʔλ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ` ߦσʔλ` ߦσʔλ ߦσʔλ ߦσʔλΛ
    ߋ৽
47. HOTͱFILLFACTOR ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ߦσʔλ ߦσʔλ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ` ߦσʔλ` ߦσʔλ ߦσʔλ ߦσʔλΛ
    ߋ৽ ͜ͷۭ͖ྖҬΛ؅ཧ͢Δ࢓૊Έ͕ '*--'"$503
    ςʔϒϧϑΝΠϧͷσϑΥϧτ͸ ͳͷͰۭ͖ྖҬΛ࡞Βͳ͍
48. HOTͱFILLFACTOR ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ߦσʔλ ߦσʔλ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ` ߦσʔλ` ߦσʔλ ߦσʔλ ߦσʔλΛ
    ߋ৽ ݕࡧ࣌ͷಈ࡞
    
    ΞΠςϜϙΠϯλΛ֬ೝ
    
    ΞΠςϜϙΠϯλ`Λ֬ೝ
    
    ߦσʔλ`Λऔಘ
49. HOTͱFILLFACTOR ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ߦσʔλ ߦσʔλ ϖʔδϔομ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ ΞΠςϜϙΠϯλ` ߦσʔλ` ߦσʔλ ߦσʔλ ߦσʔλΛ
    ߋ৽ ۭ͖ྖҬΛ࢖͑Δͱ*/%&9Λߋ ৽͢Δ͜ͱແ͘σʔλΛߋ৽
    ݹ͍Ϩίʔυ͸7"$66.Λ଴ ͨͣʹ࠶ར༻Մೳ
    
50. HOTͱFILLFACTOR )05ߋ৽͕ར༻Ͱ͖Δ৚݅
    w ಉҰϖʔδ಺ʹߋ৽ޙͷσʔλΛ อଘͰ͖Δ
    w *OEFYྻΛߋ৽͠ͳ͍
51. PostgreSQLͷ಺෦ߏ଄ 1PTUHSF42-ͷ಺෦ߏ଄Λ஌Δ
    
52. PostgreSQLͷ಺෦ߏ଄ 1PTUHSF42-ͷ಺෦ߏ଄Λ஌Δ
    ˣ
    ϞχλϦϯάͷצॴ͕ݟ͑ͯ͘Δ
53. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ಺෦ߏ଄ ̏ɹPostgreSQLͷ౷ܭ৘ใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
54. PostgreSQLͷ౷ܭ৘ใ ౷ܭ৘ใίϨΫλ͕
    αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใΛऩू
    
55. PostgreSQLͷ಺෦ߏ଄ L L E E L X E I N D AE
    E
    W
    
    W E E
56. PostgreSQLͷ౷ܭ৘ใ αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ
    
57. PostgreSQLͷ౷ܭ৘ใ αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ
    ˣ
    ౷ܭ৘ใ
58. PostgreSQLͷ౷ܭ৘ใ ౷ܭ৘ใ
    ެࣜυΩϡϝϯτ
    IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMNPOJUPSJOHTUBUTIUNM
59. PostgreSQLͷ౷ܭ৘ใ QPTUHSFTRMDPOGͰ
    QH@TUBU@TUBUFNFOUTΛ༗ޮʹ͢Δ
60. PostgreSQLͷ౷ܭ৘ใ QPTUHSFTRMDPOGͰ
    QH@TUBU@TUBUFNFOUTΛ༗ޮʹ͢Δ 42-จͷ࣮ߦ࣌ؒɺ࣮ߦճ਺ɺऔಘͨ͠ʢ·ͨ͸ߋ৽ͨ͠ʣ Ϩίʔυ਺ͳͲ͕อଘ͞ΕΔ
61. PostgreSQLͷ౷ܭ৘ใ ౷ܭ৘ใΛ·ͱΊͨศརͳ7JFX
62. ౷ܭ৘ใͷओͳView 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ
63. ౷ܭ৘ใͷओͳView 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ νΣοΫϙΠϯτͷ࣮ߦճ਺΍࣮ߦ࣌ؒͳͲ͕ݟΕΔ
    ߋ৽͕ܹ͍͠৔߹ʹ͸νΣοΫϙΠϯτ͕ॏཁͳՕॴʹͳΔ
64. ౷ܭ৘ใͷओͳView 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ ࠓ·͞ʹ࣮ߦ͞Ε͍ͯΔ42-ͳͲ͕Θ͔Δ
    2VFSZ͕͍ࢗͬͯ͞Δͱ͖΍Ұ෦ͷ8FCαʔόͷΞΫηε͕ॏ͍౳͕ݟ͑ͯ͘Δ
65. ౷ܭ৘ใͷओͳView 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ σʔλϕʔεશମͷࢀরɾ௥Ճɾߋ৽ɾ࡟আճ਺ͳͲ͕ݟΕΔ
    σουϩοΫͷճ਺΍Ұ࣌ϑΝΠϧͷ࡞੒ճ਺΋ݟΕΔͷͰʮٸʹ%#͕ॏ͘ͳΔ લʹؾ෇͘ʯͨΊʹܧଓతʹϞχλϦϯά͕େࣄ
66. 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ ౷ܭ৘ใͷओͳView QH@TUBU@EBUBCBTFΛΑΓৄࡉʹςʔϒϧ୯ҐͰݟΕΔ༷ʹͳͬͨ7JFX
    ߋʹ)05ͷߋ৽ߦ਺΍ෆཁͳλϓϧɺ7"$66.ͷ࣮ߦճ਺ͳͲ͕ݟΕΔ
67. 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ ౷ܭ৘ใͷओͳView QH@TUBU@VTFS@UBCMFTʹࣅ͍ͯΔ͕τϥϯβΫγϣϯதͷσʔλͷΈ͕දࣔ͞ΕΔ
    QH@TUBU@YBDU@VTFS@UBCMFTʹදࣔ͞Ε͍ͯΔ৔߹͸ɺQH@TUBU@VTFS@UBCMFTʹ͸ ؚ·Ε͍ͯͳ͍
68. 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ ౷ܭ৘ใͷओͳView */%&9ͷར༻ঢ়گ͕Θ͔Δ7JFX
    */%&9Λར༻ͨ͠ճ਺ɺ*/%&9ʹؚ·ΕΔΧϥϜͷར༻ݸ਺ɺ*/%&9Λར༻ͯ͠ औΓग़ͨ͠ߦ਺ͳͲ͕ݟΕΔ
    *OEFY0OMZ4DBO͕༗ޮ׆༻͞Ε͍ͯΔ৔߹͸JEY@UVQ@SFBEʹରͯ͠JEY@UVQ@GFUDI ͕গͳ͘ͳΔɻ
    8)&3&ͷൣғ͕޿͍৔߹ͳͲ͸JEY@UVQ@GFUDIͷ૿ՃྔΛݟΔ͜ͱͰ܏޲Λ༧ଌ ͢Δ͜ͱ͕Ͱ͖Δ
69. 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ ౷ܭ৘ใͷओͳView ςʔϒϧ୯Ґͷ*0͕Θ͔ΔͷͰόοϑΝΩϟογϡͷঢ়گΛܭࢉ͢Δ͜ͱ͕Ͱ͖Δ
    IFBQ@CMLT@SFBE͕IFBQ@CMLT@IJUΑΓ΋͔ͳΓগͳ͍ͷͰ͋Ε͹΄΅ΧʔωϧΛݺͼ ग़͢͜ͱແ݁͘ՌΛฦͤͯΔͷͰૣ͍͸ͣɻ
    50"45ͷঢ়گ΋ݟΕΔͨΊɺେ͖ͳσʔλΛѻ͍ͬͯΔςʔϒϧͰ͸ཁ؂ࢹ
70. 7JFX໊ આ໌ QH@TUBU@CHXSJUFS όοΫάϥ΢ϯυϥΠλϓϩηεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใ QH@TUBU@BDUJWJUZ ઀ଓ͞Ε͍ͯΔϓϩηεͷݱࡏͷ׆ಈঢ়گʹؔ࿈ͨ͠৘ใ QH@TUBU@EBUBCBTF σʔλϕʔε୯Ґͷશମͷ৘ใ QH@TUBU@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢ΔΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBU@YBDU@VTFS@UBCMFT ݱࡏͷτϥϯβΫγϣϯதͷΞΫηεʹؔ͢Δ৘ใ QH@TUBU@VTFS@JOEFYFT ಛఆͷΠϯσοΫε΁ͷΞΫηεʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@UBCMFT ಛఆͷςʔϒϧʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ QH@TUBUJP@VTFS@JOEFYFT ಛఆͷΠϯσοΫεʹର͢Δ*0ʹؔ͢Δ౷ܭ৘ใ ౷ܭ৘ใͷओͳView QH@TUBUJP@VTFS@UBCMFTͷ*/%&9൛
    όοϑΝΩϟογϡͷώοτ཰ͳͲܭࢉͰ͖Δ
71. PostgreSQLͷ౷ܭ৘ใ ౷ܭ৘ใʹ͸42-ͰΞΫηεͰ͖Δ
72. PostgreSQLͷ౷ܭ৘ใ ౷ܭ৘ใͷ׆༻ͷྫ
    w ൃߦ͞Ε͍ͯΔ2VFSZ
    w ϩοΫͷ಺༰
    w */%&9ͷར༻ঢ়گ
    w νΣοΫϙΠϯτͷॲཧঢ়گ
    ʜFUD
73. εϧʔϓοτΤϥʔͷ֬ೝ =# SELECT datname, xact_commit, xact_rollback FROM pg_stat_database; datname | xact_commit | xact_rollback -----------+-------------+--------------- template1 | 0 | 0 template0 | 0 | 0 postgres | 101216 | 1
74. Ωϟογϡώοτ཰ͷ֬ೝ =# SELECT datname, round(blks_hit*100/(blks_hit+blks_read), 2) AS cache_hit_ratio FROM pg_stat_database WHERE blks_read > 0; datname | cache_hit_ratio ----------+----------------- postgres | 99.00 ※ blks_hit+blks_read ʹ஫ҙ
75. ςʔϒϧͷ
    Ωϟογϡώοτ཰ͷ֬ೝ =# SELECT relname, round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) AS cache_hit_ratio FROM pg_statio_user_tables WHERE heap_blks_read > 0 ORDER BY cache_hit_ratio; relname | cache_hit_ratio ------------------+----------------- pgbench_accounts | 97.00 pgbench_tellers | 99.00 pgbench_history | 99.00 pgbench_branches | 99.00
76. ΠϯσοΫεͷ
    Ωϟογϡώοτ཰ͷ֬ೝ =# SELECT relname, indexrelname, round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) AS cache_hit_ratio FROM pg_statio_user_indexes WHERE idx_blks_read > 0 ORDER BY cache_hit_ratio; relname | indexrelname | cache_hit_ratio ------------------+-----------------------+------------------ pgbench_tellers | pgbench_tellers_pkey | 90.00 pgbench_branches | pgbench_branches_pkey | 99.00 pgbench_accounts | pgbench_accounts_pkey | 99.00
77. දεΩϟϯ͋ͨΓͷಡΈऔΓߦ਺ͷ֬ೝ
    =# SELECT relname, seq_scan, seq_tup_read, seq_tup_read/seq_scan AS tup_per_read FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY tup_per_read DESC; relname | seq_scan | seq_tup_read | tup_per_read ------------------+----------+--------------+-------------- pgbench_accounts | 1 | 100000 | 100000 pgbench_tellers | 153613 | 1000010 | 6 pgbench_branches | 35659 | 16461 | 0
78. )05ߋ৽ͷൺ཰ͷ֬ೝ =# SELECT relname, n_tup_upd, n_tup_hot_upd, round(n_tup_hot_upd*100/n_tup_upd, 2) AS hot_upd_ratio FROM pg_stat_user_tables WHERE n_tup_upd > 0 ORDER BY hot_upd_ratio; relname | n_tup_upd | n_tup_hot_upd | hot_upd_ratio ------------------+-----------+---------------+--------------- pgbench_accounts | 100000 | 96079 | 96.00 pgbench_tellers | 100000 | 99921 | 99.00 pgbench_branches | 100000 | 99548 | 99.00
79. ϩοΫ଴ͪॲཧͷ֬ೝ =# SELECT l.locktype, c.relname, l.pid, l.mode, substring(a.current_query, 1, 6) AS query, (current_timestamp - xact_start)::interval(3) AS duration FROM pg_locks l LEFT OUTER JOIN pg_stat_activity a ON l.pid = a. procpid LEFT OUTER JOIN pg_class c ON l.relation = c.oid WHERE NOT l.granted ORDER BY l.pid; locktype | relname | pid | mode | query | duration ---------------+----------+------+---------------+--------+-------------- tuple | tellers | 2700 | ExclusiveLock | UPDATE | 00:00:00.013 transactionid | | 2701 | ShareLock | INSERT | 00:00:00.004 transactionid | | 2702 | ShareLock | UPDATE | 00:00:00.014 tuple | tellers | 2703 | ExclusiveLock | UPDATE | 00:00:00.004 tuple | tellers | 2704 | ExclusiveLock | UPDATE | 00:00:00.009 tuple | branches | 2705 | ExclusiveLock | UPDATE | 00:00:00.001 transactionid | | 2706 | ShareLock | UPDATE | 00:00:00.001 transactionid | | 2707 | ShareLock | UPDATE | 00:00:00.017 transactionid | | 2708 | ShareLock | UPDATE | 00:00:00.007
80. PostgreSQLͷ౷ܭ৘ใ -FUT
    1PTUHSFT
    lՔಈ౷ܭ৘ใΛ׆༻͠Α͏z
    IUUQTMFUTQPTUHSFTRMKQEPDVNFOUTUFDIOJDBMTUBUJTUJDT
81. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ಺෦ߏ଄ ̏ɹPostgreSQLͷ౷ܭ৘ใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
82. PostgreSQLͷϞχλϦϯά ౷ܭ৘ใΛ࢖͏ͱ
    1PTUHSF42-ΛϞχλϦϯάͰ͖Δ
83. PostgreSQLͷϞχλϦϯά ͔͠͠ྺ࢙Λ࣋ͨͳ͍
84. PostgreSQLͷϞχλϦϯά ͔͠͠ྺ࢙Λ࣋ͨͳ͍
    ˣ
    ࣌ܥྻ%#ʹอଘͯ͠ՄࢹԽ
85. PostgreSQLͷϞχλϦϯά
86. PostgreSQLͷϞχλϦϯά ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ ϓϩηε؂ࢹ ˓ ˓ ˓ ༰ྔ؂ࢹ ☓ ˓ ˓ ϩά؂ࢹ ☓ ˓ ˓ ύϑΥʔϚϯε ؂ࢹ ☓ ☓ ˓
87. PostgreSQLͷϞχλϦϯά ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ ϓϩηε؂ࢹ ˓ ˓ ˓ ༰ྔ؂ࢹ ☓ ˓ ˓ ϩά؂ࢹ ☓ ˓ ˓ ύϑΥʔϚϯε ؂ࢹ ☓ ☓ ˓ ΋ͬͱ΋γϯϓϧ
    QH@JTSFBEZ
    Ͱྑ͍
    ͔͠͠αʔϏε͕མͪΔ·ͰΘ͔Βͳ͍
88. PostgreSQLͷϞχλϦϯά ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ ϓϩηε؂ࢹ ˓ ˓ ˓ ༰ྔ؂ࢹ ☓ ˓ ˓ ϩά؂ࢹ ☓ ˓ ˓ ύϑΥʔϚϯε ؂ࢹ ☓ ☓ ˓ ঢ়ଶͷมԽ͕Θ͔Δ
    ͔͠͠มԽͷࠩ෼͕ͳ͍ͨΊ ༧ஹʹ͸ؾ෇͖ʹ͍͘
89. PostgreSQLͷϞχλϦϯά ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ ϓϩηε؂ࢹ ˓ ˓ ˓ ༰ྔ؂ࢹ ☓ ˓ ˓ ϩά؂ࢹ ☓ ˓ ˓ ύϑΥʔϚϯε ؂ࢹ ☓ ☓ ˓ աڈͱࠓͷࠩ෼Λൺֱ͢Δ͜ͱͰݱঢ়Λ ΑΓਖ਼͘͠೺ѲͰ͖Δ
    άϥϑͰՄࢹԽ͢Δ͜ͱͰΘ͔Γ΍͍͢
90. PostgreSQLͷϞχλϦϯά 1PTUHSF42-Λӡ༻͢ΔͨΊͷ؂ࢹ
91. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ PostgreSQLͷϞχλϦϯά
92. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ PostgreSQLͷϞχλϦϯά σΟεΫ༰ྔɺσʔλϕʔεɺΦϒδΣΫτ౳ͷ αΠζΛ؂ࢹ
93. σΟεΫ༰ྔ؂ࢹ w σΟεΫ༰ྔ͸04ίϚϯυ EG
    ɺEV
    rT
    ͳͲ Ͱ؂ࢹ
    w ಛʹҎԼͷྖҬʹ஫ҙ
    w σʔλϑΝΠϧ
    
    w 8"-ϑΝΠϧ
    
    w ΞʔΧΠϒྖҬ ϩʔΧϧσΟεΫʹ഑ஔ͍ͯ͠Δ৔߹
    
    w σʔλϕʔε΍ΦϒδΣΫταΠζ͸ؔ਺Ͱ֬ೝ
    w QH@EBUBCBTF@TJ[F bEBUBCBTF`
    
    w QH@SFMBUJPO@TJ[F bPCKFDU`
    
    w QH@UPUBM@SFMBUJPO@TJ[F bUBCMF`
    
94. PostgreSQLͷσʔλ %#ྖҬ
    8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ ςʔϒϧϑΝΠϧ */%&9ϑΝΠϧ 8"- ΞʔΧΠϒ
    ϑΝΠϧ 7.
    '4.
    EG΍EV
    TͰ֬ೝ͢Δ
    ΞʔΧΠϒϑΝΠϧ͸όοΫΞοϓͷ΍Γํ ΍ස౓ʹΑͬͯอଘ͢Δର৅͕͔ΘΔͷͰ ઃܭ࣌ʹ͔ͬ͠Γͱݟੵ΋Δࣄ
95. PostgreSQLͷσʔλ %#ྖҬ
    8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ ςʔϒϧϑΝΠϧ */%&9ϑΝΠϧ 8"- ΞʔΧΠϒ
    ϑΝΠϧ 7.
    '4.
    σʔλϕʔε΍ΦϒδΣΫταΠζ͸ؔ਺Ͱ֬ೝ
    QH@SFMBUJPO@TJ[F ʹ͸*/%&9໊΋ࢦఆͰ͖Δ
    QH@UPUBM@SFMBUJPO@TJ[F ʹ͸*/%&9ͳͲͷαΠζ
    ΋ؚ·ΕΔ
96. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ PostgreSQLͷϞχλϦϯά ౷ܭ৘ใ͔Β࣮ߦঢ়ଶΛ؂ࢹ͢Δ
97. VACUUMͷඞཁੑ 7"$66.ͱ7"$66.
    '6--͕͋Δ
    w
    7"$66.Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞Εͨߦͷ࠶ར༻
    w τϥϯβΫγϣϯ*%पճͷճආ
    w 7"$66.
    '6--Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞ΕͨߦΛ੾Γ٧ΊΔ
    w σΟεΫͷ࢖༻ྔΛ෺ཧతʹॖখ͢Δ
98. 7"$66.ͱ7"$66.
    '6--͕͋Δ
    w
    7"$66.Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞Εͨߦͷ࠶ར༻
    w τϥϯβΫγϣϯ*%पճͷճආ
    w 7"$66.
    '6--Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞ΕͨߦΛ੾Γ٧ΊΔ
    w σΟεΫͷ࢖༻ྔΛ෺ཧతʹॖখ͢Δ VACUUMͷඞཁੑ QH@TUBU@VTFS@UBCMFTͳͲΛ׆༻ͯ͠ਖ਼࣮͘͠ߦ ͞Ε͍ͯΔࣄΛ֬ೝ͢Δ
99. 7"$66.ͱ7"$66.
    '6--͕͋Δ
    w
    7"$66.Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞Εͨߦͷ࠶ར༻
    w τϥϯβΫγϣϯ*%पճͷճආ
    w 7"$66.
    '6--Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞ΕͨߦΛ੾Γ٧ΊΔ
    w σΟεΫͷ࢖༻ྔΛ෺ཧతʹॖখ͢Δ VACUUMͷඞཁੑ QH@TUBU@VTFS@UBCMFTͳͲΛ׆༻ͯ͠ਖ਼࣮͘͠ߦ ͞Ε͍ͯΔࣄΛ֬ೝ͢Δ 7"$66.࣮ߦ࣌͸ύϑΥʔϚϯεྼԽͷτϦΨʔ ʹͳΓ΍͍͢ͷͰ࣮ߦλΠϛϯάΛอଘ͢Δ
100. ϞχλϦϯάͷର৅
     w σΟεΫ༰ྔ؂ࢹ
     w 7"$66.؂ࢹ
     w ϨϓϦέʔγϣϯ؂ࢹ
     w αʔόϩά؂ࢹ PostgreSQLͷϞχλϦϯά 8"-ͷసૹঢ়گ΍λΠϜϥΠϯͳͲΛ֬ೝ͢Δ
     ࠓ೔͸ϨϓϦέʔγϣϯͷηογϣϯ͕͋ΔͷͰׂѪ
101. ϞχλϦϯάͷର৅
     w σΟεΫ༰ྔ؂ࢹ
     w 7"$66.؂ࢹ
     w ϨϓϦέʔγϣϯ؂ࢹ
     w αʔόϩά؂ࢹ PostgreSQLͷϞχλϦϯά Τϥʔϩάͷग़ྗ݁ՌͳͲΛ؂ࢹ͢Δ
102. PostgreSQLͷϩά MPH@NJO@NFTTBHFTͷ
     ΤϥʔϨϕϧΛࢦఆ͢Δ
103. PostgreSQLͷϩά ਂࠁ౓ ࢖༻ํ๏ TZTMPH %&#6(%&#6( ։ൃऀ͕࢖༻͢Δ࿈ଓత͔ͭΑΓৄࡉͳ৘ใΛఏڙ͠·͢ɻ %&#6( */'0 7"$66.
     7&3#04&ͷग़ྗͳͲͷɺ
     ϢʔβʹΑͬͯ҉໧తʹཁٻ ͞Εͨ৘ใΛఏڙ͠·͢ɻ */'0 /05*$& ௕͍ࣝผࢠͷ੾Γ٧Ίʹؔ͢Δ஫ҙͳͲɺ
     ϢʔβͷิॿʹͳΔ৘ใ Λఏڙ͠·͢ɻ /05*$& 8"3/*/( τϥϯβΫγϣϯϒϩοΫ֎Ͱͷ$0..*5ͷ༷ͳɺ
     Ϣʔβ΁ͷܯࠂ Λఏڙ͠·͢ɻ /05*$& &3303 ݱࡏͷίϚϯυΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใࠂ͠·͢ɻ 8"3/*/( -0( νΣοΫϙΠϯτͷ׆ಈͷ༷ͳɺ
     ؅ཧऀʹؔ৺ͷ͋Δ৘ใΛใࠂ͠ ·͢ɻ */'0 '"5"- ݱࡏͷηογϣϯΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใࠂ͠·͢ɻ &33 1"/*$ શͯͷσʔλϕʔεηογϣϯΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใ ࠂ͠·͢ɻ $3*5
104. PostgreSQLͷϩά ᮢ஋Λ௒͑ͨ42-ͷ؂ࢹ ߲໨໊ ༻్ MPH@NJO@FSSPS@TUBUFNFOU ࢦఆͷΤϥʔϨϕϧʹ֘౰͢Δ42-Λه࿥͢Δ MPH@NJO@EVSBUJPO@TUBUFNFOU ࢦఆͨ͠ϛϦඵҎ্ͷ࣮ߦ࣌ؒͷ42-Λه࿥͢Δ MPH@MPDL@XBJUT ϩοΫ֫ಘʹཁ͕ͨ࣌ؒ͠λΠϜΞ΢τͨ͠42- Λه࿥͢Δ
105. PostgreSQLͷϞχλϦϯά ͜ΕΒΛՄࢹԽͯ͠
     1PTUHSF42-ͷৼΔ෣͍Λ஌Δ
106. PostgreSQLͷϞχλϦϯά ՄࢹԽͷͨΊͷπʔϧ
     w .BDLFSFM
     
     QMVHJO
     w ;BCCJY
     
     QH@NPO[
     w QH@TUBUTJOGP
     
     QH@TUBUT@SFQPSUFS
107. PostgreSQLͷϞχλϦϯά ՄࢹԽͷͨΊͷπʔϧ
     w .BDLFSFM
     
     QMVHJO
     w ;BCCJY
     
     QH@NPO[
     w QH@TUBUTJOGP
     
     QH@TUBUT@SFQPSUFS ؆୯Ͱ4BB4ͳͷͰ4FSWFSΛ༻ҙ͠ͳ͍͍ͯ͘ͷͰָ
     ଟ͘ͷϛυϧ΢ΣΞʹରԠ͍ͯ͠ΔͷͰҰׅ؅ཧͰ͖Δ
     ͔͠͠1PTUHSF42-ͷৄࡉΛݟΔʹ͸߲໨͕গͳ͍
108. PostgreSQLͷϞχλϦϯά ՄࢹԽͷͨΊͷπʔϧ
     w .BDLFSFM
     
     QMVHJO
     w ;BCCJY
     
     QH@NPO[
     w QH@TUBUTJOGP
     
     QH@TUBUT@SFQPSUFS .BDLFSFMಉ༷ʹෳ਺ͷϛυϧ΢ΣΞʹରԠ
     1PTUHSF42-ͷࡉ͔͍৘ใ·ͰݟΕΔ
     4FSWFS͕ඞཁͰ୆਺͕૿͑Δͱ;BCCJYͷνϡʔχϯά΋ඞཁ
109. PostgreSQLͷϞχλϦϯά ՄࢹԽͷͨΊͷπʔϧ
     w .BDLFSFM
     
     QMVHJO
     w ;BCCJY
     
     QH@NPO[
     w QH@TUBUTJOGP
     
     QH@TUBUT@SFQPSUFS 1PTUHSF42-ઐ༻πʔϧ
     ৄࡉͳ৘ใ·ͰՄࢹԽͰ͖Δ
     Έ͔͔੡ͳͷͰ೔ຊޠυΩϡϝϯτ΋͋Δ
     ͔͠͠1PTUHSF42-ઐ༻ͳ্ʹ؀ڥߏங͕൥ࡶ
110. PostgreSQLͷϞχλϦϯά ࣗ෼ʹ߹ͬͨπʔϧΛબͿ
111. PostgreSQLͷϞχλϦϯά ࣗ෼ʹ߹ͬͨπʔϧΛબͿ
     ˣ
     ΠνϩʔͷόοτΛ࢖͔ͬͨΒͱݴͬͯ
     ΠνϩʔʹͳΕΔΘ͚Ͱ͸ͳ͍
112. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ಺෦ߏ଄ ̏ɹPostgreSQLͷ౷ܭ৘ใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
113. ·ͱΊ ઌਓͷ஌ܙΛ࢖͏
114. ·ͱΊ ઌਓͷ஌ܙΛ࢖͏
     ˣ
     ެࣜυΩϡϝϯτΛಡ΋͏
     
115. ·ͱΊ ઌਓͷ஌ܙΛ࢖͏
     ˣ
     ެࣜυΩϡϝϯτΛಡ΋͏
     ҆৺ͷ೔ຊޠυΩϡϝϯτ
116. ·ͱΊ ·ͣ͸ՄࢹԽΛ͢Δ
117. ·ͱΊ ਪଌΑΓܭଌ
118. ·ͱΊ ਪଌΑΓܭଌ ↓ ܭଌΑΓ؍ଌ
119. ·ͱΊ ࣄ࣮ΛΑΓଟ͘ɺਖ਼͘͠஌Δ͜ͱͰ ະདྷΛਖ਼͘͠༧ଌͰ͖Δ
121. ·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ
122. ·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ ↓ ͳΜͱͳ͘Ͱ࢓ࣄ͸ग़དྷͳ͍
123. ·ͱΊ
124. ·ͱΊ
125. ·ͱΊ
126. ·ͱΊ ςετίʔυ͸ϓϩάϥϜͷ඼࣭ͷՄࢹԽ ϞχλϦϯά͸αʔϏεͷ඼࣭ͷՄࢹԽ
127. ·ͱΊ lߴ౓ʹൃୡͨ͠γεςϜͷҟৗ͸
     ਆͷౖΓͱݟ෼͚͕͔ͭͳ͍z
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     Š
     Z@VVLJ
128. ·ͱΊ ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍
129. ·ͱΊ ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍ ↓ ඼࣭ΛՄࢹԽ͚ͨͩ͠Ͱ͸վળ͸͞Εͳ͍
130. ·ͱΊ lखΛಈ͔ͨ͠ਓ͚͕ͩੈքΛม͑Δz
     Š
     :BTVIJSP
     0OJTIJ
131. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ