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

Question

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?

Answer 1

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?

Answer 2

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.