pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedUnable to connect to server
4+ messages / 2 participants
[nested] [flat]
* Unable to connect to server
@ 2017-01-03 10:00 Vucomir Ianculov <vukomir@os-ux.com>
0 siblings, 1 reply; 4+ messages in thread
From: Vucomir Ianculov @ 2017-01-03 10:00 UTC (permalink / raw)
To: pgsql-performance
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,
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable to connect to server
@ 2017-01-04 09:12 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Vucomir Ianculov <vukomir@os-ux.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2017-01-04 09:12 UTC (permalink / raw)
To: pgsql-performance
Vucomir Ianculov <vukomir@os-ux.com> wrote:
> 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
you can increase authentication_timeout, default is 1 minute. How many
clients do you have, do you really needs 1000 connections? If yes,
please consider a connection pooler like pgbouncer.
Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable to connect to server
@ 2017-01-04 11:03 Vucomir Ianculov <vukomir@os-ux.com>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 4+ messages in thread
From: Vucomir Ianculov @ 2017-01-04 11:03 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-performance
HI Andreas,
thanks for your replay, i have checked connection number for the last 3 mouths and maximum number of connection was 300, so i can reduce this is it's cousin the issue.
during this error server was 99% idle, the only thing is that there was a pg_dump running on one of the DB, can this cause this error, also i have restarted pg server 2 days before because of server cert update.
Thanks.
----- Original Message -----
From: "Andreas Kretschmer" <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Sent: Wednesday, January 4, 2017 11:12:23 AM
Subject: Re: [PERFORM] Unable to connect to server
Vucomir Ianculov <vukomir@os-ux.com> wrote:
> 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
you can increase authentication_timeout, default is 1 minute. How many
clients do you have, do you really needs 1000 connections? If yes,
please consider a connection pooler like pgbouncer.
Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable to connect to server
@ 2017-01-04 11:12 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Vucomir Ianculov <vukomir@os-ux.com>
0 siblings, 0 replies; 4+ messages in thread
From: Andreas Kretschmer @ 2017-01-04 11:12 UTC (permalink / raw)
To: pgsql-performance
Vucomir Ianculov <vukomir@os-ux.com> wrote:
> HI Andreas,
>
> thanks for your replay, i have checked connection number for the last 3 mouths
> and maximum number of connection was 300, so i can reduce this is it's cousin
> the issue.
> during this error server was 99% idle, the only thing is that there was a
> pg_dump running on one of the DB, can this cause this error, also i have
no.
Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-01-04 11:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-03 10:00 Unable to connect to server Vucomir Ianculov <vukomir@os-ux.com>
2017-01-04 09:12 ` Andreas Kretschmer <akretschmer@spamfence.net>
2017-01-04 11:03 ` Vucomir Ianculov <vukomir@os-ux.com>
2017-01-04 11:12 ` Andreas Kretschmer <akretschmer@spamfence.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox