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.

I am using PostgreSQL 9.0 on Centos 5.7 Final.

At any given there are around 1,500-2,200 connections open for PostgreSQL on port 5432 which are in TIME_WAIT state.

I have a feeling this is not good. Is this wrong? If yes how to get rid of this?

EDIT

There are mainly 3 applications connecting to the database:

  1. A Server which inserts around 5,000 rows every minute.
  2. A web app.
  3. A reporting server
share|improve this question
    
Would be interesting to know that how many connections do you expect at any given time (what sort of application connects and how), and what is in your config regarding connections. –  dezso Sep 10 '12 at 12:57
    
On which operating system is it running? –  a_horse_with_no_name Sep 10 '12 at 18:27
    
centos 5.7 final –  indyaah Sep 11 '12 at 9:25

2 Answers 2

2000 connections in TIME_WAIT state does not look that much.

Compare this to the output of:

# cat /proc/sys/net/ipv4/ip_local_port_range

which would typically be 32768 61000 and can be enlarged if necessary.

It would be a problem if you exhausted that range, though.

If you're worried about that, you could set /proc/sys/net/ipv4/tcp_tw_reuse to 1, which would make the sockets in TIME_WAIT state disappear much faster. See this related question on serverfault.com for some background.

On the other hand, if you have lots of connections in ESTABLISHED state, consider a connection pooler as per @FrankHeikens answer.

Note that the pooler itself is not supposed to reduce the number of connections in TIME_WAIT state, though, since these will just sit between your app and the connection pooler instead of sitting between your app and postgresql. The pooler is mainly meant to reduce the number of connections established to the database per unit of time, which is not exactly the same issue.

share|improve this answer
    
Actually there are only 5 connections in Established state.!! BTW are you sure making TCP sockets reusable wont create mess?? –  indyaah Sep 11 '12 at 9:51
    
@indyaah: I had to do it on a mysql server and it worked fine. But anyway, I don't think that you even need this in your case because you're way below the threshold where tweaking this becomes necessary. –  Daniel Vérité Sep 11 '12 at 16:02
    
ohk. Thanks. :) –  indyaah Sep 12 '12 at 8:38

1500 to 2200 concurrent connections are only useful when you need them. Otherwise, bring the total number of open connections down and start using a connection pool like pgBouncer or PgPool-II. See Number of Connections (on the wiki). We do about 2500 transactions per second on 150 connections.

Don't forget, each connection uses RAM, RAM that could be used to get some real work done. PostgreSQL gets slower when you use too many connections without a connection pool. What "too many" is depends on your hardware and workload, but it's very unlikely that your system will perform better with 2000 than it will with 100; probably much worse.

share|improve this answer
    
Each back-end also uses up shared memory and system memory that could be used for cache/buffers, and AFAIK imposes some synchronisation costs on the postmaster. Pooling connections is a much smarter idea. –  Craig Ringer Sep 12 '12 at 9:05

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.