effective_io_concurrency = 200
We face some connection issue in postgres server.
From pg_log, we found the following error:
LOG: could not receive data from client: Connection reset by peer
From application the error is as follows:
PSQLException : An I/O error occured while sending to backend.
This happens evey time when they try to do bulk delete from a particular table. The where condition used for delete statement is id which has an index. The number of rows in this table is usually less than 20.
Memory : 8 GB
CPU : 4 Intel(R) Xeon(R)
DB Specification :
DB size : 150 MB
Table size : 792 KB
Index size : 80 KB
shared_buffers = 2GB
effective_cache_size = 6GB
We have changed the following parameters but still the issue is occuring
default_statistics_target = 100
Thanks in advance.
The messages saying 'LOG: could not receive data from client: Connection reset by peer' in the server log just means that the client went away unexpectedly.
There might be a couple of reasons for that :
1. Client connection waited/idle for a long time after establishing DB connection and existed unexpectedly.
2. The established connection is not disconnection DB session explicitly
3. In some cases, connection probably died with an out of memory error. (Check if you are getting any out of memory errors in database logs)
>This happens every time when they try to do bulk delete from a particular table. The where condition used for delete statement is id which has an index. The >number of rows in this table is usually less than 20.
While executing DELETE statement, monitor its execution check whether the statement is waiting for a table lock.
>PSQLException : An I/O error occured while sending to backend.
1. Check is there any firewall terminating connection object after the period of inactivity and hoe the connection object is getting handled at application end.
2. JDBC socket is timing out after a certain time before the function could finish executing. You can try setting the socket timeout, check link
Hi Ranjan, Thanks for the quick response.
I couldn't find Out of Memory, CPU spike or Locks. Also there is no firewall in the network and JDBC socket timeout is disabled.
The delete suceeds almost every time but fails at some time. is there any parameter that can be tuned to over it.
>The delete succeeds almost every time but fails at some time
This seems to the network issue, connection getting dropped in between from application end. Besides JDBC timeout parameters, no other parameter can control timeout behavior in such cases.
Please provide us few log line including error message (few lines above and below the error message).