$30 off During Our Annual Pro Sale. View Details »

PostgreSQL Architecture And Performance Monitoring

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

soudai sone

October 30, 2017
Tweet

More Decks by soudai sone

Other Decks in Technology

Transcript

  1. PostgreSQLͷ಺෦ߏ଄
    ͱ
    PostgreSQLͷ؂ࢹͷجຊ
    1PTUHSF42-ΧϯϑΝϨϯε

    View Slide

  2. PostgreSQLͷ಺෦ߏ଄
    Φεεϝຊʂ
    ͚ͩͲͷ࿩

    View Slide

  3. PostgreSQLͷ಺෦ߏ଄
    ɹQHͷ
    ೖ໳هࣄ͕͋Δ

    View Slide

  4. What is it?
    ͳͥ؂ࢹʢϞχλϦϯάʣ͕ඞཁ͔

    View Slide

  5. What is it?
    • ͍ͪૣ͘ো֐ൃੜΛ֬ೝͰ͖ΔΑ͏ʹ͢ΔͨΊ
    • ෮چʹ͍ͪૣ͘औΓֻ͔ΕΔΑ͏ʹ͢ΔͨΊ
    • ঢ়گͷมԽΛ࣌ܥྻͰ؅ཧ͢Δ͜ͱͰɺΩϟύγςΟ
    ϓϥϯχϯά΍ো֐ͷ༧ஹͷ೺Ѳʹ໾ཱͯΔͨΊ

    View Slide

  6. What is it?
    αʔϏεΛਖ਼͘͠ӡ༻͢ΔͨΊʹ͸
    ඞཁෆՄܽͳࣄʂ

    View Slide

  7. What is it?
    PostgreSQLΛݟΔ

    View Slide

  8. What is it?
    PostgreSQLΛݟΔ

    ͦͯ͠஌Δ

    View Slide

  9. What is it?
    ਖ਼͘͠ӡ༻͢ΔͨΊͷҰา໨

    View Slide

  10. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹPostgreSQLͷ಺෦ߏ଄
    ̏ɹPostgreSQLͷ౷ܭ৘ใ
    ̐ɹPostgreSQLͷϞχλϦϯά
    ̑ɹ·ͱΊ

    View Slide

  11. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹPostgreSQLͷ಺෦ߏ଄
    ̏ɹPostgreSQLͷ౷ܭ৘ใ
    ̐ɹPostgreSQLͷϞχλϦϯά
    ̑ɹ·ͱΊ

    View Slide

  12. ࣗݾ঺հ
    ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ
    ೥ྸɿ33ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ
    ৬ۀɿCustomer Reliability Engineering
    ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ
    ɹɹɹ೔ຊPostgreSQLϢʔβձ
    ɹɹɹɹɹɹ ษڧձ୲౰
    ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

    View Slide

  13. ࣗݾ঺հ
    ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ
    ೥ྸɿ33ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ
    ৬ۀɿCustomer Reliability Engineering
    ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ
    ɹɹɹ೔ຊPostgreSQLϢʔβձ
    ɹɹɹɹɹɹ ษڧձ୲౰
    ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

    View Slide

  14. Mackerel

    View Slide

  15. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹPostgreSQLͷ಺෦ߏ଄
    ̏ɹPostgreSQLͷ౷ܭ৘ใ
    ̐ɹPostgreSQLͷϞχλϦϯά
    ̑ɹ·ͱΊ

    View Slide

  16. PostgreSQLͷ಺෦ߏ଄
    ԿΛϞχλϦϯά͢Δ͔ʁ

    View Slide

  17. PostgreSQLͷ಺෦ߏ଄
    ԿΛϞχλϦϯά͢Δ͔ʁ
    ˣ
    ಺෦ߏ଄Λ஌Βͳ͍ͱ
    ஋͕ཧղͰ͖ͳ͍

    View Slide

  18. PostgreSQLͷϓϩηε
    L
    L
    E
    E
    L
    X E
    I N
    D
    AE
    E
    W

    W
    E
    E

    View Slide

  19. ϓϩηε໊ આ໌
    Ϛελʔαʔό ࠷ॳʹىಈ͞ΕΔ਌ϓϩηε
    ϥΠλ ڞ༗όοϑΝͷ಺༰ΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ
    WALϥΠλ WALόοϑΝͷ಺༰ΛWALϑΝΠϧʹॻ͖ग़͢ɻ
    νΣοΫϙΠϯλ શͯͷμʔςΟʔϖʔδΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ
    ࣗಈVACUUMϥϯνϟ ઃఆʹ͕ͨͬͯࣗ͠ಈVACUUMϫʔΧΛىಈ͢Δɻ
    ࣗಈVACUUMϫʔΧ ࣗಈVACUUMΛ࣮ߦ͢Δɻෳ਺ىಈ͢Δ͜ͱ͕͋Δɻ
    ౷ܭ৘ใίϨΫλ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใΛऩू͢Δɻ
    όοΫΤϯυϓϩηε ΫϥΠΞϯτͷ઀ଓཁٻຖʹىಈ͠ɺཁٻʹରͯ͠ॲཧ͢Δɻ
    ϩΨʔ PostgreSQLͷϩάΛϑΝΠϧ΁ॻ͖ग़͢ɻ
    ΞʔΧΠό WALϩάΛΞʔΧΠϒ͢Δɻ
    WALηϯμ ϨϓϦέʔγϣϯ࣌ʹWALΛεϨʔϒαʔόʹసૹ͢Δɻ
    WALϨγʔό ϨϓϦέʔγϣϯ࣌ʹWALΛϚελʔαʔό͔Βड৴͢Δɻ
    ओͳϓϩηε܈

    View Slide

  20. PostgreSQLͷ಺෦ߏ଄
    ओͳϝϞϦ܈
    ໊લ આ໌
    ڞ༗όοϑΝ
    (shared_buffers)
    ςʔϒϧ΍ΠϯσοΫεͷσʔλΛΩϟογϡ͢ΔྖҬͰ͢ɻ
    WALόοϑΝ
    (wal_buffers)
    σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍τϥϯβΫγϣϯϩάΛΩϟογϡ͢ΔྖҬͰ͢ɻ
    ՄࢹੑϚοϓ
    (Visibility Map)
    ςʔϒϧͷσʔλ͕ࢀরग़དྷΔ͔൱͔؅ཧ͢Δ৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹॲཧର৅ͷϖʔ
    δ͔൑அ͢Δࡍʹར༻͞Ε·͢ɻ·ͨՄࢹੑϚοϓ͸VACUUMॲཧ΍֤ߋ৽ॲཧͷࡍʹߋ৽͞Ε·͢ɻ
    PostgreSQL 9.2Ҏ߱Ͱ͸ΠϯσοΫεɾΦϯϦʔɾεΩϟϯͱݴ͏ͱͯ΋ߴ଎ͳݕࡧํࣜͷࡍʹ΋ར༻͞
    Ε·͢ɻ
    ۭ͖ྖҬϚοϓ
    (Free Scan Map)
    ςʔϒϧ্ͷར༻ՄೳͳྖҬΛࢦࣔ͢͠৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹશ͘ࢀর͞Ε͍ͯͳ͍
    ߦΛ୳ۭ͖ͯ͠ྖҬͱͯ͠࠶ར༻ग़དྷΔঢ়ଶʹ͠·͢ɻͦͷޙɺ௥Ճ΍ߋ৽࣌ʹۭ͖ྖҬϚοϓΛ୳ࡧ
    ͠ɺۭ͖ྖҬΛ࠶ར༻͠·͢ɻ

    View Slide

  21. ໊લ આ໌
    σʔλϑΝΠϧ
    ςʔϒϧσʔλͷ࣮ମ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧ͸ෳ਺ͷ8192όΠτͷϖʔδ
    (OracleDBͰ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ
    ̍Fileͷ࠷େ͸1GBͰ͢ɻ
    INDEXϑΝΠϧ
    INDEX৘ใ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧͱಉ༷ʹෳ਺ͷ8192όΠτͷϖʔδ(OracleDB
    Ͱ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ
    WALϑΝΠϧ
    Write Ahead LoggingͷུͰτϥϯβΫγϣϯϩάΛPostgreSQLͰ͸WALͱݺͼ·͢ɻߋ৽ʹؔΘΔ৘ใ
    ΛهԱ͢Δ͜ͱͰσʔλϕʔεͷӬଓੑͷอূΛߦ͍ͬͯ·͢ɻpg_xlogʢPostgreSQL 10͔Β͸pg_walʣ
    σΟϨΫτϦ഑Լʹอଘ͞Εɺ16MBͷݻఆαΠζͰ࡞੒͞Ε·͢ɻ
    PostgreSQLͷ಺෦ߏ଄
    ओͳϑΝΠϧ܈

    View Slide

  22. όοΫΤϯυϓϩηε
    PostgreSQLͷϝϞϦ
    όοΫΤϯυϓϩηε
    ϫʔΫϝϞϦ ϫʔΫϝϞϦ
    ڞ༗όοϑΝ 8"-όοϑΝ
    04ͷόοϑΝΩϟογϡ
    σʔλϑΝΠϧ 8"-
    %#
    σΟεΫྖҬ
    ϝϞϦྖҬ
    ϓϩηεྖҬ

    View Slide

  23. PostgreSQLͷσʔλ
    %#ྖҬ 8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ
    ςʔϒϧϑΝΠϧ
    */%&9ϑΝΠϧ
    8"-
    ΞʔΧΠϒ
    ϑΝΠϧ
    7.
    '4.

    View Slide

  24. PostgreSQLͷσʔλߏ଄
    (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7.
    '4.
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ

    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ

    ߦσʔλ
    ߦσʔλ
    ςʔϒϧϑΝΠϧ
    (#

    View Slide

  25. PostgreSQLͷσʔλߏ଄
    (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7.
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ

    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ

    ߦσʔλ
    ߦσʔλ
    ςʔϒϧϑΝΠϧ
    (#
    ςʔϒϧͷϖʔδϔομ͸
    CZUFݻఆ
    '4.

    View Slide

  26. PostgreSQLͷσʔλߏ଄
    (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7.
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ

    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ

    ߦσʔλ
    ߦσʔλ
    ΞΠςϜϙΠϯλ ߦϔομ

    ͸CZUF
    ςʔϒϧϑΝΠϧ
    (#
    '4.

    View Slide

  27. PostgreSQLͷσʔλߏ଄
    (#ͷςʔϒϧ
    ςʔϒϧϑΝΠϧ
    (#
    7.
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ
    ,#ϖʔδ

    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ

    ߦσʔλ
    ߦσʔλ
    ςʔϒϧϑΝΠϧ
    (#
    ࣮ࡍͷϨίʔυͷαΠζ
    '4.

    View Slide

  28. PostgreSQLͷߦσʔλߏ଄
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ

    ߦσʔλ
    ߦσʔλ
    JE ໊લ ࡞੒೔࣌
    IPHF
    'VHB
    'PP
    #BS
    JOU 5FYU UJNFTUBNQ
    CZUF CZUFจࣈྻͷCZUF CZUF

    View Slide

  29. PostgreSQLͷINDEXσʔλߏ଄
    ϖʔδϔομ
    ϊʔυϔομ
    ϊʔυϔομ

    */%&9ΤϯτϦ
    */%&9ΤϯτϦ
    JE ໊લ ࡞੒೔࣌
    IPHF
    'VHB
    'PP
    #BS
    JOU
    CZUF

    View Slide

  30. PostgreSQLͷINDEXσʔλߏ଄
    ϖʔδϔομ
    ϊʔυϔομ
    ϊʔυϔομ

    */%&9ΤϯτϦ
    */%&9ΤϯτϦ
    JE ໊લ ࡞੒೔࣌
    IPHF
    'VHB
    'PP
    #BS
    JOU
    CZUF
    ओΩʔͷ*/%&9ͷ৔߹
    JEྻͷ஋Λ֤*/%&9ΤϯτϦ
    ʹอ࣋͢Δ

    View Slide

  31. PostgreSQLͷINDEXσʔλߏ଄
    ϖʔδϔομ
    ϊʔυϔομ
    ϊʔυϔομ

    */%&9ΤϯτϦ
    */%&9ΤϯτϦ
    JE ໊લ ࡞੒೔࣌
    IPHF
    'VHB
    'PP
    #BS
    JOU
    CZUF
    ϊʔυϔομ͸CZUF
    ϖʔδϔομͷݻఆ͸CZUF

    View Slide

  32. PostgreSQLͷσʔλ
    %#ྖҬ 8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ
    ςʔϒϧϑΝΠϧ
    */%&9ϑΝΠϧ
    8"-
    ΞʔΧΠϒ
    ϑΝΠϧ
    7.
    '4.
    ௨ৗ͸ͭ.#ͷ8"-ηάϝϯτ
    ϑΝΠϧΛ॥؀ར༻
    ෆཁͳ8"-͸ࣗಈతʹॳظԽ͞ΕΔ
    ʢNBY@XBM@TJ[FͷઃఆˡҎ߱ʣ

    View Slide

  33. PostgreSQLͷϓϩηε
    L
    L
    E
    E
    L
    X E
    I N
    D
    AE
    E
    W

    W
    E
    E

    View Slide

  34. PostgreSQLͷσʔλ
    %#ྖҬ 8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ
    ςʔϒϧϑΝΠϧ
    */%&9ϑΝΠϧ
    8"-
    ΞʔΧΠϒ
    ϑΝΠϧ
    7.
    '4.
    ΞʔΧΠϒϞʔυΛ0/ʹ͢Δͱ8"-
    ͷফࣦΛ๷͙ͨΊʹຊདྷ॥؀͢Δ8"-
    Λ࢒͓ͯ͘͠

    View Slide

  35. PostgreSQLͷ಺෦ߏ଄
    ԿΛϞχλϦϯά͢Δ͔ʁ

    View Slide

  36. PostgreSQLͷ಺෦ߏ଄
    ԿΛϞχλϦϯά͢Δ͔ʁ
    ˣ
    42-ͷྲྀΕΛ஌Βͳ͍ͱ
    Կ͕ى͍ͬͯ͜Δ͔Θ͔Βͳ͍

    View Slide

  37. PostgreSQLͷ಺෦ߏ଄
    2VFSZͷड৴
    ߏจղੳ
    ॻ͖׵͑
    ࣮ߦܭըੜ੒࠷దԽ
    ࣮ߦ
    ݁Ռૹ৴
    1BSTFS
    42-ͷߏจղੳɾจ๏Τϥʔݕग़ɾߏจ໦ͷੜ੒
    3FXSJUFS
    7JFXɾ3VMFʹجͮ͘ߏจ໦ͷॻ͖׵͑
    1MBOOFS0QUJNBJ[FS
    ࣮ߦܭըͷੜ੒౷ܭ৘ใͳͲΛར༻ͨ͠࠷దԽ
    &YFDVUPS
    ࣮ߦܭըͷج͍ͮͨ2VFSZͷ࣮ߦɾ8"-ͷ௥هͳͲ
    42-จͷॲཧ͞ΕΔྲྀΕ

    View Slide

  38. PostgreSQLͷ಺෦ߏ଄
    '30.۟
    0/۟
    +0*/۟
    8)&3&۟
    (3061#:۟
    )"7*/(۟
    4&-&$5۟
    %*45*/$5۟
    03%&3#:۟
    -*.*5۟
    42-จͷධՁ͞ΕΔॱ
    IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM

    View Slide

  39. PostgreSQLͷ಺෦ߏ଄
    '30.۟
    0/۟
    +0*/۟
    8)&3&۟
    (3061#:۟
    )"7*/(۟
    4&-&$5۟
    %*45*/$5۟
    03%&3#:۟
    -*.*5۟
    42-จͷධՁ͞ΕΔॱ
    IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM
    ͲΕ͘Β͍ͷσʔλΛѻ͏͔
    ೗ԿʹσʔλΛߜΓࠐΉ͔

    View Slide

  40. PostgreSQLͷ಺෦ߏ଄
    1PTUHSF42-͸௥هܕ

    View Slide

  41. PostgreSQLͷ಺෦ߏ଄
    1
    2
    3

    1
    3

    2
    PostgreSQL(




    View Slide

  42. PostgreSQLͷ಺෦ߏ଄
    1
    2
    3

    1
    3
    2

    2
    PostgreSQL(



    View Slide

  43. PostgreSQLͷ಺෦ߏ଄
    1
    2
    3

    1
    3
    2

    2
    PostgreSQL(



    ͜ͷ࢒ͬͨچϨίʔυ
    Λ៉ྷ͢Δͷ͕7"$66.

    View Slide

  44. PostgreSQLͷ಺෦ߏ଄
    1PTUHSF42-͸௥هܕ

    View Slide

  45. PostgreSQLͷ಺෦ߏ଄
    1PTUHSF42-͸௥هܕ
    ˣ
    )05 )FBQ0OMZ5VQMF
    ͱ
    '*--'"$503

    View Slide

  46. HOTͱFILLFACTOR
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ߦσʔλ
    ߦσʔλ
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ`
    ߦσʔλ`
    ߦσʔλ
    ߦσʔλ
    ߦσʔλΛ
    ߋ৽

    View Slide

  47. HOTͱFILLFACTOR
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ߦσʔλ
    ߦσʔλ
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ`
    ߦσʔλ`
    ߦσʔλ
    ߦσʔλ
    ߦσʔλΛ
    ߋ৽
    ͜ͷۭ͖ྖҬΛ؅ཧ͢Δ࢓૊Έ͕
    '*--'"$503
    ςʔϒϧϑΝΠϧͷσϑΥϧτ͸
    ͳͷͰۭ͖ྖҬΛ࡞Βͳ͍

    View Slide

  48. HOTͱFILLFACTOR
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ߦσʔλ
    ߦσʔλ
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ`
    ߦσʔλ`
    ߦσʔλ
    ߦσʔλ
    ߦσʔλΛ
    ߋ৽
    ݕࡧ࣌ͷಈ࡞
    ΞΠςϜϙΠϯλΛ֬ೝ
    ΞΠςϜϙΠϯλ`Λ֬ೝ
    ߦσʔλ`Λऔಘ

    View Slide

  49. HOTͱFILLFACTOR
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ߦσʔλ
    ߦσʔλ
    ϖʔδϔομ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ
    ΞΠςϜϙΠϯλ`
    ߦσʔλ`
    ߦσʔλ
    ߦσʔλ
    ߦσʔλΛ
    ߋ৽
    ۭ͖ྖҬΛ࢖͑Δͱ*/%&9Λߋ
    ৽͢Δ͜ͱແ͘σʔλΛߋ৽
    ݹ͍Ϩίʔυ͸7"$66.Λ଴
    ͨͣʹ࠶ར༻Մೳ

    View Slide

  50. HOTͱFILLFACTOR
    )05ߋ৽͕ར༻Ͱ͖Δ৚݅
    w ಉҰϖʔδ಺ʹߋ৽ޙͷσʔλΛ
    อଘͰ͖Δ
    w *OEFYྻΛߋ৽͠ͳ͍

    View Slide

  51. PostgreSQLͷ಺෦ߏ଄
    1PTUHSF42-ͷ಺෦ߏ଄Λ஌Δ

    View Slide

  52. PostgreSQLͷ಺෦ߏ଄
    1PTUHSF42-ͷ಺෦ߏ଄Λ஌Δ
    ˣ
    ϞχλϦϯάͷצॴ͕ݟ͑ͯ͘Δ

    View Slide

  53. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹPostgreSQLͷ಺෦ߏ଄
    ̏ɹPostgreSQLͷ౷ܭ৘ใ
    ̐ɹPostgreSQLͷϞχλϦϯά
    ̑ɹ·ͱΊ

    View Slide

  54. PostgreSQLͷ౷ܭ৘ใ
    ౷ܭ৘ใίϨΫλ͕
    αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใΛऩू

    View Slide

  55. PostgreSQLͷ಺෦ߏ଄
    L
    L
    E
    E
    L
    X E
    I N
    D
    AE
    E
    W

    W
    E
    E

    View Slide

  56. PostgreSQLͷ౷ܭ৘ใ
    αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ

    View Slide

  57. PostgreSQLͷ౷ܭ৘ใ
    αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ
    ˣ
    ౷ܭ৘ใ

    View Slide

  58. PostgreSQLͷ౷ܭ৘ใ
    ౷ܭ৘ใ
    ެࣜυΩϡϝϯτ
    IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMNPOJUPSJOHTUBUTIUNM

    View Slide

  59. PostgreSQLͷ౷ܭ৘ใ
    QPTUHSFTRMDPOGͰ
    QH@TUBU@TUBUFNFOUTΛ༗ޮʹ͢Δ

    View Slide

  60. PostgreSQLͷ౷ܭ৘ใ
    QPTUHSFTRMDPOGͰ
    QH@TUBU@TUBUFNFOUTΛ༗ޮʹ͢Δ
    42-จͷ࣮ߦ࣌ؒɺ࣮ߦճ਺ɺऔಘͨ͠ʢ·ͨ͸ߋ৽ͨ͠ʣ
    Ϩίʔυ਺ͳͲ͕อଘ͞ΕΔ

    View Slide

  61. PostgreSQLͷ౷ܭ৘ใ
    ౷ܭ৘ใΛ·ͱΊͨศརͳ7JFX

    View Slide

  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ʹؔ͢Δ౷ܭ৘ใ

    View Slide

  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ʹؔ͢Δ౷ܭ৘ใ
    νΣοΫϙΠϯτͷ࣮ߦճ਺΍࣮ߦ࣌ؒͳͲ͕ݟΕΔ
    ߋ৽͕ܹ͍͠৔߹ʹ͸νΣοΫϙΠϯτ͕ॏཁͳՕॴʹͳΔ

    View Slide

  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αʔόͷΞΫηε͕ॏ͍౳͕ݟ͑ͯ͘Δ

    View Slide

  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ʹؔ͢Δ౷ܭ৘ใ
    σʔλϕʔεશମͷࢀরɾ௥Ճɾߋ৽ɾ࡟আճ਺ͳͲ͕ݟΕΔ
    σουϩοΫͷճ਺΍Ұ࣌ϑΝΠϧͷ࡞੒ճ਺΋ݟΕΔͷͰʮٸʹ%#͕ॏ͘ͳΔ
    લʹؾ෇͘ʯͨΊʹܧଓతʹϞχλϦϯά͕େࣄ

    View Slide

  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.ͷ࣮ߦճ਺ͳͲ͕ݟΕΔ

    View Slide

  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ʹ͸
    ؚ·Ε͍ͯͳ͍

    View Slide

  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ͷ૿ՃྔΛݟΔ͜ͱͰ܏޲Λ༧ଌ
    ͢Δ͜ͱ͕Ͱ͖Δ

    View Slide

  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ͷঢ়گ΋ݟΕΔͨΊɺେ͖ͳσʔλΛѻ͍ͬͯΔςʔϒϧͰ͸ཁ؂ࢹ

    View Slide

  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൛
    όοϑΝΩϟογϡͷώοτ཰ͳͲܭࢉͰ͖Δ

    View Slide

  71. PostgreSQLͷ౷ܭ৘ใ
    ౷ܭ৘ใʹ͸42-ͰΞΫηεͰ͖Δ

    View Slide

  72. PostgreSQLͷ౷ܭ৘ใ
    ౷ܭ৘ใͷ׆༻ͷྫ
    w ൃߦ͞Ε͍ͯΔ2VFSZ
    w ϩοΫͷ಺༰
    w */%&9ͷར༻ঢ়گ
    w νΣοΫϙΠϯτͷॲཧঢ়گʜFUD

    View Slide

  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

    View Slide

  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 ʹ஫ҙ

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  80. PostgreSQLͷ౷ܭ৘ใ
    -FUT1PTUHSFT
    lՔಈ౷ܭ৘ใΛ׆༻͠Α͏z
    IUUQTMFUTQPTUHSFTRMKQEPDVNFOUTUFDIOJDBMTUBUJTUJDT

    View Slide

  81. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹPostgreSQLͷ಺෦ߏ଄
    ̏ɹPostgreSQLͷ౷ܭ৘ใ
    ̐ɹPostgreSQLͷϞχλϦϯά
    ̑ɹ·ͱΊ

    View Slide

  82. PostgreSQLͷϞχλϦϯά
    ౷ܭ৘ใΛ࢖͏ͱ
    1PTUHSF42-ΛϞχλϦϯάͰ͖Δ

    View Slide

  83. PostgreSQLͷϞχλϦϯά
    ͔͠͠ྺ࢙Λ࣋ͨͳ͍

    View Slide

  84. PostgreSQLͷϞχλϦϯά
    ͔͠͠ྺ࢙Λ࣋ͨͳ͍
    ˣ
    ࣌ܥྻ%#ʹอଘͯ͠ՄࢹԽ

    View Slide

  85. PostgreSQLͷϞχλϦϯά

    View Slide

  86. PostgreSQLͷϞχλϦϯά
    ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ
    ϓϩηε؂ࢹ ˓ ˓ ˓
    ༰ྔ؂ࢹ ☓ ˓ ˓
    ϩά؂ࢹ ☓ ˓ ˓
    ύϑΥʔϚϯε
    ؂ࢹ
    ☓ ☓ ˓

    View Slide

  87. PostgreSQLͷϞχλϦϯά
    ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ
    ϓϩηε؂ࢹ ˓ ˓ ˓
    ༰ྔ؂ࢹ ☓ ˓ ˓
    ϩά؂ࢹ ☓ ˓ ˓
    ύϑΥʔϚϯε
    ؂ࢹ
    ☓ ☓ ˓
    ΋ͬͱ΋γϯϓϧ QH@JTSFBEZͰྑ͍

    ͔͠͠αʔϏε͕མͪΔ·ͰΘ͔Βͳ͍

    View Slide

  88. PostgreSQLͷϞχλϦϯά
    ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ
    ϓϩηε؂ࢹ ˓ ˓ ˓
    ༰ྔ؂ࢹ ☓ ˓ ˓
    ϩά؂ࢹ ☓ ˓ ˓
    ύϑΥʔϚϯε
    ؂ࢹ
    ☓ ☓ ˓
    ঢ়ଶͷมԽ͕Θ͔Δ
    ͔͠͠มԽͷࠩ෼͕ͳ͍ͨΊ
    ༧ஹʹ͸ؾ෇͖ʹ͍͘

    View Slide

  89. PostgreSQLͷϞχλϦϯά
    ߲໨ ࢮ׆؂ࢹ νΣοΫ؂ࢹ Ϧιʔε؂ࢹ
    ϓϩηε؂ࢹ ˓ ˓ ˓
    ༰ྔ؂ࢹ ☓ ˓ ˓
    ϩά؂ࢹ ☓ ˓ ˓
    ύϑΥʔϚϯε
    ؂ࢹ
    ☓ ☓ ˓
    աڈͱࠓͷࠩ෼Λൺֱ͢Δ͜ͱͰݱঢ়Λ
    ΑΓਖ਼͘͠೺ѲͰ͖Δ
    άϥϑͰՄࢹԽ͢Δ͜ͱͰΘ͔Γ΍͍͢

    View Slide

  90. PostgreSQLͷϞχλϦϯά
    1PTUHSF42-Λӡ༻͢ΔͨΊͷ؂ࢹ

    View Slide

  91. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ
    PostgreSQLͷϞχλϦϯά

    View Slide

  92. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ
    PostgreSQLͷϞχλϦϯά
    σΟεΫ༰ྔɺσʔλϕʔεɺΦϒδΣΫτ౳ͷ
    αΠζΛ؂ࢹ

    View Slide

  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`

    View Slide

  94. PostgreSQLͷσʔλ
    %#ྖҬ 8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ
    ςʔϒϧϑΝΠϧ
    */%&9ϑΝΠϧ
    8"-
    ΞʔΧΠϒ
    ϑΝΠϧ
    7.
    '4.
    EG΍EVTͰ֬ೝ͢Δ
    ΞʔΧΠϒϑΝΠϧ͸όοΫΞοϓͷ΍Γํ
    ΍ස౓ʹΑͬͯอଘ͢Δର৅͕͔ΘΔͷͰ
    ઃܭ࣌ʹ͔ͬ͠Γͱݟੵ΋Δࣄ

    View Slide

  95. PostgreSQLͷσʔλ
    %#ྖҬ 8"-ྖҬ
    ΞʔΧΠϒ
    8"-ྖҬ
    σΟεΫྖҬ
    ςʔϒϧϑΝΠϧ
    */%&9ϑΝΠϧ
    8"-
    ΞʔΧΠϒ
    ϑΝΠϧ
    7.
    '4.
    σʔλϕʔε΍ΦϒδΣΫταΠζ͸ؔ਺Ͱ֬ೝ
    QH@SFMBUJPO@TJ[F
    ʹ͸*/%&9໊΋ࢦఆͰ͖Δ
    QH@UPUBM@SFMBUJPO@TJ[F
    ʹ͸*/%&9ͳͲͷαΠζ΋ؚ·ΕΔ

    View Slide

  96. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ
    PostgreSQLͷϞχλϦϯά
    ౷ܭ৘ใ͔Β࣮ߦঢ়ଶΛ؂ࢹ͢Δ

    View Slide

  97. VACUUMͷඞཁੑ
    7"$66.ͱ7"$66.'6--͕͋Δ
    w 7"$66.Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞Εͨߦͷ࠶ར༻
    w τϥϯβΫγϣϯ*%पճͷճආ
    w 7"$66.'6--Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞ΕͨߦΛ੾Γ٧ΊΔ
    w σΟεΫͷ࢖༻ྔΛ෺ཧతʹॖখ͢Δ

    View Slide

  98. 7"$66.ͱ7"$66.'6--͕͋Δ
    w 7"$66.Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞Εͨߦͷ࠶ར༻
    w τϥϯβΫγϣϯ*%पճͷճආ
    w 7"$66.'6--Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞ΕͨߦΛ੾Γ٧ΊΔ
    w σΟεΫͷ࢖༻ྔΛ෺ཧతʹॖখ͢Δ
    VACUUMͷඞཁੑ
    QH@TUBU@VTFS@UBCMFTͳͲΛ׆༻ͯ͠ਖ਼࣮͘͠ߦ
    ͞Ε͍ͯΔࣄΛ֬ೝ͢Δ

    View Slide

  99. 7"$66.ͱ7"$66.'6--͕͋Δ
    w 7"$66.Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞Εͨߦͷ࠶ར༻
    w τϥϯβΫγϣϯ*%पճͷճආ
    w 7"$66.'6--Λ࣮ߦ͢Δ໨త
    w ߋ৽࡟আ͞ΕͨߦΛ੾Γ٧ΊΔ
    w σΟεΫͷ࢖༻ྔΛ෺ཧతʹॖখ͢Δ
    VACUUMͷඞཁੑ
    QH@TUBU@VTFS@UBCMFTͳͲΛ׆༻ͯ͠ਖ਼࣮͘͠ߦ
    ͞Ε͍ͯΔࣄΛ֬ೝ͢Δ
    7"$66.࣮ߦ࣌͸ύϑΥʔϚϯεྼԽͷτϦΨʔ
    ʹͳΓ΍͍͢ͷͰ࣮ߦλΠϛϯάΛอଘ͢Δ

    View Slide

  100. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ
    PostgreSQLͷϞχλϦϯά
    8"-ͷసૹঢ়گ΍λΠϜϥΠϯͳͲΛ֬ೝ͢Δ
    ࠓ೔͸ϨϓϦέʔγϣϯͷηογϣϯ͕͋ΔͷͰׂѪ

    View Slide

  101. ϞχλϦϯάͷର৅
    w σΟεΫ༰ྔ؂ࢹ
    w 7"$66.؂ࢹ
    w ϨϓϦέʔγϣϯ؂ࢹ
    w αʔόϩά؂ࢹ
    PostgreSQLͷϞχλϦϯά
    Τϥʔϩάͷग़ྗ݁ՌͳͲΛ؂ࢹ͢Δ

    View Slide

  102. PostgreSQLͷϩά
    MPH@NJO@NFTTBHFTͷ
    ΤϥʔϨϕϧΛࢦఆ͢Δ

    View Slide

  103. PostgreSQLͷϩά
    ਂࠁ౓ ࢖༻ํ๏ TZTMPH
    %(%( ։ൃऀ͕࢖༻͢Δ࿈ଓత͔ͭΑΓৄࡉͳ৘ใΛఏڙ͠·͢ɻ %(
    */'0
    7"$66.7&3#04&ͷग़ྗͳͲͷɺϢʔβʹΑͬͯ҉໧తʹཁٻ
    ͞Εͨ৘ใΛఏڙ͠·͢ɻ
    */'0
    /05*$&
    ௕͍ࣝผࢠͷ੾Γ٧Ίʹؔ͢Δ஫ҙͳͲɺϢʔβͷิॿʹͳΔ৘ใ
    Λఏڙ͠·͢ɻ
    /05*$&
    8"3/*/(
    τϥϯβΫγϣϯϒϩοΫ֎Ͱͷ$0..*5ͷ༷ͳɺϢʔβ΁ͷܯࠂ
    Λఏڙ͠·͢ɻ
    /05*$&
    &3303 ݱࡏͷίϚϯυΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใࠂ͠·͢ɻ 8"3/*/(
    -0(
    νΣοΫϙΠϯτͷ׆ಈͷ༷ͳɺ؅ཧऀʹؔ৺ͷ͋Δ৘ใΛใࠂ͠
    ·͢ɻ
    */'0
    '"5"- ݱࡏͷηογϣϯΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใࠂ͠·͢ɻ &33
    1"/*$
    શͯͷσʔλϕʔεηογϣϯΛதஅͤ͞ΔݪҼͱͳͬͨΤϥʔΛใ
    ࠂ͠·͢ɻ
    $3*5

    View Slide

  104. PostgreSQLͷϩά
    ᮢ஋Λ௒͑ͨ42-ͷ؂ࢹ
    ߲໨໊ ༻్
    MPH@NJO@FSSPS@TUBUFNFOU ࢦఆͷΤϥʔϨϕϧʹ֘౰͢Δ42-Λه࿥͢Δ
    MPH@NJO@EVSBUJPO@TUBUFNFOU ࢦఆͨ͠ϛϦඵҎ্ͷ࣮ߦ࣌ؒͷ42-Λه࿥͢Δ
    MPH@MPDL@XBJUT
    ϩοΫ֫ಘʹཁ͕ͨ࣌ؒ͠λΠϜΞ΢τͨ͠42-
    Λه࿥͢Δ

    View Slide

  105. PostgreSQLͷϞχλϦϯά
    ͜ΕΒΛՄࢹԽͯ͠
    1PTUHSF42-ͷৼΔ෣͍Λ஌Δ

    View Slide

  106. PostgreSQLͷϞχλϦϯά
    ՄࢹԽͷͨΊͷπʔϧ
    w .BDLFSFMQMVHJO
    w ;BCCJYQH@NPO[
    w QH@TUBUTJOGPQH@TUBUT@SFQPSUFS

    View Slide

  107. PostgreSQLͷϞχλϦϯά
    ՄࢹԽͷͨΊͷπʔϧ
    w .BDLFSFMQMVHJO
    w ;BCCJYQH@NPO[
    w QH@TUBUTJOGPQH@TUBUT@SFQPSUFS
    ؆୯Ͱ4BB4ͳͷͰ4FSWFSΛ༻ҙ͠ͳ͍͍ͯ͘ͷͰָ
    ଟ͘ͷϛυϧ΢ΣΞʹରԠ͍ͯ͠ΔͷͰҰׅ؅ཧͰ͖Δ
    ͔͠͠1PTUHSF42-ͷৄࡉΛݟΔʹ͸߲໨͕গͳ͍

    View Slide

  108. PostgreSQLͷϞχλϦϯά
    ՄࢹԽͷͨΊͷπʔϧ
    w .BDLFSFMQMVHJO
    w ;BCCJYQH@NPO[
    w QH@TUBUTJOGPQH@TUBUT@SFQPSUFS
    .BDLFSFMಉ༷ʹෳ਺ͷϛυϧ΢ΣΞʹରԠ
    1PTUHSF42-ͷࡉ͔͍৘ใ·ͰݟΕΔ
    4FSWFS͕ඞཁͰ୆਺͕૿͑Δͱ;BCCJYͷνϡʔχϯά΋ඞཁ

    View Slide

  109. PostgreSQLͷϞχλϦϯά
    ՄࢹԽͷͨΊͷπʔϧ
    w .BDLFSFMQMVHJO
    w ;BCCJYQH@NPO[
    w QH@TUBUTJOGPQH@TUBUT@SFQPSUFS
    1PTUHSF42-ઐ༻πʔϧ
    ৄࡉͳ৘ใ·ͰՄࢹԽͰ͖Δ
    Έ͔͔੡ͳͷͰ೔ຊޠυΩϡϝϯτ΋͋Δ
    ͔͠͠1PTUHSF42-ઐ༻ͳ্ʹ؀ڥߏங͕൥ࡶ

    View Slide

  110. PostgreSQLͷϞχλϦϯά
    ࣗ෼ʹ߹ͬͨπʔϧΛબͿ

    View Slide

  111. PostgreSQLͷϞχλϦϯά
    ࣗ෼ʹ߹ͬͨπʔϧΛબͿ
    ˣ
    ΠνϩʔͷόοτΛ࢖͔ͬͨΒͱݴͬͯ
    ΠνϩʔʹͳΕΔΘ͚Ͱ͸ͳ͍

    View Slide

  112. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹPostgreSQLͷ಺෦ߏ଄
    ̏ɹPostgreSQLͷ౷ܭ৘ใ
    ̐ɹPostgreSQLͷϞχλϦϯά
    ̑ɹ·ͱΊ

    View Slide

  113. ·ͱΊ
    ઌਓͷ஌ܙΛ࢖͏

    View Slide

  114. ·ͱΊ
    ઌਓͷ஌ܙΛ࢖͏
    ˣ
    ެࣜυΩϡϝϯτΛಡ΋͏

    View Slide

  115. ·ͱΊ
    ઌਓͷ஌ܙΛ࢖͏
    ˣ
    ެࣜυΩϡϝϯτΛಡ΋͏
    ҆৺ͷ೔ຊޠυΩϡϝϯτ

    View Slide

  116. ·ͱΊ
    ·ͣ͸ՄࢹԽΛ͢Δ

    View Slide

  117. ·ͱΊ
    ਪଌΑΓܭଌ

    View Slide

  118. ·ͱΊ
    ਪଌΑΓܭଌ

    ܭଌΑΓ؍ଌ

    View Slide

  119. ·ͱΊ
    ࣄ࣮ΛΑΓଟ͘ɺਖ਼͘͠஌Δ͜ͱͰ
    ະདྷΛਖ਼͘͠༧ଌͰ͖Δ

    View Slide

  120. View Slide

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

    View Slide

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

    ͳΜͱͳ͘Ͱ࢓ࣄ͸ग़དྷͳ͍

    View Slide

  123. ·ͱΊ

    View Slide

  124. ·ͱΊ

    View Slide

  125. ·ͱΊ

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    ඼࣭ΛՄࢹԽ͚ͨͩ͠Ͱ͸վળ͸͞Εͳ͍

    View Slide

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

    View Slide

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

    View Slide