Amazon Relational Database Service(Amazon RDS) for PostgreSQL 데이터베이스에 성능 문제 및 오류를 식별하기 위해 로그 분석이 필요합니다. Amazon CloudWatch Logs에서 로그를 분석하려고 합니다.
간략한 설명
데이터베이스 로그를 분석하려면 먼저 RDS for PostgreSQL 로깅 파라미터를 구성하고 로그를 Amazon CloudWatch로 내보내야 합니다. 그런 다음 CloudWatch Logs Insights를 사용하려 로그 데이터를 쿼리하고 분석합니다.
해결 방법
RDS for PostgreSQL 로그 파라미터 구성
다음 로깅 파라미터를 사용하여 사용자 지정 파라미터 그룹을 만듭니다.
- log_statement: all, mod 또는 ddl로 설정하여 문 로깅을 제어합니다.
- log_min_duration_statement: 밀리초 임계값을 설정하여 장기 실행 쿼리를 로깅합니다.
- log_autovacuum_min_duration: 밀리초 임계값을 설정하여 autovacuum 작업을 로깅합니다.
- log_lock_waits: 1로 설정하여 잠긴 상태의 세션을 로깅합니다.
- log_connections 및 log_disconnections: 둘 다 1로 설정하여 세션 활동을 추적합니다.
- log_temp_files: 로깅하려는 임시 파일의 최소 크기를 설정합니다.
기존 파라미터 그룹을 수정하려면 Amazon RDS의 DB 파라미터 그룹에서 파라미터 수정을 참조하십시오.
로그를 CloudWatch로 내보내기
RDS for PostgreSQL 데이터베이스(DB) 인스턴스가 로그를 CloudWatch로 내보내도록 구성합니다.
CloudWatch Logs Insights로 로그 분석
특정 데이터베이스 활동을 분석하려면 CloudWatch Logs Insights를 사용하십시오.
사용자 연결 해제 추적
각 사용자 연결의 세션 기간 및 연결 호스트의 IP 주소, 데이터베이스 이름, 연결 포트를 표시하려면 다음 쿼리를 실행합니다.
fields @timestamp, @message
parse @message /session time: (?<sessionTime>.*?) user=(?<user>.*?) database=(?<database>.*?) host=(?<host>.*?) port=(?<port>.*?)$/
| filter user like "postgres"
참고: postgres를 분석하려는 사용자 이름으로 바꾸십시오.
출력을 사용하여 연결 문제가 표시되거나 알 수 없는 IP 주소로부터의 예기치 않은 연결 패턴을 표시하는 짧은 세션을 식별합니다. 또한 사용자 연결 해제를 추적하여 다양한 시간의 데이터베이스 액세스 패턴을 모니터링할 수도 있습니다.
사용자 연결 추적
데이터베이스에 연결하는 애플리케이션과 특정 시간의 연결 패턴을 모니터링하려면 다음 쿼리를 실행합니다.
fields @timestamp, @message
| filter @message like /connection authorized: user=postgres/
| parse @message "*:*(*):*@*:[*]:LOG: connection authorized: user=postgres database=* application_name=* SSL" as date_time, ip_address, port, user, db, pid, database, app_name
| display @timestamp, ip_address, database, app_name
참고: postgres를 분석하려는 사용자 이름으로 바꾸십시오.
출력을 사용하여 예기치 않은 애플리케이션 연결을 식별하고 연결의 SSL 사용량을 검사할 수도 있습니다.
임시 파일 사용량 모니터링
work_mem을 초과하는 쿼리를 식별하고 임시 파일을 만들려면 다음 쿼리를 실행합니다.
fields @timestamp, @message
| filter @message like /temporary file:/
| parse @message /temporary file: path "(?<filepath>.*?)", size (?<filesize>\d+)/
| display @timestamp, filepath, filesize
참고: 또한 앞선 쿼리를 사용하여 임시 작업의 디스크 공간 사용량을 보거나 최적화가 필요한 쿼리를 찾을 수도 있습니다.
출력에 대규모 임시 파일이 자주 표시되는 경우, 다음 작업 중 하나 이상을 수행합니다.
- DB 인스턴스에 사용 가능한 메모리가 있는 경우, work_mem을 늘립니다.
- 대규모 임시 파일을 만드는 쿼리를 검토하여 최적화합니다.
- 테이블 파티션으로 임시 파일 사용량을 줄일 수 있는지 확인합니다.
교착 상태 식별
교착된 세션의 프로세스 ID를 알아내려면 다음 쿼리를 실행합니다.
fields @timestamp, @message
| filter @message like /ShareLock|while|process|lock/
| parse @message /Process (?<blockingProcess>\d+) waits for ShareLock on transaction (?<transactionId>\d+); blocked by process (?<blockedByProcess>\d+)/
| filter blockedByProcess > 0
| display @timestamp, blockingProcess, transactionId, blockedByProcess
참고: 출력에 잠김을 초래하는 트랜잭션 ID와 교착이 발생했을 때의 타임스탬프도 표시될 수 있습니다.
교착이 자주 확인되는 경우, 다음 작업 중 하나 이상을 수행합니다.
- 애플리케이션 트랜잭션 패턴을 검토합니다.
- 잠금을 보유한 장기 실행 트랜잭션이 있는지 확인합니다.
- 해당하는 경우, 행 수준 잠금을 추가합니다.
- 트랜잭션 격리 수준을 분석합니다.
장기 실행 쿼리 식별
각 문에 대하여 지정한 기간과 실행 시간을 초과하는 SQL 문을 보려면 다음 쿼리를 실행합니다.
fields @timestamp, @message
| parse @message /duration: (?<duration>.*?) ms\s+statement: (?<statement>.*?)$/
| filter duration > 1000
| sort duration desc
참고: 1000을 기본 설정 기간 임계값(밀리초 단위)으로 바꾸십시오.
느린 쿼리를 찾은 경우, 다음 작업 중 하나 이상을 수행합니다.
- 문에 대하여 EXPLAIN ANALYZE를 실행하여 실행 계획을 봅니다.
- 테이블 통계가 최신인지 확인합니다.
- 인덱스 사용량을 확인합니다.
- 쿼리를 최적화하거나 테이블 파티션을 사용합니다.
- DB 인스턴스의 리소스 할당을 검토합니다.
autovacuum 통계 분석
autovacuum 작업을 추적하고 튜플 제거 레이트를 모니터링하려면 다음 쿼리를 실행합니다.
fields @timestamp, @message
| parse @message "tuples: " as @tuples_temp
| parse @tuples_temp "* removed," as @tuples_removed
| parse @tuples_temp "remain, * are dead but not yet removable, " as @tuples_not_removable
| filter @message like / automatic vacuum /
| sort @timestamp
| stats avg(@tuples_removed) as avg_tuples_removed,
avg(@tuples_not_removable) as avg_tuples_not_removable
by bin(5m)
참고: 해당 출력은 데드 튜플 수가 많은 테이블을 식별하는 데에도 도움이 될 수 있습니다.
autovacuum을 사용할 수 없는 경우, 다음 작업 중 하나 이상을 수행합니다.
- autovacuum_vacuum_scale_factor를 조정합니다.
- autovacuum_vacuum_threshold 설정을 검토합니다.
- vacuum 작업을 차단하는 장기 실행 트랜잭션이 있는지 검사합니다.
- 트래픽이 적은 기간에는 수동 VACUUM 작업을 사용합니다.
모니터링 대시보드 설정
CloudWatch 대시보드를 만들어 로그 분석을 시각화하십시오.
다음 값에 대하여 위젯 추가:
- 시간의 흐름에 따른 쿼리 실행 시간
- 교착 빈도
- 임시 파일 사용량 패턴
- Autovacuum 효능
데이터베이스 로그 오류에 대하여 알림을 설정할 수도 있습니다.
관련 정보
Amazon RDS 모범 사례
Amazon CloudWatch 경보 사용
RDS 및 Amazon Aurora PostgreSQL 호환 버전 로그를 사용한 작업: 1부