Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
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
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Idle sessions with uncommitted transactions are often naughty culprits! –  FreshPhilOfSO 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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