Hello,
I have created a simple structure to monitor the pg_stat_activity table on the database. There's no issue with this; I can log it as desired.
However, our main need is to see the CPU usage percentage consumed by each session. You can think of it as an analysis similar to the spWhoIsActive method in SQL Server.
However, I cannot determine the load caused by a specific query on the server at a given moment.
Even if I try to match PIDs on the system, this approach might not yield accurate results due to interventions like session kills during the check.
I would appreciate it if you could share your experiences and recommendations on this matter.
In standard processes, I can perform real-time analyses using the following query:
SELECT
pss.userid,
pss.dbid,
pd.datname AS db_name,
round(pss.total_exec_time::numeric, 2) AS total_exec_time,
pss.calls,
round(pss.mean_exec_time::numeric, 2) AS mean_exec_time,
round((100 * pss.total_exec_time / sum(pss.total_exec_time::numeric) OVER ())::numeric, 2) AS cpu_portion_pctg,
pss.query,
psa.client_addr AS client_host
FROM
pg_stat_statements pss
JOIN
pg_database pd ON pd.oid = pss.dbid
JOIN
pg_stat_activity psa ON psa.query = pss.query
ORDER BY
pss.total_exec_time DESC
LIMIT
30;
When I attempt to log this query, it will only provide statistical responses, meaning the differences will not yield 100% accurate results on the actual server.
Have a nice day!
