Azure Database for PostgreSQL - 유연한 서버에서 높은 IOPS 사용률 문제 해결

적용 대상: Azure Database for PostgreSQL - 유연한 서버

이 문서에서는 높은 IOPS(초당 입력/출력 작업) 사용률의 근본 원인을 빠르게 식별하는 방법을 보여 주고 Azure Database for PostgreSQL 유연한 서버를 사용할 때 IOPS 사용률을 제어할 수 있는 수정 작업을 제공합니다.

이 문서에서는 다음 방법을 설명합니다.

• 근본 원인을 완화하기 위한 권장 사항을 식별하고 가져오는 문제 해결 가이드에 대해 설명합니다.
• Azure Metrics, 쿼리 데이터 저장소 및 pg_stat_statements와 같은 높은 I/O(입력/출력) 사용률을 식별하는 도구를 사용합니다.
• 장기 실행 쿼리, 검사점 타이밍, 중단 자동 진공 디먼 프로세스 및 높은 스토리지 사용률과 같은 근본 원인을 식별합니다.
• 분석 설명을 사용하고, 검사점 관련 서버 매개 변수를 조정하고, 자동 진공 디먼을 조정하여 높은 I/O 사용률을 해결합니다.

문제 해결 가이드

Azure Database for PostgreSQL 유연한 서버 포털에서 사용할 수 있는 기능 문제 해결 가이드를 사용하여 높은 IOPS 사용률 시나리오 완화에 대한 가능한 근본 원인 및 권장 사항을 찾을 수 있습니다. 이를 사용하도록 문제 해결 가이드를 설정하는 방법은 설정 문제 해결 가이드를 따르세요.

높은 I/O 사용률 식별 도구

높은 I/O 사용률을 식별하려면 다음 도구를 고려하세요.

Azure Metrics

Azure Metrics는 한정된 날짜 및 기간에 대한 I/O 사용률을 확인하기 위한 좋은 시작점입니다. 메트릭은 I/O 사용률이 높은 기간에 대한 정보를 제공합니다. 쓰기 IOP, 읽기 IOP, 읽기 처리량 및 쓰기 처리량 그래프를 비교하여 워크로드로 인해 I/O 사용률이 높아진 경우를 파악합니다. 사전 모니터링을 위해 Metrics에 경고를 구성할 수 있습니다. 단계별 지침은 Azure Metrics를 참조하세요.

쿼리 저장소

쿼리 데이터 저장소 기능은 쿼리 및 런타임 통계의 기록을 자동으로 캡처하고 검토를 위해 보존합니다. 시간별 사용 패턴을 볼 수 있도록 데이터를 시간별로 분할합니다. 모든 사용자, 데이터베이스 및 쿼리에 대한 데이터는 Azure Database for PostgreSQL 유연한 서버 인스턴스의 azure_sys라는 데이터베이스에 저장됩니다. 단계별 지침은 쿼리 데이터 저장소로 성능 모니터링을 참조하세요.

다음 문을 사용하여 I/O를 사용하는 상위 5개 SQL 문을 봅니다.

select * from query_store.qs_view qv where is_system_query is FALSE
order by blk_read_time + blk_write_time  desc limit 5;

pg_stat_statements 확장

pg_stat_statements 확장은 서버에서 I/O를 소비하는 쿼리를 식별하는 데 도움이 됩니다.

다음 문을 사용하여 I/O를 사용하는 상위 5개 SQL 문을 봅니다.

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time desc
LIMIT 5;

근본 원인 식별

I/O 소비 수준이 전반적으로 높으면 다음과 같은 근본 원인이 있을 수 있습니다.

장기 실행 트랜잭션

장기 실행 트랜잭션은 I/O를 사용할 수 있으므로 I/O 사용률이 높아질 수 있습니다.

다음 쿼리는 가장 오랫동안 실행되는 연결을 식별하는 데 도움이 됩니다.

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

검사점 타이밍

높은 I/O는 검사점이 너무 자주 발생하는 시나리오에서도 볼 수 있습니다. 이를 식별하는 한 가지 방법은 Azure Database for PostgreSQL 유연한 서버 로그 파일에서 "LOG: 검사점이 너무 자주 발생합니다."라는 로그 텍스트를 확인하는 것입니다.

타임스탬프를 사용하여 pg_stat_bgwriter의 정기적인 스냅샷을 저장하는 방법을 사용하여 조사할 수도 있습니다. 저장된 스냅샷을 사용하여 평균 검사점 간격, 요청된 검사점 수 및 시간이 지정된 검사점 수를 계산할 수 있습니다.

중단 자동 진공 디먼 프로세스

다음 쿼리를 실행하여 자동 진공을 모니터링합니다.

SELECT schemaname, relname, n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_all_tables WHERE n_live_tup > 0;

이 쿼리는 데이터베이스의 테이블이 진공되는 빈도를 확인하는 데 사용됩니다.

• last_autovacuum: 테이블에서 마지막 자동 진공이 실행된 날짜 및 시간입니다.
• autovacuum_count: 테이블이 진공된 횟수입니다.
• autoanalyze_count: 테이블이 분석된 횟수입니다.

높은 I/O 사용률 해결

높은 I/O 사용률을 해결하려면 다음 세 가지 방법 중 하나라도 사용할 수 있습니다.

EXPLAIN ANALYZE 명령

높은 I/O를 사용하는 쿼리를 식별한 후 EXPLAIN ANALYZE를 사용하여 쿼리를 자세히 조사하고 조정합니다. EXPLAIN ANALYZE 명령에 대한 자세한 내용은 EXPLAIN 계획을 검토하세요.

장기 실행 트랜잭션 종료

선택적으로 장기 실행 트랜잭션을 종료하는 것도 고려할 수 있습니다.

세션 PID(프로세스 ID)를 종료하려면 다음 쿼리를 사용하여 PID를 검색해야 합니다.

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

또한 usename(사용자 이름), datname(데이터베이스 이름) 등의 다른 속성을 기준으로 필터링할 수도 있습니다.

세션의 PID가 있으면 다음 쿼리를 사용하여 종료할 수 있습니다.

SELECT pg_terminate_backend(pid);

서버 매개 변수 조정

검사점이 너무 자주 발생하는 것으로 확인되면 대부분의 검사점이 요청 없이 시간에 따라 진행될 때까지 max_wal_size 서버 매개 변수를 늘립니다. 결과적으로 90% 이상이 시간에 따라 진행되며, 두 검사점 사이의 간격은 서버에 설정된 checkpoint_timeout 값에 가까워집니다.

• max_wal_size: 가장 바쁜 업무 시간은 max_wal_size 값에 도달하기에 적합한 시간입니다. 값에 도달하려면 다음을 수행합니다.

  1. 다음 쿼리를 실행하여 현재 WAL LSN을 가져온 후 결과를 확인합니다.

     select pg_current_wal_lsn();
     

  2. 몇 checkpoint_timeout초 동안 기다립니다. 다음 쿼리를 실행하여 현재 WAL LSN을 가져온 후 결과를 확인합니다.

     select pg_current_wal_lsn();
     

  3. 두 결과를 사용하는 다음 쿼리를 실행하여 기가바이트(GB) 크기의 차이를 확인합니다.

     select round (pg_wal_lsn_diff ('LSN value when run second time', 'LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;
     

• checkpoint_completion_target: 값을 0.9로 설정하는 것이 좋습니다. 예를 들어 5분의 checkpoint_timeout 동안 값 0.9는 검사점 완료 목표가 270초(0.9*300초)임을 나타냅니다. 값 0.9는 상당히 일관된 I/O 로드를 제공합니다. checkpoint_completion_target에 대해 공격적인 값을 지정하면 서버의 I/O 로드가 증가할 수 있습니다.

• checkpoint_timeout: 서버에 설정된 기본값에서 checkpoint_timeout 값을 늘릴 수 있습니다. 이 값을 늘리면 크래시 복구 시간도 늘어나게 된다는 사실을 고려하세요.

중단을 줄이도록 자동 진공 조정

자동 진공이 너무 많이 중단되는 시나리오의 모니터링 및 조정에 대한 자세한 내용은 자동 진공 조정을 검토하세요.

스토리지 늘리기

스토리지를 늘리면 서버에 더 많은 IOPS를 추가할 때 도움이 됩니다. 스토리지 및 관련 IOPS에 대한 자세한 내용은 컴퓨팅 및 스토리지 옵션을 검토하세요.

관련 콘텐츠

• 자동 진공 문제 해결 및 튜닝
• 컴퓨팅 및 스토리지 옵션