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

Tomáš and Tony,

thank you.

> Have you done some basic monitoring? This typically happens when the
> machine does a lot of I/O (swapping, checkpoints, ...) - not sure how this
> is logged.

This is dedicated server, used only for PostgreSql.

I filtered windows event logs near this time (12:16) . The is error

The OpsMgr Connector could not connect to OPSMAN.yyyyy.xxx:5723.  The error
code is 11004L(The requested name is valid, but no data of the requested
type was found.).  Please verify there is network connectivity, the server
is running and has registered it's listening port, and there are no
firewalls blocking traffic to the destination.

and OpsMgr related warnings. I do'nt know what is OpsMgr but expect that
this is not realted to this issue.

postgres log at this time shows

2011-07-11 11:45:27 EEST LOG:  could not receive data from client: No
connection could be made because the target machine actively refused it.

2011-07-11 11:45:27 EEST LOG:  unexpected EOF on client connection
2011-07-11 12:18:35 EEST LOG:  could not receive data from client: No
connection could be made because the target machine actively refused it.


2011-07-11 12:18:35 EEST LOG:  unexpected EOF on client connection
2011-07-11 12:18:46 EEST LOG:  could not receive data from client: No
connection could be made because the target machine actively refused it.


2011-07-11 12:18:46 EEST LOG:  unexpected EOF on client connection
2011-07-11 12:20:55 EEST LOG:  could not receive data from client: No
connection could be made because the target machine actively refused it.

those messages appear all the time so I expect that they are also not
related this issue.

> First, you have to determine what's wrong. Set up some basic monitoring,
> on Linux I'd use iostat/vmstat, not sure about the windows - try process
> explorer from sysinternals.

Users report hangup later, I cannot monitor this at hangup time.
No idea what / how to monitor more. Anyway I added lines

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 and restarted server.

>> 10-25 users manually entering sales order, e.q. low server usage
>
> So they're entering the data directly into the database?

Try are using windows application from RDP (other computer in LAN) which
generates insert, update, delete commands to server for every entered order.

> Are you sure
> there's not something wrong in the application (e.g. a loop that takes a
> lot of time in some cases)?

This application is used for may other sites without hangup.
I havent written indentionally such loops, statements cannot take so much
time.

>> Server is installed using setup program from postgresql.org directed
>> site,
>> postresql.conf file is not modified after installation.
>
> Not sure what setup program you mean (there's an install from
> EnterpriseDB), but the default config values are usually too low (e.g. the
> default shared buffers is 24MB IIRC, but in your case 512MB would be
> probably better). Not sure if that's the problem, though.

PostgreSql was installed from EnterpriceDB, postgresql.org site contains
link into it.

I ran now EnterpiseDb Tuning wizard and optimized postgresql.conf using it
for mixed app server and restarted service.

postgresql.conf now contains

# NOTE: This has been modified by EnterpriseDB's Tuning Wizard on 2011/07/13
14:10:42
#       Original Value for "shared_buffers" was "32MB"
shared_buffers = 69608   # min 128kB

Andrus.