PostgreSQL Architecture And Performance Monitoring

88f4e84b94fe07cddbd9e6479d689192?s=47 soudai sone
October 30, 2017

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

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

October 30, 2017
Tweet

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 )FBQ0OMZ5VQMF ͱ '*--'"$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ͷ౷ܭ৘ใ -FUT1PTUHSFT 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ɺEVrTͳͲ Ͱ؂ࢹ 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΍EVTͰ֬ೝ͢Δ ΞʔΧΠϒϑΝΠϧ͸όοΫΞοϓͷ΍Γํ ΍ස౓ʹΑͬͯอଘ͢Δର৅͕͔ΘΔͷͰ ઃܭ࣌ʹ͔ͬ͠Γͱݟੵ΋Δࣄ
  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. ·ͱΊ ࣄ࣮ΛΑΓଟ͘ɺਖ਼͘͠஌Δ͜ͱͰ ະདྷΛਖ਼͘͠༧ଌͰ͖Δ

  120. None
  121. ·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ

  122. ·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ ↓ ͳΜͱͳ͘Ͱ࢓ࣄ͸ग़དྷͳ͍

  123. ·ͱΊ

  124. ·ͱΊ

  125. ·ͱΊ

  126. ·ͱΊ ςετίʔυ͸ϓϩάϥϜͷ඼࣭ͷՄࢹԽ ϞχλϦϯά͸αʔϏεͷ඼࣭ͷՄࢹԽ

  127. ·ͱΊ lߴ౓ʹൃୡͨ͠γεςϜͷҟৗ͸ ਆͷౖΓͱݟ෼͚͕͔ͭͳ͍z ŠZ@VVLJ

  128. ·ͱΊ ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍

  129. ·ͱΊ ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍ ↓ ඼࣭ΛՄࢹԽ͚ͨͩ͠Ͱ͸վળ͸͞Εͳ͍

  130. ·ͱΊ lखΛಈ͔ͨ͠ਓ͚͕ͩੈքΛม͑Δz Š:BTVIJSP0OJTIJ

  131. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ