# Migrating 3 millions of records from RDBMS to Elastic Search using logstash **URL:** \ **Category:** Logstash\ **Created:** [July 8, 2020, 8:08pm UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427 "2020-07-08T20:08:46Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![rao7806](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rao7806/32/48333_2.png) [@rao7806](https://discuss.elastic.co/u/rao7806)\ **Post date:** [July 8, 2020, 8:08pm UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/1 "2020-07-08T20:08:46Z")
Hi All, We are trying to migrate around 3 million records from oracle to Elastic Search using Logstash. We are applying a couple of jdbc\_streaming filters as a part of our logstash script, one to load connecting nested objects and another to run a hierarchical query to load data to another nested object in the index. We are able to index 0.4 million records in 24 hours. The total size occupied by .4 million records is around 300MB. We tried multiple approaches to migrate data quickly into elastic from oracle but were not able to achieve desired results. Please find below the approaches we tried : 1.In the logstash script, we used jdbc\_fetch\_size, jdbc\_page\_size, jdbc\_paging\_enabled, clean\_run parameters, set pipeline workers to 20 and pipeline batch size to 125 in logstash.yml file. 2. On the elastic side, we set the number of replicas to 0, refresh interval to -1, tried increasing the value of indices.memory.index\_buffer\_size parameter, increased number of watcher queues in the elastic.yml file. We basically googled out and followed various suggestions from this site and others too but nothing seems to work out so far. We are using a single node elastic setup and neither the DB nor the elastic node are present on the machine from which we are running the logstash script. Please find below the logstash config file input { jdbc { jdbc\_driver\_library =\> "LIB" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "connection url" jdbc\_user =\> "user" jdbc\_password =\> "pwd" statement =\> "select \* from " } } filter{ jdbc\_streaming { jdbc\_driver\_library =\> "LIB" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "connection url" jdbc\_user =\> "user" jdbc\_password =\> "pwd" #statement =\> "select claimnumber,claimtype,is\_active from claim where policynumber = :policynumber" parameters =\> {"policynumber" =\> "policynumber"} target =\> "nested node" } stdout { codec =\> json } } filter{ jdbc\_streaming { jdbc\_driver\_library =\> "LIB" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "connection url" jdbc\_user =\> "user" jdbc\_password =\> "pwd" statement =\> "select listagg(column name,'/' ) within group(order by column name) from where LEVEL \> 1 start with =: connect by prior = " parameters =\> {"p1" =\> "p1"} target =\> "nested node1" } } output { elasticsearch { hosts =\> [""] index =\> "\" document\_id =\> "%{doc\_id}" } } Can you please help us identify bottlenecks and also make suggestions on how to increase indexing performance. Thank You ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [July 8, 2020, 10:18pm UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/2 "2020-07-08T22:18:01Z")
Rather than using the two filters, what about making a view on the database that provides you with the end structure you need and then just using that in the input? ---
**Author:** ![rao7806](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rao7806/32/48333_2.png) [@rao7806](https://discuss.elastic.co/u/rao7806)\ **Post date:** [July 9, 2020, 5:37am UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/3 "2020-07-09T05:37:58Z")
Thanks, @warkolm. We are representing a one to many relationships with one of the nested objects created using filters. We would be eliminating one filter by creating views. We would try this and get back. Meanwhile, If there are any more suggestions, please let us know. ---
**Author:** ![rao7806](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rao7806/32/48333_2.png) [@rao7806](https://discuss.elastic.co/u/rao7806)\ **Post date:** [July 16, 2020, 8:46am UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/4 "2020-07-16T08:46:17Z")
@warkolm, We tried loading oracle table data directly to elastic without any transformations and the performance is same. It is still taking around a day to load around a million records. Will there be any other settings to speed up the indexing process. ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [July 16, 2020, 10:40pm UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/5 "2020-07-16T22:40:44Z")
Not sure sorry, hopefully someone else will be able to assist. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 17, 2020, 1:59am UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/6 "2020-07-17T01:59:35Z")
You need to identify where the bottleneck is. Start off by just running your jdbc input with a sink output. Then add one filter, then add the other. Then add something like ``` output { stdout { codec => dots } } ``` Then try sending the data to elasticsearch. If the throughput massively drops when you start sending to data to elasticsearch the bottleneck may be in ES rather than logstash. ---
**Author:** ![rao7806](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rao7806/32/48333_2.png) [@rao7806](https://discuss.elastic.co/u/rao7806)\ **Post date:** [July 17, 2020, 2:52pm UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/7 "2020-07-17T14:52:39Z")
No problem 🙂 Thanks for your inputs @warkolm ---
**Author:** ![rao7806](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rao7806/32/48333_2.png) [@rao7806](https://discuss.elastic.co/u/rao7806)\ **Post date:** [July 17, 2020, 3:27pm UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/8 "2020-07-17T15:27:33Z")
@Badger, I tired running the script by removing elastic search output tags and tried the given output tags. I see messages displayed on the console that data is being fetched from DB in batches of 500 until 5000 after which no messages are displayed on to the console ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 14, 2020, 3:27pm UTC](https://discuss.elastic.co/t/migrating-3-millions-of-records-from-rdbms-to-elastic-search-using-logstash/240427/9 "2020-08-14T15:27:36Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.