# Import data from DB causes OutOfMemory

**URL:** <https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573>\
**Category:** Logstash\
**Created:** [August 1, 2018, 12:53pm UTC](https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573 "2018-08-01T12:53:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Isa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/isa/32/70526_2.png) [@Isa](https://discuss.elastic.co/u/Isa)\
**Post date:** [August 1, 2018, 12:53pm UTC](https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573/1 "2018-08-01T12:53:00Z")

</div>

Hi

i am trying to import data from a postgreSQL DB however it never completes the imports becuase logstash runs out of Memory. If I limit the data in my select statement the data imports successfully.

Current JVM Arg -Xmx4000m -Xms256m -Xss2048k

The Data is 5G (7 475 233 records).

Logstash Config  
**INPUT:**

```
input {
        beats {
                host => "0.0.0.0"
                port => 10514
                tags => "syslog_index"
        }
        beats {
                host => "0.0.0.0"
                port => 10516
                tags => "metricbeat"
        }
        jdbc {
            # Postgres jdbc connection string to our database
            jdbc_connection_string => "jdbc:postgresql://server:5432/Database"
            # The user we wish to execute our statement as
            jdbc_user => "user"
            jdbc_password => "password"
            # The path to our downloaded jdbc driver
            jdbc_driver_library => "/opt/logstash/postgresql-9.4-1204.jdbc41.jar"
            # The name of the driver class for Postgresql
            jdbc_driver_class => "org.postgresql.Driver"
            last_run_metadata_path => "/opt/logstash/logstash_jdbc_last_run"
            # our query
            statement => "SELECT * FROM table"
            tags => "test_index"
        }
}

```

**OUTPUT:**

```
output {
  if "test_index" in [tags] {
        elasticsearch {
      hosts => ["ES_SERVER:9200"]
      index => "test_index"
      document_type => "table"
      document_id => "%{table_id}"
      sniffing => false
      timeout => "480"
        }
  }
}
```

---

<div class="post-metadata">

**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\
**Post date:** [August 1, 2018, 6:05pm UTC](https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573/2 "2018-08-01T18:05:39Z")

</div>

The trouble here is that the input plugin is attempting to load all 7MM+ documents all at once, which as you have indicated is ~5GB, larger than your configured ~4GB maximum memory to allocate to the JVM running Logstash.

You can window your query using the [`sql_last_value` predefined parameter](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_predefined_parameters), so that the input will run multiple times, each time returning a new "page" that can fit into memory.

Its use depends a bit on the structure of your table. If you have an integer, auto-incrementing key `id`, something like this could work:

```auto
input {
  jdbc {
    # ...
    statement => "SELECT * FROM table WHERE id > :sql_last_value ORDER BY id ASC"
    use_column_value => true
    tracking_column => "id"
  }
  # ...
}

```

---

<div class="post-metadata">

**Author:** ![Isa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/isa/32/70526_2.png) [@Isa](https://discuss.elastic.co/u/Isa)\
**Post date:** [August 1, 2018, 8:36pm UTC](https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573/3 "2018-08-01T20:36:00Z")

</div>

thanks for the tip, however logstash ran OutOfMemory after a bit. Do I also need to set page size/num of row as part of :sql\_last\_value?

---

<div class="post-metadata">

**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\
**Post date:** [August 2, 2018, 4:50pm UTC](https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573/4 "2018-08-02T16:50:24Z")

</div>

d'oh. yes. the whole point was to limit the page size and I failed to include that.

So something more like:

```auto
input {
  jdbc {
    # ...
    statement => "SELECT * FROM table WHERE id > :sql_last_value ORDER BY id ASC LIMIT 10000"
    use_column_value => true
    tracking_column => "id"
  }
  # ...
}

```

---

<div class="post-metadata">

**Author:** ![Isa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/isa/32/70526_2.png) [@Isa](https://discuss.elastic.co/u/Isa)\
**Post date:** [August 6, 2018, 6:32pm UTC](https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573/5 "2018-08-06T18:32:37Z")

</div>

Thanks that worked perfectly 😀

---

<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:** [September 3, 2018, 6:32pm UTC](https://discuss.elastic.co/t/import-data-from-db-causes-outofmemory/142573/6 "2018-09-03T18:32:41Z")

</div>

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