2

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
Improve this question
3
  • 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, 2012 at 12:57
  • On which operating system is it running?
    – user1822
    Sep 10, 2012 at 18:27
  • centos 5.7 final
    – Anuj Patel
    Sep 11, 2012 at 9:25

2 Answers 2

Reset to default
3

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.

Improve this answer
1
  • 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, 2012 at 9:05
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.

Improve this answer
2
  • Actually there are only 5 connections in Established state.!! BTW are you sure making TCP sockets reusable wont create mess??
    – Anuj Patel
    Sep 11, 2012 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, 2012 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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