How many records does jdbc input plugin can read at once?

[Sreenivas1 (Sreenivas A)]

Hi Team,

We have to pull data of 70million records from oracle database in a week and load into elastic cluster of 3 nodes. So we have a scheduler in database side which will load 50k or 1million records in 6min to a temporary table . After every 6min it will truncate the table and load 50k or 1million new set of records from actual table.

Logstash jdbc plugin will issue an sql query (select * from temptable) every 3min against the temp table to read 50k or 1million records within the 6min otherwise temp table will be overridden with new set of data and hence leads to data loss.

In this case is there any way to track whether logstash is reading all the 50k or 1million records each time.
How can I increase read capacity of logstash. kindly suggest best configuration to read more data at once. please explain how the jdbc backend execute sql query and read data.

Here is my plugin configuration

          jdbc {
                jdbc_driver_library => "${jdbc_path}"
                jdbc_driver_class => "${oracle_driver}"
                jdbc_connection_string => "${jdbc_url}"
                jdbc_user => "${jdbc_username}"
                jdbc_password => "${jdbc_password}"
                statement => "${query}"
                schedule => "*/3 * * * *"
                jdbc_default_timezone => "UTC"
             }

Regards,
Sreenivas A

[Badger]

@Sreenivas1

In this case is there any way to track whether logstash is reading all the 50k or 1million records each time.

Not that I know of.

To fetch a million records in 3 minutes means moving 5,555 records per second. That feels high for a single thread. Modifying jdbc_fetch_size is one tuning option.

If you have an indexed id column then you could run multiple inputs each fetching a subset of the data, but that requires detailed knowledge of your use case.