PostgreSQL Performance Monitoring

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

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

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&ग़ͯ͜ͳ͍ͷͰ
    HSFQ
    DSF
    ͍ͯͩ͘͠͞ʂʂ
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ͷϞχλϦϯά -FUT
    1PTUHSFT
    lՔಈ౷ܭ৘ใΛ׆༻͠Α͏z
    IUUQTMFUTQPTUHSFTRMKQEPDVNFOUTUFDIOJDBMTUBUJTUJDT
53. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹPostgreSQLͷ಺෦ߏ଄ ̏ɹPostgreSQLͷ౷ܭ৘ใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
54. ·ͱΊ ઌਓͷ஌ܙΛ࢖͏
55. ·ͱΊ ઌਓͷ஌ܙΛ࢖͏
    ˣ
    ެࣜυΩϡϝϯτΛಡ΋͏
    
56. ·ͱΊ ઌਓͷ஌ܙΛ࢖͏
    ˣ
    ެࣜυΩϡϝϯτΛಡ΋͏
    ҆৺ͷ೔ຊޠυΩϡϝϯτ
57. ·ͱΊ ·ͣ͸ՄࢹԽΛ͢Δ
58. ·ͱΊ ਪଌΑΓܭଌ
59. ·ͱΊ ਪଌΑΓܭଌ ↓ ܭଌΑΓ؍ଌ
60. ·ͱΊ ࣄ࣮ΛΑΓଟ͘ɺਖ਼͘͠஌Δ͜ͱͰ ະདྷΛਖ਼͘͠༧ଌͰ͖Δ
62. ·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ
63. ·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ ↓ ͳΜͱͳ͘Ͱ࢓ࣄ͸ग़དྷͳ͍
64. ·ͱΊ
65. ·ͱΊ
66. ·ͱΊ
67. ·ͱΊ ςετίʔυ͸ϓϩάϥϜͷ඼࣭ͷՄࢹԽ ϞχλϦϯά͸αʔϏεͷ඼࣭ͷՄࢹԽ
68. ·ͱΊ lߴ౓ʹൃୡͨ͠γεςϜͷҟৗ͸
    ਆͷౖΓͱݟ෼͚͕͔ͭͳ͍z
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    Š
    Z@VVLJ
69. ·ͱΊ ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍
70. ·ͱΊ ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍ ↓ ඼࣭ΛՄࢹԽ͚ͨͩ͠Ͱ͸վળ͸͞Εͳ͍
71. ·ͱΊ lखΛಈ͔ͨ͠ਓ͚͕ͩੈքΛม͑Δz
    Š
    :BTVIJSP
    0OJTIJ
72. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ