Logstash JDBC Inputs SQL Connections are never closed

Elastic Stack Logstash
1

Hey Folks,
I have a logstash configuration that has 10 jdbc inputs connecting to the same SQL Server but to diffrent databases. Scheduler is set to run every 2 minutes. All works correctly however the SQL Connections are never closed.

My logstash pipeline config example(partial)
        input {
          jdbc {
            jdbc_driver_library => "/usr/share/logstash/jars/mssql-jdbc-12.6.1.jre8.jar"
            jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            jdbc_connection_string => "jdbc:sqlserver://******:1433;databaseName=******1;trustservercertificate=true"
            jdbc_user => "${****}"
            jdbc_password => "${****}"     
            tracking_column => "unix_ts_in_secs"
            use_column_value => true
            tracking_column_type => "numeric"
            schedule => " */2 * * * *"
            add_field => { "source_index" => "logstash-idx_index1" }
            last_run_metadata_path => "/usr/share/logstash/data/index1"
            statement => "${OBJECTINSTANCE_QUERY}"
          }}
        input {
          jdbc {
            jdbc_driver_library => "/usr/share/logstash/jars/mssql-jdbc-12.6.1.jre8.jar"
            jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
            jdbc_connection_string => "jdbc:sqlserver://******:1433;databaseName=******2;trustservercertificate=true"
            jdbc_user => "${****}"
            jdbc_password => "${****}"     
            jdbc_paging_enabled => true
            jdbc_pool_timeout => 60
            tracking_column => "unix_ts_in_secs"
            use_column_value => true
            tracking_column_type => "numeric"
            schedule => " */2 * * * *"
            add_field => { "source_index" => "logstash-idx_index1" }
            last_run_metadata_path => "/usr/share/logstash/data/index2"
            statement => "${OBJECTINSTANCE_QUERY}"
          }}

Any suggestions? I have tried various timeout props and sequel_opts without any success.

2

That is expected. The input opens up the connection pool when register is called (i.e. when the pipeline is being initialized) and keeps reusing them. It does not close them unless the pipeline is stopped.

If the DB closes long-lived connections then you can validate them before use.

3

My logstash may ultimately have over 200 inputs to different databases of the same schema on the same SQL Server. Some may have have slightly cron schedules. Keeping 200+ connections open for this sounds expensive.
It also seems that each input has it's own set of connection pools. Is there any capability of reusing\sharing the same connection pool that spans multiple inputs?

4

I do not think so, but adjusting the size of the connection pool is explicitly called out in the documentation of sequel_opts option, so you may be able to reduce the number of connections. The default size is 4.

1 Like