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

PostgreSQL Performance Monitoring

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

PostgreSQL Performance Monitoring

吉祥寺.pm #12 の登壇資料です

https://kichijojipm.connpass.com/event/64456/

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

October 20, 2017
Tweet

Transcript

  1. PostgreSQLͷ ύϑΥʔϚϯε ϞχλϦϯά ٢঵ࣉQN

  2. What is it? ٢঵ࣉ.pm

  3. What is it? ٢঵ࣉ.p(erformance)m(onitoring)

  4. What is it? ϞχλϦϯάͯ͠·͔͢ʁ

  5. What is it? ࠓ೔͸15෼͔͠ͳ͍ͷͰ ಘҙͳϠπͷ࿩͠·͢

  6. What is it? ٢঵ࣉ.pm

  7. What is it? ٢঵ࣉ.pm ↓ ٢঵ࣉ. P(ostgreSQL) M(ySQL)

  8. What is it? PostgreSQLͷ࿩͠·͢

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

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

  11. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿCustomer Reliability Engineering ॴଐɿגࣜձࣾ ͸ͯͳʢMackerelνʔϜʣ ɹɹɹ೔ຊPostgreSQLϢʔβձ ɹɹɹɹɹɹ

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

    ษڧձ୲౰ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
  13. Mackerel

  14. ͸ͯͳ͸஥ؒΛ୳ͯ͠·͢ curl -sIL mackerel.io | grep engineer

  15. ͸ͯͳ͸஥ؒΛ୳ͯ͠·͢ curl -sIL mackerel.io | grep engineer ͜Εͩͱ$3&ग़ͯ͜ͳ͍ͷͰHSFQDSF͍ͯͩ͘͠͞ʂʂ

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

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

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

  19. ϓϩηε໊ આ໌ Ϛελʔαʔό ࠷ॳʹىಈ͞ΕΔ਌ϓϩηε ϥΠλ ڞ༗όοϑΝͷ಺༰ΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ WALϥΠλ WALόοϑΝͷ಺༰ΛWALϑΝΠϧʹॻ͖ग़͢ɻ νΣοΫϙΠϯλ શͯͷμʔςΟʔϖʔδΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ

    ࣗಈVACUUMϥϯνϟ ઃఆʹ͕ͨͬͯࣗ͠ಈVACUUMϫʔΧΛىಈ͢Δɻ ࣗಈVACUUMϫʔΧ ࣗಈVACUUMΛ࣮ߦ͢Δɻෳ਺ىಈ͢Δ͜ͱ͕͋Δɻ ౷ܭ৘ใίϨΫλ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭ৘ใΛऩू͢Δɻ όοΫΤϯυϓϩηε ΫϥΠΞϯτͷ઀ଓཁٻຖʹىಈ͠ɺཁٻʹରͯ͠ॲཧ͢Δɻ ϩΨʔ PostgreSQLͷϩάΛϑΝΠϧ΁ॻ͖ग़͢ɻ ΞʔΧΠό WALϩάΛΞʔΧΠϒ͢Δɻ WALηϯμ ϨϓϦέʔγϣϯ࣌ʹWALΛεϨʔϒαʔόʹసૹ͢Δɻ WALϨγʔό ϨϓϦέʔγϣϯ࣌ʹWALΛϚελʔαʔό͔Βड৴͢Δɻ ओͳϓϩηε܈
  20. ໊લ આ໌ σʔλϑΝΠϧ ςʔϒϧσʔλͷ࣮ମ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧ͸ෳ਺ͷ8192όΠτͷϖʔδ (OracleDBͰ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ INDEXϑΝΠϧ INDEX৘ใ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧͱಉ༷ʹෳ਺ͷ8192όΠτͷϖʔδ(OracleDB Ͱ͸ϒϩοΫ)ʹΑͬͯߏ੒͞Ε·͢ɻ WALϑΝΠϧ Write

    Ahead LoggingͷུͰτϥϯβΫγϣϯϩάΛPostgreSQLͰ͸WALͱݺͼ·͢ɻߋ৽ʹؔΘΔ৘ใ ΛهԱ͢Δ͜ͱͰσʔλϕʔεͷӬଓੑͷอূΛߦ͍ͬͯ·͢ɻpg_xlogσΟϨΫτϦ഑Լʹอଘ͞Εɺ 16MBͷݻఆαΠζͰ࡞੒͞Ε·͢ɻ PostgreSQLͷ಺෦ߏ଄ ओͳϑΝΠϧ܈
  21. PostgreSQLͷ಺෦ߏ଄ ओͳϝϞϦ܈ ໊લ આ໌ ڞ༗όοϑΝ (shared_buffers) ςʔϒϧ΍ΠϯσοΫεͷσʔλΛΩϟογϡ͢ΔྖҬͰ͢ɻ WALόοϑΝ (wal_buffers) σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍τϥϯβΫγϣϯϩάΛΩϟογϡ͢ΔྖҬͰ͢ɻ

    ՄࢹੑϚοϓ (Visibility Map) ςʔϒϧͷσʔλ͕ࢀরग़དྷΔ͔൱͔؅ཧ͢Δ৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹॲཧର৅ͷ ϖʔδ͔൑அ͢Δࡍʹར༻͞Ε·͢ɻ·ͨՄࢹੑϚοϓ͸VACUUMॲཧ΍֤ߋ৽ॲཧͷࡍʹߋ৽͞Ε ·͢ɻPostgreSQL 9.2Ҏ߱Ͱ͸ΠϯσοΫεɾΦϯϦʔɾεΩϟϯͱݴ͏ͱͯ΋ߴ଎ͳݕࡧํࣜͷࡍʹ ۭ͖ྖҬϚοϓ (Free Scan Map) ςʔϒϧ্ͷར༻ՄೳͳྖҬΛࢦࣔ͢͠৘ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹશ͘ࢀর͞Ε͍ͯ ͳ͍ߦΛ୳ۭ͖ͯ͠ྖҬͱͯ͠࠶ར༻ग़དྷΔঢ়ଶʹ͠·͢ɻͦͷޙɺ௥Ճ΍ߋ৽࣌ʹۭ͖ྖҬϚοϓΛ ୳ࡧ͠ɺۭ͖ྖҬΛ࠶ར༻͠·͢ɻ
  22. PostgreSQLͷ಺෦ߏ଄ L L E E L X E I N

    D AE  E  W   W E E
  23. PostgreSQLͷ಺෦ߏ଄ 2VFSZͷड৴ ߏจղੳ ॻ͖׵͑ ࣮ߦܭըੜ੒࠷దԽ ࣮ߦ ݁Ռૹ৴ 1BSTF 42-ͷߏจղੳɾจ๏Τϥʔݕग़ɾߏจ໦ͷੜ੒ 3FXSJUF

    7JFXɾ3PMFʹجͮ͘ߏจ໦ͷॻ͖׵͑ 1MBO0QUJNJ[F ࣮ߦܭըͷੜ੒౷ܭ৘ใͳͲΛར༻ͨ͠࠷దԽ &YFDVUF ࣮ߦܭըͷج͍ͮͨ2VFSZͷ࣮ߦɾ8"-ͷ௥هͳͲ 42-จͷॲཧ͞ΕΔྲྀΕ
  24. PostgreSQLͷ಺෦ߏ଄ '30.۟ 0/۟ +0*/۟ 8)&3&۟ (3061#:۟ )"7*/(۟ 4&-&$5۟ %*45*/$5۟ 03%&3#:۟

    -*.*5۟ 42-จͷධՁ͞ΕΔॱ IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM
  25. PostgreSQLͷ಺෦ߏ଄  1  2  3   1

     3   2 PostgreSQL(      
  26. PostgreSQLͷ಺෦ߏ଄  1  2  3   1

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

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

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

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

  31. PostgreSQLͷ಺෦ߏ଄ L L E E L X E I N

    D AE  E  W   W E E
  32. PostgreSQLͷ౷ܭ৘ใ αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ

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

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

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

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

  37. PostgreSQLͷϞχλϦϯά ϞχλϦϯάͷྫ w ൃߦ͞Ε͍ͯΔ2VFSZ w ϩοΫͷ಺༰ w */%&9ͷར༻ঢ়گ w νΣοΫϙΠϯτͷॲཧঢ়گʜFUD

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

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

  40. PostgreSQLͷϞχλϦϯά

  41. PostgreSQLͷϞχλϦϯά QH@TUBU@TUBUFNFOUTΛ࢖͏ IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMQHTUBUTUBUFNFOUTIUNM

  42. PostgreSQLͷϞχλϦϯά QH@TUBU@TUBUFNFOUTΛ࢖͏ ˣ 42-ͷ࣮ߦΛ௥͑Δ

  43. PostgreSQLͷϞχλϦϯά QH@TUBU@TUBUFNFOUTΛ࢖͏ ˣ 42-ͷ࣮ߦΛ௥͑Δ σϑΥϧτP⒎ ࣗ෼Ͱ༗ޮʹͯ͠΍Δඞཁ͕͋Δ

  44. PostgreSQLͷϞχλϦϯά ϞχλϦϯάͷྫ

  45. εϧʔϓοτΤϥʔͷ֬ೝ =# SELECT datname, xact_commit, xact_rollback FROM pg_stat_database; datname |

    xact_commit | xact_rollback -----------+-------------+--------------- template1 | 0 | 0 template0 | 0 | 0 postgres | 101216 | 1
  46. Ωϟογϡώοτ཰ͷ֬ೝ =# 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 ʹ஫ҙ
  47. ςʔϒϧͷ Ωϟογϡώοτ཰ͷ֬ೝ =# 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
  48. ΠϯσοΫεͷ Ωϟογϡώοτ཰ͷ֬ೝ =# 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
  49. දεΩϟϯ͋ͨΓͷಡΈऔΓߦ਺ͷ֬ೝ =# 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
  50. )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
  51. ϩοΫ଴ͪॲཧͷ֬ೝ =# 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
  52. PostgreSQLͷϞχλϦϯά -FUT1PTUHSFT lՔಈ౷ܭ৘ใΛ׆༻͠Α͏z IUUQTMFUTQPTUHSFTRMKQEPDVNFOUTUFDIOJDBMTUBUJTUJDT

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

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

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

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

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

  58. ·ͱΊ ਪଌΑΓܭଌ

  59. ·ͱΊ ਪଌΑΓܭଌ ↓ ܭଌΑΓ؍ଌ

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

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

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

  64. ·ͱΊ

  65. ·ͱΊ

  66. ·ͱΊ

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

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

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

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

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

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