pg_proctab: accessing system stats in postgresql
DESCRIPTION
pg_proctab is a collection of PostgreSQL stored functions that provide access to the operating system process table using SQL. We'll show you which functions are available and where they collect the data, and give examples of their use to collect processor and I/O statistics on SQL queries. These stored functions currently only work on Linux-based systems.TRANSCRIPT
![Page 1: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/1.jpg)
pg proctabAccessing System Stats in PostgreSQL
Mark Wong [email protected] Roth [email protected]
PGWest Seattle (JDCon) 2009
Oct 16-18, 2009
![Page 3: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/3.jpg)
Soooo . . .
You can query the PostgreSQL system catalog tables (e.g.pg stat activity, pg stat all tables,pg stat all indexes) to find out which queries are taking a longtime, which indexes are being scanned an unreasonable number oftimes, etc.
![Page 4: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/4.jpg)
Example:
portal=# SELECT datname, procpid, usename, current_query
FROM pg_stat_activity;
datname | procpid | usename | current_query
---------+---------+----------+-------------------------------------------------
portal | 5412 | markwkm | <IDLE>
portal | 5437 | postgres | SELECT datname, procpid, usename, current_query
: FROM pg_stat_activity;
(2 rows)
![Page 5: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/5.jpg)
What if you want to know about the OS?
pg proctab provides a collection of four C stored functions:
◮ pg cputime
◮ pg loadavg
◮ pg memusage
◮ pg proctab
![Page 6: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/6.jpg)
What can do you with pg proctab?
◮ Query operating system process table
◮ Query operating system statistics◮ Processor time◮ Load averages◮ Memory usage
◮ Without escaping out to a shell!
◮ ...plus generate reports about timeslices
![Page 7: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/7.jpg)
pg cputime() Example
SELECT *
FROM pg_cputime();
user | nice | system | idle | iowait
--------+--------+--------+------------+--------
681317 | 109924 | 395481 | 1466101128 | 462661
(1 row)
![Page 8: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/8.jpg)
pg cputime() Column Description
From Linux kernel source code atDocumentation/filesystems/proc.txt:user: normal processes executing in user modenice: niced processes executing in user modesystem: processes executing in kernel modeidle: processes twiddling thumbsiowait: waiting for I/O to complete
![Page 9: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/9.jpg)
pg loadavg() Example
SELECT *
FROM pg_loadavg();
load1 | load5 | load15 | last_pid
-------+-------+--------+----------
0.99 | 0.78 | 0.67 | 27719
(1 row)
![Page 10: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/10.jpg)
pg loadavg() Column Description
load1: load average of last minuteload5: load average of last 5 minutesload15: load average of last 15 minuteslast pid: last pid running
![Page 11: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/11.jpg)
pg memusage() Example
SELECT *
FROM pg_memusage();
memused | memfree | memshared | membuffers | memcached | swapused | swapfree | swapcached
---------+---------+-----------+------------+-----------+----------+----------+------------
3809140 | 224084 | 0 | 60656 | 2389700 | 76 | 8385844 | 0
(1 row)
![Page 12: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/12.jpg)
pg memusage() Column Description
Paraphrased from Linux kernel source code atDocumentation/filesystems/proc.txt:memused: Total physical RAM usedmemfree: Total physical RAM not usedmemshared: Not used, always 0. (I don’t remember why. . . )membuffers: Temporary storage for raw disk blocksmemcached: In-memory cache for files read from diskswapused: Total swap space usedswapfree: Memory evicted from RAM that is now temporary ondiskswapcached: Memory that was swapped out, now swapped in butstill in swap
![Page 13: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/13.jpg)
pg proctab() Example 1
SELECT datname, procpid, usesysid, usename, uid, username
FROM pg_stat_activity, pg_proctab()
WHERE procpid = pid;
datname | procpid | usesysid | usename | uid | username
---------+---------+----------+----------+-----+----------
markwkm | 27801 | 10 | markwkm | 500 | markwkm
dbt3 | 27787 | 16770 | postgres | 500 | markwkm
(2 rows)
![Page 14: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/14.jpg)
pg proctab() Example 2
SELECT datname, procpid, processor, state, fullcomm
FROM pg_stat_activity, pg_proctab()
WHERE procpid = pid;
datname | procpid | processor | state | fullcomm
---------+---------+-----------+-------+------------------------------------------
markwkm | 27801 | 0 | R | postgres: markwkm markwkm [local] SELECT
dbt3 | 29325 | 3 | R | postgres: markwkm dbt3 [local] SELECT
dbt3 | 29327 | 0 | R | postgres: markwkm dbt3 [local] SELECT
dbt3 | 29333 | 3 | R | postgres: markwkm dbt3 [local] SELECT
dbt3 | 29328 | 2 | R | postgres: markwkm dbt3 [local] SELECT
dbt3 | 29329 | 0 | R | postgres: markwkm dbt3 [local] SELECT
dbt3 | 29324 | 3 | R | postgres: markwkm dbt3 [local] SELECT
dbt3 | 29331 | 0 | R | postgres: markwkm dbt3 [local] SELECT
dbt3 | 27787 | 1 | S | postgres: postgres dbt3 [local] idle
(9 rows)
![Page 15: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/15.jpg)
pg proctab() Partial Column Description
Everything from the operating system such as /proc/<pid>/stat,/proc/<pid>/io and /proc/<pid>/cmdline as well as datafrom PostgreSQL system catalog such as pg stat activity tableare available but we’ll only cover some of the fields here:Informative:
◮ pid
◮ comm - filename of the executable
◮ fullcomm (/proc/<pid>/cmdline)
◮ uid
◮ username
Processor:
◮ utime - user mode jiffies
◮ stime - kernel mode jiffies
. . .
![Page 16: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/16.jpg)
pg proctab() Partial Column Description (cont.)
Memory:
◮ vsize - virtual memory size
◮ rss - resident set memory size
I/O:
◮ syscr - number of read I/O operations
◮ syscw - number of write I/O operations
◮ reads - number of bytes which this process really did cause tobe fetched from the storage layer
◮ writes - number of bytes which this process really did cause tobe sent from the storage layer
◮ cwrites - number of bytes which this process caused to nothappen, by truncating pagecache
![Page 17: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/17.jpg)
__ __ / \
/ \~~~/ \ . o O | Let’s try something |
,----( oo ) | more useful. |
/ \__ __/ \ /
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
![Page 18: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/18.jpg)
__ __ / \
/ \~~~/ \ . o O | Measuring performance |
,----( oo ) | of a query. |
/ \__ __/ \ /
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
![Page 19: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/19.jpg)
(You can find the following examples in the pg proctab contribdirectory.)
![Page 20: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/20.jpg)
Create snapshot tables.
(Only need to do this once.)
\i create-ps_procstat-tables.sql
![Page 21: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/21.jpg)
Identify yourself.
SELECT *
FROM pg_backend_pid();
pg_backend_pid
----------------
4590
(1 row)
![Page 22: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/22.jpg)
Take a snapshot before running the query
\i ps_procstat-snap.sql
BEGIN
ps_snap_stats
---------------
1
(1 row)
COMMIT
![Page 23: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/23.jpg)
Execute the query
Don’t focus too much on the actual query, the idea is that is youwant to collect statistics for a single query:
SELECT nation,
o_year,
Sum(amount) AS sum_profit
FROM (SELECT n_name AS nation,
Extract(YEAR FROM o_orderdate) AS o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
FROM part,
supplier,
lineitem,
partsupp,
orders,
nation
WHERE s_suppkey = l_suppkey
AND ps_suppkey = l_suppkey
AND ps_partkey = l_partkey
AND p_partkey = l_partkey
AND o_orderkey = l_orderkey
AND s_nationkey = n_nationkey
AND p_name LIKE ’%white%’) AS profit
GROUP BY nation,
o_year
ORDER BY nation,
o_year DESC;
![Page 24: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/24.jpg)
Take a snapshot after running the query
\i ps_procstat-snap.sql
BEGIN
ps_snap_stats
---------------
2
(1 row)
COMMIT
![Page 25: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/25.jpg)
Calculate Processor Utilization
$ ./ps-processor-utilization.sh [pid] [before] [after]
$ ./ps-processor-utilization.sh 4590 1 2
Processor Utilization = 1.00 %
What’s going on (partially):
SELECT stime, utime, stime + utime AS total,
extract(epoch FROM time)
FROM ps_snaps a, ps_procstat b
WHERE pid = ${PID}
AND a.snap = b.snap
AND a.snap = ${SNAP1}
TIMEDIFF=‘echo "scale = 2; (${TIME2} - ${TIME1}) * ${HZ}" | bc -l‘
U=‘echo "scale = 2; (${TOTAL2} - ${TOTAL1}) / ${TIMEDIFF} * 100" | bc -l‘
![Page 26: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/26.jpg)
Calculate Disk Utilization
$ ./ps-io-utilization.sh 4590 1 2
Reads = 276981
Writes = 63803
Reads (Bytes) = 2164604928
Writes (Bytes) = 508166144
Cancelled (Bytes) = 36880384
SELECT syscr, syscw, reads, writes, cwrites
FROM ps_snaps a, ps_procstat b
WHERE pid = ${PID}
AND a.snap = b.snap
AND a.snap = ${SNAP1}
TIMEDIFF=‘echo "scale = 2; (${TIME2} - ${TIME1}) * ${HZ}" | bc -l‘
U=‘echo "scale = 2; (${TOTAL2} - ${TOTAL1}) / ${TIMEDIFF} * 100" | bc -l‘
![Page 27: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/27.jpg)
__ __ / \
/ \~~~/ \ . o O | Creating Custom |
,----( oo ) | Reports! |
/ \__ __/ \ /
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
![Page 28: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/28.jpg)
__ __ / \
/ \~~~/ \ . o O | Warning! Too much data |
,----( oo ) | to fit on screen! |
/ \__ __/ \ /
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
![Page 29: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/29.jpg)
Creating Reports: Section 1
Database : dbt3
Snapshot Start : 2009-04-18 00:43:56.716034-07
Snapshot End : 2009-04-18 00:45:17.031167-07
-------------------
Database Statistics
-------------------
Commits : 0
Rollbacks : 2
Blocks Read : 213295
Blocks Hit : 1679509
![Page 30: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/30.jpg)
Creating Reports: Section 2
================
Table Statistics
================
------------------------------------------ -------- ------------ -------- ------------- --------- ---------
Schema.Relation Seq Scan Seq Tup Read Idx Scan Idx Tup Fetch N Tup Ins N Tup Upd
------------------------------------------ -------- ------------ -------- ------------- --------- ---------
information_schema.sql_features 0 0 0 0 0
information_schema.sql_implementation_info 0 0 0 0 0
information_schema.sql_languages 0 0 0 0 0
information_schema.sql_packages 0 0 0 0 0
information_schema.sql_parts 0 0 0 0 0
information_schema.sql_sizing 0 0 0 0 0
information_schema.sql_sizing_profiles 0 0 0 0 0
pg_catalog.pg_aggregate 0 0 2 2 0
pg_catalog.pg_am 1 1 0 0 0
pg_catalog.pg_amop 0 0 19 46 0
pg_catalog.pg_amproc 0 0 11 11 0
pg_catalog.pg_attrdef 0 0 1 2 0
pg_catalog.pg_attribute 0 0 137 331 0
pg_catalog.pg_auth_members 0 0 0 0 0
pg_catalog.pg_authid 3 2 0 0 0
pg_catalog.pg_autovacuum 0 0 0 0 0
pg_catalog.pg_cast 0 0 160 51 0
pg_catalog.pg_class 3 747 101 88 0
pg_catalog.pg_constraint 0 0 0 0 0
pg_catalog.pg_conversion 0 0 0 0 0
pg_catalog.pg_database 5 12 0 0 0
pg_catalog.pg_depend 0 0 0 0 0
pg_catalog.pg_description 0 0 0 0 0
pg_catalog.pg_index 2 200 39 50 0
...
![Page 31: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/31.jpg)
Creating Reports: Section 2 - Falling off the right side...
◮ N Tup Upd
◮ N Tup Del
◮ Last Vacuum
◮ Last Autovacuum
◮ Last Analyze
◮ Last Autoanalyze
![Page 32: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/32.jpg)
Creating Reports: Section 3
================
Index Statistics
================
------------------------------------------------------------ -------- ------------ -------------
Schema.Relation.Index Idx Scan Idx Tup Read Idx Tup Fetch
------------------------------------------------------------ -------- ------------ -------------
pg_catalog.pg_aggregate.pg_aggregate_fnoid_index 2 2 2
pg_catalog.pg_am.pg_am_name_index 0 0 0
pg_catalog.pg_am.pg_am_oid_index 0 0 0
pg_catalog.pg_amop.pg_amop_opc_strat_index 12 36 36
pg_catalog.pg_amop.pg_amop_opr_opc_index 7 10 10
pg_catalog.pg_amproc.pg_amproc_opc_proc_index 11 11 11
pg_catalog.pg_attrdef.pg_attrdef_adrelid_adnum_index 1 2 2
pg_catalog.pg_attrdef.pg_attrdef_oid_index 0 0 0
pg_catalog.pg_attribute.pg_attribute_relid_attnam_index 0 0 0
pg_catalog.pg_attribute.pg_attribute_relid_attnum_index 137 331 331
pg_catalog.pg_auth_members.pg_auth_members_member_role_index 0 0 0
pg_catalog.pg_auth_members.pg_auth_members_role_member_index 0 0 0
pg_catalog.pg_authid.pg_authid_oid_index 0 0 0
pg_catalog.pg_authid.pg_authid_rolname_index 0 0 0
pg_catalog.pg_autovacuum.pg_autovacuum_vacrelid_index 0 0 0
pg_catalog.pg_cast.pg_cast_oid_index 0 0 0
pg_catalog.pg_cast.pg_cast_source_target_index 160 51 51
pg_catalog.pg_class.pg_class_oid_index 71 71 71
pg_catalog.pg_class.pg_class_relname_nsp_index 30 17 17
pg_catalog.pg_constraint.pg_constraint_conname_nsp_index 0 0 0
pg_catalog.pg_constraint.pg_constraint_conrelid_index 0 0 0
pg_catalog.pg_constraint.pg_constraint_contypid_index 0 0 0
pg_catalog.pg_constraint.pg_constraint_oid_index 0 0 0
pg_catalog.pg_conversion.pg_conversion_default_index 0 0 0
...
![Page 33: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/33.jpg)
What else can we do with pg proctab?
Enable pg top to monitor remote databases by providing access tothe database system’s operating system process table.
![Page 34: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/34.jpg)
pg top
![Page 35: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/35.jpg)
__ __
/ \~~~/ \ . o O ( Thank you! )
,----( oo )
/ \__ __/
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
![Page 36: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/36.jpg)
. . . the fine print . . .
◮ Linux-only
◮ Developed on 8.3; still works on 8.4
◮ Download it from:http://git.postgresql.org/gitweb?p=pg_proctab.git
◮ Change it:git clone
git://git.postgresql.org/git/pg_proctab.git
◮ Patches welcome! We’ll be in the (Sn—H)ackers’ Lounge!
![Page 37: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/37.jpg)
Acknowledgements
Haley Jane Wakenshaw
__ __
/ \~~~/ \
,----( oo )
/ \__ __/
/| (\ |(
^ \ /___\ /\ |
|__| |__|-"
![Page 38: pg_proctab: Accessing System Stats in PostgreSQL](https://reader033.vdocuments.us/reader033/viewer/2022050920/549f755bac795906768b4a1b/html5/thumbnails/38.jpg)
License
This work is licensed under a Creative Commons Attribution 3.0Unported License. To view a copy of this license, (a) visithttp://creativecommons.org/licenses/by/3.0/us/; or, (b)send a letter to Creative Commons, 171 2nd Street, Suite 300, SanFrancisco, California, 94105, USA.