Re: Increase in max_connections

Thanks Jeff. We have scripts in place now to capture the incoming rate of requests. Waiting on the crash to happen to see if it spikes up :)

Re: min_log_duration – we *do* see a good number of requests in the log that hit our cap (of 100ms). Just that nothing stands out when we have the issue. Whatever queries we do see slow down seem to be after we start the CPU spike, and so an effect and not a cause.

We typically see about 500-700 active queries at a time – and that seems to match how high connection limit goes. We tried pg_bouncer, however, at session level pooling, it slowed down our applications (they maintain persistent connections once established, so any connection overhead slows them down), and with transaction level pooling, simply did not work.

Thanks,
Karthik

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com<mailto:jeff(dot)janes(at)gmail(dot)com>>
Date: Tuesday, March 11, 2014 9:23 AM
To: "Anand Kumar, Karthik" <Karthik(dot)AnandKumar(at)classmates(dot)com<mailto:Karthik(dot)AnandKumar(at)classmates(dot)com>>
Cc: "pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>" <pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>>
Subject: Re: [GENERAL] Increase in max_connections

On Mon, Mar 10, 2014 at 6:04 PM, Anand Kumar, Karthik <Karthik(dot)AnandKumar(at)classmates(dot)com<mailto:Karthik(dot)AnandKumar(at)classmates(dot)com>> wrote:
Hi all,

We're running postgres 9.3.2, server configuration below.

Seemingly randomly, we will see the number of active queries in postgres go up until we hit max_connections. The DB will recover after a few minutes.

We had the issue a couple of times in Feb 2014. We then upgraded the postgres server from 9.1 to 9.3.2, and the occurrence has gone up significantly – to several times a day.

The user CPU goes up as well to a 100%, no increase in I/O or system CPU.
We have slow query logging, and there is no dramatic change in the slow queries either.

I think you might need to change your cutoff for what makes a slow query. It must be the case that either your are seeing an abnormal spike in query requests, or that the queries are taking an abnormally long time (or both). If not many queries are hitting log_min_duration_statement, that just means you can lower it further without causing too much log bloat.

Usually when people hit max_connections under load, it means they need to move the limit into a connection pooler (where it can be more graceful, by putting them in a queue for a free slot, rather than returning an error) and *lower* max_connections.

max_connections|1500|configuration file

That is very high.

Cheers,

Jeff