[check_postgres] output pg_stat_activity on warning

[Thomas Güttler]

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/