9

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I haven't started doing a full diagnosis, but I wanted to know if this is a good place to start looking.

I am also using PgBouncer with transaction-level pooling. I am suspecting that I can easily reduce the number of idle connections by tuning the pool size. However, I don't want to start doing too many changes unless there is a good reason to do it.

Can many idle connections in PostgreSQL 9.2 affect performance?

Thanks very much!

  • what does "too many idle connections" mean? dozens? hundreds? – jap1968 Apr 10 '13 at 21:01
  • Edited question. Thanks for pointing this out. Just to make it clear, last count was ~250 – Juan Carlos Coto Apr 10 '13 at 21:06
  • Maybe? I know the Oracle DBA here has asked people to set their dev tools to use a single session for all windows (instead of a new session for every window) when the development database started running very slow. The exact number that counts as "too many" will probably depend on the resources available to your database. – FrustratedWithFormsDesigner Apr 10 '13 at 21:07
3

I seriously doubt idle connections are your issue. PostgreSQL is very capable of handling idle sessions since they take almost no resources. Active sessions on the other hand are much more likely the cause of slowness.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections for reference on the number of database connections.

  • Idle sessions with uncommitted transactions are often naughty culprits! – Philᵀᴹ Apr 10 '13 at 23:54
  • I agree, Phil, although that really isn't about PostgreSQL - it's more about how the client end is written (unless I'm missing something). – Max Vernon Apr 11 '13 at 13:53
  • Please see a related question about this. It'd really help out if you pitched in on it. Thanks! – Juan Carlos Coto Apr 11 '13 at 14:04
  • Actually idle connections do have a lot of (memory) overhead and they should be avoided (I think it allocates around 10MB per connection so in your case you would be wasting at least 2.5GB of memory) – Kedare Nov 7 '17 at 20:19
  • 1
    @MaxVernon - in my running postgresql 9.5 server, i have 25 idle processes, for the RES column, ea. process is on average about 12MB (range: 4.5MB - 584MB, i suspect the larger ones have uncommited tx). – cerd Jan 30 '18 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.