PostgreSQL Performance Monitoring

by soudai sone

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

What is it? ٢঵ࣉ.pm

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

What is it? ϞχλϦϯάͯ͠·͔͢ʁ

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

What is it? ٢঵ࣉ.pm

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

What is it? PostgreSQLͷ࿩͠·͢

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

Slide 13

Slide 13 text

Mackerel

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

PostgreSQLͷ಺෦ߏ଄ 2VFSZͷड৴ ߏจղੳ ॻ͖׵͑ ࣮ߦܭըੜ੒࠷దԽ ࣮ߦ ݁Ռૹ৴ 1BSTF 42-ͷߏจղੳɾจ๏Τϥʔݕग़ɾߏจ໦ͷੜ੒ 3FXSJUF 7JFXɾ3PMFʹجͮ͘ߏจ໦ͷॻ͖׵͑ 1MBO0QUJNJ[F ࣮ߦܭըͷੜ੒౷ܭ৘ใͳͲΛར༻ͨ͠࠷దԽ &YFDVUF ࣮ߦܭըͷج͍ͮͨ2VFSZͷ࣮ߦɾ8"-ͷ௥هͳͲ 42-จͷॲཧ͞ΕΔྲྀΕ

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

PostgreSQLͷ౷ܭ৘ใ ౷ܭ৘ใίϨΫλ͕ αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใΛऩू

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

PostgreSQLͷ౷ܭ৘ใ αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ

Slide 33

Slide 33 text

PostgreSQLͷ౷ܭ৘ใ αʔόͷ׆ಈঢ়گʹؔ͢Δ৘ใ ˣ ౷ܭ৘ใ

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

PostgreSQLͷϞχλϦϯά

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

εϧʔϓοτΤϥʔͷ֬ೝ =# SELECT datname, xact_commit, xact_rollback FROM pg_stat_database; datname | xact_commit | xact_rollback -----------+-------------+--------------- template1 | 0 | 0 template0 | 0 | 0 postgres | 101216 | 1

Slide 46

Slide 46 text

Ωϟογϡώοτ཰ͷ֬ೝ =# 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 ʹ஫ҙ

Slide 47

Slide 47 text

ςʔϒϧͷ Ωϟογϡώοτ཰ͷ֬ೝ =# 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

Slide 48

Slide 48 text

ΠϯσοΫεͷ Ωϟογϡώοτ཰ͷ֬ೝ =# 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

Slide 49

Slide 49 text

දεΩϟϯ͋ͨΓͷಡΈऔΓߦ਺ͷ֬ೝ =# 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

Slide 50

Slide 50 text

)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

Slide 51

Slide 51 text

Slide 52

Slide 52 text

PostgreSQLͷϞχλϦϯά -FUT1PTUHSFT lՔಈ౷ܭ৘ใΛ׆༻͠Α͏z IUUQTMFUTQPTUHSFTRMKQEPDVNFOUTUFDIOJDBMTUBUJTUJDT

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

·ͱΊ ઌਓͷ஌ܙΛ࢖͏

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

·ͱΊ ·ͣ͸ՄࢹԽΛ͢Δ

Slide 58

Slide 58 text

·ͱΊ ਪଌΑΓܭଌ

Slide 59

Slide 59 text

·ͱΊ ਪଌΑΓܭଌ ↓ ܭଌΑΓ؍ଌ

Slide 60

Slide 60 text

·ͱΊ ࣄ࣮ΛΑΓଟ͘ɺਖ਼͘͠஌Δ͜ͱͰ ະདྷΛਖ਼͘͠༧ଌͰ͖Δ

Slide 61

Slide 61 text

No content

Slide 62

Slide 62 text

·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ

Slide 63

Slide 63 text

·ͱΊ ΤϯδχΞʹ͸ࠜڌ͕ඞཁ ↓ ͳΜͱͳ͘Ͱ࢓ࣄ͸ग़དྷͳ͍

Slide 64

Slide 64 text

·ͱΊ

Slide 65

Slide 65 text

·ͱΊ

Slide 66

Slide 66 text

·ͱΊ

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

·ͱΊ ମॏܭʹ৐Δ͚ͩͰ͸૫ͤͳ͍

Slide 70

Slide 70 text

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