HTTP Interface Connection Pooling: Transitioning to Distributed Load Balancing

Core Configuration Guidance

For stable HTTP connections through an external load balancer, align timeouts as follows:

1. ClickHouse keep_alive_timeout should be set to 60–120 seconds, approximately 10–20% longer than the load balancer's idle timeout.
2. Load balancer idle_timeout should typically be configured between 50–100 seconds to ensure it does not close connections before ClickHouse.
3. max_connections must accommodate the total persistent connections from the load balancer pool. Set the pool size equal to or below this limit.

Interaction Between Server Limits and Load Balancer Pools

The max_connections setting governs the maximum concurrent TCP connections ClickHouse accepts. When using a load balancer:

• The load balancer maintains its own connection pool to backend servers.
• If the pool size exceeds max_connections, ClickHouse will reject excess connections with ECONNREFUSED, resulting in 503 errors.
• Monitor system.events for ConnectionRejected events to detect pool misalignment.

Practical Configuration Steps

1. Set ClickHouse HTTP timeouts:

   keep_alive_timeout = 120

2. Configure load balancer idle timeout:

   # Nginx example
   proxy_read_timeout 100s;
   proxy_send_timeout 100s;

3. Limit load balancer pool size:

   # Nginx upstream example
   upstream clickhouse {
       server ch-node1:8123;
       server ch-node2:8123;
       keepalive 32;  # Must be <= max_connections across cluster
   }
   
   proxy_http_version 1.1;
   proxy_set_header Connection "";

4. Monitor for issues:

   # Check for connection issues
   SELECT * FROM system.events WHERE Event LIKE '%Connection%';
   
   # Monitor broken pipes
   grep "Broken pipe" /var/log/clickhouse-server/clickhouse-server.log

Verification and Monitoring

After deployment, validate the configuration:

• Use wrk or ab to simulate traffic and observe connection stability.
• Check ss -s on ClickHouse nodes to confirm socket count stays within max_connections.
• Monitor query_log for ConnectionAborted or ConnectionTimeout events.

Important Considerations

Be aware that:

• Excessively long keep_alive_timeout values increase memory usage due to per-connection overhead.
• Older ClickHouse versions (pre-24.x) may have different default behaviors for HTTP connection handling.
• Load balancers performing TCP keep-alive probes should have probe intervals shorter than ClickHouse's timeout.