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 assist discover bottlenecks or unusual behavior. Top, htop, vmstat, and iostat 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.
PostgreSQL resource monitoring is required to maintain database health, enhance query performance, and avoid downtime. PostgreSQL has built-in views such as pg_stat_activity, pg_stat_database, and pg_stat_replication for monitoring the status of active queries, database statistics, and replication details. Furthermore, extensions like pg_stat_statements may be deployed to provide precise information regarding query performance.
In this blog, we will discuss using pg_proctab with pg_state_activity in critical situations.
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.
Importance of pg_proctab:
- 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.
- 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.
- 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.
Installing pg_proctab on local machines:
| 1 | [postgres@mysql-01 wget https://github.com/markwkm/pg_proctab/archive/refs/heads/main.zip |
| 1 2 3 4 5 6 7 8 9 10 11 | [postgres@mysql-01 pg_proctab-main]$ ll total 24 drwxr-xr-x 2 postgres postgres 4096 May 8 2020 contrib -rw-r--r-- 1 postgres postgres 1011 May 8 2020 COPYRIGHT drwxr-xr-x 2 postgres postgres 31 May 8 2020 doc -rw-r--r-- 1 postgres postgres 775 May 8 2020 Makefile -rw-r--r-- 1 postgres postgres 893 May 8 2020 META.json -rw-r--r-- 1 postgres postgres 157 May 8 2020 pg_proctab.control -rw-r--r-- 1 postgres postgres 137 May 8 2020 PORTING drwxr-xr-x 2 postgres postgres 93 Jan 20 02:17 sql drwxr-xr-x 2 postgres postgres 46 Jan 20 02:38 src |
Creating extension in postgres:
| 1 2 3 4 5 6 | [postgres@mysql-01 pg_proctab-main]$ psql psql (16.6) Type "help" for help. postgres=# CREATE EXTENSION pg_proctab; CREATE EXTENSION |
| 1 2 3 4 | postgres=# SELECT * FROM pg_loadavg(); load1 | load5 | load15 | last_pid -------+-------+--------+---------- 0.69 | 0.46 | 0.19 | 2559 |
Verifying different options:
Monitoring memory using pg_memusage
| 1 2 3 4 5 | postgres=# SELECT *FROM pg_memusage(); memused | memfree | memshared | membuffers | memcached | swapused | swapfree | swapcached ---------+---------+-----------+------------+-----------+----------+----------+------------ 1620200 | 168172 | 0 | 1048 | 581848 | 596896 | 1533020 | 297312 (1 row) |
| 1 2 3 4 5 | postgres=# SELECT *FROM pg_cputime(); user | nice | system | idle | iowait -------+------+--------+---------+-------- 97810 | 4368 | 122896 | 4401878 | 51997 (1 row) |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | postgres=# SELECT * FROM pg_proctab(); pid | comm | fullcomm | state | ppid | pgrp | session | tty_nr | tpgid | flags | minflt | cminflt | majflt | cmajflt | utime | stime | cutime | cstime | priority | nice | num_threads | itrealvalue | starttime | vsize | rss | exit_signal | processor | rt_priority | policy | delayacct_blkio_ticks | uid | username | rchar | wchar | syscr | syscw | re ads | writes | cwrites -------+----------+----------------------------------------------------+-------+-------+-------+---------+--------+-------+---------+--------+---------+--------+---------+-------+-------+--------+--------+- ---------+------+-------------+-------------+-----------+-----------+-------+-------------+-----------+-------------+--------+-----------------------+-----+----------+---------+--------+-------+-------+---- -----+--------+--------- 85458 | postgres | postgres: autovacuum launcher | S | 85446 | 85458 | 85458 | 0 | -1 | 4210752 | 280 | 0 | 0 | 0 | 5 | 8 | 0 | 0 | 20 | 0 | 1 | 0 | 1048644 | 468770816 | 8864 | 17 | 3 | 0 | 0 | 0 | 26 | postgres | 41740 | 1 | 260 | 1 | 8192 | 0 | 0 85460 | postgres | postgres: logical replication launcher | S | 85446 | 85460 | 85460 | 0 | -1 | 4210752 | 261 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 20 | 0 | 1 | 0 | 1048645 | 468783104 | 6600 | 17 | 1 | 0 | 0 | 0 | 26 | postgres | 524 | 1 | 1 | 1 | 0 | 0 | 0 (2 rows) |
Checking memory consumption in active and idle connection using below query:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | postgres=# SELECT sa.pid, sa.query, pgprettysize(ps.rss * 1024) AS memory_consumption FROM pg_stat_activity sa JOIN pg_proctab() ps ON sa.pid = ps.pid WHERE sa.state = 'idle' limit 2; pid | query | memory_consumption -------+---------------------------------------+-------------------- 1488 | SELECT pg_catalog.pg_is_in_recovery() | 18 MB 55981 | select *from language limit 1; | 20 MB (2 rows) |
This will help the users to find the sessions which are consuming more memory and CPU and helps to troubleshoot the issues quickly.
