Logstash high CPU on JDBC plugin

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

Screenshot from 2021-01-09 21-56-05

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.

Anyone? :frowning: