JXInsight / JDBInsight provides unique
and powerful timeline analysis that can help
a performance engineer detect whether performance
slow downs occur under a certain level of concurrency
for particular transactions patterns or SQL
statements. It is also possible to correlate
high maximums for both transactions and SQL
statement with JVM events occurring within the
servers JVM such as garbage collection, waiting
and blocking.
There are many benefits in capturing a timeline
analysis under normal production workloads but
here we will focus on database / SQL concurrency
analysis with a real world customer example.
In this customer case study a single use case,
made up of various user and resource transactions,
was executed with varying number of threads.
In test
[A] 1 thread worker was used to perform 300 executions
of the use case. In test
[B] 5 thread workers were used
each performing 60 executions concurrently.
Test case
[C] had
10 threads and test
[D] had
15 threads.
Each use case performed a number of SELECT (color
= yellow) statements against various reference
data tables with a SELECT & INSERT (color
= green) statement performed on the main table
(configuration item) and SELECT and UPDATE (color
= blue) statements performed on a sequence/audit
table.
With test
[A] we can see that
the INSERT statements on the main table appear
on the radar though with relatively small execution
times. The SELECT statements that do register
relate to the main table.
With test
[B] we can see that
the UPDATE statements on the sequence/audit
table appear on the radar with transaction and
SQL times growing. The SELECT statements appearing
more prominently are for relatively simple selections
on the sequence/audit table and not the main
table. All SQL statements on the main table
showed little growth in execution times. The
database transaction and SQL concurrency charts
above the timeline graphic show growth (intensity
of color and length of graphic bars).
With test
[C] we see
more and more growth in the length of the UPDATE
operations on the sequence/audit table and transaction
COMMIT operations (color = grey). The SELECT
statements appearing prominently in the graphic
all relate to the simple SELECT on the sequence/audit
table.
With test
[D] the growth rate
seems to have diminished. After further investigation
it was determined that the application server
had been configured to throttle the request
traffic with a thread pool size of 10.
Conclusion
A singular focus on tuning of complex SQL statements
via EXPLAIN plans and timing under unrealistic
workloads (single user) can overlook contention
issues as of result of database design, SQL
statements, execution patterns and concurrency
levels.
William Louth, JXInsight's Product Architect
