Logstash JDBC plugin

[calvinlyc (Calvin Liu)]

Hi guys,

We are currently using LogStash JDBC plugin to feed the data between MYSQL and ElasticSearch.

However, MySQL database has issues by using OFFSET with large set of data which can slowdown the performance by the time being.

The recommendation from StackOverflow is using id range rather than OFFSET to act the same behaviour of pagination for the data feed which LogStash does not provide from my understanding.

Our LogStash conf file looks like this

input {
jdbc {
jdbc_connection_string => "jdbc:mysql://localhost:13306/database"
jdbc_user => "root"
jdbc_password => "Password"
jdbc_validate_connection => true
jdbc_driver_library => "/tmp/share/logstash/driver/mysql-connector-java-5.1.41-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
statement => "some query here"
}
}

output {
elasticsearch {
hosts => "host_uri"
index => "index_name"
document_type => "foo"
document_id => "%{foo_id}"
flush_size => "30000"
}
}

Would you please give us some indication either the existing LogStash JDBC plugin can workaround the id range or there is some new features that plugin will provide in the future?

Thanks for your help.

[magnusbaeck (Magnus Bäck)]

It sounds like the jdbc input's sql_last_value query parameter should be able to help.

[shane_lee]

Hi Magnus,

We tuned the jdbc input filter using useCursorFetch instead.

Sample input config below:

input {
jdbc {
jdbc_connection_string => "jdbc:mysql://localhost:3307/paf?useCursorFetch=true"
jdbc_user => "xxx"
jdbc_password => "xxxx"
jdbc_validate_connection => true
jdbc_driver_library => "/tmp/share/address-lookup/data/logstash/driver/mysql-connector-java-5.1.41-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_fetch_size => "50000"
statement => "select * from address_view"
}
}

Locally on my mac with 8 service workers, was averaging throughput of 5000 events per second.