이번 게시글에서는 AWS PostgreSQL RDS에서 Slow Query 로그를 확인하는 방법에 대해 기술하고자 한다. 대나무 숲 프로젝트 진행 중 성능 개선을 위해 서비스에 이슈가 될 만한 쿼리를 분석하고자 로그를 확인할 필요가 있었다. MySQL을 기준으로 관련 게시글은 많이 존재하지만, PostgreSQL에 대한 정보는 상대적으로 부족해 구현에 있어서 어려움이 있었다. 나와 같은 사람들에게 도움이 됐으면 좋겠다.

1. Slow Query

Slow Query는 데이터베이스에서 실행 시간이 오래 걸리는 쿼리를 의미한다. 이러한 쿼리는 데이터베이스 성능을 저하시킬 수 있으며, 시스템의 응답 시간을 늘리고 자원 사용률을 높인다.
따라서 성능 개선을 위해 Slow Query를 식별하고 최적화하는 과정이 중요하다. Slow Query를 분석함으로써 인덱스 추가, 쿼리 재작성, 데이터베이스 구조 변경 등 다양한 성능 최적화 방법을 적용할 수 있다.

2. 파라미터 그룹 설정

AWS RDS에서 PostgreSQL 인스턴스의 파라미터를 설정하여 Slow Query 로그를 활성화할 수 있다. 이를 위해 다음과 같은 파라미터를 설정해야 한다

• log_statement: 이 파라미터는 어떤 유형의 SQL 문을 로그에 기록할지 결정한다. 허용된 값은 다음과 같다. 참고로 all 또는 mod로 설정하면 DML 쿼리들의 duration이 출력되지 않는다고 한다.
  • none: SQL 문을 기록하지 않음
  • ddl: 데이터 정의 언어(DDL) 문만 기록
  • mod: 데이터 정의 언어(DDL)와 데이터 조작 언어(DML) 문을 기록
  • all: 모든 SQL 문을 기록

• log_min_duration_statement: 이 파라미터는 지정된 시간(밀리초) 이상 실행된 모든 SQL 문을 로그에 기록한다. 허용된 값은 -1에서 2147483647 사이이다:

  • -1: 실행 시간을 기준으로 SQL 문을 기록하지 않음
  • 0: 모든 SQL 문을 기록
  • 0: 지정된 시간 이상 실행된 SQL 문을 기록

• rds.log_retention_period: 이 파라미터는 로그 보존 기간을 분 단위로 설정한다. 허용된 값은 1440에서 10080 사이이다:

  • 최소 값: 1440 (1일)
  • 최대 값: 10080 (7일)

파라미터 그룹을 생성하거나 기존 그룹을 수정하여 이러한 설정을 적용할 수 있다. 참고로 해당 설정들의 적용 유형이 Dynamic이기 때문에 파라미터를 수정하면 자동으로 재시작되므로 주의가 필요하다.

3. 로그 확인

파라미터 설정 후, PostgreSQL 데이터베이스에서 Slow Query 로그를 확인할 수 있다. 예를 들어, "pg_sleep" 함수를 사용하여 2초 동안 대기하는 쿼리를 실행하면 해당 쿼리가 로그에 기록된다.

postgres=> SELECT pg_sleep(2);

콘솔

로그는 AWS RDS 콘솔의 "로그 및 이벤트" 섹션에서 확인할 수 있다. 원하는 로그 파일을 선택하여 내용을 확인하거나 다운로드할 수 있다. 예시 로그는 다음과 같다.

root@postgres:[4352]:LOG: duration: 2011.402 ms statement: SELECT pg_sleep(2);

쿼리 실행 시간이 2011.402밀리초(2초)를 초과했음을 보여준다. 이러한 로그를 통해 실행 시간이 오래 걸리는 쿼리를 식별하고, 해당 쿼리를 최적화할 수 있다.

Cloud Watch

콘솔을 이용한 로그 확인은 쉽지 않다는 단점이 있다. 때문에 Cloud Watch를 이용하여 로그를 확인하는 것을 추천한다. 수정 버튼을 누른뒤, 로그 내보내기에서 PostgreSQL 로그를 체크하고 즉시 적용을 완료하면 된다.

사진과 같이, cloudWatch로 이동해서 로그 그룹을 확인하면 된다.