Re: 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