Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cWJo2-0001Tn-2b for pgsql-performance@arkaria.postgresql.org; Wed, 25 Jan 2017 09:23:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cWJo1-0006lE-FB for pgsql-performance@arkaria.postgresql.org; Wed, 25 Jan 2017 09:23:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cWJo0-0006ke-9X for pgsql-performance@postgresql.org; Wed, 25 Jan 2017 09:23:52 +0000 Received: from webmail.os-ux.com ([78.46.104.104]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cWJns-0004Y2-64 for pgsql-performance@postgresql.org; Wed, 25 Jan 2017 09:23:50 +0000 Received: from localhost (localhost [127.0.0.1]) by webmail.os-ux.com (Postfix) with ESMTP id 139D93BDBC1 for ; Wed, 25 Jan 2017 10:23:41 +0100 (CET) Received: from webmail.os-ux.com ([127.0.0.1]) by localhost (webmail.os-ux.com [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id 7VGi6j04bkqk for ; Wed, 25 Jan 2017 10:23:41 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by webmail.os-ux.com (Postfix) with ESMTP id B7D423BDBC2 for ; Wed, 25 Jan 2017 10:23:40 +0100 (CET) DKIM-Filter: OpenDKIM Filter v2.9.2 webmail.os-ux.com B7D423BDBC2 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=os-ux.com; s=8262FE2E-C2B5-11E2-BC5C-8AEC8B26ED38; t=1485336220; bh=Edso327m18Foop9P0ksw+DG/ooFlKtMkkxsx1k90OIA=; h=Date:From:To:Message-ID:Subject:MIME-Version:Content-Type; b=ROYS9QdtP/S77wY9/eiLfVZyzEigcSH8Dsthfk5Mj8Mkq8SALjJ1mp1ynucAcT4DC drJpj+cVZaFr9EDDIxmwx0ohIHnmlsedek6LoXUGepZ4rLuGP9Df5/7YCyaBJ/j/pf 2Y2xSSEdrDDVICeVk13DSrqaHTXqnqjBzn//88h8= X-Virus-Scanned: amavisd-new at os-ux.com Received: from webmail.os-ux.com ([127.0.0.1]) by localhost (webmail.os-ux.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id h2cZKVSwv4hG for ; Wed, 25 Jan 2017 10:23:40 +0100 (CET) Received: from webmail.os-ux.com (localhost [127.0.0.1]) by webmail.os-ux.com (Postfix) with ESMTP id 59BDA3BDBC1 for ; Wed, 25 Jan 2017 10:23:40 +0100 (CET) Date: Wed, 25 Jan 2017 10:23:39 +0100 (CET) From: Vucomir Ianculov To: pgsql-performance@postgresql.org Message-ID: <199526991.862.1485336217892.JavaMail.vukomir@DESKTOP-9I7P6HN> In-Reply-To: <1739044124.833.1485335545472.JavaMail.vukomir@DESKTOP-9I7P6HN> Subject: pgsql connection timeone MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_861_1971854483.1485336217889" X-Priority: 1 Importance: high X-Originating-IP: [78.46.104.104] X-Mailer: Zimbra 8.6.0_GA_1153 (Zimbra Desktop/7.2.8_12102_Windows) Thread-Topic: pgsql connection timeone Thread-Index: mMz1WZa2RYoUV9GvQhyI4vzgiZCVig== X-Pg-Spam-Score: -5.2 (-----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_861_1971854483.1485336217889 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Hi, i'm seeing a lot of connection time out in postgresql log 2017-01-25 11:09:47 EET [6897-1] XXX@YYY FATAL: canceling authentication due to timeout 2017-01-25 11:10:15 EET [6901-1] XXX@YYY FATAL: canceling authentication due to timeout 2017-01-25 11:10:17 EET [6902-1] xxx@YYY FATAL: canceling authentication due to timeout 2017-01-25 11:10:33 EET [6906-1] xxxx@YYY FATAL: canceling authentication due to timeout 2017-01-25 11:10:44 EET [6912-1] xxx@YYY FATAL: canceling authentication due to timeout 2017-01-25 11:10:48 EET [6913-1] xxx@YYY FATAL: canceling authentication due to timeout 2017-01-25 11:10:52 EET [6920-1] xxx@YYY: canceling authentication due to timeout 2017-01-25 11:10:52 EET [6930-1] postgres@postgres FATAL: canceling authentication due to timeout 2017-01-25 11:10:53 EET [6921-1] xxx@YYY FATAL: canceling authentication due to timeout 2017-01-25 11:11:08 EET [6933-1] xxx@YYY FATAL: canceling authentication due to timeout i also tired to login as postgres user from command line. postgres@sppgsql01:~$ psql psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. server load is ok, under 1, memory usage is also ok. root@server ~ # free -m total used free shared buff/cache available Mem: 128908 3398 795 31671 124714 92980 Swap: 8191 1418 6773 system is a ubuntu 16.04 and i'm using postgresql 9.3 connection limit is at 500 at the moment i have 180 connection, authentication_timeout is default 1 min. here is the postgresql.conf data_directory = '/var/lib/postgresql/9.3/main' # use data in another directory # (change requires restart) hba_file = '/etc/postgresql/9.3/main/pg_hba.conf' # host-based authentication file # (change requires restart) ident_file = '/etc/postgresql/9.3/main/pg_ident.conf' # ident configuration file # (change requires restart) external_pid_file = '/var/run/postgresql/9.3-main.pid' # write an extra PID file # (change requires restart) listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) port = 5432 # (change requires restart) max_connections = 500 # (change requires restart) unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories # (change requires restart) # (change requires restart) # (change requires restart) # (change requires restart) ssl = on # (change requires restart) ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers # (change requires restart) ssl_cert_file = 'server.crt' # (change requires restart) ssl_key_file = 'server.key' # (change requires restart) password_encryption = on # 0 selects the system default # 0 selects the system default # 0 selects the system default # (change requires restart) # (change requires restart) # in kB, or -1 for no limit max_files_per_process = 5000 # min 25 # (change requires restart) vacuum_cost_delay = 20 # 0-100 milliseconds vacuum_cost_page_hit = 1 # 0-10000 credits vacuum_cost_page_miss = 10 # 0-10000 credits vacuum_cost_page_dirty = 20 # 0-10000 credits vacuum_cost_limit = 200 # 1-10000 credits # (change requires restart) fsync = on # turns forced synchronization on or off synchronous_commit = on # synchronization level; # off, local, remote_write, or on wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync # fdatasync (default on Linux) # fsync # fsync_writethrough # open_sync full_page_writes = on # recover from partial page writes wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers # (change requires restart) wal_writer_delay = 200ms # 1-10000 milliseconds commit_delay = 0 # range 0-100000, in microseconds commit_siblings = 5 # range 1-1000 checkpoint_segments = 64 # in logfile segments, min 1, 16MB each checkpoint_timeout = 15min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 30s # 0 disables log_checkpoints = on log_line_prefix = '%t [%p-%l] %q%u@%d ' # special values: log_timezone = 'Europe/Bucharest' track_activities = on track_counts = on stats_temp_directory = '/var/run/postgresql/9.3-main.pg_stat_tmp' autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and autovacuum_max_workers = 5 # max number of autovacuum subprocesses # (change requires restart) autovacuum_naptime = 5min # time between autovacuum runs autovacuum_vacuum_threshold = 500 # min number of row updates before # vacuum autovacuum_analyze_threshold = 500 # min number of row updates before # analyze autovacuum_vacuum_scale_factor = 0.4 # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.2 # fraction of table size before analyze autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for datestyle = 'iso, mdy' timezone = 'Europe/Bucharest' client_encoding = sql_ascii # actually, defaults to database # encoding lc_messages = 'C' # locale for system error message # strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting default_text_search_config = 'pg_catalog.english' # (change requires restart) # (change requires restart) # directory 'conf.d' default_statistics_target = 100 # pgtune wizard 2016-12-11 maintenance_work_mem = 1GB # pgtune wizard 2016-12-11 constraint_exclusion = on # pgtune wizard 2016-12-11 effective_cache_size = 88GB # pgtune wizard 2016-12-11 work_mem = 64MB # pgtune wizard 2016-12-11 wal_buffers = 32MB # pgtune wizard 2016-12-11 shared_buffers = 30GB # pgtune wizard 2016-12-11 dose anyone know why this thing are happening? Thanks. Br, Vuko ------=_Part_861_1971854483.1485336217889 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: arial,helvetica,sans-serif; font-size: 10pt; colo= r: #000000'>Hi,

i'm seeing a lot of connection time out in postgresq= l log

2017-01-25 11:09:47 EET [6897-1] XXX@YYY FATAL:  cancelin= g authentication due to timeout
2017-01-25 11:10:15 EET [6901-1] XXX@YYY= FATAL:  canceling authentication due to timeout
2017-01-25 11:10:1= 7 EET [6902-1] xxx@YYY FATAL:  canceling authentication due to timeout=
2017-01-25 11:10:33 EET [6906-1] xxxx@YYY FATAL:  canceling authen= tication due to timeout
2017-01-25 11:10:44 EET [6912-1] xxx@YYY FATAL:&= nbsp; canceling authentication due to timeout
2017-01-25 11:10:48 EET [6= 913-1] xxx@YYY FATAL:  canceling authentication due to timeout
2017= -01-25 11:10:52 EET [6920-1] xxx@YYY:  canceling authentication due to= timeout
2017-01-25 11:10:52 EET [693= 0-1] postgres@postgres FATAL:  canceling authentication due to timeout=
2017-01-25 11:10:53 EET [6921-1] xxx@YYY FATAL:  canceling = authentication due to timeout
2017-01-25 11:11:08 EET [6933-1] xxx@YYY F= ATAL:  canceling authentication due to timeout

i also tired to = login as postgres user from command line.

postgres@sppgsql01:~$ psql=
psql: server closed the connection u= nexpectedly
        This probably means t= he server terminated abnormally
        b= efore or while processing the request.

server load is ok, und= er 1,

memory usage is also ok.

root@server ~ # free -m
&n= bsp;            = ; total        used   &nb= sp;    free      shared  buff/= cache   available
Mem:      &nbs= p;  128908        3398  &= nbsp;      795      = 31671      124714     &n= bsp; 92980
Swap:          8= 191        1418    &= nbsp;   6773

system is a ubuntu 16.04 and i'm using postgr= esql 9.3

connection limit is at 500 at the moment i have 180 connect= ion, authentication_timeout is default 1 min.

here is the postgresql= .conf

data_directory =3D '/var/lib/postgresql/9.3/main'   =       # use data in another directory
  = ;            &n= bsp;            = ;             #= (change requires restart)
hba_file =3D '/etc/postgresql/9.3/main/pg_hba.c= onf'       # host-based authentication file
=             &nb= sp;            =             &nb= sp;  # (change requires restart)
ident_file =3D '/etc/postgresql/9.3/= main/pg_ident.conf'   # ident configuration file
  &n= bsp;            = ;            &n= bsp;            # (c= hange requires restart)
external_pid_file =3D '/var/run/postgresql/9.3-mai= n.pid'           &nb= sp;      # write an extra PID file
  &n= bsp;            = ;            &n= bsp;            # (c= hange requires restart)
listen_addresses =3D '*'    &n= bsp;     # what IP address(es) to listen on;
 &nb= sp;            =             &nb= sp;            = # comma-separated list of addresses;
      =             &nb= sp;            =          # defaults to 'localhost';= use '*' for all
         &nb= sp;            =             &nb= sp;     # (change requires restart)
port =3D 5432 = ;            &n= bsp;            = ;   # (change requires restart)
max_connections =3D 500 # (chang= e requires restart)
unix_socket_directories =3D '/var/run/postgresql' # co= mma-separated list of directories
<= span style=3D"font-style: italic;">      &nbs= p;            &= nbsp;           &nbs= p;        # (change requires restart)
&= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)
      &= nbsp;           &nbs= p;            &= nbsp;        # (change requires restart)=
ssl =3D on    =             &nb= sp;            =    # (change requires restart)
ssl_ciphers =3D 'DEFAULT:!LOW:!EX= P:!MD5:@STRENGTH'        # allowed SSL c= iphers
           &= nbsp;           &nbs= p;            &= nbsp;   # (change requires restart)
ssl_cert_file =3D 'server.cr= t'            &= nbsp;   # (change requires restart)
ssl_key_file =3D 'server.key= '         # (change requires restar= t)
password_encryption =3D on
       &= nbsp;           &nbs= p;            &= nbsp;       # 0 selects the system default
&= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;  # 0 selects the system default
     &nbs= p;            &= nbsp;           &nbs= p;         # 0 selects the system d= efault
           &= nbsp;           &nbs= p;            &= nbsp;   # (change requires restart)
    &nbs= p;            &= nbsp;           &nbs= p;          # (change requires= restart)
          &nbs= p;            &= nbsp;           &nbs= p;    # in kB, or -1 for no limit
max_files_per_process =3D= 5000            # m= in 25
           &n= bsp;            = ;            &n= bsp;   # (change requires restart)
vacuum_cost_delay =3D &n= bsp;   20         &n= bsp;    # 0-100 milliseconds
vacuum_cost_page_hit =3D 1&nbs= p;            &= nbsp;  # 0-10000 credits
vacuum_cost_page_miss =3D 10  &nbs= p;           # 0-10000 cr= edits
vacuum_cost_page_dirty =3D 20      &nb= sp;      # 0-10000 credits
vacuum_cost_limit =3D = 200         # 1-10000 credits
&nbs= p;            &= nbsp;           &nbs= p;            &= nbsp; # (change requires restart)
<= span style=3D"font-style: italic;">fsync =3D on    &nbs= p;            &= nbsp;            # t= urns forced synchronization on or off
synchronous_commit =3D on  = ;            &n= bsp;  # synchronization level;
      &n= bsp;            = ;            &n= bsp;        # off, local, remote_write, = or on
wal_sync_method =3D fsync       &= nbsp;         # the default is the = first option
          &= nbsp;           &nbs= p;            &= nbsp;    # supported by the operating system:
  &= nbsp;           &nbs= p;            &= nbsp;            #&n= bsp;  open_datasync
        &= nbsp;           &nbs= p;            &= nbsp;      #   fdatasync (default on Lin= ux)
           &nbs= p;            &= nbsp;           &nbs= p;   #   fsync
=       &nb= sp;            =             &nb= sp;        #   fsync_writethro= ugh
           &nbs= p;            &= nbsp;           &nbs= p;   #   open_sync
full_page_writes =3D on  =             &nb= sp;    # recover from partial page writes
wal_buffers =3D -= 1            &n= bsp;           # min 32kB= , -1 sets based on shared_buffers
<= span style=3D"font-style: italic;">      &nbs= p;            &= nbsp;           &nbs= p;        # (change requires restart)
w= al_writer_delay =3D 200ms        &n= bsp;       # 1-10000 milliseconds
commit_del= ay =3D 0           &= nbsp;            # r= ange 0-100000, in microseconds
commit_siblings =3D 5   &nbs= p;            &= nbsp;    # range 1-1000
checkpoint_segments =3D 64 &nb= sp;            =   # in logfile segments, min 1, 16MB each
checkpoint_timeout =3D 15mi= n            &n= bsp; # range 30s-1h
checkpoint_completion_target =3D 0.9   =    # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = =3D 30s           &n= bsp;    # 0 disables
      &n= bsp;            = ;          
log_checkpo= ints =3D on
log_line_prefix =3D '%t [%p-%l] %q%u@%d '   &nb= sp;            = # special values:
         &= nbsp;           &nbs= p;            &= nbsp;   
log_timezone =3D 'Europe/Bucharest'
track_activi= ties =3D on
track_counts =3D on
stats_temp_directory =3D '/var/run/postgr= esql/9.3-main.pg_stat_tmp'
autovacuum =3D on     =             &nb= sp;       # Enable autovacuum subprocess?&nbs= p; 'on'
           =             &nb= sp;            =     # requires track_counts to also be on.
log_autovacuum= _min_duration =3D 0         # -1 di= sables, 0 logs all actions and
       &= nbsp;           &nbs= p;            &= nbsp;     
autovacuum_max_workers =3D 5 &nb= sp;            # max= number of autovacuum subprocesses
=       &nb= sp;            =             &nb= sp;        # (change requires restart)
= autovacuum_naptime =3D 5min        =        # time between autovacuum runs<= br style=3D"font-style: italic;">autova= cuum_vacuum_threshold =3D 500       # min num= ber of row updates before
        =             &nb= sp;            =        # vacuum
autovacuum_analyze_threshold= =3D 500      # min number of row updates before
=             &nb= sp;            =             &nb= sp;  # analyze
autovacuum_vacuum_scale_factor =3D 0.4  &nbs= p; # fraction of table size before vacuum
autovacuum_analyze_scale_factor = =3D 0.2   # fraction of table size before analyze
  &n= bsp;            = ;            &n= bsp;           
au= tovacuum_vacuum_cost_delay =3D 20ms     # default vacuu= m cost delay for
         &nb= sp;            =             &nb= sp;  
datestyle =3D 'iso, mdy'
timezone =3D 'Europe/Bucharest'=
client_enc= oding =3D sql_ascii         &n= bsp;   # actually, defaults to database
    =             &nb= sp;            =            # encoding
lc= _messages =3D 'C'         &nbs= p;             = # locale for system error message
<= span style=3D"font-style: italic;">      &nbs= p;            &= nbsp;           &nbs= p;        # strings
lc_monetary =3D 'C'=             &nb= sp;          # locale for mone= tary formatting
lc_numeric =3D 'C'      &nbs= p;            &= nbsp;    # locale for number formatting
lc_time =3D 'C'&nbs= p;            &= nbsp;           &nbs= p; # locale for time formatting
default_text_search_config =3D 'pg_catalog= .english'
          &nbs= p;            &= nbsp;           &nbs= p;    # (change requires restart)
    &= nbsp;           &nbs= p;            &= nbsp;          # (change requi= res restart)
          &= nbsp;           &nbs= p;            &= nbsp;    # directory 'conf.d'
default_statistics_target =3D= 100 # pgtune wizard 2016-12-11
maintenance_work_mem =3D 1GB # pgtune wiza= rd 2016-12-11
constraint_exclusion =3D on # pgtune wizard 2016-12-11
effe= ctive_cache_size =3D 88GB # pgtune wizard 2016-12-11
work_mem =3D 64MB # p= gtune wizard 2016-12-11
wal_buffers =3D 32MB # pgtune wizard 2016-12-11
s= hared_buffers =3D 30GB # pgtune wizard 2016-12-11


dose anyone know why this thing are happening?

Thanks.=

Br,
Vuko
------=_Part_861_1971854483.1485336217889--