Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cOLtm-0008UW-Bq for pgsql-performance@arkaria.postgresql.org; Tue, 03 Jan 2017 10:00: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 1cOLtl-00047Y-QS for pgsql-performance@arkaria.postgresql.org; Tue, 03 Jan 2017 10:00: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 1cOLtk-00047K-TJ for pgsql-performance@postgresql.org; Tue, 03 Jan 2017 10:00:53 +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 1cOLth-0000R3-8c for pgsql-performance@postgresql.org; Tue, 03 Jan 2017 10:00:51 +0000 Received: from localhost (localhost [127.0.0.1]) by webmail.os-ux.com (Postfix) with ESMTP id CCDE892BF1E for ; Tue, 3 Jan 2017 11:00:45 +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 2bnDoLhKQ5EX for ; Tue, 3 Jan 2017 11:00:45 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by webmail.os-ux.com (Postfix) with ESMTP id CF08792E318 for ; Tue, 3 Jan 2017 11:00:44 +0100 (CET) DKIM-Filter: OpenDKIM Filter v2.9.2 webmail.os-ux.com CF08792E318 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=os-ux.com; s=8262FE2E-C2B5-11E2-BC5C-8AEC8B26ED38; t=1483437644; bh=44VDxY1O5CSQBRnIlscGlTyolaghDJwu/v1Txeuhdck=; h=Date:From:To:Message-ID:Subject:MIME-Version:Content-Type; b=EovWLCHxPkYBmMVpWcL/ZdWJsjUGDVRpGEwhDxWvW52evDayFApntOZ8FlLAcwIuH k/6s3UoyADPsxxdnQx83/0yiQXWW+J7Gu62cKr65bhxQFRIzU2D5v80tawHiLLMYiC x6r8oXSfeJ6gEkMa9G7LxwVZBiqJC/wyiVaYA9aA= 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 JyOLz8rIPHoB for ; Tue, 3 Jan 2017 11:00:44 +0100 (CET) Received: from webmail.os-ux.com (localhost [127.0.0.1]) by webmail.os-ux.com (Postfix) with ESMTP id B19FE92BF1E for ; Tue, 3 Jan 2017 11:00:44 +0100 (CET) Date: Tue, 3 Jan 2017 11:00:44 +0100 (CET) From: Vucomir Ianculov To: pgsql-performance@postgresql.org Message-ID: <1796991063.571.1483437640199.JavaMail.vukomir@DESKTOP-9I7P6HN> In-Reply-To: <1587952740.561.1483437295901.JavaMail.vukomir@DESKTOP-9I7P6HN> Subject: Unable to connect to server MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_570_1337646980.1483437640198" X-Originating-IP: [78.46.104.104] X-Mailer: Zimbra 8.6.0_GA_1153 (Zimbra Desktop/7.2.8_12102_Windows) Thread-Topic: Unable to connect to server Thread-Index: SBVOz5XotYo53L5k5je2S6Wt551GBQ== 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_570_1337646980.1483437640198 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Hi, today i head a issue with pgsql 9.3, a lot of clients complained that they are unable to connect to the server. when i have checked the logs i sow a lot of FATAL: canceling authentication due to timeout server configuration is: Ubuntu 16.04 128G RAM RAID 10 4X 2T HDD Intel(R) Xeon(R) CPU E5-1650 v2 @ 3.50GHz clients are connecting remotely to the server, maybe i have misconfigured someting in postgresql setup. pgsql config: data_directory = '/var/lib/postgresql/9.3/main' # use data in another directory hba_file = '/etc/postgresql/9.3/main/pg_hba.conf' # host-based authentication file ident_file = '/etc/postgresql/9.3/main/pg_ident.conf' # ident configuration file external_pid_file = '/var/run/postgresql/9.3-main.pid' # write an extra PID file listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 1000 # (change requires restart) unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories ssl = on # (change requires restart) ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers ssl_cert_file = 'server.crt' # (change requires restart) ssl_key_file = 'server.key' # (change requires restart) password_encryption = on 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 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: 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' 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 Thanks, ------=_Part_570_1337646980.1483437640198 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,

today i head a issue with pgsql 9.3, a lot of client= s complained that they are unable to connect to the server.
when i have = checked the logs i sow a lot of
FATAL:  canceling authentication due to timeout=

server configuration is:
Ubuntu 16.04
128G RAM
RAID 10 4X = 2T HDD
Intel(R) Xeon(R) CPU E5-1650 v2 @ 3.50GHz

clients are conn= ecting remotely to the server, maybe i have misconfigured someting in postg= resql setup.

pgsql config:=
data_directory =3D '/var/lib/postgresql/9.3/main'   &nbs= p;     # use data in another directory
hba_file =3D = '/etc/postgresql/9.3/main/pg_hba.conf'       = # host-based authentication file
ident_file =3D '/etc/postgresql/9.3/mai= n/pg_ident.conf'   # ident configuration file
external_pid_fil= e =3D '/var/run/postgresql/9.3-main.pid'      = ;            # write= an extra PID file
listen_addresses =3D '*'     = ;     # what IP address(es) to listen on;
port =3D 5= 432            =             &nb= sp;    # (change requires restart)
max_connections =3D 10= 00            &= nbsp;     # (change requires restart)
unix_socket_di= rectories =3D '/var/run/postgresql' # comma-separated list of directoriesssl =3D on          &nb= sp;            =          # (change requires restart= )
ssl_ciphers =3D 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'   &n= bsp;    # allowed SSL ciphers
ssl_cert_file =3D 'server.c= rt'            =     # (change requires restart)
ssl_key_file =3D 'server.= key'         # (change requires res= tart)
password_encryption =3D on
max_files_per_process =3D 5000 =            # min 25
&n= bsp;            = ;            &n= bsp;            = ;  # (change requires restart)
vacuum_cost_delay =3D  &nb= sp;  20          &nb= sp;   # 0-100 milliseconds
vacuum_cost_page_hit =3D 1 &nb= sp;            =   # 0-10000 credits
vacuum_cost_page_miss =3D 10   &= nbsp;          # 0-10000 credi= ts
vacuum_cost_page_dirty =3D 20      &nbs= p;      # 0-10000 credits
vacuum_cost_limit =3D= 200         # 1-10000 credits
&= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;  # (change requires restart)
fsync =3D on    = ;            &n= bsp;            = ; # turns forced synchronization on or off
synchronous_commit =3D on&nbs= p;            &= nbsp;   # synchronization level;
     = ;            &n= bsp;            = ;          # off, local, remot= e_write, or on
wal_sync_method =3D fsync     &n= bsp;           # the defa= ult is the first option

full_page_writes =3D on   &nb= sp;            =    # recover from partial page writes
wal_buffers =3D -1 =             &nb= sp;          # min 32kB, -1 se= ts based on shared_buffers
       &nb= sp;            =             &nb= sp;       # (change requires restart)
wal_= writer_delay =3D 200ms         = ;       # 1-10000 milliseconds
commit_dela= y =3D 0           &n= bsp;            # ra= nge 0-100000, in microseconds
commit_siblings =3D 5   &nb= sp;            =      # range 1-1000
checkpoint_segments =3D 64 =             &nb= sp;  # in logfile segments, min 1, 16MB each
checkpoint_timeout =3D= 15min           &nb= sp;  # range 30s-1h
checkpoint_completion_target =3D 0.9  = ;    # checkpoint target duration, 0.0 - 1.0
checkpoint_w= arning =3D 30s          &= nbsp;     # 0 disables
 
log_checkpoints =3D= on
log_line_prefix =3D '%t [%p-%l] %q%u@%d '    &nb= sp;            # spe= cial values:
 
track_activities =3D on
track_counts =3D onstats_temp_directory =3D '/var/run/postgresql/9.3-main.pg_stat_tmp'
aut= ovacuum =3D on          &= nbsp;           &nbs= p;  # Enable autovacuum subprocess?  'on'
   &n= bsp;            = ;            &n= bsp;           # requires= track_counts to also be on.
log_autovacuum_min_duration =3D 0 &nbs= p;       # -1 disables, 0 logs all actions an= d
autovacuum_max_workers =3D 5       =        # max number of autovacuum subprocesse= s
           &nbs= p;            &= nbsp;           &nbs= p;   # (change requires restart)
autovacuum_naptime =3D 5min&n= bsp;            = ;  # time between autovacuum runs
autovacuum_vacuum_threshold =3D 5= 00       # min number of row updates before            &= nbsp;           &nbs= p;            &= nbsp;  # vacuum
autovacuum_analyze_threshold =3D 500  &nb= sp;   # min number of row updates before
   &nb= sp;            =             &nb= sp;           # analyzeautovacuum_vacuum_scale_factor =3D 0.4    # fraction of ta= ble size before vacuum
autovacuum_analyze_scale_factor =3D 0.2 &nbs= p; # fraction of table size before analyze

autovacuum_vacuum_cost_de= lay =3D 20ms     # default vacuum cost delay for
datestyle =3D 'iso, mdy'
timezone =3D 'Europe/Bucharest'

client_= encoding =3D sql_ascii         = ;    # actually, defaults to database
   &= nbsp;           &nbs= p;            &= nbsp;           # encodin= g
lc_messages =3D 'C'        &nb= sp;            =   # locale for system error message
     &= nbsp;           &nbs= p;            &= nbsp;         # strings
lc_monet= ary =3D 'C'          &nbs= p;            # loca= le for monetary formatting
lc_numeric =3D 'C'    &nb= sp;            =        # locale for number formatting
lc_t= ime =3D 'C'          &nbs= p;            &= nbsp;   # locale for time formatting
default_text_search_confi= g =3D 'pg_catalog.english'

default_statistics_target =3D 100 # pgtun= e wizard 2016-12-11
maintenance_work_mem =3D 1GB # pgtune wizard 2016-12= -11
constraint_exclusion =3D on # pgtune wizard 2016-12-11
effective_= cache_size =3D 88GB # pgtune wizard 2016-12-11
work_mem =3D 64MB # pgtun= e wizard 2016-12-11
wal_buffers =3D 32MB # pgtune wizard 2016-12-11
s= hared_buffers =3D 30GB # pgtune wizard 2016-12-11

Thanks,

------=_Part_570_1337646980.1483437640198--