Hi!
I am running Logstash with JDBC plugin postgreSQL, as I check the performance of our server, because I are having some timeout issues, I found out that Logstash when running is having more than 100% CPU usage.
Whenever I start logstash, the CPU usage spikes more than 200%
I already increased the heap size to 8GB
I am using AWS EC2 m5.xlarge which has 4CPUs and 16GB memory and running on ubuntu 18 OS.
I really need help optimizing and configuring Logstash correctly.
Here's my config:
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://localhost/tms?user=postgres&password=xxxxx"
jdbc_user => "postgres"
jdbc_driver_library => ""
jdbc_driver_class => "org.postgresql.Driver"
jdbc_paging_enabled => true
jdbc_page_size => 150000
schedule => "* * * * *"
use_column_value => true
tracking_column => id
statement => "SELECT id, serial_number, title FROM mytms"
clean_run => true
}
}
filter {
jdbc_streaming {
jdbc_connection_string => "jdbc:postgresql://localhost/tms?user=postgres&password=xxxxx"
jdbc_user => "postgres"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT text from tmdesc WHERE serial_number=:serial_number AND LEFT(code, 2) = 'GS'"
parameters => {"serial_number" => "[serial_number]"}
target => "[class]"
}
jdbc_streaming {
jdbc_connection_string => "jdbc:postgresql://localhost/tms?user=postgres&password=xxxxx"
jdbc_user => "postgres"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT code from classifications WHERE serial_number=:serial_number"
parameters => {"serial_number" => "[serial_number]"}
target => "[code]"
}
jdbc_streaming {
jdbc_connection_string => "jdbc:postgresql://localhost/tms?user=postgres&password=xxxxx"
jdbc_user => "postgres"
jdbc_driver_class => "org.postgresql.Driver"
statement => "SELECT name from owners WHERE serial_number=:serial_number AND entry_number = '1' LIMIT 1"
parameters => {"serial_number" => "[serial_number]"}
target => "[owner]"
}
}
output {
elasticsearch {
index => "my_idx"
hosts => "http://localhost:9211"
document_id => "%{id}"
}
}
Here's the hot thread request result
$ curl -XGET 'localhost:9600/_node/hot_threads?human=true'
::: {}
Hot threads at 2021-01-07T09:57:23+00:00, busiestThreads=10:
######################################################################
23.97 % of cpu usage, state: runnable, thread name: '[main]>worker0', thread id: 26
java.base@11.0.8/java.lang.String.equalsIgnoreCase(String.java:1147)
java.base@11.0.8/com.sun.crypto.provider.AESCrypt.init(AESCrypt.java:84)
java.base@11.0.8/com.sun.crypto.provider.GaloisCounterMode.init(GaloisCounterMode.java:303)
java.base@11.0.8/com.sun.crypto.provider.CipherCore.init(CipherCore.java:589)
java.base@11.0.8/com.sun.crypto.provider.AESCipher.engineInit(AESCipher.java:346)
java.base@11.0.8/javax.crypto.Cipher.init(Cipher.java:1431)
java.base@11.0.8/sun.security.ssl.SSLCipher$T13GcmReadCipherGenerator$GcmReadCipher.decrypt(SSLCipher.java:1886)
java.base@11.0.8/sun.security.ssl.SSLSocketInputRecord.decodeInputRecord(SSLSocketInputRecord.java:260)
java.base@11.0.8/sun.security.ssl.SSLSocketInputRecord.decode(SSLSocketInputRecord.java:181)
java.base@11.0.8/sun.security.ssl.SSLTransport.decode(SSLTransport.java:110)
java.base@11.0.8/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1406)
java.base@11.0.8/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1371)
java.base@11.0.8/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:958)
app//org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
app//org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
app//org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
app//org.postgresql.core.PGStream.receiveChar(PGStream.java:321)
app//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1978)
app//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
app//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
app//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
app//org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:311)
app//org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:297)
app//org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274)
app//org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:225)
jdk.internal.reflect.GeneratedMethodAccessor45.invoke(Unknown Source)
java.base@11.0.8/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.base@11.0.8/java.lang.reflect.Method.invoke(Method.java:566)
app//org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(JavaMethod.java:456)
app//org.jruby.javasupport.JavaMethod.invokeDirect(JavaMethod.java:317)
app//org.jruby.java.invokers.InstanceMethodInvoker.call(InstanceMethodInvoker.java:42)
java.base@11.0.8/java.lang.invoke.LambdaForm$DMH/0x000000010072a440.invokeVirtual(LambdaForm$DMH)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x0000000100792c40.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x000000010074a840.guard(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x000000010074a840.guard(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.Invokers$Holder.linkToCallSite(Invokers$Holder)
usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_38_dot_0.lib.sequel.adapters.jdbc.RUBY$block$execute$4(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.38.0/lib/sequel/adapters/jdbc.rb:268)
java.base@11.0.8/java.lang.invoke.DirectMethodHandle$Holder.invokeStatic(DirectMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x000000010138e040.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.Invokers$Holder.invokeExact_MT(Invokers$Holder)
app//org.jruby.runtime.CompiledIRBlockBody.yieldDirect(CompiledIRBlockBody.java:148)
app//org.jruby.runtime.IRBlockBody.yieldSpecific(IRBlockBody.java:66)
app//org.jruby.runtime.Block.yieldSpecific(Block.java:153)
app//org.jruby.ir.targets.YieldSite.yieldSpecific(YieldSite.java:156)
java.base@11.0.8/java.lang.invoke.LambdaForm$DMH/0x0000000100777040.invokeVirtual(LambdaForm$DMH)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x0000000100729040.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x0000000100778040.guard(LambdaForm$MH)
################################################################################
23.89 % of cpu usage, state: runnable, thread name: '[main]>worker2', thread id: 28
java.base@11.0.8/java.net.SocketOutputStream.socketWrite0(Native Method)
java.base@11.0.8/java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:110)
java.base@11.0.8/java.net.SocketOutputStream.write(SocketOutputStream.java:150)
java.base@11.0.8/sun.security.ssl.SSLSocketOutputRecord.deliver(SSLSocketOutputRecord.java:319)
java.base@11.0.8/sun.security.ssl.SSLSocketImpl$AppOutputStream.write(SSLSocketImpl.java:1197)
java.base@11.0.8/java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:81)
java.base@11.0.8/java.io.BufferedOutputStream.flush(BufferedOutputStream.java:142)
app//org.postgresql.core.PGStream.flush(PGStream.java:540)
app//org.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:1393)
app//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:307)
app//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
app//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
app//org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:311)
app//org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:297)
app//org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274)
app//org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:225)
jdk.internal.reflect.GeneratedMethodAccessor45.invoke(Unknown Source)
java.base@11.0.8/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.base@11.0.8/java.lang.reflect.Method.invoke(Method.java:566)
app//org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(JavaMethod.java:456)
app//org.jruby.javasupport.JavaMethod.invokeDirect(JavaMethod.java:317)
app//org.jruby.java.invokers.InstanceMethodInvoker.call(InstanceMethodInvoker.java:42)
java.base@11.0.8/java.lang.invoke.LambdaForm$DMH/0x000000010072a440.invokeVirtual(LambdaForm$DMH)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x0000000100792c40.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x000000010074a840.guard(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x000000010074a840.guard(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.Invokers$Holder.linkToCallSite(Invokers$Holder)
usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_38_dot_0.lib.sequel.adapters.jdbc.RUBY$block$execute$4(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.38.0/lib/sequel/adapters/jdbc.rb:268)
java.base@11.0.8/java.lang.invoke.DirectMethodHandle$Holder.invokeStatic(DirectMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x000000010138e040.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.Invokers$Holder.invokeExact_MT(Invokers$Holder)
app//org.jruby.runtime.CompiledIRBlockBody.yieldDirect(CompiledIRBlockBody.java:148)
app//org.jruby.runtime.IRBlockBody.yieldSpecific(IRBlockBody.java:66)
app//org.jruby.runtime.Block.yieldSpecific(Block.java:153)
app//org.jruby.ir.targets.YieldSite.yieldSpecific(YieldSite.java:156)
java.base@11.0.8/java.lang.invoke.LambdaForm$DMH/0x0000000100777040.invokeVirtual(LambdaForm$DMH)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x0000000100729040.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x0000000100778040.guard(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.Invokers$Holder.linkToCallSite(Invokers$Holder)
usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_38_dot_0.lib.sequel.database.logging.RUBY$method$log_connection_yield$0(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.38.0/lib/sequel/database/logging.rb:38)
java.base@11.0.8/java.lang.invoke.DirectMethodHandle$Holder.invokeStatic(DirectMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x0000000100859840.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x00000001007bf840.guardWithCatch(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x00000001007c0440.invoke(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
java.base@11.0.8/java.lang.invoke.LambdaForm$MH/0x00000001007bbc40.guard(LambdaForm$MH)
java.base@11.0.8/java.lang.invoke.DelegatingMethodHandle$Holder.delegate(DelegatingMethodHandle$Holder)
This is just 1 of 3 configuration that I have to optimize.
I really need help on how to optimize this or need to know what is the right set up on this so that my Logstash won't take that much CPU usage.
