Thread: Connection reset by peer / broken pipe
Much the same as this person here:
http://archives.postgresql.org/pgsql-general/2008-02/msg00689.php
I am running Postgres 8.2.3 and am seeing the following error messages in my logs:
LOG: SSL SYSCALL error: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection
LOG: could not send data to client: Broken pipe
The end result is my users are connecting on TCPIP and their connection is "hanging" and they aren't able to finish their database transaction. Sometimes this will also cause the database server to be extremely slow.
I am using a fair number of default values in my postgresql.conf file.
Do you have any advice? My configuration is below. Thanks in advance--
Jeff Wigal
CONFIGURATION
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
max_connections = 100
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#bonjour_name = ''
#authentication_timeout = 60
ssl = on
#password_encryption = on
#db_user_namespace = off
# - TCP Keepalives -
# see 'man 7 tcp' for details
#tcp_keepalives_idle = 0
#tcp_keepalives_interval = 0
#tcp_keepalives_count = 0
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 1000
#temp_buffers = 1000
#max_prepared_transactions = 5
#work_mem = 1024
#maintenance_work_mem = 16384
#max_stack_depth = 2048
# - Free Space Map -
#max_fsm_pages = 20000
#max_fsm_relations = 1000
# - Kernel Resource Usage -
#max_files_per_process = 1000
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = on # enable autovacuum subprocess?
#autovacuum_naptime = 60 # time between autovacuum runs, in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_limit
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10
# note: each lock table slot uses ~220 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
VMSTAT -S
442368 total memory
51800 used memory
0 active memory
0 inactive memory
390568 free memory
0 buffer memory
0 swap cache
0 total swap
0 used swap
0 free swap
8882893 non-nice user cpu ticks
58510 nice user cpu ticks
952202 system cpu ticks
1078995945 idle cpu ticks
8062131 IO-wait cpu ticks
0 IRQ cpu ticks
0 softirq cpu ticks
0 pages paged in
0 pages paged out
0 pages swapped in
0 pages swapped out
0 interrupts
2452189757 CPU context switches
1197496622 boot time
48285711 forks
FREE -M
total used free shared buffers cached
Mem: 432 50 381 0 0 0
-/+ buffers/cache: 50 381
Swap: 0 0 0
http://archives.postgresql.org/pgsql-general/2008-02/msg00689.php
I am running Postgres 8.2.3 and am seeing the following error messages in my logs:
LOG: SSL SYSCALL error: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection
LOG: could not send data to client: Broken pipe
The end result is my users are connecting on TCPIP and their connection is "hanging" and they aren't able to finish their database transaction. Sometimes this will also cause the database server to be extremely slow.
I am using a fair number of default values in my postgresql.conf file.
Do you have any advice? My configuration is below. Thanks in advance--
Jeff Wigal
CONFIGURATION
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
max_connections = 100
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#bonjour_name = ''
#authentication_timeout = 60
ssl = on
#password_encryption = on
#db_user_namespace = off
# - TCP Keepalives -
# see 'man 7 tcp' for details
#tcp_keepalives_idle = 0
#tcp_keepalives_interval = 0
#tcp_keepalives_count = 0
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 1000
#temp_buffers = 1000
#max_prepared_transactions = 5
#work_mem = 1024
#maintenance_work_mem = 16384
#max_stack_depth = 2048
# - Free Space Map -
#max_fsm_pages = 20000
#max_fsm_relations = 1000
# - Kernel Resource Usage -
#max_files_per_process = 1000
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0
#vacuum_cost_page_hit = 1
#vacuum_cost_page_miss = 10
#vacuum_cost_page_dirty = 20
#vacuum_cost_limit = 200
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = on # enable autovacuum subprocess?
#autovacuum_naptime = 60 # time between autovacuum runs, in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 500 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_limit
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10
# note: each lock table slot uses ~220 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
VMSTAT -S
442368 total memory
51800 used memory
0 active memory
0 inactive memory
390568 free memory
0 buffer memory
0 swap cache
0 total swap
0 used swap
0 free swap
8882893 non-nice user cpu ticks
58510 nice user cpu ticks
952202 system cpu ticks
1078995945 idle cpu ticks
8062131 IO-wait cpu ticks
0 IRQ cpu ticks
0 softirq cpu ticks
0 pages paged in
0 pages paged out
0 pages swapped in
0 pages swapped out
0 interrupts
2452189757 CPU context switches
1197496622 boot time
48285711 forks
FREE -M
total used free shared buffers cached
Mem: 432 50 381 0 0 0
-/+ buffers/cache: 50 381
Swap: 0 0 0
"Jeff Wigal (Referee Assistant)" <jeff@referee-assistant.com> writes:
> I am running Postgres 8.2.3 and am seeing the following error messages in my
> logs:
> LOG: SSL SYSCALL error: Connection reset by peer
> LOG: could not receive data from client: Connection reset by peer
> LOG: unexpected EOF on client connection
> LOG: could not send data to client: Broken pipe
Do your client applications tend to leave an open connection sitting
idle for awhile? If so you might be getting burnt by idle-connection
timeouts in intervening routers. NAT-capable boxes in particular
will kill a connection that carries no data for "too long". If you're
lucky the router will offer a way to adjust its timeout ...
regards, tom lane
That's possible. They are communicating with the server using MS Access, which is connecting to the server through the Postgres ODBC driver.
On Tue, Apr 1, 2008 at 5:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Jeff Wigal (Referee Assistant)" <jeff@referee-assistant.com> writes:Do your client applications tend to leave an open connection sitting
> I am running Postgres 8.2.3 and am seeing the following error messages in my
> logs:
> LOG: SSL SYSCALL error: Connection reset by peer
> LOG: could not receive data from client: Connection reset by peer
> LOG: unexpected EOF on client connection
> LOG: could not send data to client: Broken pipe
idle for awhile? If so you might be getting burnt by idle-connection
timeouts in intervening routers. NAT-capable boxes in particular
will kill a connection that carries no data for "too long". If you're
lucky the router will offer a way to adjust its timeout ...
regards, tom lane
I'm in the process of tracking down the cause of this... Is there any way on the server side of things to terminate a connection after "x" number of minutes? For what we're doing, there is no reason to have a connection open after 10 minutes.
Thanks in advance--
Thanks in advance--
On Tue, Apr 1, 2008 at 5:44 PM, Jeff Wigal (Referee Assistant) <jeff@referee-assistant.com> wrote:
That's possible. They are communicating with the server using MS Access, which is connecting to the server through the Postgres ODBC driver.On Tue, Apr 1, 2008 at 5:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:"Jeff Wigal (Referee Assistant)" <jeff@referee-assistant.com> writes:Do your client applications tend to leave an open connection sitting
> I am running Postgres 8.2.3 and am seeing the following error messages in my
> logs:
> LOG: SSL SYSCALL error: Connection reset by peer
> LOG: could not receive data from client: Connection reset by peer
> LOG: unexpected EOF on client connection
> LOG: could not send data to client: Broken pipe
idle for awhile? If so you might be getting burnt by idle-connection
timeouts in intervening routers. NAT-capable boxes in particular
will kill a connection that carries no data for "too long". If you're
lucky the router will offer a way to adjust its timeout ...
regards, tom lane
Jeff Wigal wrote: >>> I am running Postgres 8.2.3 and am seeing the following error messages in my >>> logs: >>> >>> LOG: SSL SYSCALL error: Connection reset by peer >>> LOG: could not receive data from client: Connection reset by peer >>> LOG: unexpected EOF on client connection >>> LOG: could not send data to client: Broken pipe >> >> Do your client applications tend to leave an open connection sitting >> idle for awhile? If so you might be getting burnt by idle-connection >> timeouts in intervening routers. NAT-capable boxes in particular >> will kill a connection that carries no data for "too long". If you're >> lucky the router will offer a way to adjust its timeout ... > > I'm in the process of tracking down the cause of this... Is > there any way on the server side of things to terminate a > connection after "x" number of minutes? For what we're > doing, there is no reason to have a connection open after 10 > minutes. There are tcp_keepalives_idle, tcp_keepalives_interval, and tcp_keepalives_count (see http://www.postgresql.org/docs/8.3/static/runtime-config-connection.html). They can make the server check idle connections and detect dead ones, which will be closed. Yours, Laurenz Albe