Validating ActiveRecord Connections22 Jan 2016
ActiveRecord has been around as long as Ruby on Rails and since the framework was pretty much build all from scratch database connection pooling has been invented along the way (as it got critical [driven by JRuby]).
What pooling does is that instead of each thread opening its own connection a pool decides whether it will open a new connection or can re-use an existing one. Which sometimes leads to pooled connections sitting around idle (when application load decreases after a high load).
A fairly known issue when keeping (idle) connections open, for longer periods, is that they eventually become stale. Thus pools tend to verify connections being still active on each checkout. Yet, talking over non-reliable TCP networks, this is often not enough as sockets might end up in a “black-hole” state where pings seems to be sent fine but never return properly and only error after a socket timeout is reached. The application thread/request seemingly hangs until the timeout kicks in.
One solution would be to set a lower timeout while creating a network socket (decent drivers have a socket-timeout configuration option) however that’s more of a work-around. Instead validating “ping” queries with a guard timer, expected to return in a given time frame (say 3 seconds), should be used. The connection gets killed if a response is not received and a reconnect is attempted.
This pattern works well for most databases and is built into
Java’s database connectivity APIs.
Thus under JRuby, using activerecord-jdbc-adapter, all is easy.
Consult the JDBC driver if validation timeouts are supported and than configure
:connection_sql_timeout: 3 (in database.yml).
Note that setting a timeout isn’t necessary when a JDBC pool is used (
jndi: is set),
in such cases instead make the underlying pool perform periodic pings for idle connections.
Alternatively, under MRI, go and invent a validating thread …