# Logstash jdbc input jdbc\_fetch\_size not working making first load extremely slower

**URL:** <https://discuss.elastic.co/t/logstash-jdbc-input-jdbc-fetch-size-not-working-making-first-load-extremely-slower/230289>\
**Category:** Logstash\
**Created:** [April 29, 2020, 2:59am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-jdbc-fetch-size-not-working-making-first-load-extremely-slower/230289 "2020-04-29T02:59:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![MChat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mchat/32/46467_2.png) [@MChat](https://discuss.elastic.co/u/MChat)\
**Post date:** [April 29, 2020, 2:59am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-jdbc-fetch-size-not-working-making-first-load-extremely-slower/230289/1 "2020-04-29T02:59:45Z")

</div>

I am trying to pull all the data from oracle DB table for first load and then it would be delta based on tracking column and schedule.

Below is the jdbc conf in logstash:

```auto

input {
	jdbc { 
		jdbc_connection_string => "${oracle_jdbc_connection_string}"
		jdbc_user => "${oracle_jdbc_user}"
		jdbc_password => "${oracle_jdbc_password}"
		jdbc_driver_library => ""
		jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
		jdbc_fetch_size => 1000
        statement_filepath => "${logstash_project_path}"
		last_run_metadata_path => "${logstash_project_data_path}"
		use_column_value => true
		tracking_column => "${tracking_column}"
		tracking_column_type => "numeric"
		schedule => "* * * * * *"
		type => "${type}" 
     }
}

```

I was able to pull and index a data set up to 25k in fewer mins but when trying full load of 4m records it takes more than 24 hrs to pull data from oracle table and indexing to ES takes 90 mins.

Did not realize if jdbc\_fetch\_size is working while i ran logstash for 25k records ? It seems jdbc\_fetch\_size has no impact and oracle DB tries to run one query to pull & prepare result set of 4m records which takes ~24 hrs.

What would be the alternative here to improve the performance of fetch? i faced almost similar issue with MySQL but i was getting OutOfMemory when executed logstash for 28m records load.

I fixed the issue with MySQL logstash by setting useCursorFetch=true in connection string. How the same i can achieve in case of Oracle.

I already tried jdbc\_paging\_enabled and fetch size in oracle connection string but nothing seems to help resolve the performance issue with DB fetch.

---

<div class="post-metadata">

**Author:** ![MChat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mchat/32/46467_2.png) [@MChat](https://discuss.elastic.co/u/MChat)\
**Post date:** [May 15, 2020, 11:01pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-jdbc-fetch-size-not-working-making-first-load-extremely-slower/230289/2 "2020-05-15T23:01:08Z")

</div>

Can any body help me here ?

---

<div class="post-metadata">

**Author:** ![ptamba](https://avatars.discourse-cdn.com/v4/letter/p/7feea3/32.png) [@ptamba](https://discuss.elastic.co/u/ptamba)\
**Post date:** [May 16, 2020, 6:57am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-jdbc-fetch-size-not-working-making-first-load-extremely-slower/230289/3 "2020-05-16T06:57:06Z")

</div>

how long does it take to get the 4m records if you run the query directly in the db?

fetch\_size is used to determine number of rows to be fetched everytime the driver make database calls, then store them in memory. if you don’t limit the number of results, the database will still prepare all 4M records. it’s just that the driver will fetch 25k rows every time.

---

<div class="post-metadata">

**Author:** ![MChat](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mchat/32/46467_2.png) [@MChat](https://discuss.elastic.co/u/MChat)\
**Post date:** [May 26, 2020, 2:33pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-jdbc-fetch-size-not-working-making-first-load-extremely-slower/230289/5 "2020-05-26T14:33:24Z")

</div>

I could fetch 100K in 30 mins , gets stuck for full 4M fetch from DB. My concern is jdbc\_fetch\_size it doesn't have any effect at all. I tried to limit the batch size in query itself by using ROWNUM but after fetching first batch, next batch stucks forever.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 23, 2020, 2:33pm UTC](https://discuss.elastic.co/t/logstash-jdbc-input-jdbc-fetch-size-not-working-making-first-load-extremely-slower/230289/6 "2020-06-23T14:33:29Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.