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:
[postgres@mysql-01 wget https://github.com/markwkm/pg_proctab/archive/refs/heads/main.zip
[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:
[postgres@mysql-01 pg_proctab-main]$ psql psql (16.6) Type "help" for help. postgres=# CREATE EXTENSION pg_proctab; CREATE EXTENSION
Checking load based on 1min, 5mins and 15mins time frame and last pid:
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
postgres=# SELECT *FROM pg_memusage(); memused | memfree | memshared | membuffers | memcached | swapused | swapfree | swapcached ---------+---------+-----------+------------+-----------+----------+----------+------------ 1620200 | 168172 | 0 | 1048 | 581848 | 596896 | 1533020 | 297312 (1 row)
Monitoring CPU and IO uses:
postgres=# SELECT *FROM pg_cputime(); user | nice | system | idle | iowait -------+------+--------+---------+-------- 97810 | 4368 | 122896 | 4401878 | 51997 (1 row)
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:
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.
