Search Postgresql Archives

Re: Server stops responding randomly for 5 minutes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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%';



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux