Re: max_connections reached in postgres 9.3.3

"Vasudevan, Ramya" <ramya(dot)vasudevan(at)classmates(dot)com> wrote:

> On the waiting queries - When  we reached 1500 connections, we
> had 759 connections that were in active state (116 COMMIT, 238
> INSERT, UPDATE 176, 57 AUTHENTICATION, 133 BIND. These active
> INSERTS and UPDATES also includes the 80-90 waiting sessions (We
> checked pg_stat_activity for 'waiting' state.  And pg_locks for
> granted=f). The blocking and the waiting queries were simple one
> row updates/inserts/deletes. These shouldn’t be blocking each
> other normally (unless, we think, there was a problem writing to
> the disk). Correct me if I am wrong.

You may want to consider this:

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

> [vmstat show up to 173111 context switches per second, with high
> cs rate corresponding to user CPU% between 64 and 82]

That usually means there is high contention for spinlocks,
potentially with processes getting suspended while holding
spinlocks, making things worse.

> 2) Should we look into changing our I/O scheduler from CFQ to
> deadline?
> (http://www.cybertec.at/postgresql-linux-kernel-io-tuning/)

That is likely to help when disk I/O is the bottleneck, but your
problem now is you need to be using a connection pooler in
transaction mode, so that when more than some limit of transactions
are active (that limit probably being somewhere around twice the
core count on the machine), new requests to start a transaction are
queued.  You will see much better throughput and much better
latency if you can do that.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company