Date: Wed, 4 Jul 2018 22:56:36 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: =?iso-8859-1?Q?Jos=E9_Mello_J=FAnior?= <jose.mello.junior@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: tcp_keepalives
Message-ID: <20180705035636.GE7025@telsasoft.com>
References: 
 <CABJnyhD1vsC3xtkwfOLKiTaKiYCPeT9VSr=Pt+KqC=ue16025g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CABJnyhD1vsC3xtkwfOLKiTaKiYCPeT9VSr=Pt+KqC=ue16025g@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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