Re: max_connections reached in postgres 9.3.3

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.

Thank You
Ramya

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
Sent: Thursday, June 12, 2014 11:26 AM
To: Vasudevan, Ramya
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] max_connections reached in postgres 9.3.3

On Thu, Jun 12, 2014 at 1:23 PM, Vasudevan, Ramya <ramya(dot)vasudevan(at)classmates(dot)com> wrote:
> Thank you for the response.
>
> On further investigation, we found out that select statements were happening normally. But DMLs (writes to the DB) were hung for minutes at a time, and some of them went through. And we had 2 checkpoints during this period. Yesterday when we had the issue, we had 759 connections that were not idle (116 COMMIT, 238 INSERT, UPDATE 176, 57 AUTHENTICATION, 133 BIND). So, it looked like writes (simple inserts and updates) were not happening as fast and caused connections to back up in the DB. So, it didn’t look like any one bad query.
> It almost seems like postgres could not write to the WAL logs.
>
> We normally have 600-700 connections in the database. Since we
> migrated lot more applications to this postgres database from oracle,
> we increased max_connections just as a test to see if we legitimately
> need to allow more connections or if it is an issue. And quickly
> realized that we already had a high number (1500)

Be sure to rule out locks -- some operation blocks queries until it clears then everyone storms in. It's good idea to count up blocked queries (select count(*) from pg_locks where waiting) and raise alarms when you see any accumulation there.

merlin