Diagnosing Common Database Performance Hotspots in our Java Code

Diagnosing Common Database Performance Hotspots in our Java Code:

For this article I focus on the database as I am sure all of your apps are suffering from one of these access patterns! You can use pretty much any profiling, tracing or APM tool available in the market, but I am using the free Dynatrace Personal License. Java also comes with great tools such as Java Mission Control. Many frameworks that access data – such as Hibernate or Spring – also offer diagnostics options typically through logging output.

Using these tracing tools doesn’t require any code changes as they all leverage JVMTI (JVM Tooling Interface) to capture code level information and even to trace calls across remoting tiers. This is very useful in distributed, (micro)service-oriented applications; just modify your startup command line options for your JVM to get the tools loaded. Some tool vendors provide IDE integration where you can simply say “run with XYZ Profiling turned on”. I have a shortYouTube tutorial demonstrating how to trace an app launched from Eclipse!

Identify Database Performance Hotspots

When it turns out that the database is the main contributor to the overall response time of requests to your application, be careful about blaming the database and finger pointing at the DBAs! There might be several reasons that would cause the database to be that busy:

• Inefficient use of the database: wrong query design, poor application logic, incorrect configuration of data access framework
• Poor design and data structure in the database: table relations, slow stored views, missing or wrong indexes, outdated table statistics
• Inappropriate database configuration: memory, disk, tablespaces, connection pools

In this article I mainly want to focus on what you can do from the application side to minimize the time spent in the database:

Diagnose Bad Database Access Patterns

When diagnosing applications I have several database access patterns I always check for. I look at individual requests and put them into the following DB Problem Pattern categories:

• Excessive SQLs: Executing a lot (> 500) different SQL Statements
• N+1 Query Problem: Executing the same SQL statement multiple times (>20):
• Slow Single SQL Issue: Executing a single SQL that contributes > 80% of response time
• Data-Driven Issue: Same request executes different SQL depending on input parameters
• Database Heavy: Database Contribution Time is > 60% of overall response time
• Unprepared Statements: Executing the same SQL without preparing the statement
• Pool Exhaustion: Impacted by High Connection Acquisition Time (getConnection time > executeStatement)
• Inefficient Pool Access: Excessive access to connection pool (calling getConnection > 50% of executeStatement count)
• Overloaded Database Server: Database server is simply overloaded with too many requests from different apps