# Tuning logstash and elasticsearch for loading data from oracle database **URL:** \ **Category:** Elasticsearch\ **Created:** [August 19, 2019, 6:30pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795 "2019-08-19T18:30:58Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 19, 2019, 6:30pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/1 "2019-08-19T18:30:59Z")
Hi Team, We are trying to load data from oracle database to elasticsearch using logstash. We have created a config file with input section having the driver info, connection to db etc. The data is extracted using select query and the output points to the data nodes of the elastic cluster. After doing the config updates, we are now trying to get maximum data loading speed. But the max we could get was 25 GB per hour. Do you have any advise on what to be tuned, where to look at for finding the bottleneck? We are using elastic and logstash 7.1.1. Logstash workers = 12 (16 cpus) and pipeline batch size is 64000. JVM is 64G on 128 G server. Elastic has 7 data nodes. Each having jvm 32G off 64 G machine. Thank You in Advance Aby. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 19, 2019, 6:58pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/2 "2019-08-19T18:58:12Z")
How have you verified that it is Elasticsearch that is the bottleneck and not the Oracle input? ---
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 19, 2019, 7:03pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/3 "2019-08-19T19:03:58Z")
Christian, For verifing that, I loaded the oracle table output to another elasticsearch first. Then used logstasgh to load from that elasticsearch to this one, the speed remains the same, 25 G per hour. Thank You for the response. Aby. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 19, 2019, 7:06pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/4 "2019-08-19T19:06:39Z")
What type of storage do you have? What type of networking? ---
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 19, 2019, 7:16pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/5 "2019-08-19T19:16:50Z")
The storage is a non ssd disk and VLAN for network. Is that what you are looking for? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 19, 2019, 7:28pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/6 "2019-08-19T19:28:50Z")
Have you looked at disk I/O, iowait and network performance to see if any of those seem to be limiting throughput? How many indices and shards are you actively indexing into? ---
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 19, 2019, 7:40pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/7 "2019-08-19T19:40:32Z")
Yes Christian. We had done monitoring on the nodes. The network traffic and the i/o looks okay. For testing purpose I have taken only one index. It has 7 shards. One on each of the data node. ---
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 19, 2019, 9:22pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/8 "2019-08-19T21:22:44Z")
Logstash is able to load data from elastic cluster 1 to cluster 2 in 1 GB per min speed. Its only when we pull data from oracle database, the loading is slow. We also observed that logstah spends some time processing data where teh cpu/meme goes up in the logstash's server, and after a while the data load takes place. So can we assume that when the data gets "formatted" from oracle database's form to elasticsearch form, the process takes time? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 19, 2019, 9:45pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/9 "2019-08-19T21:45:06Z")
How quickly are you able to load from Oracle if you e.g. just write data directly to file instead? This should give an indication of the extraction speed from Oracle. ---
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 19, 2019, 10:21pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/10 "2019-08-19T22:21:13Z")
Yes, tested just now. If I extract the data trough a sql developer (which uses jdbc driver) it takes 7 mins for 1GB. I think the bottleneck is jdbc driver. Is using jdbc driver for oracle database the right way or we have some other method? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [August 20, 2019, 5:46am UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/11 "2019-08-20T05:46:13Z")
If you can partition the query you might be able to run several jdbc input plugins in parallel, which might improve performance. It could also be that it is the database and not JDBC that is the limiting factor. If you have any faster way to get the results of the query out to disk you could try using a file input plugin instead together with this and see if that speeds things up. ---
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 20, 2019, 4:02pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/12 "2019-08-20T16:02:24Z")
Yes Yes. Let me try that. Thank You Christian. ---
**Author:** ![abygin.martin](https://avatars.discourse-cdn.com/v4/letter/a/e36b37/32.png) [@abygin.martin](https://discuss.elastic.co/u/abygin.martin)\ **Post date:** [August 20, 2019, 4:18pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/13 "2019-08-20T16:18:35Z")
Christian, I exported a huge amount of data to csv and then uploaded to elasticsearch. The performance is acceptable. The export to csv was taking long time though. Are you aware of any other way in which we can load data from oracle database to elasticsearch? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 17, 2019, 4:18pm UTC](https://discuss.elastic.co/t/tuning-logstash-and-elasticsearch-for-loading-data-from-oracle-database/195795/14 "2019-09-17T16:18:45Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.