Re: Server stops responding in every week

On 01/23/2011 04:38 AM, Andrus Moor wrote:
> Scott,
>
> Thank you.
>
>> 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.
>
> I can run scripts from windows scheduler for every 4 minutes or maybe from
> pgadmin or maybe frequently accessed table triggers.
>
> Whan command should be used to detect presence of hangup ( maybe query running more than 60
> seconds or too many idle transactions?)
> should pg_stat_activity.query_start column used for this ?
>
> How to log memory usage ?
>
> Is it best way to use initially:
>
> create table activity as select * from pg_stat_activity limit 0;
> create table locks as select * from pg_locks limit 0;
>
> and if script detects hangup it invokes
>
> insert into activity select * from pg_stat_activity;
> insert into locks select * from pg_locks;
>
> How to add log timestamp column to activity and locks tables ?
> How to log memory usage ?
> Can users notice server perfomance degration due to this?
> What else to log ?
> How to create plpgsql procedure for this ?
>
> There are few tables which are used frequently. Hangup occurs probably if
> queries are invoked againt those tables.
> Is it better to implement this as trigger for those tables?
>
> Andrus.
>
>

How about:
a) Tell users not to restart database. Make them call you first. Tell them it wont get fixed until that happens.
b) If they insist on doing your job, they have to do all of your job: make a script for them to run that collects the info. Tell them they have to run the script before they restart the db.

I swear... users are like kids... they'd open windows with hammers if you let them. :-)

-Andy