PostgreSQL 성능 저하 Troubleshooting 가이드-1부-

 

 

작성자: 서준섭 과장- DB 기술센터 I DB Tech팀

PostgreSQL 트러블슈팅 가이드: OS 리소스 확인 및 세션 정보 조회 

데이터베이스 성능 저하는 애플리케이션 전체의 성능에 큰 악영향을 미칩니다. 데이터베이스의 성능이 저하되면, 웹사이트 로딩 속도가 느려지거나 트랜잭션 처리 속도가 감소하여 사용자 경험이 크게 악화될 수 있습니다. 이러한 성능 저하는 갑작스럽게 발생할 수 있으며, 원인을 빠르게 찾아내고 해결하는 것이 매우 중요합니다. 

 

성능 저하 문제를 해결하기 위해서는 체계적인 troubleshooting 프로세스가 필요합니다. 먼저 데이터베이스 서버의 전반적인 리소스 사용량을 확인하여 병목 지점을 찾아내야 합니다. CPU, 메모리, 디스크 I/O, 네트워크 사용량 등을 모니터링하여 어느 리소스가 가장 많이 사용되고 있는지 파악해야 합니다. 이후 데이터베이스 연결 상태, 실행 중인 쿼리, Lock 상태 등을 면밀히 살펴보며 문제의 원인을 분석해야 합니다. 예를 들어, 특정 쿼리가 지나치게 오래 걸리거나 여러 트랜잭션이 동시에 실행되면서 Lock 이 발생하는 경우가 있을 수 있습니다. 

 

본 1부 가이드에서는 PostgreSQL 데이터베이스 성능 저하 문제를 해결하기 위한 초기 단계별 troubleshooting 방법을 제시하고자 합니다. 먼저 OS 리소스 사용량 확인을 한 후, 데이터베이스 연결 상태 분석 등의 핵심 단계를 차례로 살펴보겠습니다. 

 

OS Resource 확인

OS 리소스를 확인하기 위해서는 다음과 같은 Linux 명령어를 활용하여 CPU, 메모리, 디스크 I/O 등의 시스템 리소스 사용량을 모니터링하여 병목 지점을 찾아내야 합니다. 

 

- top: CPU, 메모리, 프로세스 등 실시간 시스템 정보 확인

- iostat: 디스크 I/O 성능 모니터링

- vmstat: 메모리, 스왑, 프로세스, CPU 등 시스템 전반의 성능 지표 확인

- sar: 과거 시스템 활동 데이터 확인

 

특히 DB 서버 프로세스의 PID 값을 확인하여 해당 프로세스의 리소스 사용량을 집중적으로 모니터링해야 합니다. 

 

CPU 과점유 Process 의 PID 확인 (top 이용)

 

시스템 리소스 부족, 네트워크 장애, 디스크 오류 등의 이벤트들이 데이터베이스 성능 저하의 원인이 되었을 가능성이 있을 수 있습니다. 이러한 이벤트를 살펴보려면 /var/log/messages 파일을 검토하여 DB 성능 이슈가 발생한 시간대에 시스템 상황을 파악해야 합니다. 

 

var/log/messages 파일 확인 방법:

1. 터미널에서 tail -n 100 /var/log/messages 명령어를 실행하면 최근 100개의 로그 메시지를 확인할 수 있습니다.

2. grep 명령어를 사용하여 특정 키워드가 포함된 로그를 검색할 수 있습니다. 예를 들어 grep "error" /var/log/messages를 실행하면 "error" 문자열이 포함된 로그를 확인할 수 있습니다.

3. less 명령어를 사용하면 파일 전체를 스크롤하며 확인할 수 있습니다.

 

 

이처럼 OS 리소스 사용량 확인과 시스템 로그 분석을 통해 데이터베이스 성능 저하의 근본 원인을 찾아낼 수 있습니다. 이 과정은 CPU, 메모리, 디스크 I/O 등 병목 지점을 식별하고, 관련 이벤트나 오류 로그를 확인하여 문제의 원인을 종합적으로 진단합니다. 

Session 정보 조회 

pg_stat_activity와 pg_stat_statements를 통해 문제가 되는 세션과 쿼리를 식별하고, max_connections 설정과 현재 세션 수를 확인하여 리소스 부족 문제를 해결하는 것이 두 번째 트러블슈팅 단계입니다. 

 

PostgreSQL에서 pg_stat_activity와 pg_stat_statements는 각각 다른 목적으로 사용되는 시스템 카탈로그입니다. pg_stat_activity는 PostgreSQL에서 기본적으로 제공되는 카탈로그 뷰이며, pg_stat_statements는 PostgreSQL에서 설치가 필요한 extension입니다. 이 둘의 차이와 용도는 다음과 같습니다:

pg_stat_activity	pg_stat_statements
현재 데이터베이스 서버에 연결된 모든 세션 정보 제공	데이터베이스에서 실행된 모든 SQL 문의 통계 정보 제공
각 세션의 PID, 사용자 이름, 데이터베이스 이름, 클라이언트 주소, 현재 실행 중인 쿼리, 쿼리 실행 시간 등의 정보 포함	각 SQL 문의 텍스트, 실행 횟수, 총 실행 시간, CPU 사용량, 메모리 사용량, I/O 사용량 등의 정보 포함
데이터베이스 관리자는 어떤 세션이 현재 실행 중인지, 어떤 쿼리를 수행하고 있는지 확인 가능	데이터베이스 관리자는 어떤 SQL 문이 가장 많은 리소스를 사용하고 있는지 확인 가능
장시간 실행 중인 쿼리나 리소스를 과도하게 사용하는 세션 식별 유용	성능 저하의 원인이 되는 문제 쿼리를 신속하게 식별하고 최적화함
이 정보를 바탕으로 문제가 되는 세션 종료, 쿼리 최적화 등의 조치를 취할 수 있음	SQL 문의 실행 패턴을 분석하여 인덱스 생성이나 쿼리 재작성 등의 최적화 방안을 수립할 수 있음

 

요약하면, pg_stat_activity는 현재 실행 중인 세션 정보를, pg_stat_statements는 과거 실행된 SQL 문의 통계 정보를 제공합니다. 데이터베이스 관리자는 이 두 가지 정보를 종합적으로 활용하여 데이터베이스 성능 문제의 원인을 신속하게 진단하고 해결책을 마련할 수 있습니다. 

 

확인한 PID 로 실행 중인 Query 확인

 

예를 들어, pg_stat_activity에서 장시간 실행 중인 쿼리가 있다면, 이 쿼리를 종료하는 것이 긴급 해결책이 될 수 있습니다. 리소스를 과도하게 사용하는 쿼리를 종료함 으로써 짧은 쿼리들이 데이터베이스를 사용할 기회를 얻게 됩니다. 때로는 이러한 쿼리를 종료하는 것이 내부 서버 오류가 발생하여 전체 데이터베이스를 사용할 수 없게 만드는 것보다 낫습니다.

 

이렇게 Session 정보를 조회하고 SQL 실행 통계를 확인하면 데이터베이스 성능 저하의 원인이 되는 문제 쿼리와 세션을 식별할 수 있게 됩니다. 

 

또한, max_connections 설정 값과 현재 연결된 세션 수를 확인하는 것도 중요합니다. 

 

max_connections 확인 방법 예시: 

Example

select  *   from   (select count(*) as total_used         from pg_stat_activity                                             ) q1 , (select count(*)*100/(select current_setting('max_connections')::int) as used_ratio from pg_stat_activity) q2 , (select setting::int res_for_superuser from pg_settings where name=$$superuser_reserved_connections$$    ) q3 , (select setting::int max_conn          from pg_settings where name=$$max_connections$$                   ) q4 ;  total_used | used_ratio | res_for_superuser | max_conn ------------+------------+-------------------+----------             6 |             6 |                     3 |      100 (1개 행)

만약 max_connections 값이 부족하면 새로운 세션 연결이 거부되어 성능 저하를 일으킬 수 있습니다. 위 쿼리의 경우는 안정적일 때의 예시이지만, total_used 세션 수가 max_connections 값에 근접하거나 동일할 경우, 메모리 사용량이 과도해질 수 있습니다. 이러한 경우, max_connections 값을 늘리거나 메모리 용량을 확장해야 할 것입니다. 

 

2부에 계속 되는 Troubleshooting 가이드

1부 아티클에서는 PostgreSQL의 성능 저하 문제를 해결하기 위한 초기 단계별 troubleshooting 방법을 소개했습니다. OS 리소스 사용량을 확인하여 병목 지점을 파악하거나, pg_stat_activity를 통해 현재 진행 중인 세션 정보 pg_stat_statements로 과거 실행된 SQL문의 통계 정보를 확인하여 문제의 원인을 진단할 수 있습니다. 

 

다음 2부에서는 slow query가 시스템에 어떤 악영향을 줄 수 있는지에 대해 알아보고, 그 이슈를 확인하고 해결하는 방법에 대해 알아보도록 하겠습니다.