CODEWITHSUNDEEP
javamysqlhibernatejdbc
zx_wing
zx_wing

Reputation: 1966

Mysql jconnector spends 50% time in com.myql.jdbc.utils.ReadAheadInputStream.fill()

I am profiling my application which uses Spring, Hibernate, and mysql-java-connector. The VisualVM shows that more than 50% of CPU time is spent in com.myql.jdbc.utils.ReadAheadInputStream.fill() method when there are 1000 parallel connections doing read.

Is there any optimization to make it faster?

Upvotes: 12

Views: 2892

Answers (3)

Steve McKay
Steve McKay

Reputation: 2223

VisualVM counts a thread as using CPU time whenever the JVM thinks it's runnable. This means that any thread not waiting on a lock is considered runnable, more or less, including threads waiting for I/O in the kernel! This is where the large amount of CPU usage in com.myql.jdbc.utils.ReadAheadInputStream.fill() is coming from. So instead of a CPU problem you have an I/O problem.

There are some things you can do on the JVM side, but not a lot of straightforward optimizing:

  1. Tweak the connection pool size. 1,000 concurrent queries is a lot. Unless your MySQL instance is truly massive it's going to have trouble handling that level of load, and eat up a lot of time just switching between queries. Try dropping the pool size, to 250 or even 50, and benchmark there.
  2. Do fewer or smaller queries. If your app is small it might be trivially obvious that every row from every query is necessary, but maybe your app is bigger than that. Are the different places that query the same data, or can two different queries be combined into one that will satisfy both?

Upvotes: 3

Sanne
Sanne

Reputation: 6107

On top of the other suggestions, consider also experimenting with a much lower amount of connections (i.e. 20). It's very possible that the overhead of handling such a large amount of open connections is slightly fooling your profiling observations.

Not least, make sure you're using a recent version of Hibernate ORM. We made version 5.0+ much smarter than previous versions, especially regarding performance improvements ;-) Improvements are applied daily, so keeping up to date or at least trying the latest might be an easy win.

Upvotes: 2

Denis Bazhenov
Denis Bazhenov

Reputation: 9945

It's hard to answer your question without additional information. Here some information needs that should be fulfilled before.

  1. is it your estimate of CPU time absolute or relative? If fill() method uses half of CPU time available to system it seems strange. But if this number was get using VisualVM which reports usage time relative to time spent in application, it just may be the rest of your application is not doing significant work?
  2. Do these profiling measurements confirmed using system level tools? You can use pidstat, mpstat and sar to crosscheck if you on a Linux. I've seen VisualVM marked time spent in SocketInputStream.socketRead0() method as a CPU time, which was not confirmed by pidstat. I guess it's consequences of some measurement approximations in VisualVM itself or JVM behavior. So it's always good idea to crosscheck using OS tools.

Upvotes: 0

Related Questions