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?
There are mainly 3 applications connecting to the database:
- A Server which inserts around 5,000 rows every minute.
- A web app.
- A reporting server
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.
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.