cancel
Showing results for 
Search instead for 
Did you mean: 

Connection gets reset

Adventurer

Connection gets reset

Hi All,

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.

 

System specification:

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 

random_page_cost = 1.1
effective_io_concurrency = 200
 
Please let us know if there is any solution to it. If any further information is required add it in comment section.

Thanks in advance.

3 REPLIES
EDB Team Member

Re: Connection gets reset

Hi ram1992,

 

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

https://jdbc.postgresql.org/documentation/head/connect.html

Adventurer

Re: Connection gets reset

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.

 

 

EDB Team Member

Re: Connection gets reset

Hi,

>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).