Re: Server stops responding randomly for 5 minutes - Mailing list pgsql-general

Peter,

>> Sometimes per week server stops randomly responding for approx 5 minutes.
>> User should wait for 5 minutes before server responds.
>> Other users can work normally at same time.
>Sounds very much like a locking issue.

Thank you.
This may be the issue.

> Are you doing something like
>storing a frequently updated system-wide setting in a table with a
>single row?
>http://wiki.postgresql.org/wiki/Lock_Monitoring

AFAIK Order entry should not update single row global parameter setting
table. Order id and numbers, order detail row ids and row order numbers  are
read from PostgreSql sequence tables using nextval. Hopefully this cannot
cause the issue.
Specific for this site there every order entry invokes lot of tiny updates
for single row foreign key fields on large rid table which are shown below.
Not sure that they cause delay.

>When the problem happens again, report back what the top query you see
> on that wiki page shows.

Issue is reported by users later, I cannot access to server at the time when
issue occurs. Users can report exact time when issue occured.
How to set additional logging for find reason for this after issue is
reported?

I optimized postgresql.conf to mized server using Tuning Wizard and  added

log_lock_waits = on
log_temp_files = 2000
log_min_duration_statement = 10000
log_line_prefix='%t %u %d '
log_min_error_statement = warning

to end of postgresql.conf

How to tune logging so that reason is logged if this happens again ??

Andrus.


Order entry updates:

update rid SET rid7obj='.', reakuupaev=?dok.kuupaev where (toode like 'KM%'
or toode like 'TT%') and
   dokumnr= <<TRANSFORM(dok.dokumnr)>>;
update rid SET rid7obj='.' where toode like 'YM%' and
dokumnr=<<TRANSFORM(dok.dokumnr)>>;
update rid SET reakuupaev=?dok.kuupaev where toode like 'TT%' and
dokumnr=<<TRANSFORM(dok.dokumnr)>>;

update rid SET rid6obj='XX-XXX' where dokumnr=<<TRANSFORM(dok.dokumnr)>> and
toode like 'TT%' ;
update rid SET rid6obj='XX-XXX' where dokumnr=<<TRANSFORM(dok.dokumnr)>> and
toode like 'K%' ;
update rid SET rid6obj='XX-XXX'   where  dokumnr=<<TRANSFORM(dok.dokumnr)>>
and toode like 'Y%' ;

update rid SET myygikood='E' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MLE%';
update rid SET myygikood='Y' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MLE%' and rid6obj like 'EU%' ;
update rid SET rid3obj='MNOEU' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MLE%';
update rid SET rid3obj='MEURO' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MLE%' and rid6obj like 'EU%' ;

update rid SET myygikood='E' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MMA%';
update rid SET rid3obj='MNOEU' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MMA%';
update rid SET myygikood='Y' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MMA%' and rid6obj like 'EU%';
update rid SET rid3obj='MEURO' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MMA%' and rid6obj like 'EU%';

update rid SET myygikood='Y' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MPU%';
update rid SET rid3obj='MNOEU' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MPU%';
update rid SET rid3obj='MEURO' where  dokumnr=<<TRANSFORM(dok.dokumnr)>> and
rid2obj like 'MPU%' and rid6obj like 'EU%';