From: Justin Pryzby <pryzby@telsasoft.com>
To: José Mello Júnior <jose.mello.junior@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: tcp_keepalives
Date: Wed, 4 Jul 2018 22:56:36 -0500
Message-ID: <20180705035636.GE7025@telsasoft.com> (raw)
In-Reply-To: <CABJnyhD1vsC3xtkwfOLKiTaKiYCPeT9VSr=Pt+KqC=ue16025g@mail.gmail.com>
References: <CABJnyhD1vsC3xtkwfOLKiTaKiYCPeT9VSr=Pt+KqC=ue16025g@mail.gmail.com>
Hi,
Sorry for my poor Portugese; I didn't translate back since I cannot verify its
accuracy.
On Wed, Jul 04, 2018 at 10:19:41AM -0300, José Mello Júnior wrote:
> Pessoal, estou em um cliente que tem um servidor Linux (não sei qual
> distribuição) mas de um tempo para cá está aparecendo uma mensagem sobre
> excesso de conexões e então para de fazer conexões.
translated:
| Personally, I'm on a client that has a Linux server (I do not know which
| one distribution) but from time to time a message is over connections
| and then stop connections.
TCP keepalives allow closing a connection which is already broken, such as due
to NAT router "forgetting" about the connection (perhaps because it's idle, or
perhaps because the router rebooted). Keepalives may also help to AVOID the
connection from breaking in the first place, by continuing to send traffic on
an otherwise idle connection.
Is the connection from a client on a remote subnet ? If not, keepalives will
have no effect.
What is the current value of max_connections? And when you hit
max_connections, causing new connections to be rejected, what are all the
existing connections doing?
SELECT backend_start, pid, datname, usename, state, left(query,222) FROM pg_stat_activity ORDER BY 1;
Does the application keep connections opened forever ? Or does it keep opened
the "record" number of connections opened from a multi-process or thread pool?
Do you run both the client applications and the database server ?
Are there many application connections at once ?
You could check with: netstat -anpe |grep 5432 |grep PID
I'd typically expect only one connection to postgres per client process.
> Assistindo a uma palestra, o cidadão citou que se eu alterasse essa
> configuração para 50, o postgresql começaria a matar as conexões com algum
> tempo automaticamente.
translated:
| While attending a lecture, the citizen mentioned that if I changed this
| setting to 50, postgresql would start killing the connections with some
| time automatically.
Unless I misunderstand, I wouldn't think so: TCP keepalives only close
connections which are *already* broken.
It's also possible that during a spike in transactions, clients are bogging
each other down, and I recall some people have reported success using a
connection pooler (like pgbouncer) to reject a small fraction of the clients
during the peak, to allow the rest of the client requests to be quickly
serviced, rather than all of them running slowly and many timing out.
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, jose.mello.junior@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: tcp_keepalives
In-Reply-To: <20180705035636.GE7025@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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