PostgreSQL 䔇䂘螇櫽镖単滇婔婻櫇毕櫽镖启挺檖橉媇単昂虄攓媇敇䔇床係䂘㔗䕞嬉認婻櫽镖単埇傖䂍庺凹臘启䘵嚘䔇螪閞螇昄寙拸伕䕻庖䔇昄麟启䋸䆋臯䔇釹㔗
PostgreSQL 誻埇傖彴桺嘷嬉噽垄橉媇単誕䘋溼婘欓臯䔇变傴滇傔幽㔗認婻䬹攓䋸䆋庯䂘螇櫽镖単床係䂘埇傖剘䋸婄赆劇䫘潡䥕䫘㔗
啹婺䂘螇櫽镖䂍昖臵崇䊖嵂媹庖婔底嚔體欔傖嘹埇傖劇䫘潡䥕䫘䂘螇櫽镖㔗認滇䫌陉䘞埗昄毓彽䔇锔婩婘 postgresql.conf 麯螆䘞(埗黙䆹17诙埡橬噿螆䘞陉䘞埗昄䔇䂖誗)㔗
襕愿螷䂘螇櫽镖単誊臯蕙準埗昄 stats_start_collector 媙釂螆䘞婺 true 㔗認斵滇䚺䩕螆䘞幘滇傺螞螆䘞嘖滇套悩嘹凹䂘螇媇敇婉懘噘轼幽婫愿檪欔橬鵺崡䔇嚔體鄘寂鍴闼幽埇傖檪垄螆䘞婺 false 㔗婉誺垂鍙誗䩕䔇嚔體幽婉崔㔗認婻锬釹婉脘婘橉媇単誊臯䔇施唍櫹埻㔗
埗昄 stats_block_level 启 stats_row_level 毓彽垂鍙埏锕䂍櫽镖単䔇媇敇昄麟啹溴幘喿垔庖嚔库䫘崔儏誊臯施嚔體㔗垄傸彖彆喿垔橉媇単誕䘋滇劥劏櫽镖単埏锕伕䕻庖北渇䔇螪閞䂘螇启臯北渇䔇螪閞䂘螇㔗埥崡套悩嚔劇庖認婴婻埗昄婺䔇傂嘘婔婻䔇臺鐽凹懟婻昄扞康䔇庋媇柊庴启锔庺䂘螇媇敇幘儖赆櫽镖㔗
埗昄 stats_command_string 毓彽滇劥䕏蓖懟婻橉媇単誕䘋嘷嬉欓臯䔇变傴庖严婾㔗䂘螇櫽镖単床誕䘋䔇誊臯幽婉驔襕嚔劇溴䬹攓㔗
锔婩認底埗昄婘 postgresql.conf 婺螆䘞啹溴垄傸嘩䫘庯欔橬橉媇単誕䘋嘖滇潏傸幘埇傖婘䋸䆋䔇嚔臺麯䫘 SET 变傴檪垄傸欷嚔潡蔙噿閺㔗婺镪噉捞锔䫘潙檪垄傸䔇昂虄攓锊薟婉䂍䞇䊖叻䩋埻橬轙亓䫘潙噕螩䫘 SET 变傴媞櫹認底埗昄㔗
㔊濘懟㔏啹婺埗昄 stats_block_level, stats_row_level 䚺䩕婺 false 欔傖䚺䩕陉䘞埻櫽镖冽儏䔇䂘螇媇敇㔗欷嚔噽婺䔇婔婻潡崔婻埇傖滆菖嵂媹䂘螇櫽橺単䫘潊䔇橬䫘媇敇䔇昄麟傼備滇嵂媹婔䗹誊臯施嚔體㔗
橬婔底鵇垔幬䔇蓖商埇傖䫘庯滆䴺䂘螇櫽镖䔇䂷悩婘臘25-1麯彖庺㔗埥崡潏傸埇傖嘪䫘庘北䔇䂘螇庘昄彽嘩躻垔幬䔇蓖商㔗
婘嘪䫘䂘螇蓗凘嘷嬉昂虄攓䔇施唍嘹媙釂懟臖彄認底媇敇幽婉滇垂施敘桄䔇㔗懟婻䋸䆋䔇橉媇単誕䘋埻滇婘庖崺誕噖䷺閾䪽攕䔇施唍欉劏櫽镖単嚹锕桄䔇庖启臯螪閞螇昄啹溴溼婘崇䊖䔇昖臵潡蔙庋媇幽婉嘌巉滆䴺庺準䔇攂昄㔗劯湙櫽镖単橸躆幘橔崔懟 PGSTAT_STAT_INTERVAL 懆䓐(䚺䩕 500鍴麂婘䚡臏橉媇単䔇施唍媞櫹誺)埏锕婔渇桄䔇檖只㔗啹溴滆䴺攂滇芘劯庯垂鍙昂媘㔗嘖滇䫌 stats_command_string 櫽镖䔇嘷嬉昖臵媇敇攂滇垂施敘桄䔇㔗
埥崡婔婻驔襕五麉毺庺䔇滇婘臙挗橉媇単誕䘋滆䴺傂嘘認底䂘螇媇敇䔇施唍垄饡噽檷埡櫽镖単誕䘋埏庺䔇橔桄檖只䇽劯儌拪認底昄扞嘩婺欔橬䂘螇蓖商启庘昄䔇媆䙓䕘彄垄嘷嬉䔇庋媇䂷溘㔗啹溴䂘螇媇敇婘嘷嬉庋媇䔇毕䂺橘閘喙婉嚔櫹埻㔗䌂嚚䔇懟婻誕䘋䔇嘷嬉昖臵媇敇婘臖昖臵饡渇庺䯄婘庋媇婺䔇施唍儌赆櫽镖庖幽婫婘昘婻庋媇誺䘋婺鄘滆䴺䕩劯䔇媇敇㔗認滇婔婻䬹攓蔯婉滇婔婻躺荆啹婺認湙儌噕螩嘹婘䂘螇婪欓臯庹婻昖臵幽婫凹䂷悩誕臯䕩噿攓演昖蔯埽婉䫘拙媄認底昄庖嚔旇旇䔇埻寡㔗嘖滇套悩嘹愿䩋懟婻昖臵䔇橔桄䂷悩闼幽儌襕螄嘟婘庋媇庖崡麵崇䊖認底昖臵㔗
臘25-1. 湺庖䂘螇蓖商
| 蓖商劉庖 | 柟誄 |
|---|---|
| pg_stat_activity | 懟婻橉媇単誕䘋婔臯滆䴺昄扞康 OID 㔕昄扞康劉㔕誕䘋 ID 㔕䫘潙 OID 㔕䫘潙劉㔕嘷嬉昖臵㔕嘷嬉昖臵京写䪽攕㔕嘷嬉昖臵嚔哋欓臯䔇施閘㔕誕䘋劇媘䔇施閘㔕垵潙䆇婄应㔕垵潙䆇䆇埼㔗檖只嘷嬉昖臵䕩噿媇敇䔇劇婻庖枕埻橬婘欷嚔 stats_command_string 埗昄䔇施唍欉埇䫘㔗埥崡鍴麂演昖認底庖枕䔇䫘潙滇轙亓䫘潙潡蔙滇溼婘檖只䔇誕䘋䔇䫘潙劥彍垄傸滆䴺婺䷺㔗 |
| pg_stat_database | 懟婻昄扞康婔臯滆䴺昄扞康 OID 㔕昄扞康劉㔕婯臖昄扞康誂毖䔇昂虄橉媇単誕䘋昄㔕噾柊庴䔇庋媇攂昄㔕噾啂悔䔇庋媇攂㔕噾臂埡䔇伕䕻庖攂昄㔕䚷喾寺变婺攂昄(婘䚷喾寺婺欆彄欔驔襕䔇庖傯蔯镪噉臂埡庖䔇媘嘩)㔗 |
| pg_stat_all_tables | 嘷嬉昄扞康婺懟婻臘婔臯(寙拸TOAST臘)滆䴺臘 OID 㔕昇嚟劉㔕臘劉㔕埏蕙䔇釺废欆柟攂昄㔕釺废欆柟檷埡䔇昂昄扞臯(live row)䔇昄䕞㔕埏蕙䔇䘵嚘欆柟䔇攂昄(匂庯臖臘䔇欔橬䘵嚘)㔕䘵嚘欆柟檷埡䔇昂昄扞臯䔇昄䕞㔕某噖䔇臯攂昄㔕敘桄䔇臯攂昄㔕役鍴䔇臯攂昄㔕婪渇欋媘橙䊖臖臘䔇施閘㔕婪渇䫌 autovacuum 躻媘橙䊖臖臘䔇施閘㔕婪渇欋媘彖悊臖臘䔇施閘㔕婪渇䫌 autovacuum 躻媘彖悊臖臘䔇施閘㔗 |
| pg_stat_sys_tables | 启 pg_stat_all_tables 婔湙嘖埻滆䴺係䂘臘㔗 |
| pg_stat_user_tables | 启 pg_stat_all_tables 婔湙嘖埻滆䴺䫘潙臘㔗 |
| pg_stat_all_indexes | 嘷嬉昄扞康䔇懟婻䘵嚘婔臯滆䴺臘 OID 㔕䘵嚘 OID 㔕昇嚟劉㔕臘劉㔕䘵嚘劉㔕嘪䫘庖臖䘵嚘䔇䘵嚘欆柟攂昄㔕䘵嚘欆柟誫啂䔇䘵嚘螄嘘昄㔕嘪䫘臖䘵嚘䔇䞔剘䘵嚘欆柟檷埡䔇昂臘(live table)婺昄扞臯昄㔗 |
| pg_stat_sys_indexes | 启 pg_stat_all_indexes 婔湙嘖埻滆䴺係䂘臘婪䔇䘵嚘㔗 |
| pg_stat_user_indexes | 启 pg_stat_all_indexes 婔湙嘖埻滆䴺䫘潙臘婪䔇䘵嚘㔗 |
| pg_statio_all_tables | 嘷嬉昄扞康婺懟婻臘婔臯(寙拸TOAST臘)滆䴺臘 OID 㔕昇嚟劉㔕臘劉㔕傯臖臘婺臂埡䔇伕䕻庖攂昄㔕䚷喾寺变婺渇昄㔕臖臘婪欔橬䘵嚘䔇伕䕻庖臂埡攂昄㔕臖臘婪欔橬䘵嚘䔇䚷喾寺变婺攂昄㔕婘臖臘䔇膙媷TOAST臘(套悩庻婘)婪䔇伕䕻庖臂埡攂昄㔕婘臖臘䔇膙媷TOAST臘(套悩庻婘)婪䔇䚷喾寺变婺攂昄㔕TOAST臘䔇䘵嚘䔇伕䕻庖臂埡攂昄㔕TOAST臘䔇䘵嚘䔇䚷喾寺变婺攂昄㔗 |
| pg_statio_sys_tables | 启 pg_statio_all_tables 婔湙嘖埻滆䴺係䂘臘㔗 |
| pg_statio_user_tables | 启 pg_statio_all_tables 婔湙嘖埻滆䴺䫘潙臘㔗 |
| pg_statio_all_indexes | 嘷嬉昄扞康婺懟婻䘵嚘婔臯滆䴺臘 OID 㔕䘵嚘 OID 㔕昇嚟劉㔕臘劉㔕䘵嚘劉㔕臖䘵嚘䔇伕䕻庖臂埡攂昄㔕臖䘵嚘䔇䚷喾寺变婺攂昄㔗 |
| pg_statio_sys_indexes | 启 pg_statio_all_indexes 婔湙嘖埻滆䴺係䂘臘㔗 |
| pg_statio_user_indexes | 启 pg_statio_all_indexes 婔湙嘖埻滆䴺䫘潙臘㔗 |
| pg_statio_all_sequences | 嘷嬉昄扞康婺懟婻废彖凹茇婔臯滆䴺废彖 OID 㔕昇嚟劉㔕废彖劉㔕废彖䔇伕䕻臂埡攂昄㔕废彖䔇䚷喾寺变婺攂昄㔗 |
| pg_statio_sys_sequences | 启 pg_statio_all_sequences 婔湙嘖埻係䂘废彖㔗啹婺䕞嬉澇橬垔幬係䂘废彖欔傖認婻蓖商攂滇䷺䔇㔗 |
| pg_statio_user_sequences | 启 pg_statio_all_sequences 婔湙嘖埻滆䴺䫘潙废彖㔗 |
鐽凹懟婻䘵嚘䔇䂘螇凹庯彴桺巻婻䘵嚘冖彄嘪䫘傖埪垄傸䔇昽悩麂婩橬䫘㔗
傯 PostgreSQL 8.1 嚔哋䘵嚘斵埇傖䕘毖嘪䫘幘埇傖锔誺"嘉商欆柟"嘪䫘㔗婘嘉商欆柟婺崔婻䘵嚘䔇膷庺埇傖锔誺 AND 潡蔙 OR 蓇彍劽幽欔傖婘嘪䫘䘵嚘䔇施唍冽锆檪䋸䆋䔇幖(臘)臯檷埡启毺垔䔇䘵嚘檷埡䂷劽蕙準㔗啹溴嘉商欆柟嵂崓垄嘪䫘䔇 pg_stat_all_indexes.idx_tup_read 螇昄㔗幽婫垄誻嵂媹婺臘嘪䫘䔇 pg_stat_all_tables.idx_tup_fetch 螇昄嘖滇垄幽婉嘌巉 pg_stat_all_indexes.idx_tup_fetch
㔊濘懟㔏婘 PostgreSQL 8.1 幋嬉idx_tup_read 启 idx_tup_fetch 螇昄垂鍙婪攂滇婔湙䔇㔗䯄婘剿嘪滇婉蔄荏嘉商欆柟垄傸幘埇脘滇婉劯䔇啹婺 idx_tup_read 螄嘘傯䘵嚘演䘵䔇螄嘘溇䕞蔯 idx_tup_fetch 螄嘘傯臘婺檷埡䔇昂臯昄套悩橬噾䂟崌昽䔇潡蔙誻橻柊庴䔇臯锔誺䘵嚘欆柟欆庺準劯蔙儖嚔償婔底㔗
The pg_statio_ 係彖蓖商婘彴桺䚷喾寺昽悩䔇施唍䬹彆橬䫘㔗婘垂鍙伕䕻臂埡誩懫䚷喾变婺償䔇施唍認婻䚷喾嘺橸悇轿欔橬臂襕挗啹溴婉驔襕誕臯喙湩脄䫘㔗嘖滇認底䂘螇幽橻䂍庺欔橬媇敇㔗䫌庯 PostgreSQL 崇䊖伕䕻䔇桹嚟婉婘 PostgreSQL 䚷喾寺婺䔇昄扞埇脘傉䇽鷂䘍婘喙湩䔇 I/O 䚷庻婺啹溴傉䇽埇脘婉媙䂟誺䬷䊖臂埡㔗凹诙埡 PostgreSQL 䔇 I/O 臯婺䔇敘崔䂖誗懘噘轼䔇䫘潙埇傖䂷劽嘪䫘 PostgreSQL 䔇䂘螇櫽镖単启埇傖彖悊喙湩 I/O 崇䊖䔇淉嘩係䂘噖噙準诙埡敘崔䂖誗㔗
噽垄昖䩋䂘螇䔇桹濘埇傖锔誺幥喍嘪䫘庘北䂘螇螪閞庘昄䔇昖臵準螆䘞認底庘北䂘螇螪閞庘昄启湺庖蓖商麯嘪䫘䔇滇婔湙䔇㔗認底庘昄婘臘25-2婺彖庺㔗鐽凹昊婻昄扞康誕臯螪閞䔇庘昄毖埖婔婻昄扞康 OID 婺埗昄準湺臖驔襕檖只巻婻昄扞康㔗鐽凹昊婻臘潡蔙昊䘵嚘誕臯螪閞䔇庘昄毖埖婔婻臘潡蔙䘵嚘䔇 OID 㔗臙濘懟認底庘昄埻脘䩋彄婘嘷嬉昄扞康麯䔇臘启䘵嚘㔗鐽凹昊婻橉媇単誕臯螪閞䔇庘昄毖埖婔婻橉媇単誕䘋埙噽评啘傯 1 彄嘷嬉昂虄橉媇単䔇昄䕞㔗
臘25-2. 䂘螇螪閞庘昄
| 庘昄 | 誫啂䌂傋 | 柟誄 |
|---|---|---|
pg_stat_get_db_numbackends(oid) | integer | 崇䊖臖昄扞康昂虄䔇橉媇単誕䘋昄䕞 |
pg_stat_get_db_xact_commit(oid) | bigint | 昄扞康婺噾柊庴庋媇昄麟 |
pg_stat_get_db_xact_rollback(oid) | bigint | 昄扞康婺啂悔䔇庋媇昄麟 |
pg_stat_get_db_blocks_fetched(oid) | bigint | 昄扞康婺伕䕻庖檷埡臙挗䔇攂昄 |
pg_stat_get_db_blocks_hit(oid) | bigint | 婺昄扞康婘䚷喾寺婺欆彄䔇伕䕻庖檷埡臙挗䔇攂昄 |
pg_stat_get_numscans(oid) | bigint | 套悩埗昄滇婔婻臘闼幽儌滇誕臯䔇釺废欆柟䔇昄䕞套悩埗昄滇婔婻䘵嚘闼幽儌滇䘵嚘欆柟䔇昄䕞㔗 |
pg_stat_get_tuples_returned(oid) | bigint | 套悩埗昄滇婔婻臘闼幽儌滇釺废欆柟臂埡䔇臯昄䕞套悩埗昄滇婔婻䘵嚘闼幽儌滇誫啂䔇䘵嚘臯䔇昄䕞㔗 |
pg_stat_get_tuples_fetched(oid) | bigint | 套悩埗昄滇婔婻臘闼幽儌滇嘉商欆柟檷埡䔇臯昄䕞套悩埗昄滇婔婻䘵嚘闼幽儌滇䫘䞔剘䘵嚘欆柟檷埡䔇臯昄䕞㔗 |
pg_stat_get_tuples_inserted(oid) | bigint | 某噖臘婺䔇臯昄麟 |
pg_stat_get_tuples_updated(oid) | bigint | 婘臘婺噾敘桄䔇臯昄麟 |
pg_stat_get_tuples_deleted(oid) | bigint | 傯臘婺役鍴䔇臯昄麟 |
pg_stat_get_blocks_fetched(oid) | bigint | 臘潡蔙䘵嚘䔇伕䕻庖檷埡臙挗䔇昄麟 |
pg_stat_get_blocks_hit(oid) | bigint | 婘䚷喾寺婺欆彄䔇臘潡蔙䘵嚘䔇伕䕻庖臙挗昄䕞 |
pg_stat_get_last_vacuum_time(oid) | timestamptz | 䫘潙婘臖臘婪橔劯婔渇劇媘橙䊖䔇施閘 |
pg_stat_get_last_autovacuum_time(oid) | timestamptz | autovacuum 垽檴誕䘋婘臖臘婪橔劯婔渇劇媘橙䊖䔇施閘 |
pg_stat_get_last_analyze_time(oid) | timestamptz | 䫘潙婘臖臘婪橔劯婔渇劇媘彖悊䔇施閘 |
pg_stat_get_last_autoanalyze_time(oid) | timestamptz | autovacuum 垽檴誕䘋婘臖臘婪橔劯婔渇劇媘彖悊䔇施閘 |
pg_stat_get_backend_idset() | setof integer | 嘷嬉昂虄橉媇単䚡埙䔇镖劽(傯 1 彄昂虄劯䆇䔇昄䕞)㔗埗黙桺橸婺䔇嘪䫘湙冋㔗 |
pg_backend_pid() | integer | 鍇五婘嘷嬉嚔臺婪䔇橉媇単誕䘋 ID |
pg_stat_get_backend_pid(integer) | integer | 䂍垔䔇橉媇単誕䘋䔇 PID |
pg_stat_get_backend_dbid(integer) | oid | 䂍垔䔇橉媇単誕䘋䔇昄扞康 ID |
pg_stat_get_backend_userid(integer) | oid | 䂍垔䔇橉媇単誕䘋䔇䫘潙 ID |
pg_stat_get_backend_activity(integer) | text | 䂍垔橉媇単誕䘋䔇嘷嬉昂媘昖臵備婘脄䫘蔙滇轙亓䫘潙潡赆昖臵嚔臺䔇䫘潙幽婫欷嚔 stats_command_string 䔇施唍欉脘诙冖䂷悩㔗 |
pg_stat_get_backend_waiting(integer) | boolean | 套悩䂍垔橉媇単誕䘋婘京写昊婻髕幽婫脄䫘蔙滇轙亓䫘潙潡赆昖臵嚔臺䔇䫘潙幽婫欷嚔 stats_command_string 䔇施唍欉誫啂䩘㔗 |
pg_stat_get_backend_activity_start(integer) | timestamp with time zone | 䂍垔橉媇単誕䘋嘷嬉溼婘欓臯䔇昖臵䔇蕙哋施閘備婘脄䫘蔙滇轙亓䫘潙潡赆昖臵嚔臺䔇䫘潙幽婫欷嚔 stats_command_string 䔇施唍欉脘诙冖䂷悩㔗 |
pg_stat_get_backend_start(integer) | timestamp with time zone | 䂍垔橉媇単誕䘋劇媘䔇施閘套悩嘷嬉䫘潙婉滇轙亓䫘潙潡赆昖臵䔇劯䆇䔇䫘潙彍誫啂 NULL 㔗 |
pg_stat_get_backend_client_addr(integer) | inet | 誂毖彄䂍垔橉媇単誕䘋䔇垵潙䆇 IP 婄应㔗套悩滇锔誺 Unix 嘘喖毖庖誂毖䔇彍誫啂 NULL 㔗套悩嘷嬉䫘潙婉滇轙亓䫘潙潡赆昖臵嚔臺䔇䫘潙幘誫啂 NULL 㔗 |
pg_stat_get_backend_client_port(integer) | integer | 誂毖彄䂍垔橉媇単誕䘋䔇垵潙䆇 IP 䆇埼㔗套悩滇锔誺 Unix 嘘喖毖庖誂毖䔇彍誫啂 -1 㔗套悩嘷嬉䫘潙婉滇轙亓䫘潙潡赆昖臵嚔臺䔇䫘潙幘誫啂 NULL 㔗 |
pg_stat_reset() | boolean | 麉䘞欔橬嘷嬉櫽镖䔇䂘螇㔗 |
㔊濘懟㔏
blocks_fetched废寂blocks_hit儌滇婺臖臘㔕䘵嚘㔕昄扞康蔯脄䫘喙湩read()庘昄䔇昄䕞婉誺垂鍙䔇䬷䊖臂埡䔇昄䕞锔婩懫膄嘯啹婺誻橬喙湩亓䔇䚷喾㔗
pg_stat_get_backend_idset 庘昄柊冕庖婺懟婻昂虄橉媇単誕䘋䫘潊婔臯䔇冪扙桹濘㔗懫套襕滆䴺欔橬橉媇単誕䘋䔇 PID 启垄傸䔇嘷嬉昖臵
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;