Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
PostgreSQL Performance Monitoring
soudai sone
October 20, 2017
Technology
6
3.4k
PostgreSQL Performance Monitoring
吉祥寺.pm #12 の登壇資料です
https://kichijojipm.connpass.com/event/64456/
soudai sone
October 20, 2017
Tweet
Share
More Decks by soudai sone
See All by soudai sone
目指すべきソフトウェア開発と 今日から始める最初の一歩 / First step for good development
soudai
1
350
あの人の自分戦略を聞きたい!2022 / developers-summit-2022
soudai
3
1.5k
今まで生き残ってきたRDBMSとこの先10年戦えるデータストア戦略 / Database now and in the past
soudai
8
8.1k
如何にデータベースが重要でなぜ私達が学ぶのか / Reasons for learning a database
soudai
20
10k
ソフトウェアエンジニアと技術力 / developer-lifework
soudai
33
21k
問題を解決するために必要な習慣 / developer-lifehack
soudai
29
9.8k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
39
13k
時間枠の扱いをいい感じにする話 / good-time-schedule-window
soudai
6
1.2k
RDBMSの苦手なことを 如何に乗り越えていくか / challenge-to-rdbms
soudai
17
3.6k
Other Decks in Technology
See All in Technology
Persistence in Serverless Applications - ServerlessDays NYC
marcduiker
0
250
現状のFedCMの動作解説と OIDCとの親和性について- OpenID TechNight vol.19
ritou
2
460
スクラムのスケールとチームトポロジー / Scaled Scrum and Team Topologies
daiksy
1
450
Build 2022で発表されたWindowsアプリ開発のあれこれ振り返ろう
hatsunea
1
390
Apa itu DevOps & Kenapa perlu belajar DevOps?
dicodingevent
0
110
アーキテクチャを明文化して開発に臨んだ話
akkie76
0
350
FastConnect 冗長性のベスト・プラクティス
ocise
0
120
SlackBotで あらゆる業務を自動化。問い合わせ〜DevOpsまで #CODT2022
kogatakanori
0
940
DOM Invader - prototype pollution対応の衝撃 - / DOM Invader - prototype pollution
okuken
0
160
Retca Cloud
bau
0
530
RDRA + JavaによるレジャーSaaSプロダクトの要件定義と実装のシームレスな接続
jjebejj
PRO
3
760
紙にまつわる苦しみを機能化してきた カミナシの歴史
kaminashi
0
1.3k
Featured
See All Featured
The Brand Is Dead. Long Live the Brand.
mthomps
46
2.7k
Building Flexible Design Systems
yeseniaperezcruz
310
34k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
226
15k
Automating Front-end Workflow
addyosmani
1351
200k
Streamline your AJAX requests with AmplifyJS and jQuery
dougneiner
127
8.5k
Product Roadmaps are Hard
iamctodd
34
6.5k
Building a Scalable Design System with Sketch
lauravandoore
448
30k
Designing the Hi-DPI Web
ddemaree
272
32k
Atom: Resistance is Futile
akmur
255
20k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
19
1.4k
Why Our Code Smells
bkeepers
PRO
324
55k
Intergalactic Javascript Robots from Outer Space
tanoku
261
25k
Transcript
PostgreSQLͷ ύϑΥʔϚϯε ϞχλϦϯά ٢ࣉQN
What is it? ٢ࣉ.pm
What is it? ٢ࣉ.p(erformance)m(onitoring)
What is it? ϞχλϦϯάͯ͠·͔͢ʁ
What is it? ࠓ15͔͠ͳ͍ͷͰ ಘҙͳϠπͷ͠·͢
What is it? ٢ࣉ.pm
What is it? ٢ࣉ.pm ↓ ٢ࣉ. P(ostgreSQL) M(ySQL)
What is it? PostgreSQLͷ͠·͢
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹPostgreSQLͷ෦ߏ ̏ɹPostgreSQLͷ౷ܭใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹPostgreSQLͷ෦ߏ ̏ɹPostgreSQLͷ౷ܭใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿCustomer Reliability Engineering ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹɹ
ษڧձ୲ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
ࣗݾհ ໊લɿીࠜɹେʢͦͶɹ͚ͨͱʣ ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿCustomer Reliability Engineering ॴଐɿגࣜձࣾ ͯͳʢMackerelνʔϜʣ ɹɹɹຊPostgreSQLϢʔβձ ɹɹɹɹɹɹ
ษڧձ୲ ɹɹٕज़తʹLLܥݴޠͱ͔RDB͕͖Ͱ͢
Mackerel
ͯͳؒΛ୳ͯ͠·͢ curl -sIL mackerel.io | grep engineer
ͯͳؒΛ୳ͯ͠·͢ curl -sIL mackerel.io | grep engineer ͜Εͩͱ$3&ग़ͯ͜ͳ͍ͷͰHSFQDSF͍ͯͩ͘͠͞ʂʂ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹPostgreSQLͷ෦ߏ ̏ɹPostgreSQLͷ౷ܭใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
PostgreSQLͷ෦ߏ ԿΛϞχλϦϯά͢Δ͔ʁ
PostgreSQLͷ෦ߏ ԿΛϞχλϦϯά͢Δ͔ʁ ˣ ෦ߏΛΒͳ͍ͱ ͕ཧղͰ͖ͳ͍
ϓϩηε໊ આ໌ Ϛελʔαʔό ࠷ॳʹىಈ͞ΕΔϓϩηε ϥΠλ ڞ༗όοϑΝͷ༰ΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ WALϥΠλ WALόοϑΝͷ༰ΛWALϑΝΠϧʹॻ͖ग़͢ɻ νΣοΫϙΠϯλ શͯͷμʔςΟʔϖʔδΛσʔλϑΝΠϧʹॻ͖ग़͢ɻ
ࣗಈVACUUMϥϯνϟ ઃఆʹ͕ͨͬͯࣗ͠ಈVACUUMϫʔΧΛىಈ͢Δɻ ࣗಈVACUUMϫʔΧ ࣗಈVACUUMΛ࣮ߦ͢Δɻෳىಈ͢Δ͜ͱ͕͋Δɻ ౷ܭใίϨΫλ σʔλϕʔεͷ׆ಈঢ়گʹؔ͢Δ౷ܭใΛऩू͢Δɻ όοΫΤϯυϓϩηε ΫϥΠΞϯτͷଓཁٻຖʹىಈ͠ɺཁٻʹରͯ͠ॲཧ͢Δɻ ϩΨʔ PostgreSQLͷϩάΛϑΝΠϧॻ͖ग़͢ɻ ΞʔΧΠό WALϩάΛΞʔΧΠϒ͢Δɻ WALηϯμ ϨϓϦέʔγϣϯ࣌ʹWALΛεϨʔϒαʔόʹసૹ͢Δɻ WALϨγʔό ϨϓϦέʔγϣϯ࣌ʹWALΛϚελʔαʔό͔Βड৴͢Δɻ ओͳϓϩηε܈
໊લ આ໌ σʔλϑΝΠϧ ςʔϒϧσʔλͷ࣮ମ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧෳͷ8192όΠτͷϖʔδ (OracleDBͰϒϩοΫ)ʹΑͬͯߏ͞Ε·͢ɻ INDEXϑΝΠϧ INDEXใ͕อଘ͞ΕΔϑΝΠϧͰ͢ɻςʔϒϧϑΝΠϧͱಉ༷ʹෳͷ8192όΠτͷϖʔδ(OracleDB ͰϒϩοΫ)ʹΑͬͯߏ͞Ε·͢ɻ WALϑΝΠϧ Write
Ahead LoggingͷུͰτϥϯβΫγϣϯϩάΛPostgreSQLͰWALͱݺͼ·͢ɻߋ৽ʹؔΘΔใ ΛهԱ͢Δ͜ͱͰσʔλϕʔεͷӬଓੑͷอূΛߦ͍ͬͯ·͢ɻpg_xlogσΟϨΫτϦԼʹอଘ͞Εɺ 16MBͷݻఆαΠζͰ࡞͞Ε·͢ɻ PostgreSQLͷ෦ߏ ओͳϑΝΠϧ܈
PostgreSQLͷ෦ߏ ओͳϝϞϦ܈ ໊લ આ໌ ڞ༗όοϑΝ (shared_buffers) ςʔϒϧΠϯσοΫεͷσʔλΛΩϟογϡ͢ΔྖҬͰ͢ɻ WALόοϑΝ (wal_buffers) σΟεΫʹॻ͖ࠐ·Ε͍ͯͳ͍τϥϯβΫγϣϯϩάΛΩϟογϡ͢ΔྖҬͰ͢ɻ
ՄࢹੑϚοϓ (Visibility Map) ςʔϒϧͷσʔλ͕ࢀরग़དྷΔ͔൱͔ཧ͢ΔใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹॲཧରͷ ϖʔδ͔அ͢Δࡍʹར༻͞Ε·͢ɻ·ͨՄࢹੑϚοϓVACUUMॲཧ֤ߋ৽ॲཧͷࡍʹߋ৽͞Ε ·͢ɻPostgreSQL 9.2Ҏ߱ͰΠϯσοΫεɾΦϯϦʔɾεΩϟϯͱݴ͏ͱͯߴͳݕࡧํࣜͷࡍʹ ۭ͖ྖҬϚοϓ (Free Scan Map) ςʔϒϧ্ͷར༻ՄೳͳྖҬΛࢦࣔ͢͠ใΛѻ͏ྖҬͰ͢ɻVACUUMॲཧͷࡍʹશ͘ࢀর͞Ε͍ͯ ͳ͍ߦΛ୳ۭ͖ͯ͠ྖҬͱͯ͠࠶ར༻ग़དྷΔঢ়ଶʹ͠·͢ɻͦͷޙɺՃߋ৽࣌ʹۭ͖ྖҬϚοϓΛ ୳ࡧ͠ɺۭ͖ྖҬΛ࠶ར༻͠·͢ɻ
PostgreSQLͷ෦ߏ L L E E L X E I N
D AE E W W E E
PostgreSQLͷ෦ߏ 2VFSZͷड৴ ߏจղੳ ॻ͖͑ ࣮ߦܭըੜ࠷దԽ ࣮ߦ ݁Ռૹ৴ 1BSTF 42-ͷߏจղੳɾจ๏Τϥʔݕग़ɾߏจͷੜ 3FXSJUF
7JFXɾ3PMFʹجͮ͘ߏจͷॻ͖͑ 1MBO0QUJNJ[F ࣮ߦܭըͷੜ౷ܭใͳͲΛར༻ͨ͠࠷దԽ &YFDVUF ࣮ߦܭըͷج͍ͮͨ2VFSZͷ࣮ߦɾ8"-ͷهͳͲ 42-จͷॲཧ͞ΕΔྲྀΕ
PostgreSQLͷ෦ߏ '30.۟ 0/۟ +0*/۟ 8)&3&۟ (3061#:۟ )"7*/(۟ 4&-&$5۟ %*45*/$5۟ 03%&3#:۟
-*.*5۟ 42-จͷධՁ͞ΕΔॱ IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMTFMFDUIUNM
PostgreSQLͷ෦ߏ 1 2 3 1
3 2 PostgreSQL(
PostgreSQLͷ෦ߏ 1 2 3 1
3 2 2 PostgreSQL(
PostgreSQLͷ෦ߏ Φεεϝຊʂ ͚ͩͲͷ
PostgreSQLͷ෦ߏ ɹQHͷ ೖهࣄ͕͋Δ
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹPostgreSQLͷ෦ߏ ̏ɹPostgreSQLͷ౷ܭใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
PostgreSQLͷ౷ܭใ ౷ܭใίϨΫλ͕ αʔόͷ׆ಈঢ়گʹؔ͢ΔใΛऩू
PostgreSQLͷ෦ߏ L L E E L X E I N
D AE E W W E E
PostgreSQLͷ౷ܭใ αʔόͷ׆ಈঢ়گʹؔ͢Δใ
PostgreSQLͷ౷ܭใ αʔόͷ׆ಈঢ়گʹؔ͢Δใ ˣ ౷ܭใ
PostgreSQLͷ౷ܭใ ౷ܭใ ެࣜυΩϡϝϯτ IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMNPOJUPSJOHTUBUTIUNM
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹPostgreSQLͷ෦ߏ ̏ɹPostgreSQLͷ౷ܭใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
PostgreSQLͷϞχλϦϯά ౷ܭใΛ͏ͱ 1PTUHSF42-ΛϞχλϦϯάͰ͖Δ
PostgreSQLͷϞχλϦϯά ϞχλϦϯάͷྫ w ൃߦ͞Ε͍ͯΔ2VFSZ w ϩοΫͷ༰ w */%&9ͷར༻ঢ়گ w νΣοΫϙΠϯτͷॲཧঢ়گʜFUD
PostgreSQLͷϞχλϦϯά ͔͠͠ྺ࢙Λ࣋ͨͳ͍
PostgreSQLͷϞχλϦϯά ͔͠͠ྺ࢙Λ࣋ͨͳ͍ ˣ ࣌ܥྻ%#ʹอଘͯ͠ՄࢹԽ
PostgreSQLͷϞχλϦϯά
PostgreSQLͷϞχλϦϯά QH@TUBU@TUBUFNFOUTΛ͏ IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMQHTUBUTUBUFNFOUTIUNM
PostgreSQLͷϞχλϦϯά QH@TUBU@TUBUFNFOUTΛ͏ ˣ 42-ͷ࣮ߦΛ͑Δ
PostgreSQLͷϞχλϦϯά QH@TUBU@TUBUFNFOUTΛ͏ ˣ 42-ͷ࣮ߦΛ͑Δ σϑΥϧτP⒎ ࣗͰ༗ޮʹͯ͠Δඞཁ͕͋Δ
PostgreSQLͷϞχλϦϯά ϞχλϦϯάͷྫ
εϧʔϓοτΤϥʔͷ֬ೝ =# SELECT datname, xact_commit, xact_rollback FROM pg_stat_database; datname |
xact_commit | xact_rollback -----------+-------------+--------------- template1 | 0 | 0 template0 | 0 | 0 postgres | 101216 | 1
Ωϟογϡώοτͷ֬ೝ =# 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 ʹҙ
ςʔϒϧͷ Ωϟογϡώοτͷ֬ೝ =# 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
ΠϯσοΫεͷ Ωϟογϡώοτͷ֬ೝ =# 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
දεΩϟϯ͋ͨΓͷಡΈऔΓߦͷ֬ೝ =# 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
)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
ϩοΫͪॲཧͷ֬ೝ =# 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
PostgreSQLͷϞχλϦϯά -FUT1PTUHSFT lՔಈ౷ܭใΛ׆༻͠Α͏z IUUQTMFUTQPTUHSFTRMKQEPDVNFOUTUFDIOJDBMTUBUJTUJDT
͋͐͡Μͩ ̍ɹࣗݾհ ̎ɹPostgreSQLͷ෦ߏ ̏ɹPostgreSQLͷ౷ܭใ ̐ɹPostgreSQLͷϞχλϦϯά ̑ɹ·ͱΊ
·ͱΊ ઌਓͷܙΛ͏
·ͱΊ ઌਓͷܙΛ͏ ˣ ެࣜυΩϡϝϯτΛಡ͏
·ͱΊ ઌਓͷܙΛ͏ ˣ ެࣜυΩϡϝϯτΛಡ͏ ҆৺ͷຊޠυΩϡϝϯτ
·ͱΊ ·ͣՄࢹԽΛ͢Δ
·ͱΊ ਪଌΑΓܭଌ
·ͱΊ ਪଌΑΓܭଌ ↓ ܭଌΑΓ؍ଌ
·ͱΊ ࣄ࣮ΛΑΓଟ͘ɺਖ਼͘͠Δ͜ͱͰ ະདྷΛਖ਼͘͠༧ଌͰ͖Δ
None
·ͱΊ ΤϯδχΞʹࠜڌ͕ඞཁ
·ͱΊ ΤϯδχΞʹࠜڌ͕ඞཁ ↓ ͳΜͱͳ͘Ͱࣄग़དྷͳ͍
·ͱΊ
·ͱΊ
·ͱΊ
·ͱΊ ςετίʔυϓϩάϥϜͷ࣭ͷՄࢹԽ ϞχλϦϯάαʔϏεͷ࣭ͷՄࢹԽ
·ͱΊ lߴʹൃୡͨ͠γεςϜͷҟৗ ਆͷౖΓͱݟ͚͕͔ͭͳ͍z Z@VVLJ
·ͱΊ ମॏܭʹΔ͚ͩͰ૫ͤͳ͍
·ͱΊ ମॏܭʹΔ͚ͩͰ૫ͤͳ͍ ↓ ࣭ΛՄࢹԽ͚ͨͩ͠Ͱվળ͞Εͳ͍
·ͱΊ lखΛಈ͔ͨ͠ਓ͚͕ͩੈքΛม͑Δz :BTVIJSP0OJTIJ
͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ