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.
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>1.2.0</version>
<scope>test</scope>
</dependency>
For Gradle (Kotlin):
testImplementation("org.dbquerywatch", "dbquerywatch", "1.2.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.CatchSlowQueries;
@SpringBootTest
@AutoConfigureMockMvc
@CatchSlowQueries // (1)
class SomeIntegrationTest {
@Autowired MockMvc mvc;
// ...
}
-
Enables the slow query detector
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.CatchSlowQueries;
@SpringBootTest(webEnvironment = WebEnvironment.RANDOM_PORT)
@CatchSlowQueries // (1)
class SomeIntegrationTest {
@Autowired WebTestClient client;
// ...
}
-
Enables the slow query detector
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.SlowQueriesFoundException: Potential slow queries were found! ~~~~~ Query 1/1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DataSource: dataSource (jdbc:postgresql:https://localhost:32920/test?loggerLevel=OFF) SQL: select "public"."articles"."id", "public"."articles"."published_at", "public"."articles"."author_full_name", "public"."articles"."author_last_name", "public"."articles"."title", "public"."articles"."doi", "public"."articles"."journal_id" from "public"."articles" where ("public"."articles"."published_at" >= cast(? as date) and "public"."articles"."published_at" <= cast(? as date)) Execution Plan: [{"JIT":{"Options":{"Expressions":true,"Optimization":true,"Deforming":true,"Inlining":true},"Functions":2},"Plan":{"Total Cost":10000000011.05,"Relation Name":"articles","Parallel Aware":false,"Filter":"((published_at >= '1970-01-01'::date) AND (published_at <= '1980-12-31'::date))","Alias":"articles","Node Type":"Seq Scan","Plan Width":1116,"Startup Cost":10000000000,"Async Capable":false,"Plan Rows":1}}] Issues: - Issue(type=FULL_ACCESS, objectName=articles, predicate=((published_at >= '1970-01-01'::date) AND (published_at <= '1980-12-31'::date))) Caller Methods: - org.dbquerywatch.sample.adapters.db.JooqArticleRepository::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: journals
The 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.