So I am logging lots of requests coming into my server.
However, in order to identify some details about the user making the request, I am matching the request IP and request time to my radius db, and fetching some results to add them to the log.
I am using jdbc_streaming to fetch the results as following:
filter {
if "tcplog" in [tags] {
### PARSE CSV ###
csv {
columns => [
"reqdate",
"ppoeip",
"userip",
"protocol",
"logserverip",
"destinationip",
"remove1",
"remove2",
"sourceport",
"destinationport",
"description-url"
]
separator => ";"
remove_field => ["remove1", "remove2"]
}
### END CSV PARSING ###
### CHANGE TIMEZONE TO A VALID ONE ###
mutate { gsub => [ "reqdate", "EEST", "Europe/Moscow" ] }
### END TIMEZONE ###
### CONVERT DATE TO DATETIME ###
date {
match => [ "reqdate", "MMM dd, yyyy HH:mm:ss.SSSSSSSSS ZZZ"]
target => "reqdate"
}
### END CONVERT DATE ###
### GET MYSQL ###
jdbc_streaming {
jdbc_driver_library => "/etc/logstash/mysql-connector-java-5.1.46/mysql-connector-java-5.1.46-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/ispradius"
jdbc_user => "user"
jdbc_password => "password"
statement => "SELECT User.Id as UserId, User.UserName, User.FirstName, User.LastName, User.Mobile, User.Phone, User.MailAddress, User.Address FROM ispradius.User WHERE User.Id = ( Select UserId From UserNasSession WHERE ( (StartSession <= :REQDATE AND EndSession >= :REQDATE ) OR (StartSession <= :REQDATE AND EndSession IS NULL) ) And IpAddress = :UIPADDR);"
parameters => {
"UIPADDR" => "userip"
"REQDATE" => "reqdate"
}
## parameters => { "UIPADDR" => "userip"}
target => "user_details"
}
### END MYSQL ###
}
}
This works well. Except, It's too slow!.
From my point of view, I believe the bottleneck is the mysql. I think jdbc is failing to cache because my query contains the request timestamp. which is pretty much never repeats.
Is there any advice on how to improve this?
On another note: does jdbc_streaming open / close connection for every log attempt? this could be losing time as well.
Thank you