From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Andrus Moor <kobruleht2(at)hot(dot)ee> |
Cc: | Andy Colson <andy(at)squeakycode(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Server stops responding in every week |
Date: | 2011-01-22 21:08:41 |
Message-ID: | AANLkTin2am6B0r25O-=7Y6BVZR_AA2ytTcO0cxGymdce@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I know you're running windows, but if you can get bash working on it,
here's a simple bash script I wrote that when it detects too many
people connected creates a table called pg_stat_bk_20110120140634
(i.e. date and time) so I can then look over what was in
pg_stat_activity when things were acting up.
#!/bin/bash
threshold=50;
dt=`date +%Y%m%d%H%M%S`;
active=`/usr/bin/psql www -Atc "select count(*) from pg_stat_activity
where current_query not ilike '%idle%';"`
if [[ active -gt threshold ]]; then
echo "there are "$active" backends";
echo "creating backup for pg_stat as pg_stat_bk_$dt"
psql www -c "select * into monitoring.pg_stat_bk_$dt from
pg_stat_activity where current_query not ilike '%idle%';"
fi
note that you should probably change what triggers it to things like
if there are any queries waiting or idle in transaction, etc.
Attachment | Content-Type | Size |
---|---|---|
check.sh | application/x-sh | 411 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | D M | 2011-01-23 00:09:46 | Re: SHMMAX and SHMALL question |
Previous Message | Andrus Moor | 2011-01-22 19:40:55 | Re: Server stops responding in every week |