Spring Boot에서 P6spy로 SQL 쿼리 모니터링하기: 완벽 가이드 (JPA와 MySQL 연동)

Spring Boot에서 P6spy로 SQL 쿼리 모니터링하기: 완벽 가이드 (JPA와 MySQL 연동)

 

개발자로서 데이터베이스 쿼리 성능과 정확성은 애플리케이션 품질에 직결되는 핵심 요소입니다.

특히 JPA와 같은 ORM을 사용할 때, 실제로 어떤 SQL 쿼리가 실행되는지 확인하는 것이 디버깅과 성능 최적화에 필수적입니다.

이 글에서는 Spring Boot 애플리케이션에서 P6spy를 활용해 SQL 쿼리를 효과적으로 모니터링하고 로깅하는 방법을 상세히 알아보겠습니다.

---

P6spy란 무엇이며 왜 필요한가?

P6spy는 JDBC 드라이버를 래핑(wrapping)하여 데이터베이스 작업을 모니터링하고 로깅하는 오픈소스 프레임워크입니다.

2001년에 처음 출시된 이후로, 개발자들에게 데이터베이스 상호작용을 투명하게 볼 수 있는 강력한 도구로 자리매김했습니다.

일반적인 애플리케이션 로깅으로는 실제 SQL 쿼리의 파라미터 값이나 실행 시간을 정확히 파악하기 어렵습니다.

특히 JPA나 Hibernate와 같은 ORM 프레임워크를 사용할 때, 프레임워크가 생성하는 실제 SQL을 확인하는 것은 더욱 중요합니다.

P6spy의 주요 특징:

1. 완전한 SQL 쿼리 가시성
   • 실행되는 모든 SQL 문장을 로깅
   • 바인딩된 파라미터 값을 실제 값으로 대체하여 표시
   • 쿼리 실행 시간 측정으로 성능 병목 지점 식별
2. 비침투적 통합
   • 애플리케이션 코드 변경 없이 설정만으로 적용 가능
   • JDBC 드라이버를 프록시하는 방식으로 동작
   • Spring Boot에서는 스타터 의존성으로 쉽게 통합
3. 유연한 커스터마이징
   • SQL 포맷팅 방식 커스터마이징
   • 로깅 출력 대상 설정 (콘솔, 파일 등)
   • 필터링 옵션을 통한 특정 쿼리만 로깅

P6spy는 특히 N+1 쿼리 문제나 불필요한 중복 쿼리와 같은 성능 이슈를 발견하는 데 탁월합니다.

실제 운영 환경보다는 개발 및 테스트 단계에서 활용하면 애플리케이션의 품질을 크게 향상시킬 수 있습니다.

---

 

반응형

Spring Boot 프로젝트에 P6spy 설정하기

데모 메인화면

 

인텔리제이 p6spy 콘솔로그

 

이제 실제로 Spring Boot 프로젝트에 P6spy를 적용하는 방법을 단계별로 알아보겠습니다.

이 예제에서는 Spring Boot 3.3.11, Java 17, MySQL 8.0을 기준으로 설명합니다.

1. 의존성 추가하기

먼저 프로젝트의 build.gradle 파일에 P6spy 스타터 의존성을 추가합니다:

implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.9.0'

 

Maven을 사용하는 경우 pom.xml에 다음과 같이 추가합니다:

<dependency>
    <groupId>com.github.gavlyukovskiy</groupId>
    <artifactId>p6spy-spring-boot-starter</artifactId>
    <version>1.9.0</version>
</dependency>

이 스타터 패키지는 P6spy를 Spring Boot 환경에 쉽게 통합할 수 있도록 자동 설정을 제공합니다.

2. 데이터베이스 연결 설정

application.yml 또는 application.properties 파일에서 데이터베이스 연결 설정을 확인합니다.

P6spy는 JDBC URL을 자동으로 수정하므로 일반적인 방식으로 설정하면 됩니다:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo?useSSL=false&serverTimezone=UTC&characterEncoding=UTF-8
    username: root
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true

# P6spy 로깅 레벨 설정
logging:
  level:
    p6spy: debug

3. P6spy 설정 클래스 생성

P6spy의 로깅 형식을 커스터마이징하기 위해 설정 클래스를 생성합니다:

package com.smsoft.springbootp6spydemo.config;

import com.p6spy.engine.spy.P6SpyOptions;
import jakarta.annotation.PostConstruct;
import org.springframework.context.annotation.Configuration;

@Configuration
public class P6spyConfig {
    
    @PostConstruct
    public void setLogMessageFormat() {
        P6SpyOptions.getActiveInstance().setLogMessageFormat(P6spyPrettySqlFormatter.class.getName());
    }
}

이 설정 클래스는 애플리케이션 시작 시 P6spy의 로그 메시지 포맷을 우리가 정의한 포맷터로 설정합니다.

4. SQL 포맷터 구현하기

다음으로, SQL 쿼리를 보기 좋게 포맷팅하는 클래스를 구현합니다:

package com.smsoft.springbootp6spydemo.config;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.hibernate.engine.jdbc.internal.FormatStyle;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

public class P6spyPrettySqlFormatter implements MessageFormattingStrategy {

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        sql = formatSql(category, sql);
        Date currentDate = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yy.MM.dd HH:mm:ss");
        return format.format(currentDate) + " | OperationTime : " + elapsed + "ms | " + sql;
    }

    private String formatSql(String category, String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return sql;
        }

        // DDL은 format 적용 안함
        if (category.contains("statement") && sql.trim().toLowerCase(Locale.ROOT).startsWith("create")) {
            return sql;
        }

        // Hibernate SQL 포맷 적용
        if (category.equals("statement")) {
            String trimmedSQL = sql.trim().toLowerCase(Locale.ROOT);
            if (trimmedSQL.startsWith("select") || 
                trimmedSQL.startsWith("insert") || 
                trimmedSQL.startsWith("update") || 
                trimmedSQL.startsWith("delete")) {
                sql = FormatStyle.BASIC.getFormatter().format(sql);
                return "\nHeFormatSql(P6Spy sql,Hibernate format):\n" + sql;
            }
        }
        return "\nP6Spy sql:\n" + sql;
    }
}

이 포맷터는 SQL 쿼리를 가독성 있게 표시하며, 실행 시간과 날짜 정보도 함께 로깅합니다.

Hibernate의 FormatStyle을 활용해 SQL 문을 보기 좋게 들여쓰기 합니다.

---

P6spy 활용 예제: 사용자 관리 애플리케이션

이제 P6spy가 어떻게 실제 애플리케이션에서 작동하는지 살펴보겠습니다.

간단한 사용자 관리 기능을 구현하고, P6spy를 통해 SQL 쿼리를 모니터링해 보겠습니다.

1. 엔티티 클래스 정의

먼저 User 엔티티 클래스를 생성합니다:

package com.smsoft.springbootp6spydemo.entity;

import jakarta.persistence.*;
import lombok.Data;
import org.hibernate.annotations.CreationTimestamp;

import java.time.LocalDateTime;

@Entity
@Data
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    
    private String email;
    
    @CreationTimestamp
    @Column(updatable = false)
    private LocalDateTime createdAt;
}

2. 리포지토리 구현

다음으로 Spring Data JPA 리포지토리를 생성합니다:

package com.smsoft.springbootp6spydemo.repository;

import com.smsoft.springbootp6spydemo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
    // 기본 CRUD 기능 상속
}

3. 컨트롤러 구현

웹 인터페이스를 위한 컨트롤러를 구현합니다:

package com.smsoft.springbootp6spydemo.controller;

import com.smsoft.springbootp6spydemo.entity.User;
import com.smsoft.springbootp6spydemo.repository.UserRepository;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

@Controller
@RequiredArgsConstructor
public class UserController {

    private final UserRepository userRepository;

    @GetMapping("/")
    public String home(Model model) {
        model.addAttribute("users", userRepository.findAll());
        model.addAttribute("newUser", new User());
        return "index";
    }

    @PostMapping("/users")
    public String addUser(User user) {
        userRepository.save(user);
        return "redirect:/";
    }
}

4. Thymeleaf 템플릿 생성

사용자 목록을 표시하고 새로운 사용자를 추가할 수 있는 템플릿을 생성합니다:

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>P6spy Demo</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h1>P6spy SQL 모니터링 데모</h1>
        
        <div class="card mt-4">
            <div class="card-header">
                <h3>사용자 추가</h3>
            </div>
            <div class="card-body">
                <form th:action="@{/users}" method="post" th:object="${newUser}">
                    <div class="mb-3">
                        <label for="name" class="form-label">이름</label>
                        <input type="text" class="form-control" id="name" th:field="*{name}" required>
                    </div>
                    <div class="mb-3">
                        <label for="email" class="form-label">이메일</label>
                        <input type="email" class="form-control" id="email" th:field="*{email}" required>
                    </div>
                    <button type="submit" class="btn btn-primary">추가</button>
                </form>
            </div>
        </div>
        
        <div class="card mt-4">
            <div class="card-header">
                <h3>사용자 목록</h3>
            </div>
            <div class="card-body">
                <table class="table">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>이름</th>
                            <th>이메일</th>
                            <th>생성일</th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr th:each="user : ${users}">
                            <td th:text="${user.id}"></td>
                            <td th:text="${user.name}"></td>
                            <td th:text="${user.email}"></td>
                            <td th:text="${#temporals.format(user.createdAt, 'yyyy-MM-dd HH:mm')}"></td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</body>
</html>

 

5. Docker Compose로 MySQL 실행

개발 환경에서 MySQL을 쉽게 실행하기 위해 Docker Compose 파일을 생성합니다:

version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: p6spy-demo-mysql
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: demo
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

volumes:
  mysql-data:

 

이제 docker-compose up -d 명령으로 MySQL을 실행하고, ./gradlew bootRun으로 애플리케이션을 시작합니다.

---

P6spy 로그 분석하기

P6spy가 설정된 애플리케이션을 실행하면, 콘솔에 SQL 쿼리 로그가 출력됩니다.

이 로그를 통해 다양한 데이터베이스 작업을 분석해 보겠습니다.

1. 사용자 목록 조회 시 로그

애플리케이션 메인 페이지에 접속하면 사용자 목록을 조회하는 SQL이 실행됩니다:

24.05.21 10:30:45 | OperationTime : 15ms
HeFormatSql(P6Spy sql,Hibernate format):
    select 
        u1_0.id,
        u1_0.created_at,
        u1_0.email,
        u1_0.name 
    from 
        user u1_0

이 로그에서 확인할 수 있는 정보:

• 쿼리 실행 시간: 15ms
• 실행된 SQL: select 문으로 모든 사용자 정보를 조회
• 테이블 별칭: u1_0으로 설정됨
• 조회 컬럼: id, created_at, email, name

2. 사용자 추가 시 로그

새로운 사용자를 추가하면 다음과 같은 로그가 출력됩니다:

24.05.21 10:31:20 | OperationTime : 25ms
HeFormatSql(P6Spy sql,Hibernate format):
    insert 
    into
        user
        (created_at, email, name) 
    values
        ('2024-05-21 10:31:20', 'user@example.com', '홍길동')

이 로그에서는:

• 쿼리 실행 시간: 25ms (삽입 작업이라 조회보다 조금 더 오래 걸림)
• 실행된 SQL: insert 문으로 새로운 사용자 추가
• 바인딩된 파라미터 값: 실제 값으로 대체되어 표시됨

3. JPA 관계 매핑 시 N+1 문제 확인하기

P6spy의 가장 큰 장점 중 하나는 N+1 쿼리 문제와 같은 성능 이슈를 쉽게 발견할 수 있다는 점입니다.

예를 들어, 사용자와 게시물이 1:N 관계로 매핑되어 있고 페치 전략이 효율적이지 않을 경우:

24.05.21 10:35:12 | OperationTime : 12ms
HeFormatSql(P6Spy sql,Hibernate format):
    select 
        u1_0.id,
        u1_0.created_at,
        u1_0.email,
        u1_0.name 
    from 
        user u1_0

24.05.21 10:35:12 | OperationTime : 5ms
HeFormatSql(P6Spy sql,Hibernate format):
    select 
        p1_0.user_id,
        p1_0.id,
        p1_0.content,
        p1_0.title 
    from 
        post p1_0 
    where 
        p1_0.user_id=1

24.05.21 10:35:12 | OperationTime : 4ms
HeFormatSql(P6Spy sql,Hibernate format):
    select 
        p1_0.user_id,
        p1_0.id,
        p1_0.content,
        p1_0.title 
    from 
        post p1_0 
    where 
        p1_0.user_id=2

이러한 로그를 보면, 사용자 목록을 조회한 후 각 사용자마다 추가 쿼리가 발생하는 N+1 문제를 명확히 확인할 수 있습니다.

이를 해결하기 위해 JOIN FETCH나 엔티티 그래프 등의 최적화 기법을 적용할 수 있습니다.

---

P6spy를 효과적으로 사용하기 위한 팁

P6spy를 실무에서 더욱 효과적으로 활용하기 위한 몇 가지 팁을 소개합니다:

1. 개발 환경과 운영 환경 분리

P6spy는 성능 오버헤드가 있으므로, 프로필을 활용해 개발 환경에서만 활성화하는 것이 좋습니다:

# application-dev.yml
decorator:
  datasource:
    p6spy:
      enable-logging: true

# application-prod.yml
decorator:
  datasource:
    p6spy:
      enable-logging: false

2. 로그 필터링 설정

특정 쿼리만 로깅하거나 특정 패턴의 쿼리를 제외하려면 spy.properties 파일을 사용합니다:

# src/main/resources/spy.properties
excludecategories=info,debug,result,resultset
filter=true
filter.pattern=(insert|update|delete).*

3. 로그 출력 대상 설정

로그를 파일로 저장하려면 다음과 같이 설정합니다:

# src/main/resources/spy.properties
appender=com.p6spy.engine.spy.appender.FileLogger
logfile=logs/spy.log

4. 파라미터 마스킹

보안이 중요한 정보(비밀번호 등)를 마스킹 처리할 수 있습니다:

public class P6spyMaskingFormatter implements MessageFormattingStrategy {
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, 
                               String category, String prepared, String sql, String url) {
        if (sql.toLowerCase().contains("password")) {
            // 비밀번호 필드를 마스킹 처리
            sql = sql.replaceAll("(?i)('password=)([^,]*)", "$1*****");
        }
        return now + " | " + elapsed + "ms | " + sql;
    }
}

5. 쿼리 실행 시간 기준 필터링

특정 시간 이상 걸리는 쿼리만 로깅하려면:

# src/main/resources/spy.properties
executionThreshold=100

이 설정은 100ms 이상 걸리는 쿼리만 로깅합니다.

---

P6spy의 한계와 대안

P6spy는 강력한 도구지만, 알아두어야 할 몇 가지 한계가 있습니다:

1. 성능 오버헤드

P6spy는 모든 JDBC 호출을 가로채고 로깅하기 때문에 애플리케이션 성능에 영향을 줄 수 있습니다.

특히 고부하 환경에서는 10-15% 정도의 성능 저하가 발생할 수 있습니다.

2. 메모리 사용량 증가

로깅을 위해 SQL 문과 파라미터를 메모리에 저장하므로, 대량의 쿼리가 실행되는 환경에서는 메모리 사용량이 증가할 수 있습니다.

3. 로그 파일 크기

상세한 SQL 로깅으로 인해 로그 파일 크기가 빠르게 증가할 수 있으므로, 로그 순환(rotation) 정책을 설정하는 것이 중요합니다.

P6spy의 대안

P6spy 외에도 SQL 모니터링을 위한 다양한 도구가 있습니다:

1. Hibernate Statistics: Hibernate 자체적으로 제공하는 통계 기능을 활용할 수 있습니다.
2. spring.jpa.properties.hibernate.generate_statistics=true
3. Spring Boot Actuator: 데이터베이스 메트릭을 모니터링할 수 있습니다.
4. implementation 'org.springframework.boot:spring-boot-starter-actuator'
5. APM 도구: New Relic, Datadog, Pinpoint 등의 APM(Application Performance Monitoring) 도구는 SQL 쿼리 모니터링 기능을 제공합니다.

---

실제 프로젝트에서의 활용 사례

P6spy를 활용한 실제 프로젝트 사례를 살펴보겠습니다:

사례 1: 레거시 시스템 분석

오래된 애플리케이션의 데이터베이스 동작을 파악하기 위해 P6spy를 적용했습니다.

코드 수정 없이 JDBC 드라이버만 교체하여 모든 SQL 쿼리를 로깅할 수 있었고, 이를 통해 최적화 대상을 식별했습니다.

사례 2: 성능 병목 지점 발견

대규모 트래픽을 처리하는 웹 애플리케이션에서 간헐적인 성능 저하가 발생했습니다.

P6spy 로깅을 통해 특정 시간대에 실행되는 무거운 쿼리를 발견하고, 인덱스 추가와 쿼리 최적화로 성능을 30% 개선했습니다.

사례 3: 테스트 환경에서의 자동화된 분석

자동화된 테스트 환경에서 P6spy 로그를 분석하여 각 테스트 케이스별 SQL 쿼리 수와 실행 시간을 집계했습니다.

이를 통해 테스트 성능 개선 지점을 식별하고, 전체 테스트 실행 시간을 40% 단축했습니다.

---

결론

P6spy는 Spring Boot 애플리케이션에서 SQL 쿼리를 모니터링하고 최적화하는 데 매우 유용한 도구입니다.

특히 JPA와 같은 ORM 프레임워크를 사용할 때, 실제로 생성되는 SQL을 정확히 파악하고 성능을 최적화하는 데 큰 도움이 됩니다.

이 글에서 살펴본 내용을 바탕으로 P6spy를 프로젝트에 적용하면, SQL 쿼리 가시성을 높이고 데이터베이스 성능을 개선하는 데 많은 도움이 될 것입니다.

다만, 운영 환경에서는 성능 오버헤드를 고려하여 선택적으로 사용하는 것이 좋습니다.

마지막으로, 이 글에서 소개한 모든 코드와 설정은 GitHub 저장소에서 확인할 수 있습니다.

데모 프로젝트를 직접 실행해보며 P6spy의 강력한 기능을 경험해 보세요!

---

관련 참조 링크

공식 문서 및 리소스

• P6spy 공식 사이트
• Spring Boot 공식 문서
• Spring Data JPA 공식 문서
• Hibernate 공식 문서

관련 도구 및 라이브러리

• p6spy-spring-boot-starter GitHub
• MySQL Connector/J
• Thymeleaf 공식 문서
• Docker Compose 문서

성능 모니터링 도구

• Spring Boot Actuator
• Micrometer
• New Relic APM
• Datadog APM

추가 학습 자료

• JPA N+1 문제 해결 방법
• Hibernate 성능 최적화 가이드
• SQL 쿼리 최적화 베스트 프랙티스

관련 블로그 포스트

• Baeldung - P6Spy with Spring Boot
• JPA 쿼리 최적화 전략
• Spring Boot 프로파일링 가이드

GitHub 데모 프로젝트

• Spring Boot P6spy Demo - 이 글에서 사용된 전체 예제 코드(GitHub 저장소)

GitHub - ksm1569/spring-boot-p6spy-demo: Spring Boot에서 P6spy를 활용한 SQL 쿼리 로깅 데모 프로젝트입니다. JP