postgresql의 운영에 필요한 테이블과 wait event

wait event는 성능 및 운영 관리를 위해 반드시 알아야할 중요한 요소이다. 메모리/디스크/트랜잭션 수 등 정보와 결합되어 더욱 세밀한 데이터베이스 상태 분석이 가능하다.

postgresql은 시스템 카탈로그 뷰 테이블에 프로세스 id와 데이터베이스, 접속 ip, 쿼리 등 DBMS에서 메타데이터를 제공하는 뷰이다. postgresql에서는 pg_stat_activaity 테이블에 다음과 같은 정보를 기록해둔다.

다양한 클라이언트에 

SELECT * FROM pg_stat_activity; 

쿼리를 실행하여 위와 같은 정보를 확인할 수 있다.

wait event의 종류는 너무 많아서 모두 외우기는 힘들다. 중요한 wait event만 파악하고, 나머지는 운영하면서 알아가는 것이 바람직하다고 생각한다.

1. IO:Completion: I/O 작업이 완료될 때까지 대기 중인 경우. 예를 들어, 디스크에서 데이터를 읽거나 쓰는 작업이 완료되기를 기다리는 상태

2. Lock: 락(잠금)을 기다리는 경우. 다른 세션이나 트랜잭션이 사용 중인 데이터에 대한 락을 획득하기 위해 대기하는 상태. 

3. BufferPin: 버퍼 풀 내 버퍼에 대한 핀(고정)을 기다리는 경우. 버퍼를 읽거나 쓰기 전에 해당 버퍼를 고정해야 하는 상태.

4. WalWriterSleep: WAL Writer 프로세스가 대기 중인 경우. WAL(Write-Ahead Logging) Writer는 트랜잭션 로그를 디스크에 기록하는 작업을 담당함.

5. CheckpointTimeout: 체크포인트 완료를 기다리는 경우. 체크포인트는 데이터베이스 상태를 안정화하기 위해 변경된 내용을 디스크에 지속적으로 기록하는 작업.

6. AutoVacuumMain: 자동 VACUUM 프로세스가 메인 작업을 기다리는 경우. 자동 VACUUM은 불필요한 데이터를 정리하는 작업을 수행하여 데이터베이스 성능을 최적화.

7. Vacuum: VACUUM 작업을 기다리는 경우. VACUUM은 불필요한 행 버전을 정리하고 공간을 재사용하여 데이터베이스의 무결성을 유지함.

8. BgWriter: 백그라운드 라이터가 실행 가능한 상태를 기다리는 경우. 백그라운드 라이터는 변경된 데이터를 디스크에 비동기적으로 기록하는 작업을 수행함.

9. LogicalLauncher: 논리적 레플리케이션 런처가 실행 가능한 상태를 기다리는 경우. 논리적 레플리케이션은 데이터 변경을 다른 서버로 전달하는 기능을 제공.

10. Client: 클라이언트 요청에 대한 응답을 기다리는 경우. 일반적으로 클라이언트가 서버로부터 응답을 받기 위해 대기하는 상태.

쿼리가 느리다는 말은 데이터베이스 시스템에 문제가 생겼거나, 실행계획이 엉망인 경우를 의미한다. 실행 계획이 정상적이지만, 데이터베이스 시스템이 문제가 생긴 경우에는 문제를 찾기가 어렵다. 그렇기에 postgreqsl 제공하는 문제(대기) 정보를 바탕으로 데이터베이스의 상태를 정상적인 상태로 유지해야한다.

 

 

운영에 필요한 주요 테이블

• pg_stat_activity: 현재 실행 중인 세션 및 쿼리 정보를 제공하는 테이블
• pg_stat_replication: 복제된 서버들의 상태와 통계 정보를 제공하는 테이블
• pg_stat_database: 데이터베이스별 성능 통계 정보를 제공하는 테이블
• pg_stat_user_tables: 사용자 테이블별 성능 통계 정보를 제공하는 테이블
• pg_stat_user_indexes: 사용자 인덱스별 성능 통계 정보를 제공하는 테이블
• pg_stat_bgwriter: 백그라운드 라이터 프로세스의 작업 통계 정보를 제공하는 테이블
• pg_statio_user_tables: 사용자 테이블의 I/O 통계 정보를 제공하는 테이블
• pg_locks: 데이터베이스에서 현재 활성화된 락(잠금) 정보를 제공하는 테이블
• pg_stat_ssl: SSL 연결 통계 정보를 제공하는 테이블
• pg_stat_progress_vacuum: VACUUM 진행 상태와 통계 정보를 제공하는 테이블
• pg_settings: PostgreSQL 서버의 설정 값들을 제공하는 테이블
• pg_roles: 데이터베이스 사용자 및 역할 정보를 제공하는 테이블
• pg_tables: 데이터베이스에 존재하는 테이블 정보를 제공하는 테이블
• pg_indexes: 데이터베이스에 존재하는 인덱스 정보를 제공하는 테이블
• pg_views: 데이터베이스에 존재하는 뷰(View) 정보를 제공하는 테이블
• pg_trigger: 데이터베이스에 존재하는 트리거(Trigger) 정보를 제공하는 테이블
• pg_stat_all_tables: 모든 테이블의 성능 통계 정보를 제공하는 테이블