public inbox for [email protected]
help / color / mirror / Atom feedQuestion on Alerts
5+ messages / 4 participants
[nested] [flat]
* Question on Alerts
@ 2025-02-16 13:29 sud <[email protected]>
2025-02-16 16:24 ` Re: Question on Alerts Adrian Klaver <[email protected]>
2025-02-16 16:35 ` Re: Question on Alerts Guillaume Lelarge <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: sud @ 2025-02-16 13:29 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on Alerts
2025-02-16 13:29 Question on Alerts sud <[email protected]>
@ 2025-02-16 16:24 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2025-02-16 16:24 UTC (permalink / raw)
To: sud <[email protected]>; pgsql-general <[email protected]>
On 2/16/25 05:29, sud wrote:
> 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?
Somewhere to start:
https://wiki.postgresql.org/wiki/Category:Administration#Routine_maintenance_and_monitoring
> Regards
> Yudhi
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on Alerts
2025-02-16 13:29 Question on Alerts sud <[email protected]>
@ 2025-02-16 16:35 ` Guillaume Lelarge <[email protected]>
2025-02-16 20:31 ` Re: Question on Alerts sud <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Guillaume Lelarge @ 2025-02-16 16:35 UTC (permalink / raw)
To: [email protected]
Hi,
On 16/02/2025 14:29, sud wrote:
> 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?
>
You should probably look at check_postgres and check_pgactivity. Their
source code contain numerous SQL queries, that could help you write your
own.
Regards.
--
Guillaume Lelarge
Consultant
https://dalibo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on Alerts
2025-02-16 13:29 Question on Alerts sud <[email protected]>
2025-02-16 16:35 ` Re: Question on Alerts Guillaume Lelarge <[email protected]>
@ 2025-02-16 20:31 ` sud <[email protected]>
2025-02-16 20:36 ` Re: Question on Alerts Christophe Pettus <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: sud @ 2025-02-16 20:31 UTC (permalink / raw)
To: Guillaume Lelarge <[email protected]>; +Cc: [email protected]
On Sun, Feb 16, 2025 at 10:05 PM Guillaume Lelarge <
[email protected]> wrote:
>
> You should probably look at check_postgres and check_pgactivity. Their
> source code contain numerous SQL queries, that could help you write your
> own.
>
> Regards.
>
>
Thank you very much. I am a bit new to postgres here. Can you please guide
me , where exactly I can get the source code for check_postgres and
check_pgactivity?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on Alerts
2025-02-16 13:29 Question on Alerts sud <[email protected]>
2025-02-16 16:35 ` Re: Question on Alerts Guillaume Lelarge <[email protected]>
2025-02-16 20:31 ` Re: Question on Alerts sud <[email protected]>
@ 2025-02-16 20:36 ` Christophe Pettus <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Christophe Pettus @ 2025-02-16 20:36 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: Guillaume Lelarge <[email protected]>; [email protected]
> On Feb 16, 2025, at 12:31, sud <[email protected]> wrote:
> where exactly I can get the source code for check_postgres and check_pgactivity?
https://github.com/bucardo/check_postgres
https://github.com/OPMDG/check_pgactivity
While the list is happy to help, I should note that I found these by searching for "check_postgres" and "check_pgactivity" using a web search engine, so you might want to do that step first before asking.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-16 20:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-16 13:29 Question on Alerts sud <[email protected]>
2025-02-16 16:24 ` Adrian Klaver <[email protected]>
2025-02-16 16:35 ` Guillaume Lelarge <[email protected]>
2025-02-16 20:31 ` sud <[email protected]>
2025-02-16 20:36 ` Christophe Pettus <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox