# Terribly long time to load data from MySQL **URL:** \ **Category:** Logstash\ **Created:** [September 14, 2018, 1:19pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627 "2018-09-14T13:19:25Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Gosforth](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@Gosforth](https://discuss.elastic.co/u/Gosforth)\ **Post date:** [September 14, 2018, 1:19pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/1 "2018-09-14T13:19:26Z")
It takes two seconds to display data from view in MySQL. But it takes hour (I still wait so I cannot tell it will succeed) logstash starts to load data from this view (since running the command logstash -f config) Konfig: ``` input { jdbc { jdbc_driver_library => "mysql-connector-java-5.1.47-bin.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://localhost:3306/data" jdbc_user => "someuser" jdbc_password => "somepass" statement => "SELECT * FROM some_view" } } filter{ date { match => ["Order_Date", "yyyy-MM-dd"] target => "Order_Date" locale => "en" } mutate { rename => { "gps_long" => "[location][lon]" "gps_lat" => "[location][lat]" } } } output{ elasticsearch { hosts => ["localhost:9200"] index => "some_index" } stdout {} } ``` ... Any trick it works in expected manner? ---
**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:** [September 14, 2018, 1:30pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/2 "2018-09-14T13:30:28Z")
How many rows need to be processed? ---
**Author:** ![Gosforth](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@Gosforth](https://discuss.elastic.co/u/Gosforth)\ **Post date:** [September 14, 2018, 1:35pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/3 "2018-09-14T13:35:10Z")
Only 582 000 thousands. Not many. Config is working with smaller no of rows - tested. ---
**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:** [September 14, 2018, 2:03pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/4 "2018-09-14T14:03:02Z")
Okay, so that's pretty slow. Perhaps ES is underprovisioned? Or what's the bottleneck? ES is fairly memory hungry and will provide very slow performance if I/O is slow. ---
**Author:** ![Gosforth](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@Gosforth](https://discuss.elastic.co/u/Gosforth)\ **Post date:** [September 14, 2018, 2:09pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/5 "2018-09-14T14:09:10Z")
This is logstash problem (or JDBC). I see that logstash does not event created index during this time. Test PC I'm running it on has 16GB RAM and i7 processor. Enough. Looks like jdbc\_fetch\_size and jdbc\_page\_size do not work. Maybe this JDBC version is outdated? I tried latest one but it does not work at all. ---
**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:** [September 14, 2018, 7:59pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/6 "2018-09-14T19:59:35Z")
> I see that logstash does not event created index during this time. There's no index being created in Elasticsearch? That would indicate a problem with the connection to ES. Have you looked in the Logstash log for clues? ---
**Author:** ![Gosforth](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@Gosforth](https://discuss.elastic.co/u/Gosforth)\ **Post date:** [September 17, 2018, 6:28pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/8 "2018-09-17T18:28:56Z")
It creates but after long time. What logstash is doing? How it's working? I loads all records into memory and gives some format to data before it transfers them to Elasticsearch? It stopes from time to time and system is busy working on something... ---
**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:** [September 17, 2018, 8:08pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/9 "2018-09-17T20:08:25Z")
Apart from the log (you should consider increasing its verbosity), Logstash's monitoring API could lend clues into what's going on. ---
**Author:** ![Gosforth](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@Gosforth](https://discuss.elastic.co/u/Gosforth)\ **Post date:** [September 17, 2018, 8:15pm UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/10 "2018-09-17T20:15:44Z")
How to do that? Some switch in 'logstash' command? Or maybe, better, in config file? ---
**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:** [September 18, 2018, 6:33am UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/11 "2018-09-18T06:33:32Z")
The log level can be configured either via logstash.yml or a command line option. See the documentation for details. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 16, 2018, 6:33am UTC](https://discuss.elastic.co/t/terribly-long-time-to-load-data-from-mysql/148627/12 "2018-10-16T06:33:39Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.