Look at the below screenshot, you can see that while querying a table with just 146 records it's taking 6.2 seconds to execute.
Now, take a look at my configuration files for logstash:
base.conf:
input {
jdbc {
jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.22.jar"
jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://mysql_8:3306/pcs_accounts_db"
jdbc_user => "pcs_db_user"
jdbc_password => "laravel_db"
sql_log_level => "debug"
clean_run => true
record_last_run => false
type => "txn"
statement => "SELECT * FROM ac_transaction_dump"
}
jdbc {
jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.22.jar"
jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://mysql_8:3306/pcs_accounts_db"
jdbc_user => "pcs_db_user"
jdbc_password => "laravel_db"
sql_log_level => "debug"
clean_run => true
record_last_run => false
type => "trial"
statement => "SELECT * FROM ac_daily_trial_balance"
}
}
filter {
mutate {
remove_field => ["@version", "@timestamp"]
}
}
output {
stdout { codec => rubydebug { metadata => true } }
if [type] == "txn" {
elasticsearch {
hosts => ["http://elasticsearch:9200"]
data_stream => "false"
index => "ac_transaction_dump"
document_id => "%{transaction_dump_id}"
}
}
if [type] == "trial" {
elasticsearch {
hosts => ["http://elasticsearch:9200"]
data_stream => "false"
index => "ac_daily_trial_balance"
document_id => "%{daily_trial_balance_id}"
}
}
}
change.conf:
input {
jdbc {
jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.22.jar"
jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://mysql_8:3306/pcs_accounts_db"
jdbc_user => "pcs_db_user"
jdbc_password => "laravel_db"
type => "txn"
use_column_value => true
tracking_column => 'transaction_dump_id'
last_run_metadata_path => "/usr/share/logstash/.logstash_jdbc_last_run_a'"
sql_log_level => "debug"
schedule => "*/5 * * * * *"
statement => "
SELECT *
FROM ac_transaction_dump
WHERE (created_at > :sql_last_value)
OR (updated_at > :sql_last_value);
"
}
jdbc {
jdbc_driver_library => "/usr/share/logstash/mysql-connector-java-8.0.22.jar"
jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://mysql_8:3306/pcs_accounts_db"
jdbc_user => "pcs_db_user"
jdbc_password => "laravel_db"
type => "trial"
use_column_value => true
tracking_column => 'daily_trial_balance_id'
last_run_metadata_path => "/usr/share/logstash/.logstash_jdbc_last_run_b"
sql_log_level => "debug"
schedule => "*/5 * * * * *"
statement => "
SELECT *
FROM ac_daily_trial_balance
WHERE (created_at > :sql_last_value)
OR (updated_at > :sql_last_value);
"
}
}
filter {
if [deleted_at] {
mutate {
add_field => { "[@metadata][action]" => "delete" }
}
}
mutate {
remove_field => ["@version", "@timestamp"]
}
}
# stdout { codec => rubydebug { metadata => true } }
output {
if [type] == "txn" {
if [@metadata][action] == "delete" {
elasticsearch {
hosts => ["http://elasticsearch:9200"]
index => "ac_transaction_dump"
action => "delete"
document_id => "%{transaction_dump_id}"
}
}
else {
elasticsearch {
hosts => ["http://elasticsearch:9200"]
index => "ac_transaction_dump"
document_id => "%{transaction_dump_id}"
}
}
}
if [type] == "trial" {
if [@metadata][action] == "delete" {
elasticsearch {
hosts => ["http://elasticsearch:9200"]
index => "ac_daily_trial_balance"
action => "delete"
document_id => "%{daily_trial_balance_id}"
}
}
else {
elasticsearch {
hosts => ["http://elasticsearch:9200"]
index => "ac_daily_trial_balance"
document_id => "%{daily_trial_balance_id}"
}
}
}
}
I have two tables in the database, one is "ac_transaction_dump" with 204 records and another one is "ac_daily_trial_balance" with 146 records.
It would help me a lot if anyone can suggest me the changes needed for the improvement. And also why it's taking too much time with just a few records ?
