MIME-Version: 1.0
From: sud <suds1434@gmail.com>
Date: Sun, 16 Feb 2025 18:59:38 +0530
Message-ID: <CAD=mzVX2xXh0ruTsyXdjtMPEYv=75tN0Gy+Zcs=PaWiCUvS=RA@mail.gmail.com>
Subject: Question on Alerts
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f1ff4a062e4266a5"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVX2xXh0ruTsyXdjtMPEYv%3D75tN0Gy%2BZcs%3DPaWiCUvS%3DRA%40mail.gmail.com>
Precedence: bulk

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

Hi,
We are asked to have key monitoring or alerting added to our postgres
database. And I am thinking of metrics like blocked transactions, Max used
transaction Ids,  Max Active session threshold, Deadlock, Long running
query,  replica lag, buffer cache hit ratio, read/write IOPS or latency
etc. I have below questions

1)Below are some which i tried writing, can you please let me know if these
are accurate?
2)How should we be writing the alerting query for deadlock, max used
transaction ids, read/write IOPS and latency?
3)Are there any docs available which have these sample sql queries on the
pg_* table for these critical alerts which we can easily configure through
any tool?
4)Any other alerts which we should be really having?

*****Blocking sessions
select distinct blocking_id from
   (
SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking
    ON blocking.pid = ANY(pg_blocking_pids(activity.pid))
   ) a;

**** long running beyond ~1 hours*****
SELECT
    query,
    datname,
    pid,
    now() - state_change AS idle_for
FROM    pg_stat_activity
WHERE    state IN ('active', 'idle in transaction')
    AND pid <> pg_backend_pid()
    AND xact_start < now() - interval '1 hour'
ORDER BY    age(backend_xmin) DESC NULLS LAST;

**** No of active sessions ******
SELECT count(*) AS active_connections
FROM pg_stat_activity
WHERE state = 'active';

***replica lag****
SELECT client_addr, state, sent_location, write_location, flush_location,
replay_location,
       pg_wal_lsn_diff(sent_location, replay_location) AS replica_lag
FROM pg_stat_replication;

***buffer cache hit ratio****
SELECT
   (1 - (blks_read::float / (blks_hit + blks_read))) * 100 AS
buffer_cache_hit_ratio
FROM pg_stat_database;

Regards
Yudhi

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

<div dir=3D"ltr">Hi,<br>We are asked to have key monitoring or alerting add=
ed to our postgres database. And I am thinking of metrics like blocked tran=
sactions, Max used transaction Ids,=C2=A0 Max Active session threshold, Dea=
dlock, Long running query, =C2=A0replica lag, buffer cache hit ratio, read/=
write IOPS or latency etc. I have below questions<br><br>1)Below are some w=
hich i tried writing, can you please let me know if these are accurate?<br>=
2)How should we be writing the alerting query for deadlock, max used transa=
ction ids, read/write IOPS and latency?<br>3)Are there any docs available w=
hich have these sample sql queries on the pg_* table for these critical ale=
rts which we can easily configure through any tool?<div>4)Any other alerts =
which we should be really having?<br><br>*****Blocking sessions<br>select d=
istinct blocking_id from<br>=C2=A0 =C2=A0(<br>SELECT<br>=C2=A0 =C2=A0 activ=
ity.pid,<br>=C2=A0 =C2=A0 activity.usename,<br>=C2=A0 =C2=A0 activity.query=
,<br>=C2=A0 =C2=A0 blocking.pid AS blocking_id,<br>=C2=A0 =C2=A0 blocking.q=
uery AS blocking_query<br>FROM pg_stat_activity AS activity<br>JOIN pg_stat=
_activity AS blocking<br>=C2=A0 =C2=A0 ON blocking.pid =3D ANY(pg_blocking_=
pids(activity.pid))<br>=C2=A0 =C2=A0) a;<br><br>**** long running beyond ~1=
 hours*****<br>SELECT<br>=C2=A0 =C2=A0 query,<br>=C2=A0 =C2=A0 datname,<br>=
=C2=A0 =C2=A0 pid,<br>=C2=A0 =C2=A0 now() - state_change AS idle_for<br>FRO=
M =C2=A0 =C2=A0pg_stat_activity<br>WHERE =C2=A0 =C2=A0state IN (&#39;active=
&#39;, &#39;idle in transaction&#39;)<br>=C2=A0 =C2=A0 AND pid &lt;&gt; pg_=
backend_pid()<br>=C2=A0 =C2=A0 AND xact_start &lt; now() - interval &#39;1 =
hour&#39;<br>ORDER BY =C2=A0 =C2=A0age(backend_xmin) DESC NULLS LAST;<br><b=
r>**** No of active sessions ******<br>SELECT count(*) AS active_connection=
s<br>FROM pg_stat_activity<br>WHERE state =3D &#39;active&#39;;<br><br>***r=
eplica lag****<br>SELECT client_addr, state, sent_location, write_location,=
 flush_location, replay_location,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_wal_lsn_=
diff(sent_location, replay_location) AS replica_lag<br>FROM pg_stat_replica=
tion;<br><br>***buffer cache hit ratio****<br>SELECT <br>=C2=A0 =C2=A0(1 - =
(blks_read::float / (blks_hit + blks_read))) * 100 AS buffer_cache_hit_rati=
o<br>FROM pg_stat_database;<div><br></div><div>Regards</div><div>Yudhi</div=
></div></div>

--000000000000f1ff4a062e4266a5--