# Data loading into ES index is very slow **URL:** \ **Category:** Logstash\ **Created:** [December 15, 2016, 12:34pm UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164 "2016-12-15T12:34:27Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [December 15, 2016, 12:34pm UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/1 "2016-12-15T12:34:27Z")
HI, I am trying to load data from Mysql to elasticsearch using logstash, but its loading very slow. 6500/minute into elasticsearch. The following is my Logstash config input { jdbc { type =\> "type" jdbc\_driver\_library =\> "/path/mysql-connector-java-5.1.28.jar" jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" jdbc\_connection\_string =\> "jdbc:mysql://IP:Port/DB" jdbc\_user =\> "user" jdbc\_password =\> "password" statement =\> "SELECT \* from tablename WHERE id \> :sql\_last\_value" schedule =\> "\* \* \* \* \*" use\_column\_value =\> true tracking\_column =\> id clean\_run =\> true last\_run\_metadata\_path =\> "/var/lib/logstash/.logstash\_jdbc\_last\_run" } } filter { if [type] == "type" { mutate { gsub =\> ["message", "\n", ""] } mutate { gsub =\> ["message", "\r", ""] } mutate { gsub =\> ["message", "\t", ""] } mutate { gsub =\> ["message", "=", ""] } grok { break\_on\_match =\> false match =\> ["Id", "%{BASE10NUM:id}"] match =\> ["logdata", "%{GREEDYDATA:Record}"] } if ("type\*" or "lb\*" in [Record]) { grok { match =\> [ "Record", "%{WORD:VM1} %{WORD:VM} %{TIMESTAMP\_ISO8601:Timestamp} [%{DATA:Thread\_Id}] %{WORD:Log\_Level} %{DATA:Error\_Source} - %{GREEDYDATA:Error\_Description}" ] } } if "\_grokparsefailure" in [tags] { grok { match =\> [ "Record", "%{TIMESTAMP\_ISO8601:Timestamp} [%{DATA:Thread\_Id}] %{WORD:Log\_Level} %{DATA:Error\_Source} - %{GREEDYDATA:Error\_Description}" ] } } mutate { rename =\> { "logdata" =\> "Record" } } mutate { add\_field =\> { "Error\_Source\_Analyzed" =\> "%{Error\_Source}" "Error\_Description\_Analyzed" =\> "%{Error\_Description}" } remove\_field =\> ["VM1","tags"] } date { match =\> ["Timestamp" , "yyyy-MM-dd HH:mm:ss,SSS"] } } } output { if [type] == "type" { stdout { codec =\> rubydebug } ``` elasticsearch { hosts => "IP:port" index => "Index_Name" document_id => "%{id}" } } ``` } My system hardware config is 16 gb RAM, 250 GB Hrd disk with 4 cpu/8-core I tried with changing LS\_HEAP/ES\_HEAP Can any body help with this. Thanks in advance Uday.K ---
**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:** [December 16, 2016, 1:14am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/2 "2016-12-16T01:14:22Z")
How are you measuring things? ---
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [December 16, 2016, 10:01am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/3 "2016-12-16T10:01:14Z")
Hi Warklom, Thanks for your reply, I gave limit 10K to 50K in sql select statement and checking the time to load the data into ES manually. Even after removing filter from logstash configuration, its loading 15to16K per minute. If I have data inflow like 250K per minute, what kind of setup/configuration is required. And one more info is its single node VM, all ELK and Mysql are installed in same. Is this because of many applications are running and hardware configuration is not good enough ? ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [December 20, 2016, 9:11pm UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/4 "2016-12-20T21:11:01Z")
> If I have data inflow like 250K per minute, what kind of setup/configuration is required. That depends on a lot of factors, including the kinds of documents you want to index. > And one more info is its single node VM, all ELK and Mysql are installed in same. Is this because of many applications are running and hardware configuration is not good enough ? That's definitely a possibility. Are you saturating the CPUs? Are you seeing a lot of I/O wait? What's the current bottleneck? Measure! ---
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [December 22, 2016, 9:21am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/5 "2016-12-22T09:21:41Z")
Hi Magnus, Thanks for your reply, yes cpu usage is more than 100%(like 130-160), when I run logstash config. I/O wait is fluctuating like 0.0, 0.1, 2.9, 1.6. most of the time it is 0.0 and 0.1. My current bottleneck is 15k/minute with out any filter in logstash config. How to improve logstash performance. input { jdbc { type =\> "log" jdbc\_driver\_library =\> "/path/mysql-connector-java-5.1.28.jar" jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" jdbc\_connection\_string =\> "jdbc:mysql://IP:3306/DB" jdbc\_user =\> "root" jdbc\_password =\> "root" statement =\> "SELECT \* from table limit 15000" schedule =\> "\* \* \* \* \*" use\_column\_value =\> true tracking\_column =\> id clean\_run =\> true last\_run\_metadata\_path =\> "/var/lib/logstash/.logstash\_jdbc\_last\_run" } } output { elasticsearch { hosts =\> "IP:9200" workers =\> 60 document\_id =\> "%{id}" index =\> "per\_rca\_qns" flush\_size =\> 10000 } } The following are cpu and memory stats. **CPU** **Before logstash starts** ![](https://us1.discourse-cdn.com/elastic/original/2X/9/9296b48b57119162aa07b4674739bc7b1e1a5d99.png) **After logsatsh starts** ![](https://us1.discourse-cdn.com/elastic/original/2X/0/0186313c348d0aade936d877dd0cf36b295a0588.png) **Memory** **Before logstash starts** ![](https://us1.discourse-cdn.com/elastic/original/2X/5/544764dd50a264e30f876ecc6e576abbb84f5ccb.png) **After logsatsh starts** ![](https://us1.discourse-cdn.com/elastic/original/2X/b/bedb852efcd997a1bf7c5a439e892216fd1a9bfb.png) ---
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [January 9, 2017, 5:23am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/6 "2017-01-09T05:23:00Z")
Hi, any update regarding this. Thanks, Uday.K ---
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [January 18, 2017, 2:54pm UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/7 "2017-01-18T14:54:13Z")
Hi Magnus, Based on above screenshots, can you tell me what can I do to improve logstash performance. Thanks, Uday.K ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [January 19, 2017, 6:42am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/8 "2017-01-19T06:42:24Z")
I'm not sure where the bottleneck is. The CPUs are not quite saturated but still have a sizable load so increasing concurrency won't help much. ---
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [January 19, 2017, 7:33am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/9 "2017-01-19T07:33:54Z")
Thanks for reply, Can you suggest me some logstash or elasticsearch performance improvement tips. I will try to implement and figure it out the issues. Thanks, Uday.K ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [January 19, 2017, 7:35am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/10 "2017-01-19T07:35:30Z")
As I don't know where the bottleneck is I have no concrete improvement suggestions. ---
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [January 19, 2017, 7:45am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/11 "2017-01-19T07:45:31Z")
if I need to check any other system parameters and any other things from elasticsearch and logstash end. please let me know. ---
**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:** [January 19, 2017, 7:54am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/12 "2017-01-19T07:54:38Z")
Having all components on a single VM can make it difficult to troubleshoot performance issues as they all affect each other. If you can deploy the different components on separate VMs it may be easier to get assistance in pinpointing the bottleneck and optimising the pipeline. ---
**Author:** ![udaykirankona](https://avatars.discourse-cdn.com/v4/letter/u/ac91a4/32.png) [@udaykirankona](https://discuss.elastic.co/u/udaykirankona)\ **Post date:** [January 19, 2017, 9:17am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/13 "2017-01-19T09:17:47Z")
Thanks for reply Christian. I will check that. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 16, 2017, 9:18am UTC](https://discuss.elastic.co/t/data-loading-into-es-index-is-very-slow/69164/14 "2017-02-16T09:18:09Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.