Vengala Vinay

Having 9+ Years of Experience in Software Development

Home » Troubleshooting Transient Database Connection Dropouts in Ruby Applications Mounted on OVH

Troubleshooting Transient Database Connection Dropouts in Ruby Applications Mounted on OVH

Investigating Intermittent Database Connection Failures on OVH with Ruby Applications

This document details a systematic approach to diagnosing and resolving transient database connection dropouts experienced by Ruby applications hosted on OVH infrastructure. These issues often manifest as sporadic `ActiveRecord::ConnectionNotEstablished` or similar database-level exceptions, impacting application stability and user experience. The focus here is on practical, production-oriented debugging techniques.

Understanding the OVH Network and Database Environment

OVH’s infrastructure, particularly their Public Cloud offerings, involves a complex network topology. Understanding potential points of failure is crucial. Key areas to consider include:

  • Network Latency and Packet Loss: Intermittent network issues between your application servers and the database instances (e.g., Managed Databases for PostgreSQL/MySQL) can cause connections to time out or be reset.
  • Firewall Rules: OVH’s security groups and network ACLs, as well as any custom firewall configurations on your instances, can inadvertently block or drop legitimate database traffic.
  • Database Connection Limits: Exceeding the maximum number of concurrent connections allowed by your database instance can lead to new connection attempts being rejected.
  • Resource Exhaustion on Database Instances: High CPU, memory, or I/O load on the database server can cause it to become unresponsive, leading to connection drops.
  • Load Balancer Behavior: If a load balancer (e.g., HAProxy, OVH’s internal load balancers) sits between your application and the database, its health checks and connection timeouts need careful examination.
  • Application-Level Connection Pooling: Inefficient or misconfigured connection pooling in your Ruby application (e.g., via ActiveRecord) can exacerbate connection issues.

Phase 1: Application-Level Diagnostics

Before diving into network infrastructure, ensure your application isn’t the primary culprit. We’ll focus on logging and configuration.

Enhanced Logging in Rails

Augment your Rails application’s logging to capture more granular detail around database interactions. This involves configuring ActiveRecord’s logger and potentially adding custom middleware.

ActiveRecord Logger Configuration

In your config/environments/*.rb file (e.g., config/environments/production.rb), ensure ActiveRecord logging is enabled and directed to a suitable output. For more detailed logging, consider using a custom logger instance.

# config/environments/production.rb

# Enable verbose logging for ActiveRecord
config.active_record.logger = Logger.new(STDOUT)
config.active_record.logger.level = Logger::DEBUG

# Alternatively, for more control and potentially separate log files:
# log_file = File.open('log/active_record.log', 'a')
# config.active_record.logger = ActiveSupport::Logger.new(log_file)
# config.active_record.logger.level = Logger::DEBUG

This will log all SQL queries, connection acquisition, and release events. Look for patterns around the time of connection drops.

Custom Middleware for Connection Events

You can create a Rack middleware to log connection establishment and disconnection events more explicitly.

# app/middleware/database_connection_logger.rb
class DatabaseConnectionLogger
  def initialize(app)
    @app = app
  end

  def call(env)
    ActiveRecord::Base.connection_pool.with_connection do |conn|
      Rails.logger.info "[DB_CONN] Acquired connection: #{conn.object_id}"
      begin
        status = @app.call(env)
        Rails.logger.info "[DB_CONN] Releasing connection: #{conn.object_id}"
        status
      rescue Exception => e
        Rails.logger.error "[DB_CONN] Exception during request: #{e.message}"
        Rails.logger.error "[DB_CONN] Connection ID before error: #{conn.object_id}"
        raise e # Re-raise the exception
      end
    end
  rescue ActiveRecord::ConnectionNotEstablished => e
    Rails.logger.error "[DB_CONN] Failed to establish connection: #{e.message}"
    raise e
  rescue PG::ConnectionBad, Mysql2::Error => e # Specific DB errors
    Rails.logger.error "[DB_CONN] Database specific error: #{e.message}"
    Rails.logger.error "[DB_CONN] Attempting to reconnect..."
    # Attempt to clear the pool and let it re-establish on next request
    ActiveRecord::Base.connection_pool.disconnect!
    raise e
  end
end

# config/application.rb or config/environments/*.rb
config.middleware.use DatabaseConnectionLogger

ActiveRecord Connection Pool Configuration

The default ActiveRecord connection pool settings might not be optimal. Overly aggressive timeouts or insufficient pool sizes can lead to issues.

# config/initializers/database_connection.rb
Rails.application.config.after_initialize do
  ActiveRecord::Base.connection_pool.disconnect! # Ensure pool is cleared on startup if needed

  # Example configuration:
  pool_size = ENV.fetch('RAILS_MAX_THREADS', 5).to_i + 1 # Often set based on Puma/Unicorn worker/thread count
  # For OVH Managed Databases, check their recommended limits.
  # A common starting point is 5-10 connections per application instance.

  ActiveRecord::Base.establish_connection(
    ENV.fetch('DATABASE_URL') # Or your specific DB config
  )

  ActiveRecord::Base.connection_pool.instance_variable_set(:@size, pool_size)
  ActiveRecord::Base.connection_pool.instance_variable_set(:@timeout, 5) # Seconds to wait for a connection. Adjust cautiously.

  Rails.logger.info "ActiveRecord connection pool configured: size=#{pool_size}, timeout=5s"
end

Important Considerations:

  • Pool Size: Should generally align with your web server’s concurrency (e.g., Puma threads * Puma workers). OVH Managed Databases have connection limits; do not exceed them.
  • Timeout: A low timeout (e.g., 5 seconds) means the application will quickly give up if it can’t get a connection, which might mask underlying network issues but can also cause premature failures. A higher timeout might lead to request queues building up.
  • Reaping Idle Connections: ActiveRecord’s pool automatically reaps idle connections after a certain period (default is often 30 minutes). If your database server or network infrastructure has stricter idle connection timeouts, this can cause problems. You might need to configure pool_idle_timeout in your connection pool settings or implement a background task to periodically ping the database.

Phase 2: Infrastructure and Network Diagnostics

This phase involves examining the environment outside your application code.

OVH Network Configuration Review

Access your OVH Control Panel and review the network configuration for your Public Cloud project.

Security Groups / Firewall Rules

Ensure that the security groups attached to both your application instances and your database instances allow traffic on the database port (e.g., 5432 for PostgreSQL, 3306 for MySQL) between the relevant IP addresses or subnets. Pay close attention to egress rules on your application servers and ingress rules on your database instances.

Database Instance Network Access

For OVH Managed Databases, check the “Network” or “Access” settings. Ensure your application server’s IP address or the subnet it resides in is whitelisted for access.

Server-Side Network Diagnostics

Log into your application server instances and perform network tests.

Connectivity and Latency Checks

Use tools like ping, traceroute, and mtr to assess basic connectivity and identify potential network bottlenecks or packet loss between your application server and the database endpoint. Run these tests repeatedly over time, especially when connection drops are suspected.

# Replace with your actual database host and port
DB_HOST="your-ovh-db-host.sql.ovh.com"
DB_PORT="5432"

# Basic ping (ICMP might be blocked, less reliable for network path)
ping -c 10 $DB_HOST

# Traceroute to identify hops
traceroute $DB_HOST

# MTR for continuous path and packet loss analysis
mtr --report --report-wide $DB_HOST

Look for high latency, packet loss (especially on specific hops), or timeouts. If mtr shows significant loss on a particular hop, it might indicate an issue within OVH’s network or an intermediate provider.

TCP Connection Tests

Verify that you can establish a TCP connection to the database port. This bypasses application-level protocols and tests basic network reachability.

# Replace with your actual database host and port
DB_HOST="your-ovh-db-host.sql.ovh.com"
DB_PORT="5432"

# Using netcat (nc)
nc -zv $DB_HOST $DB_PORT

# Using telnet (often available)
telnet $DB_HOST $DB_PORT

A successful connection will typically show “Connection to … port … succeeded!” or similar. Timeouts or “Connection refused” errors indicate a network or firewall issue.

Database Server-Side Monitoring

Access the monitoring tools provided by OVH for your Managed Database service. Key metrics to scrutinize include:

  • Connection Count: Monitor the number of active and idle connections. If it consistently hovers near the maximum limit, you need to optimize application connections or increase the database limit (if possible).
  • CPU, Memory, I/O Usage: High resource utilization can make the database unresponsive.
  • Network Traffic: Look for unusual spikes or drops in network traffic to/from the database instance.
  • Slow Queries: While not directly causing drops, slow queries can tie up resources and contribute to overall unresponsiveness.

Load Balancer and Proxy Checks (If Applicable)

If you are using a load balancer (e.g., HAProxy, Nginx as a TCP proxy, or OVH’s Load Balancer service) in front of your database:

  • Health Checks: Ensure health checks are configured correctly and not prematurely marking database instances as unhealthy.
  • Connection Timeouts: Check the idle connection timeout settings on the load balancer. If this is lower than your application’s or database’s expected idle time, it can cause connections to be dropped unexpectedly.
  • Backend Configuration: Verify that the backend server addresses and ports are correct and that the load balancer can reach them.

Phase 3: Advanced Troubleshooting and Mitigation

TCP Keepalives

Network devices (routers, firewalls) and even the operating system itself can drop idle TCP connections. TCP Keepalives are a mechanism to prevent this by sending periodic “keepalive” packets on an established connection, even if no data is being transmitted. This can be configured at the OS level or sometimes within the database client/driver.

OS-Level TCP Keepalives (Linux Example)

You can adjust kernel parameters related to TCP keepalives. These settings affect all TCP connections originating from the server.

# Check current settings
sysctl net.ipv4.tcp_keepalive_time
sysctl net.ipv4.tcp_keepalive_intvl
sysctl net.ipv4.tcp_keepalive_probes

# Example: Set keepalive to send a probe every 60 seconds after 5 minutes of idleness
# This requires root privileges. Make changes persistent by editing /etc/sysctl.conf
sudo sysctl -w net.ipv4.tcp_keepalive_time=300   # 300 seconds (5 minutes) idle time before first probe
sudo sysctl -w net.ipv4.tcp_keepalive_intvl=60   # 60 seconds interval between probes
sudo sysctl -w net.ipv4.tcp_keepalive_probes=5   # 5 probes before considering the connection dead

# To make persistent, add these lines to /etc/sysctl.conf and run 'sudo sysctl -p'
# net.ipv4.tcp_keepalive_time = 300
# net.ipv4.tcp_keepalive_intvl = 60
# net.ipv4.tcp_keepalive_probes = 5

Caution: Aggressively setting keepalives can consume more resources and potentially mask genuine network failures. Tune these values based on observed network behavior and database timeouts.

Database Driver Keepalives

Some database drivers or connection libraries allow configuring keepalives directly. For PostgreSQL with the pg gem (used by `psycopg2` in Python, and often indirectly by Ruby gems), you might look for connection string parameters or environment variables. For Ruby’s `pg` gem, you can set `options` during connection:

# Example using ActiveRecord with PostgreSQL adapter
# This might require a custom adapter or direct connection manipulation
# Check the specific adapter's documentation for keepalive options.

# For the 'pg' gem directly:
require 'pg'
conn = PG.connect(
  host: 'your-db-host',
  port: 5432,
  dbname: 'your-db',
  user: 'your-user',
  password: 'your-password',
  options: '-c tcp_keepalives_idle=300 -c tcp_keepalives_interval=60 -c tcp_keepalives_count=5'
)
# ActiveRecord adapter configuration might be more complex, often involving
# adapter-specific initializers or monkey-patching.

Consult the documentation for your specific Ruby database adapter (e.g., `pg`, `mysql2`) for details on how to pass these options.

Database-Specific Idle Connection Timeouts

Databases themselves often have parameters to disconnect idle clients. For PostgreSQL, this is idle_in_transaction_session_timeout and potentially network-related settings. For MySQL, it’s wait_timeout and interactive_timeout.

-- Example for PostgreSQL: Check current settings
SHOW idle_in_transaction_session_timeout; -- If set, transactions left open too long will be aborted.

-- Example for MySQL: Check current settings
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

If these timeouts are set very low, and your application holds connections open longer than these values (e.g., due to long-running requests or inefficient transaction handling), connections will be dropped by the database server. Ensure these are set appropriately, or that your application actively closes transactions and connections.

Connection Validation

ActiveRecord has mechanisms to validate connections before handing them out from the pool. Enabling this can help detect stale connections earlier.

# config/initializers/database_connection.rb
Rails.application.config.after_initialize do
  ActiveRecord::Base.connection_pool.connection_validation_timeout = 5 # Seconds to wait for validation
  # This setting is often controlled by the adapter itself.
  # For PostgreSQL, you might need to ensure the adapter runs a simple query like 'SELECT 1'
  # during validation. Check your adapter's specific implementation.
end

When a connection is requested from the pool, ActiveRecord will attempt to validate it. If validation fails (e.g., the connection is dead), it will be discarded, and a new one will be established. This adds overhead but can improve reliability.

Conclusion

Troubleshooting transient database connection dropouts requires a multi-faceted approach. Start with detailed application logging, then systematically examine network paths, firewall rules, and infrastructure configurations. Implementing TCP keepalives and understanding database-specific timeouts are crucial for maintaining stable connections in distributed environments like OVH. Continuous monitoring of both application and infrastructure metrics is key to proactive problem detection.