Our reporting service is a Python application that queries a Postgres replica in another network zone, through our data centre firewall. It uses SQLAlchemy's connection pool with the settings it was born with. From about six in the evening until seven in the morning nobody uses it.
In October the depot managers' seven o'clock dashboard sat loading for a quarter of an hour, three mornings running.
The firewall keeps a session for every TCP connection it lets through and removes a session that has been idle for an hour. It removes it silently, sending nothing to either end. Overnight our pool kept five connections open that the firewall had forgotten. In the morning SQLAlchemy handed one to the first request, psycopg2 wrote the query to the socket, and the firewall dropped the packet because it no longer belonged to anything. With no reply and no reset, the Linux kernel did what it should on a lossy network and retransmitted, waiting longer each time, up to the number of attempts in net.ipv4.tcp_retries2. The default is fifteen, which the kernel documentation puts at a little over fifteen minutes. Only then did the connection fail.
Each layer was behaving as designed. The pool does not test a connection before handing it out, because pool_pre_ping is off by default. libpq turns TCP keepalives on but leaves their timing to the kernel, and the kernel's default is to send the first one after two hours of idle, an hour after the firewall had already given up.
The connection string now sets keepalives_idle to five minutes, so every idle connection sends a keepalive that the firewall counts as activity, and its session survives the night. pool_recycle is thirty minutes, half the firewall's timeout, so a connection that is older than that is replaced when it is checked out instead of being used. tcp_user_timeout is thirty seconds, so a write that nobody acknowledges fails in seconds rather than a quarter of an hour, and pool_pre_ping is on as a last check. Each zone's firewall idle timeout now sits in our service templates beside the pool settings.
The firewall did nothing wrong, and neither did TCP. Each was patient in its own way, and together the patience came to fifteen minutes.
– Sergey Shinder
