Download to read offline
![Calculate Processor Utilization $ ./ps-processor-utilization.sh [pid] [before] [after] $ ./ps-processor-utilization.sh 4590 1 2 Processor Utilization = 1.00 % What the script does (partially) should be the same as top: 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}](https://image.slidesharecdn.com/pgeast2010-pgproctab-101004160434-phpapp01/75/pg_proctab-Accessing-System-Stats-in-PostgreSQL-30-2048.jpg)
Uploaded byCommand Prompt., Inc
pg_proctab: Accessing System Stats in PostgreSQL
The document presents an overview of using pg_proctab for accessing and analyzing PostgreSQL system statistics and operating system metrics. It includes examples and explanations of various PostgreSQL system catalog tables and statistics functions, such as pg_stat_activity and pg_stat_database, as well as operating system-related functions like pg_cputime and pg_loadavg. Additionally, it provides insights into creating custom reports, utilizing helper scripts for monitoring, and measuring query performance.
pg_proctab: Accessing System Stats in PostgreSQL
- 1. pg proctab Accessing SystemStats in PostgreSQL Mark Wong markwkm@postgresql.org PostgreSQL Conference East 2010 March 25-28, 2010
- 2. Slides available onslideshare http://www.slideshare.net/markwkm
- 3. Agenda Brief review of what PostgreSQL stats are available How pg proctab may help
- 4. Review Someof the PostgreSQL system catalog tables: pg stat activity pg stat database pg stat all tables pg stat all indexes pg statio all tables pg statio all indexes
- 5. Example: pg statactivity SELECT datname, procpid, usename, current_query FROM pg_stat_activity WHERE current_query <> ’<IDLE>’; datname dbt5 procpid 3260 usename postgres current_query SELECT * FROM TradeLookupFrame3( ’2006-2-27 9:15:0’,43000050000,20, ’2005-11-23 12:6:8’,’ENGAPRB’) ...
- 6. Example: pg statdatabase SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database ORDER BY datname; datname dbt5 numbackends 11 xact_commit 228458 xact_rollback 320
- 7. Example: pg statall tables SELECT seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_all_tables WHERE relname = ’customer’; seq_scan 10 idx_scan 152795 n_tup_ins 5000 n_tup_upd 0 n_tup_del 0
- 8. Example: pg statioall tables SELECT heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_all_tables WHERE relname = ’customer’; heap_blks_read 26897 heap_blks_hit 141581 idx_blks_read 5577 idx_blks_hit 328770
- 9. __ __ / ~~~/ . o O ( Want more! ) ,----( oo ) / __ __/ /| ( |( ^ /___ / | |__| |__|-"
- 10. What about operatingsystem statistics? I/O? — iostat Processor Utilization? — mpstat Per Process Statistics? — pidstat Other system activity? — sar and so on...
- 11. Introducing pg proctab pg proctab is a collection of four C stored functions: pg cputime pg loadavg pg memusage pg proctab
- 12. __ __ / ~~~/ . o O ( Examples? ) ,----( oo ) / __ __/ /| ( |( ^ /___ / | |__| |__|-"
- 13. Some of thethings pg proctab should help with 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 Note: Following examples are from Linux based systems.
- 14. pg cputime() Example SELECT * FROM pg_cputime(); user 31529387 nice 76 system 6865679 idle 574707718 iowait 1985455
- 15. pg cputime() ColumnDescription From Linux kernel source code at Documentation/filesystems/proc.txt: user: normal processes executing in user mode nice: niced processes executing in user mode system: processes executing in kernel mode idle: processes twiddling thumbs iowait: waiting for I/O to complete
- 16. pg loadavg() Example SELECT * FROM pg_loadavg(); load1 7.71 load5 7.73 load15 7.62 last_pid 4623
- 17. pg loadavg() ColumnDescription load1: load average of last minute load5: load average of last 5 minutes load15: load average of last 15 minutes last pid: last pid running
- 18. pg memusage() Example SELECT * FROM pg_memusage(); memused 32793836 memfree 157704 memshared 0 membuffers 94216 memcached 31749292 swapused 13960 swapfree 3986216 swapcached 1264
- 19. pg memusage() ColumnDescription Paraphrased from Linux kernel source code at Documentation/filesystems/proc.txt: memused: Total physical RAM used memfree: Total physical RAM not used memshared: Not used, always 0. (For Solaris.) membuffers: Temporary storage for raw disk blocks memcached: In-memory cache for files read from disk swapused: Total swap space used swapfree: Memory evicted from RAM that is now temporary on disk swapcached: Memory that was swapped out, now swapped in but still in swap
- 20. pg proctab() PartialColumn Description Everything from the operating system such as /proc/<pid>/stat, /proc/<pid>/io and /proc/<pid>/cmdline as well as data from PostgreSQL system catalog such as pg stat activity table are 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 ...
- 21. pg proctab() PartialColumn 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 to be fetched from the storage layer writes - number of bytes which this process really did cause to be sent from the storage layer cwrites - number of bytes which this process caused to not happen, by truncating pagecache
- 22. __ __ / ~~~/ . o O ( More useful examples? ) ,----( oo ) / __ __/ /| ( |( ^ /___ / | |__| |__|-"
- 23. pg proctab() Example SELECT datname, procpid, processor, state, fullcomm FROM pg_stat_activity, pg_proctab() WHERE procpid = pid; datname dbt5 procpid 3260 processor 6 state R fullcomm postgres: postgres dbt5 207.173.203.228(48950) SELECT datname dbt5 procpid 3261 processor 1 state R fullcomm postgres: postgres dbt5 207.173.203.228(48953) SELECT
- 24. __ __ / / ~~~/ . o O | Measuring performance | ,----( oo ) | of a query. | / __ __/ / /| ( |( ^ /___ / | |__| |__|-" You can find the following helper scripts in the pg proctab contrib directory.
- 25. Create snapshot tables Create a set of tables to hold all of the information returned by these 4 stored functions. Also creates a table to timestamp when a snapshot of data is taken. psql -f create-ps_procstat-tables.sql
- 26. Identify yourself. dbt3=# SELECT * FROM pg_backend_pid(); pg_backend_pid ---------------- 4590 (1 row)
- 27. Take a snapshotbefore running the query dbt3=# i ps_procstat-snap.sql BEGIN ps_snap_stats --------------- 1 (1 row) COMMIT
- 28. Execute an SQLstatement Don’t focus too much on the actual query, the idea is that is you want 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;
- 29. Take a snapshotafter running the query dbt3=# i ps_procstat-snap.sql BEGIN ps_snap_stats --------------- 2 (1 row) COMMIT
- 30. Calculate Processor Utilization $ ./ps-processor-utilization.sh [pid] [before] [after] $ ./ps-processor-utilization.sh 4590 1 2 Processor Utilization = 1.00 % What the script does (partially) should be the same as top: 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}
- 31. 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}
- 32. __ __ / / ~~~/ . o O | Creating Custom | ,----( oo ) | Reports! | / __ __/ / /| ( |( ^ /___ / | |__| |__|-" ps-report-pl
- 33. __ __ / / ~~~/ . o O | Warning! Too much data | ,----( oo ) | to fit on screen! | / __ __/ / /| ( |( ^ /___ / | |__| |__|-"
- 34. Creating Reports: Section1 Database : dbt5 Snapshot Start : 2010-03-26 15:24:51.516226-07 Snapshot End : 2010-03-26 15:25:51.57661-07 ------------------- Database Statistics ------------------- Commits : 421 Rollbacks : 2 Blocks Read : 13919368 Blocks Hit : 7876506
- 35. Creating Reports: Section2 ================ Table Statistics ================ ------------------------------------------ -------- ------------ -------- ------------- --------- -------- Schema.Relation Seq Scan Seq Tup Read Idx Scan Idx Tup Fetch N Tup Ins N Tup Up ------------------------------------------ -------- ------------ -------- ------------- --------- -------- ... public.account_permission 0 0 3 3 0 public.address 0 0 488 732 0 public.broker 169 8067 259 259 0 3 public.cash_transaction 0 0 952 928 37 7 public.charge 39 585 0 0 0 public.commission_rate 60 9820 18 44 0 public.company 2 5000 1496 1496 0 public.company_competitor 0 0 61 183 0 public.customer 0 0 375 375 0 public.customer_account 0 0 690 968 0 3 public.customer_taxrate 20 200000 40 40 0 public.daily_market 0 0 4322 4962 0 ...
- 36. Creating Reports: Section2 - Falling off the right side... N Tup Upd N Tup Del Last Vacuum Last Autovacuum Last Analyze Last Autoanalyze
- 37. Creating Reports: Section3 ================ Index Statistics ================ --------------------------------------------------------------------- -------- ------------ ------------- Schema.Relation.Index Idx Scan Idx Tup Read Idx Tup Fetch --------------------------------------------------------------------- -------- ------------ ------------- ... public.account_permission.pk_account_permission 3 3 3 public.address.pk_address 488 732 732 public.broker.pk_broker 259 259 259 public.cash_transaction.pk_cash_transaction 952 998 928 public.charge.pk_charge 0 0 0 public.commission_rate.pk_commission_rate 18 44 0 public.company.i_co_name 14 14 14 public.company.pk_company 1482 1482 1482 public.company_competitor.pk_company_competitor 61 183 183 public.customer.i_c_tax_id 27 27 27 public.customer.pk_customer 348 348 348 public.customer_account.i_ca_c_id 198 477 476 ...
- 38. What else canwe do with pg proctab? Enable pg top to monitor remote databases by providing access to the database system’s operating system process table.
- 39.
- 40. __ __ / ~~~/ . o O ( Thank you! ) ,----( oo ) / __ __/ /| ( |( ^ /___ / | |__| |__|-"
- 41. . . .the fine print . . . Links: http://git.postgresql.org/gitweb?p=pg_proctab.git git clone git://git.postgresql.org/git/pg proctab.git
- 42. Acknowledgements HaleyJane Wakenshaw __ __ / ~~~/ ,----( oo ) / __ __/ /| ( |( ^ /___ / | |__| |__|-"
- 43. License This work is licensed under a Creative Commons Attribution 3.0 Unported License. To view a copy of this license, (a) visit http://creativecommons.org/licenses/by/3.0/us/; or, (b) send a letter to Creative Commons, 171 2nd Street, Suite 300, San Francisco, California, 94105, USA.