{"id":2805,"date":"2025-01-29T15:10:26","date_gmt":"2025-01-29T15:10:26","guid":{"rendered":"https:\/\/bynatree.com\/?p=2805"},"modified":"2025-01-29T15:10:26","modified_gmt":"2025-01-29T15:10:26","slug":"monitoring-postgresql-enhancing-performance-with-pg_proctab-and-pg_stat_activity","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/01\/29\/monitoring-postgresql-enhancing-performance-with-pg_proctab-and-pg_stat_activity\/","title":{"rendered":"Monitoring PostgreSQL: Enhancing Performance with pg_proctab and pg_stat_activity"},"content":{"rendered":"

In this blog, we will discuss using pg_proctab<\/strong> with pg_state_activity<\/strong> in critical situations.<\/span><\/p><\/blockquote>\n

Monitoring system resources and PostgreSQL<\/strong> performance is essential particularly in critical situations, to ensure your database and applications run smoothly and reliably.<\/span><\/p>\n

 <\/p>\n

System Resource Monitoring<\/b>: <\/span><\/h3>\n

In Linux information, monitoring system resources such as CPU, memory, disk I\/O, and network consumption can assist discover bottlenecks or unusual behavior. <\/span>Top, htop, vmstat, and iostat<\/b> are popular tools for real-time monitoring. They give information on how resources are being used and can aid in diagnosing performance issues before they worsen.<\/span><\/p>\n

PostgreSQL resource monitoring is required to maintain database health, enhance query performance, and avoid downtime. PostgreSQL has built-in views such as <\/span>pg_stat_activity<\/b>, <\/span>pg_stat_database<\/b>, and <\/span>pg_stat_replication <\/b>for monitoring the status of active queries, database statistics, and replication details. Furthermore, extensions like <\/span>pg_stat_statements <\/b>may be deployed to provide precise information regarding query performance.<\/span><\/p>\n

In this blog, we will discuss using pg_proctab with pg_state_activity in critical situations.<\/span><\/p><\/blockquote>\n

When it comes to monitoring and maintaining PostgreSQL performance, both pg_proctab and pg_stat_activity are essential tools, providing complementing information that may help you keep our database system healthy and efficient.<\/span><\/p>\n

Importance of pg_proctab:<\/b><\/h3>\n
    \n
  • pg_proctab offers a full view of all system processes, not just those linked to PostgreSQL. This is critical for knowing how the total system resources are used, which might affect PostgreSQL performance.<\/span><\/li>\n
  • Detailed Process Information: It provides detailed data including CPU utilization, memory consumption, I\/O statistics, and more. This can aid in identifying system-level bottlenecks or resource contention that may impact database performance.<\/span><\/li>\n
  • Integration: By combining pg_proctab with other monitoring tools, you may get a complete picture of your server’s health, including PostgreSQL and other programs that operate on the same computer.<\/span><\/li>\n<\/ul>\n

    Installing pg_proctab on local machines:<\/span><\/h4>\n
    [postgres@mysql-01 wget https:\/\/github.com\/markwkm\/pg_proctab\/archive\/refs\/heads\/main.zip<\/pre>\n
    [postgres@mysql-01 pg_proctab-main]$ ll\ntotal 24\ndrwxr-xr-x 2 postgres postgres 4096 May&nbsp; 8&nbsp; 2020 contrib\n-rw-r--r-- 1 postgres postgres 1011 May&nbsp; 8&nbsp; 2020 COPYRIGHT\ndrwxr-xr-x 2 postgres postgres &nbsp; 31 May&nbsp; 8&nbsp; 2020 doc\n-rw-r--r-- 1 postgres postgres&nbsp; 775 May&nbsp; 8&nbsp; 2020 Makefile\n-rw-r--r-- 1 postgres postgres&nbsp; 893 May&nbsp; 8&nbsp; 2020 META.json\n-rw-r--r-- 1 postgres postgres&nbsp; 157 May&nbsp; 8&nbsp; 2020 pg_proctab.control\n-rw-r--r-- 1 postgres postgres&nbsp; 137 May&nbsp; 8&nbsp; 2020 PORTING\ndrwxr-xr-x 2 postgres postgres &nbsp; 93 Jan 20 02:17 sql\ndrwxr-xr-x 2 postgres postgres &nbsp; 46 Jan 20 02:38 src<\/pre>\n
    Creating extension in postgres:<\/span><\/h3>\n
    [postgres@mysql-01 pg_proctab-main]$ psql\npsql (16.6)\nType \"help\" for help.\n\npostgres=# CREATE EXTENSION pg_proctab;\nCREATE EXTENSION<\/pre>\n
    Checking load based on 1min, 5mins and 15mins time frame and last pid:<\/span><\/p>\n
    postgres=# SELECT * FROM pg_loadavg();\n load1 | load5 | load15 | last_pid\n-------+-------+--------+----------\n  0.69 |  0.46 |   0.19 |     2559<\/pre>\n
    Verifying different options:\u00a0<\/span><\/h2>\n
    Monitoring memory using pg_memusage<\/span><\/h3>\n
    postgres=# SELECT *FROM pg_memusage();\n memused | memfree | memshared | membuffers | memcached | swapused | swapfree | swapcached\n---------+---------+-----------+------------+-----------+----------+----------+------------\n 1620200 |  168172 |         0 |       1048 |    581848 |   596896 |  1533020 |     297312\n(1 row)<\/pre>\n
    Monitoring CPU and IO uses:<\/span><\/p>\n
    postgres=# SELECT *FROM pg_cputime();\n user  | nice | system |  idle   | iowait\n-------+------+--------+---------+--------\n 97810 | 4368 | 122896 | 4401878 |  51997\n(1 row)<\/pre>\n
    postgres=# SELECT * FROM pg_proctab();\n  pid  |   comm   |                      fullcomm                      | state | ppid  | pgrp  | session | tty_nr | tpgid |  flags  | minflt | cminflt | majflt | cmajflt | utime | stime | cutime | cstime |\npriority | nice | num_threads | itrealvalue | starttime |   vsize   |  rss  | exit_signal | processor | rt_priority | policy | delayacct_blkio_ticks | uid | username |  rchar  | wchar  | syscr | syscw |  re\nads  | writes | cwrites\n-------+----------+----------------------------------------------------+-------+-------+-------+---------+--------+-------+---------+--------+---------+--------+---------+-------+-------+--------+--------+-\n---------+------+-------------+-------------+-----------+-----------+-------+-------------+-----------+-------------+--------+-----------------------+-----+----------+---------+--------+-------+-------+----\n-----+--------+---------\n 85458 | postgres | postgres: autovacuum launcher                      | S     | 85446 | 85458 |   85458 |      0 |    -1 | 4210752 |    280 |       0 |      0 |       0 |     5 |     8 |      0 |      0 |\n      20 |    0 |           1 |           0 |   1048644 | 468770816 |  8864 |          17 |         3 |           0 |      0 |                     0 |  26 | postgres |   41740 |      1 |   260 |     1 |\n8192 |      0 |       0\n 85460 | postgres | postgres: logical replication launcher             | S     | 85446 | 85460 |   85460 |      0 |    -1 | 4210752 |    261 |       0 |      0 |       0 |     0 |     2 |      0 |      0 |\n      20 |    0 |           1 |           0 |   1048645 | 468783104 |  6600 |          17 |         1 |           0 |      0 |                     0 |  26 | postgres |     524 |      1 |     1 |     1 |\n   0 |      0 |       0\n(2 rows)<\/pre>\n
    Checking memory consumption in active and idle connection using below query:<\/span><\/h3>\n
    postgres=# SELECT\n    sa.pid,\n    sa.query,\n    pgprettysize(ps.rss * 1024) AS memory_consumption\nFROM\n    pg_stat_activity sa\nJOIN\n    pg_proctab() ps\nON\n    sa.pid = ps.pid\nWHERE\n    sa.state = 'idle' limit 2;\n  pid  |                 query                 | memory_consumption\n-------+---------------------------------------+--------------------\n  1488 | SELECT pg_catalog.pg_is_in_recovery() | 18 MB\n 55981 | select *from language limit 1;        | 20 MB\n(2 rows)\n<\/pre>\n
    This will help the users to find the sessions which are consuming more memory and CPU and helps to troubleshoot the issues quickly.<\/span><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
    In this blog, we will discuss using pg_proctab with pg_state_activity in critical situations. Monitoring system resources and PostgreSQL performance is essential particularly in critical situations, to ensure your database and applications run smoothly and reliably.   System Resource Monitoring: In Linux information, monitoring system resources such as CPU, memory, disk I\/O, and network consumption can…<\/p>\n","protected":false},"author":1,"featured_media":2806,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[114,152,200,248,249,262,279,286,321],"class_list":["post-2805","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-database-monitoring","tag-enhancing-performance","tag-monitoring-postgresql","tag-pg_proctab","tag-pg_stat_activity","tag-postgres","tag-postgresql-performance","tag-postgresql-tools","tag-sql-performance-tuning","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2805"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2805\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2806"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}