Re: max_connections reached in postgres 9.3.3

On Thu, Jun 12, 2014 at 1:51 PM, Vasudevan, Ramya
<ramya(dot)vasudevan(at)classmates(dot)com> wrote:
> Thanks Merlin.
>
> We did look at the locks in the DB and all we saw were RowExclusiveLock, AccessShareLock, RowShareLock, AccessExclusiveLock. The ExclusiveLocks we saw were all in the virtualxids.
>
> I think the max_connections maxing out is due to the DB not being able to write and complete existing connections before new legitimate ones come in, thus pilling up connections until it reaches 1500 and start erroring out.

(aside: please stop top posting) the key is not what kind of locks you
have, but if any queries are blocked. Check pg_stat_activity for
'waiting'. Via pg_locks you can check for granted=f.

If you've ruled out locking, the next step is to:
1) make sure slow query logging (log_min_duration_statement) is on --
need to rule out bad application query
2) detailed system performance diagnostics during load event. We
need to know precise values for user, system, iowait
3) know if transaction rates spiking going into (as opposed to during)
the load is interesting. We can capture tps rates by logging the
output of: select * from pg_stat_database.

Basically we need to parse the problem down to application side issue
(always suspect this first), o/s issue (there are a number of
interesting potential culprits such as THP compaction) or storage
related problems (for example, a database checkpoint storm or a page
flush storm in the o/s), or something else entirely.

merlin