JDBC Input Memory Leak: Unclosed Connections and Worker Thread Impact

Answer to the Core Questions

1. Heap‑growth trend with pipeline.workers set to 1 vs. number of CPU cores

• When pipeline.workers = 1, Logstash creates a single JDBC input instance and a single C3P0 pool. The pool size is limited by max_pool_size (default 15). Heap growth is linear with the number of open connections and any unreleased ResultSet objects, but it does not multiply across workers.
• When pipeline.workers equals the number of CPU cores (e.g., 8), Logstash spawns eight independent JDBC input threads. Each thread constructs its own C3P0 pool, so the effective pool size becomes roughly workers × max_pool_size. If each pool keeps idle connections alive, the heap can grow roughly eightfold compared to the single‑worker case, and the growth rate is proportional to the number of idle connections that are never closed.

2. Effect of idle_timeout on open‑connection counts

• Enabling idle_timeout tells C3P0 to close connections that have been idle for the specified duration. In practice, this reduces the number of live connections after a period of inactivity.
• However, if a worker thread keeps a ResultSet or Statement open, the underlying Connection is not eligible for eviction, so idle_timeout has no effect until those resources are closed.
• Also, C3P0’s default idleConnectionTestPeriod is 0 (disabled). Setting it to a small value (e.g., 60 s) can help detect dead connections that might otherwise linger.

3. Multiple worker threads each maintain separate connection pools – mitigation strategies

• Each worker’s pool is isolated; therefore, a leak in one worker does not affect others, but it does increase overall memory usage linearly with the number of workers.
• Mitigation options:
  • Reduce pipeline.workers to the minimum required for throughput.
  • Configure a shared pool by using the pool_timeout and max_pool_size options to limit the total number of connections across workers.
  • Switch to a more leak‑aware pool such as HikariCP (available via the JDBC input’s pool_class setting) and enable leakDetectionThreshold to catch unclosed resources.
  • Wrap all JDBC code in try‑with‑resources so that ResultSet, Statement, and Connection are closed in the correct order.
  • Set maxLifetime to a value slightly less than the database’s connection timeout to force periodic pool resets.

Practical Steps for Your Logstash Deployment

1. Confirm driver and pool library – the exact configuration syntax varies between drivers (e.g., Oracle, PostgreSQL, MySQL). What JDBC driver and connection‑pool library are you using?
2. Enable idle_timeout and idleConnectionTestPeriod in the JDBC input configuration.

   jdbc {
     driver => 'com.mysql.cj.jdbc.Driver'
     connection_string => 'jdbc:mysql://host/db'
     user => 'user'
     password => 'pass'
     query => 'SELECT * FROM table'
     pool_timeout => 60
     idle_timeout => 300
     max_pool_size => 5
   }
   

3. Set pipeline.workers to a value that balances throughput and memory. For a single slow query, 1 is usually sufficient.
4. Run a controlled test: open a connection, perform a query, and intentionally omit closing the connection. Observe heap growth via jcmd VM.heap_info or a profiler. Then wrap the same code in try‑with‑resources and verify that heap stabilizes.
5. Capture a heap dump (jmap -dump:live,format=b,file=heap.hprof <pid>) and a thread dump (jstack <pid>) while the pipeline is running. Inspect for lingering ResultSet objects and non‑daemon threads named like pool‑1‑thread‑#.
6. If leaks are detected, enable C3P0’s debugUnreturnedConnectionStackTraces and leakDetectionThreshold (e.g., 30000 ms) to get stack traces for unclosed resources.
7. Consider switching to HikariCP if persistent leaks continue. HikariCP’s leakDetectionThreshold and built‑in metrics make it easier to spot issues.

Assumptions & Uncertainty

• Assumes the JDBC input plugin uses C3P0 by default; newer Logstash versions allow alternative pools.
• Assumes that each worker thread indeed creates a separate pool; this is the documented behaviour but may vary with plugin updates.
• Exact impact of idle_timeout depends on the driver’s handling of idle connections; some drivers ignore the setting.

Follow these steps, observe heap and thread metrics, and adjust pipeline.workers and pool settings accordingly. If the leak persists after ensuring all JDBC objects are closed, the issue may be driver‑specific and would require checking driver logs or updating to a newer driver version.