How Plumbr will help you
The screenshot above is taken by Plumbr and is exposing the root cause where many SQL statements were executed during a single user interaction. The specific case above reflects a situation where 20,691 JDBC statements were executed, resulting in three minutes and 39 seconds of added wait time.
The information needed to fix the problem is embedded in the following two blocks. First of these exposes the SQL statements executed in the database:
SELECT * FROM Incident WHERE sessionId = ? AND transactionId = ?
This statement was executed 7,449 times and in total it took one minute and 26 seconds to complete all the roundtrips to the database.
The second SQL statement was executed 5,117 times and it took 43 seconds to complete all the 5,000+ calls
SELECT ap.* FROM AlertPolicy ap ... WHERE ... AND ap.hidden = ? AND ap.type = ?
The block next to SQL statements exposes the call stacks in the source code, zooming in to the exact lines where the statements were executed. To keep overhead at bay, Plumbr has sampled the 20,000+ call stacks and is exposing the information from 38 of such callstacks:
com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery():N/A org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement():688 eu.plumbr.portal.incident.IncidentDao.findIncident():133 eu.plumbr.portal.incident.IncidentDao$$FastClassBySpringCGLIB$$.invoke():N/A org.springframework.cglib.proxy.MethodProxy.invoke():204 ... java.util.concurrent.ThreadPoolExecutor$Worker.run():615 java.lang.Thread.run():745
Considering that the call stacks can be extracted from different locations in source code, Plumbr aggregates those call stacks into a treelike structure. Call stacks occurring most frequently are ranked higher in a tree.
Solving such problems often falls into following either of the paths
- Batching the individual statements together and executing the statements in one go. In such case, the network latency related overhead will be removed and instead of thousands of individual network round trips only one remote call is carried out.
- Reducing the amount of data requested. In situations where the retrieved results are not immediately consumed, the data might not need to be fetched all at once.
In the example above, the solution for us was to batch the calls together and instead of tens of thousands calls we ended up with only two calls reducing the impact transaction from three minutes to under one second.