logstash-plugins/logstash-input-jdbc

Issue in importing large table

Opened this issue · 0 comments

I have tried to use logstash input jdbc to import data from postgres to elasticsearch.
My table 1 has 200M rows.
I have made configure file like following according to many suggestions.

input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://postgresqlurl:5432/events?useCursorFetch=true"
jdbc_user => "username"
jdbc_password => "password"
jdbc_validate_connection => true
type => "1"
jdbc_driver_library => "/home/ubuntu/postgresql-42.2.5.jar"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT * from table1"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
jdbc_fetch_size => 50000
clean_run => true
}
jdbc {
jdbc_connection_string => "jdbc:postgresql://postgresql:5432/events?useCursorFetch=true"
jdbc_user => "username"
jdbc_password => "password"
jdbc_validate_connection => true
type => "2"
jdbc_driver_library => "/home/ubuntu/postgresql-42.2.5.jar"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT * from table 2"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
jdbc_fetch_size => 50000
clean_run => true
}
}
output {
if [type] == "1" {
elasticsearch {
index => "customers"
document_type => "customer"
document_id => "%{uid}"
hosts => "http://localhost:9200"
}
} else if [type] == "2" {
elasticsearch {
index => "events"
document_type => "event"
document_id => "%{uid}"
hosts => "http://localhost:9200"
}
}

}

But When I ran logstash config file, first 50000 rows took 10s, but next is 20s, 30s and finally someitme 140s and I should wait 12 hours for 40M rows to index.
Who does have solution in this issue ?