Thread: Troubleshooting - PostgreSQL Session CPU Analyze

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!