cancel
Showing results for 
Search instead for 
Did you mean: 

Connection gets reset

Level 2 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.

9 REPLIES 9
Level 3 Adventurer

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

Level 2 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.

 

 

Level 3 Adventurer

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

 

Level 2 Adventurer

Re: Connection gets reset

Hi Ranjan,

I have set the log_min_messages to 'DEBUG2' for that DB and was able to get the following in the log files

LOG: could not receive data from client: Connection reset by peer

DEBUG: unexpected EOF on client connection

Hope this is useful for debugging

 

 

EDB Team Member

Re: Connection gets reset

Hi,

 

From the logs it seems couple of possibilities which may cause this error:

1. Firewall timeout between client and server. Check the tcpkeepalive settings.
Also you can keep "log_line_prefix = %t [%p]: [%l-1] [trx_id=%x] user=%u,db=%d " in order to track the connection where this is happening.

2. If the characters in where clause id is long in that case also there is a possibility for this error.
In this case you can use function such as substr() to narrow down the number of characters in where clause.

Hope this resolves the issue.

 

--

Ajinkya

Level 2 Adventurer

Re: Connection gets reset

Hi Ajinkya

I was able to get the following information from the log files

DEBUG: 00000: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 2141022/1/1, nestlvl: 1, children:
LOCATION: ShowTransactionStateRec, xact.c:5001
LOG: 08006: could not receive data from client: Connection reset by peer
LOCATION: pq_recvbuf, pqcomm.c:955
DEBUG: 08003: unexpected EOF on client connection
LOCATION: SocketBackend, postgres.c:344
DEBUG: 00000: shmem_exit(0): 1 before_shmem_exit callbacks to make
LOCATION: shmem_exit, ipc.c:226
DEBUG: 00000: shmem_exit(0): 6 on_shmem_exit callbacks to make
LOCATION: shmem_exit, ipc.c:259
DEBUG: 00000: proc_exit(0): 4 callbacks to make
LOCATION: proc_exit_prepare, ipc.c:188
LOG: 00000: disconnection: session time: 37:17:30.87
LOCATION: log_disconnections, postgres.c:4497
DEBUG: 00000: exit(0)
LOCATION: proc_exit, ipc.c:141
DEBUG: 00000: shmem_exit(-1): 0 before_shmem_exit callbacks to make
LOCATION: shmem_exit, ipc.c:226
DEBUG: 00000: shmem_exit(-1): 0 on_shmem_exit callbacks to make
LOCATION: shmem_exit, ipc.c:259
DEBUG: 00000: proc_exit(-1): 0 callbacks to make
LOCATION: proc_exit_prepare, ipc.c:188

But not sure the issue is with DB or Network

EDB Team Member

Re: Connection gets reset

Hi @ram1992,

 

The messages you are getting are not warning/error messages. Those are because the connection from client is dropped.

 

Please check the application connection behavior or network gltich which could be causing it.

 

Regards,

Sudhir

Level 3 Adventurer

Re: Connection gets reset

Hi Ram,

The bleow errors indicate that the client application is dropping or losing the connection.
Either way, the pgsql backend was still alive and tried to send something to the client, and the client wasn't there.

I.e. the problem isn't generally a postgresql problem, it's a client problem.
We would request you to check with the application/network team for any known issues.

 

If you have any further queries, please revert with below information:

1) Output of below queries when you try to delete the data from a table: >
      select * from pg_stat_activity;

 

     select l.*, c.relname from pg_locks l, pg_class c where c.oid = l.relation;

     SELECT blocked_locks.pid AS blocked_pid,blocked_activity.usename AS blocked_user,blocking_locks.pid AS blocking_pid,blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity
blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;

 

2) \d+ tablename
3) \dt+ tablename
4) select count(*) from tablename;

Tags (1)
Level 2 Adventurer

Re: Connection gets reset

Thank you, Din2pg