8

I'm running pgBouncer in front of a busy postgres 9 database. For most of the time it works fine. But every few hours I'll get an error email from my application with an exception from psycopg2:

OperationalError('could not connect to server: Cannot assign requested address Is the server running on host "neo-hulk" and accepting TCP/IP connections on port 6432?')

This is a python app with a bunch of celery workers running tasks. When those errors arrive I check the pgbouncer db and the pool size is within limits. After some experimentation I've set the pool max size to 400 and pool size to 200. pool mode is "session" (requests are mostly auto-commit, almost no transactions).

What makes pgBouncer 'vanish' like that? its only for short periods of time (and in total we're talking about a tiny amount of requests compared to the sheer volume of requests its handing) but those requests that fail are important.

Thanks!

  • Operating system and version? Kernel version if Linux? Exact PostgreSQL and PgBouncer versions? Have you run PgBouncer in debug log level and seen if it reports anything useful? – Craig Ringer Feb 26 '14 at 0:02
  • Debian 6. Linux version 2.6.32-5-amd64 (Debian 2.6.32-48squeeze1) pgbouncer version 1.5.4 Postgres 9.1. The log doesn't log connect/disconnect as I thought it was a bit much, but there are no errors present when those app errors are thrown. The error comes from psycopg2 thinking there's no db server to talk to, though this problem did not exist pre pgbouncer – Harel Feb 26 '14 at 0:10
  • 1
    Hm, so current PgBouncer, and the kernel is ancient but pretty stable. I think you need to enable more detailed logging in PgBouncer with -vvv and see if you can match anomalous log output up with your errors in time. – Craig Ringer Feb 26 '14 at 0:25
  • I did a "set verbose=1; reload;" in the pgbouncer shell and could not find anything out of the ordinary in the log. this is a production system so could not stop the service to run as a non daemon with -vvv. Hopefully I've got the same result. note that the error suggests it could not connect to pgbouncer at all, i.e., could not find it listening in that port. There are thousands of connections made all the time and its strange that small number of them fail like that. – Harel Feb 26 '14 at 9:30
  • Tricky; it sounds like a potential race condition, but in what / where ... – Craig Ringer Feb 26 '14 at 9:34
14

The "Cannot assign requested address" part in the error message comes from the kernel TCP stack. When encountered intermittently, this typically means that the space of available sockets is exhausted because of too much sockets in wait state (TIME_WAIT, or less probably FIN_WAIT_1 or FIN_WAIT_2)

The range of socket ports can be output by cat /proc/sys/net/ipv4/ip_local_port_range. The default value on a stock Linux kernel is generally 32768 61000.

You may check the result of netstat -ton|grep WAIT on the client(s) and on the pgBouncer's host when the system is busy. The -o flag will show the timeout counters related to wait states.

If the total number of TCP sockets is close to 61000-32768=28232 then exhaustion of this range is likely your problem. Since a closed socket spends 60 seconds in TIME_WAIT state in normal condition, if a client host connects more than 28232 times in one minute, new connections will fail with the mentioned error until ports are freed.

As a first workaround, the TCP ports range may be extended:

 # echo "1025 65535" >/proc/sys/net/ipv4/ip_local_port_range

If it's not satisfactory, check the tcp_tw_recycle and tcp_tw_reuse flags, also tunable through /proc/sys/net/ipv4 and sysctl.

They're defined as (from man tcp):

       tcp_tw_recycle (Boolean; default: disabled; since Linux 2.4)
              Enable  fast  recycling  of  TIME_WAIT  sockets.   Enabling this
              option is not recommended since this causes problems when  work‐
              ing with NAT (Network Address Translation).

       tcp_tw_reuse (Boolean; default: disabled; since Linux 2.4.19/2.6)
              Allow  to reuse TIME_WAIT sockets for new connections when it is
              safe from protocol viewpoint.  It should not be changed  without
              advice/request of technical experts.

Personally I had success with tcp_tw_recycle when faced with this problem with a MySQL client app, but don't take this as a recommendation, my understanding of TCP being superficial at best.

  • 1
    That answer shows anything bug superficial understanding of TCP. Thank you for that. I've increased the port range and letting it run for a while to see if it has any effect. (Do I need to reboot after I set it?) – Harel Feb 26 '14 at 15:49
  • I think the port increase has done it. So far I haven't received any error. A rough count of the netstat lines shows close to 20K in the client so from there to the 28K default limit is not long. Thanks for that! – Harel Feb 26 '14 at 16:37
  • 1
    Good! You want to put the setting in /etc/sysctl.conf as net.ipv4.ip_local_port_range = 1025 65535 to have it persist across reboots. – Daniel Vérité Feb 26 '14 at 18:18
  • Thanks. I've received errors since but not that one so that is still good. Letting it run for a few days and will make the perm change. I'm glad this so far seems to work because the other changes scare me :) – Harel Feb 27 '14 at 0:43

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.