public inbox for [email protected]  

help / color / mirror / Atom feed

From: SOzcn <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Troubleshooting - PostgreSQL Session CPU Analyze
Date: Fri, 20 Dec 2024 15:23:34 +0300
Message-ID: <CAJyV5AbVatw6LAEFJJkP-7gXsehFMy4aKQTkHPj+5kvEtem+Yg@mail.gmail.com> (raw)

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!

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Troubleshooting - PostgreSQL Session CPU Analyze
  In-Reply-To: <CAJyV5AbVatw6LAEFJJkP-7gXsehFMy4aKQTkHPj+5kvEtem+Yg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox