MIME-Version: 1.0
From: SOzcn <selahattinozcnma@gmail.com>
Date: Fri, 20 Dec 2024 15:23:34 +0300
Message-ID: 
 <CAJyV5AbVatw6LAEFJJkP-7gXsehFMy4aKQTkHPj+5kvEtem+Yg@mail.gmail.com>
Subject: Troubleshooting - PostgreSQL Session CPU Analyze
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000dba8f70629b2b7c5"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJyV5AbVatw6LAEFJJkP-7gXsehFMy4aKQTkHPj%2B5kvEtem%2BYg%40mail.gmail.com>
Precedence: bulk

--000000000000dba8f70629b2b7c5
Content-Type: text/plain; charset="UTF-8"

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!

--000000000000dba8f70629b2b7c5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<br><br>I have created a simple structure to monitor=
 the pg_stat_activity table on the database. There&#39;s no issue with this=
; I can log it as desired.<br><br>However, our main need is to see the CPU =
usage percentage consumed by each session. You can think of it as an analys=
is similar to the spWhoIsActive method in SQL Server.<br><br>However, I can=
not determine the load caused by a specific query on the server at a given =
moment.<br><br>Even if I try to match PIDs on the system, this approach mig=
ht not yield accurate results due to interventions like session kills durin=
g the check.<br><br>I would appreciate it if you could share your experienc=
es and recommendations on this matter.<br><br>In standard processes, I can =
perform real-time analyses using the following query:<br><br>SELECT <br>=C2=
=A0 =C2=A0 pss.userid,<br>=C2=A0 =C2=A0 pss.dbid,<br>=C2=A0 =C2=A0 pd.datna=
me AS db_name,<br>=C2=A0 =C2=A0 round(pss.total_exec_time::numeric, 2) AS t=
otal_exec_time, <br>=C2=A0 =C2=A0 pss.calls, <br>=C2=A0 =C2=A0 round(pss.me=
an_exec_time::numeric, 2) AS mean_exec_time, <br>=C2=A0 =C2=A0 round((100 *=
 pss.total_exec_time / sum(pss.total_exec_time::numeric) OVER ())::numeric,=
 2) AS cpu_portion_pctg,<br>=C2=A0 =C2=A0 pss.query,<br>=C2=A0 =C2=A0 psa.c=
lient_addr AS client_host<br>FROM <br>=C2=A0 =C2=A0 pg_stat_statements pss<=
br>JOIN <br>=C2=A0 =C2=A0 pg_database pd ON pd.oid =3D pss.dbid<br>JOIN <br=
>=C2=A0 =C2=A0 pg_stat_activity psa ON psa.query =3D pss.query <br>ORDER BY=
 <br>=C2=A0 =C2=A0 pss.total_exec_time DESC <br>LIMIT <br>=C2=A0 =C2=A0 30;=
<br><br>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.<br><br>Have a nice day!</div>

--000000000000dba8f70629b2b7c5--