You may be thinking: Handling slow queries is straightforward: just utilize a monitoring system to generate alerts whenever a slow query is detected in the database. Yes, this approach is effective, but at what cost?
Slow system response frustrates users [1], requiring the engineering team to swiftly identify and resolve the problematic query (or queries).
Approximately 50% of SQL performance issues stem from improper utilization of indexes. Fortunately, this kind of problem could be detected in advance by analyzing the execution plan of all database operations executed by the application.
Some DBMS like PostgreSQL, for example, can reports the estimated total cost of a query. But how you can make use of this key information to establish an upper-bound for DB operations in a particular integration test?
This is where dbQueryWatch comes into play!
dbQueryWatch supports any persistence layer that uses Spring-managed datasources including, but not limited to:
-
Hibernate/JPA
-
jOOQ
-
Jdbi
-
MyBatis
To get more tenable results from the query execution plan analysis, your integration tests should be using a database as production-like as possible. dbQueryWatch supports the most popular relational databases:
-
Oracle 10g+
-
MySQL 5.7+
-
PostgreSQL 9+
The latest version of the library is available from Maven Central repository. Just include it into your test scope/configuration as shown below:
For Maven:
<dependency>
<groupId>org.dbquerywatch</groupId>
<artifactId>dbquerywatch</artifactId>
<version>2.0.0</version>
<scope>test</scope>
</dependency>For Gradle (Kotlin):
testImplementation("org.dbquerywatch", "dbquerywatch", "2.0.0")At its core, dbQueryWatch needs to link each performed database operation with its respective test class. In typical scenarios, this matching works effortlessly without any additional configuration.
Most Spring Boot integration tests are based on MockMvc that operates in a mock servlet environment, allowing test and persistence methods to run on the same thread. In that case, you only need to annotate your integration test classes with @CatchSlowQueries as shown below:
import org.dbquerywatch.api.spring.junit5.DbQueryWatch;
@SpringBootTest
@AutoConfigureMockMvc
@DbQueryWatch(allowSeqScans = false) // (1)
class SomeIntegrationTest {
@Autowired MockMvc mvc;
@Test
@DbQueryWatch(allowSeqScans = false, maxOverallCost = 10000) // (2)
void an_integration_test() {
// ...
}
}-
Enables the detector for all test methods
-
Uses custom settings for an individual test method
Some integration tests uses a real servlet environment that implicitly detaches the execution of test and persistence methods. In such case, dbQueryWatch requires context propagation provided by tracing tools like Spring Sleuth or Micrometer to ensure proper correlation between test classes and database operations. On section How to enable tracing you can find how to easily enable tracing in your project.
dbQueryWatch automatically customize the autowired WebTestClient to include the HTTP tracing headers, compatible with both W3C/OpenTelemetry and Brave tracers.
import org.dbquerywatch.api.spring.junit5.DbQueryWatch;
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@CatchSlowQueries // (1)
@Execution(CONCURRENT) // (2)
class SomeIntegrationTest {
@Test
@DbQueryWatch(allowSeqScans = false, maxOverallCost = 10000) // (3)
void an_integration_test(WebTestClient client) {
// ...
}
}-
Enables the detector for all test methods.
@CatchSlowQueriesis equivalent to@DbQueryWatch(allowSeqScans = false) -
Parallel mode is supported when using
WebEnvironment.RANDOM_PORT+ JUnit injected WebTestClient -
Uses custom settings for an individual test method
dbQueryWatch monitors all database operations executed within the scope of a test class. Once all test methods have been executed, dbQueryWatch analyzes the Execution Plan of each operation, and if one or multiple slow operation were detected, the test class fail. It then prints a issues report to the console with the following details:
SQL |
The SQL statement exactly as it was executed by your persistence layer. |
|---|---|
Execution Plan |
The execution plan as reported by the database. |
Issues |
A list of issues detected by the analysis. |
Caller Methods |
A list of application methods which directly or indirectly executed the database operation. |
A sample report taken from the sample application test class:
org.dbquerywatch.application.domain.service.DatabasePerformanceIssuesDetectedException: Found 2 database performance issues!
## 1/2 SeqScan
- DataSource: 'dataSource (PostgreSQL)'
SQL: "SELECT id, published_at, author_full_name, author_last_name, title FROM articles WHERE published_at >= ? AND published_at <= ?"
ExecutionPlan: '[{"JIT":{"Options":{"Expressions":true,"Optimization":true,"Deforming":true,"Inlining":true},"Functions":4,"Timing":{"Generation":0.181,"Total":19.134,"Emission":9.686,"Optimization":8.021,"Inlining":1.246}},"Planning Time":0.045,"Triggers":[],"Planning":{"Local Written Blocks":0,"Temp Written Blocks":0,"Shared Hit Blocks":0,"Local Hit Blocks":0,"Local Dirtied Blocks":0,"Temp Read Blocks":0,"Local Read Blocks":0,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared Written Blocks":0},"Plan":{"Relation Name":"articles","Temp Written Blocks":0,"Local Read Blocks":0,"Plan Rows":1,"Total Cost":11.05,"Local Written Blocks":0,"Shared Hit Blocks":1,"Startup Cost":0,"Actual Total Time":18.972,"Local Hit Blocks":0,"Plan Width":960,"Local Dirtied Blocks":0,"Temp Read Blocks":0,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Rows Removed by Filter":1,"Parallel Aware":false,"Actual Startup Time":18.967,"Filter":"((published_at >= '1970-01-01'::date) AND (published_at <= '1980-12-31'::date))","Alias":"articles","Actual Loops":1,"Node Type":"Seq Scan","Async Capable":false,"Actual Rows":3,"Shared Written Blocks":0},"Execution Time":19.182}]'
TotalCost: 11
CallerMethods:
- org.dbquerywatch.testapp.adapters.db.DefaultArticleRepository::query
SeqScans:
- objectName: articles
predicate: "((published_at >= '1970-01-01'::date) AND (published_at <= '1980-12-31'::date))"
## 2/2 Excessive Overall Cost
- Actual: 11
Maximum: 5
CriticalStatements:
- DataSource: 'dataSource (PostgreSQL)'
SQL: "SELECT id, published_at, author_full_name, author_last_name, title FROM articles WHERE published_at >= ? AND published_at <= ?"
ExecutionPlan: '[{"JIT":{"Options":{"Expressions":true,"Optimization":true,"Deforming":true,"Inlining":true},"Functions":4,"Timing":{"Generation":0.181,"Total":19.134,"Emission":9.686,"Optimization":8.021,"Inlining":1.246}},"Planning Time":0.045,"Triggers":[],"Planning":{"Local Written Blocks":0,"Temp Written Blocks":0,"Shared Hit Blocks":0,"Local Hit Blocks":0,"Local Dirtied Blocks":0,"Temp Read Blocks":0,"Local Read Blocks":0,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Shared Written Blocks":0},"Plan":{"Relation Name":"articles","Temp Written Blocks":0,"Local Read Blocks":0,"Plan Rows":1,"Total Cost":11.05,"Local Written Blocks":0,"Shared Hit Blocks":1,"Startup Cost":0,"Actual Total Time":18.972,"Local Hit Blocks":0,"Plan Width":960,"Local Dirtied Blocks":0,"Temp Read Blocks":0,"Shared Read Blocks":0,"Shared Dirtied Blocks":0,"Rows Removed by Filter":1,"Parallel Aware":false,"Actual Startup Time":18.967,"Filter":"((published_at >= '1970-01-01'::date) AND (published_at <= '1980-12-31'::date))","Alias":"articles","Actual Loops":1,"Node Type":"Seq Scan","Async Capable":false,"Actual Rows":3,"Shared Written Blocks":0},"Execution Time":19.182}]'
TotalCost: 11
CallerMethods:
- org.dbquerywatch.testapp.adapters.db.DefaultArticleRepository::query
As indicated by the issue’s description, there was a Full Table Scan on table articles due to a missing index over column published_at. Simply adding this index will fix the performance issue for this operation.
You can tweak the operation of the analyzer through a couple of spring properties.
Nearly all business domains have one or more tables that are destined to stay small. You can exclude those tables by setting the dbquerywatch.small-tables property. Example:
dbquerywatch:
small-tables: journalsThe tables name are case-insensitive and can include the schema qualifier, in case of ambiguity.
dbQueryWatch inspects the stacktrace to identify the application-level methods to be listed under the Caller Methods section of the issues report. The library deduce these base packages from your spring configuration, but you may want to customize them using the dbquerywatch.app-base-packages property.
Example: let’s say your application adopts the Hexagonal Architecture, and all persistence methods reside on com.example.application.adapter.db package. In addition, you want to define the com.example.application as a fallback option. Your custom setting would be:
dbquerywatch:
app-base-packages: com.example.application.adapters.db,com.example.application-
Tadaya Tsuyukubo for creating datasource-proxy
-
Arnold Galovics, for his article Configuring A Datasource-Proxy In Spring Boot
In case tracing is still not enabled for your application, you can just enable it for testing purposes.
|
💡
|
To avoid sending actual spans to a Zipkin server, you can just set the property spring.zipkin.enabled=true in your test configuration (at src/test/resources/application.yml, for example).
|
A minimal configuration would be:
For a Gradle project:
testImplementation(platform("org.springframework.cloud:spring-cloud-sleuth-dependencies:3.1.11"))
testRuntimeOnly("org.springframework.cloud", "spring-cloud-starter-sleuth")
testRuntimeOnly("org.springframework.cloud", "spring-cloud-sleuth-zipkin")For a Maven project:
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-sleuth-dependencies</artifactId>
<version>3.1.11</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-sleuth</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-sleuth-zipkin</artifactId>
<scope>test</scope>
</dependency>
</dependencies>For more advanced configurations, please refer to Spring Sleuth “How-to” Guides.
|
❗
|
You must set spring.test.observability.auto-configure=true in your test configuration in order to enable tracing for all your integration tests.
|
For a Gradle project:
testRuntimeOnly("org.springframework.boot", "spring-boot-starter-actuator") // if it's not already included
testRuntimeOnly("io.micrometer", "micrometer-tracing-bridge-brave")
testRuntimeOnly("io.zipkin.reporter2", "zipkin-reporter-brave")For a Maven project:
<dependencies>
<dependency>
<!-- if it's not already included -->
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>io.micrometer</groupId>
<artifactId>micrometer-tracing-bridge-brave</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>io.zipkin.reporter2</groupId>
<artifactId>zipkin-reporter-brave</artifactId>
<scope>test</scope>
</dependency>
</dependencies>For other possible configurations, see the section Tracer Implementations on Spring Boot Reference Documentation.