PostgreSQL Performance Monitoring is essential for maintaining healthy production databases and quickly identifying resource bottlenecks. By combining pg_proctab with pg_stat_activity, database administrators can monitor CPU usage, memory consumption, I/O activity, active sessions, and query performance from a single view. This guide explains how to install pg_proctab and use it alongside PostgreSQL monitoring views to troubleshoot performance issues efficiently.
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.
PostgreSQL Performance Monitoring Overview:
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.
PostgreSQL Performance Monitoring with 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.
Install pg_proctab for PostgreSQL Performance Monitoring
[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
Enable the pg_proctab Extension:
[postgres@mysql-01 pg_proctab-main]$ psql psql (16.6) Type "help" for help. postgres=# CREATE EXTENSION pg_proctab; CREATE EXTENSION
Monitor System Load in PostgreSQL
postgres=# SELECT * FROM pg_loadavg(); load1 | load5 | load15 | last_pid -------+-------+--------+---------- 0.69 | 0.46 | 0.19 | 2559
Verifying different options:
Monitor PostgreSQL Memory Usage
postgres=# SELECT *FROM pg_memusage();
memused | memfree | memshared | membuffers | memcached | swapused | swapfree | swapcached
---------+---------+-----------+------------+-----------+----------+----------+------------
1620200 | 168172 | 0 | 1048 | 581848 | 596896 | 1533020 | 297312
(1 row)Monitor PostgreSQL CPU and I/O Usage
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)
Monitor Active Sessions with pg_stat_activity:
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.
