How to Monitor and Optimize SQL Performance in Spring Boot with p6spy

1. Introduction

Monitoring and optimizing database operation performance is crucial for improving application efficiency. Many projects implement a feature that prints SQL execution time by recording timestamps before and after each query, allowing precise measurement of each statement's duration.

This helps developers quickly locate performance bottlenecks and provides data for database tuning, such as adjusting query logic, optimizing indexes, or refactoring the schema. It also enables early detection of potential issues during development.

What is p6spy?

P6Spy is an open‑source framework that can intercept and log JDBC activity without modifying existing application code. It includes P6Log, which records all JDBC transactions of any Java application.

2. Practical Example

2.1 Add Dependency

<code>&lt;dependency&gt;
    &lt;groupId&gt;com.github.gavlyukovskiy&lt;/groupId&gt;
    &lt;artifactId&gt;p6spy-spring-boot-starter&lt;/artifactId&gt;
    &lt;version&gt;1.9.1&lt;/version&gt;
&lt;/dependency&gt;</code>

With this dependency the default configuration logs every SQL statement automatically.

2.2 Database Operations

MyBatis Mapper

<code>@Mapper
public interface BigTableMapper {
    @Select("select * from big_table limit #{offset}, 10")
    List&lt;BigTable&gt; query(Integer offset);
}</code>

JPA Repository

<code>public interface BigTableRepository extends JpaRepository&lt;BigTable, Integer&gt; {
    @Query(value = "select * from big_table limit ?1, 10", nativeQuery = true)
    List&lt;BigTable&gt; query(Integer offset);
}</code>

The above operations are independent of p6spy; the tool merely logs them.

Test Code

<code>@Resource
private BigTableMapper bigTableMapper;
@Resource
private BigTableRepository bigTableRepository;

@Test
public void testQueryMyBatis() {
    List&lt;BigTable&gt; ret = this.bigTableMapper.query(4999910);
    System.out.println(ret);
}

@Test
public void testQueryJpa() {
    System.out.println(this.bigTableRepository.query(4999910));
}</code>

Running the tests prints the SQL statements together with their execution times, as shown in the console screenshot below.

Insert operations are also logged:

<code>@Test
public void testSave() {
    BigTable bt = new BigTable();
    bt.setAge(20);
    bt.setName("Custom");
    bt.setPassword("123456");
    bt.setSex("女");
    this.bigTableRepository.saveAndFlush(bt);
}</code>

2.3 p6spy Configuration

The default settings work out‑of‑the‑box, but p6spy offers many configurable options, such as log format, enabling/disabling logging, and slow‑query thresholds.

<code>decorator:
  datasource:
    enabled: true
    datasource-proxy:
      multiline: true
    slow-query:
      threshold: 1   # seconds
      log-level: WARN
      enable-logging: true
      logger-name: SLOW
    p6spy:
      log-format: '%(executionTime)ms|%(category)|connection%(connectionId)|%(sqlSingleLine)'
</code>

You can also direct logs to a file:

<code>decorator:
  datasource:
    p6spy:
      logging: FILE
      log-file: spy.log
</code>

2.4 Custom Extensions

You can implement a custom JdbcEventListener to handle additional events:

<code>@Bean
public JdbcEventListener myListener() {
    return new JdbcEventListener() {
        public void onAfterGetConnection(ConnectionInformation ci, SQLException e) {
            if (e != null) {
                System.err.println(e.getMessage());
            }
            System.out.println("获取连接");
        }
        public void onAfterConnectionClose(ConnectionInformation ci, SQLException e) {
            System.out.println("关闭连接");
        }
    };
}
</code>

For MyBatis you can also create an interceptor, though it may be less accurate:

<code>@Intercepts({@Signature(
    type= StatementHandler.class,
    method = "query",
    args = {Statement.class, ResultHandler.class})})
public class TooktimeInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long start = System.currentTimeMillis();
        Object ret = invocation.proceed();
        long end = System.currentTimeMillis();
        System.err.println("执行耗时：" + (end - start) + "ms");
        return ret;
    }
}
</code>

Both approaches produce similar timing output.

By integrating p6spy and optionally extending it with custom listeners or interceptors, you gain detailed insight into SQL execution times, enabling effective performance tuning for Spring Boot backend applications.