dbQueryWatch: shift-lefting the detection of slow queries

Contents

• 1. What is it?
• 2. Getting started
  • 2.1. Prerequisites
    • Application Framework
    • Persistence Framework / Library
    • Database
    • Database Schema & Indexes
  • 2.2. Adding the library to your project
  • 2.3. Enabling the detection
    • 2.3.1. MockMvc
    • 2.3.2. Real Servlet Environment
  • 2.4. Understanding the issues report
  • 2.5. Configuration
    • 2.5.1. Excluding small tables
    • 2.5.2. Specifying the base packages for your persistent layer methods
• 3. Acknowledgements
• 4. Similar projects
• 5. References
• Appendix A: How to enable tracing
  • A.1. Spring Boot 2.7
  • A.2. Spring Boot 3+

1. What is it?

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!

2. Getting started

2.1. Prerequisites

Application Framework

Your application must be based on Spring Boot 2.7 or newer.

Persistence Framework / Library

dbQueryWatch supports any persistence layer that uses Spring-managed datasources including, but not limited to:

• Hibernate/JPA

• jOOQ

• Jdbi

• MyBatis

Database

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+

Database Schema & Indexes

Schema and indexes should be in sync with your production environment.

2.2. Adding the library to your project

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:

pom.xml

    <dependency>
        <groupId>org.dbquerywatch</groupId>
        <artifactId>dbquerywatch</artifactId>
        <version>1.1.0</version>
        <scope>test</scope>
    </dependency>

For Gradle (Kotlin):

build.gradle.kts

    testImplementation("org.dbquerywatch", "dbquerywatch", "1.1.0")

2.3. Enabling the detection

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.

2.3.1. MockMvc

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;

    // ...
}

1. Enables the slow query detector

2.3.2. Real Servlet Environment

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;

    // ...
}

1. Enables the slow query detector

2.4. Understanding the issues report

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://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.

2.5. Configuration

You can tweak the operation of the analyzer through a couple of spring properties.

2.5.1. Excluding small tables

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.

2.5.2. Specifying the base packages for your persistent layer methods

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

3. Acknowledgements

• Tadaya Tsuyukubo for creating datasource-proxy

• Arnold Galovics, for his article Configuring A Datasource-Proxy In Spring Boot

• Testcontainers

4. Similar projects

• DBadvisor

5. References

1. The psychology of web performance

2. The Real Cost of Slow Time vs Downtime

3. Not Many Indexes, but the Right Ones

4. Use The Index, Luke

5. Over-Indexing

Appendix A: How to enable tracing

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.1. Spring Boot 2.7

A minimal configuration would be:

For a Gradle project:

build.gradle.kts

    testImplementation(platform("org.springframework.cloud:spring-cloud-sleuth-dependencies:3.1.10"))
    testRuntimeOnly("org.springframework.cloud", "spring-cloud-starter-sleuth")
    testRuntimeOnly("org.springframework.cloud", "spring-cloud-sleuth-zipkin")

For a Maven project:

pom.xml

    <dependencies>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-sleuth-dependencies</artifactId>
            <version>3.1.10</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.

A.2. Spring Boot 3+

❗	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:

build.gradle.kts

    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:

pom.xml

    <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.