[check_postgres] output pg_stat_activity on warning

[Ben Primrose]

Thomas,
	You're very welcome.  I'm not sure what you want the script to do, but
probably can't tell you.  I joined this list last month looking for someone
who could add modified SQL I created for the autovacuum check to the
check_postgres.pl script.   I don't speak perl, and am too busy to learn it
to change the script myself.

Ben

-----Original Message-----
From: check_postgres-bounces at bucardo.org
[mailto:check_postgres-bounces at bucardo.org] On Behalf Of Thomas Güttler
Sent: Thursday, December 17, 2015 1:49 PM
To: Check_postgres at bucardo.org
Subject: [check_postgres] output pg_stat_activity on warning

Hi Ben,

thank you for this hint.

Up to now we run a modified version of check_postgres to output the debug
SQL statements.

Is there a way to do this with an unmodified version of check_postgres?

Regards,
  Thomas Güttler

Am 17.12.2015 um 15:29 schrieb Ben Primrose:
> Pg_stat_activity will show a row per session, but a session can hold
> multiple locks.  Join it to pg_locks to get those details:
>
> SELECT locks.pid     AS pid
>       , activity.usename  AS user
>       , locks.relation::regclass AS relation
>       , locks.mode as lock_mode
> --     , activity.query    AS statement
>    FROM pg_catalog.pg_locks         locks
>    JOIN pg_catalog.pg_stat_activity activity
>      ON activity.pid = locks.pid
>   WHERE locks.GRANTED
>   ORDER BY 1, 3;
>
> -----Original Message-----
> From: check_postgres-bounces at bucardo.org
> [mailto:check_postgres-bounces at bucardo.org] On Behalf Of Thomas
> Güttler
> Sent: Thursday, December 17, 2015 8:39 AM
> To: check_postgres at bucardo.org
> Subject: [check_postgres] Checking PostgreSQL lock count
>
> Hi
>
> We use check_postgres.pl to monitor our database.
>
> We use this to check the count of the locks:
>
> https://bucardo.org/check_postgres/check_postgres.pl.html#locks
>
> We often see more than 150 locks.
>
> The question was: What is going on? We patched the script to output
> this sql statement, if the lock count was exceeded:
>
> select * from pg_stat_activity order by datname
>
> Unfortunately the result is not what I was expecting. Although there
> are more than 150 locks, pg_stat_activity shows only few (less then
> 10) queries.
>
> This has happened about twice a day during the last days, and every
> time only few lines where returned by pg_stat_activity.
>
> What is going on here?
>
> How can I further debug where these locks come from?
>
> BTW, same question here:
>
> http://dba.stackexchange.com/questions/123945/checking-postgresql-lock
> -cou
> nt-with-check-postgres-pl
>
> --
> Thomas Guettler http://www.thomas-guettler.de/
> _______________________________________________
> Check_postgres mailing list
> Check_postgres at bucardo.org
> https://mail.endcrypt.com/mailman/listinfo/check_postgres
>


--
http://www.thomas-guettler.de/
_______________________________________________
Check_postgres mailing list
Check_postgres at bucardo.org
https://mail.endcrypt.com/mailman/listinfo/check_postgres