CPU 사용률이 높으면 인스턴스 성능에 부정적인 영향을 줍니다. 인스턴스에서 수행된 모든 활동이 CPU를 사용합니다. 따라서 CPU 사용률이 높은 경우 먼저 잘못 작성된 쿼리, 장기 실행 트랜잭션 또는 기타 데이터베이스 활동 등 문제의 근본 원인을 식별해야 합니다.
이 문서에서는 인스턴스에서 CPU 병목 현상을 식별하고 인스턴스의 CPU 사용률 문제를 완화하는 방법을 설명합니다.
CPU 병목 현상 식별
쿼리 통계를 사용하여 CPU 소비가 많은 쿼리 식별
쿼리 통계는 Cloud SQL 데이터베이스의 쿼리 성능 문제를 감지하고 진단하고 방지하는 데 도움이 됩니다.
pg_proctab 확장 프로그램 사용
pg_top 유틸리티와 함께 pg_proctab 확장 프로그램을 사용하여 프로세스당 CPU 사용률 정보를 제공하는 운영체제 출력을 가져옵니다.
쿼리 사용
상태별 활성 연결 식별
데이터베이스에 대한 활성 연결마다 일정량의 CPU를 사용하므로 인스턴스의 연결 수가 많으면 누적 사용률이 높을 수 있습니다. 다음 쿼리를 사용하여 상태별 연결 수에 대한 정보를 가져옵니다.
SELECT
state,
usename,
count(1)
FROM
pg_stat_activity
WHERE
pid <> pg_backend_pid()
group by
state,
usename
order by
1;
결과는 다음과 유사합니다.
state | usename | count
---------------------+---------------+-------
active | ltest | 318
active | sbtest | 95
active | | 2
idle | cloudsqladmin | 2
idle in transaction | ltest | 32
idle in transaction | sbtest | 5
| cloudsqladmin | 3
| | 4
(8 rows)
활성 연결 수가 많은 경우 장기 실행 쿼리 또는 쿼리 실행을 차단하는 대기 이벤트가 있는지 확인합니다.
유휴 연결 수가 많으면 필요한 승인을 받은 후 다음 쿼리를 실행하여 연결을 종료합니다.
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
usename = 'sbtest'
and pid <> pg_backend_pid()
and state in ('idle');
다음 쿼리를 사용하여 pg_terminate_backend로 연결을 개별적으로 종료할 수도 있습니다.
SELECT pg_terminate_backend (<pid>);
여기에서 pg_stat_activity의 PID를 가져올 수 있습니다.
장기 실행 연결 식별
다음은 장기 실행 쿼리를 반환하는 쿼리의 예시입니다. 이 경우 5분 넘게 활성화된 쿼리를 식별할 수 있습니다.
SELECT
pid,
query_start,
xact_start,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM
pg_stat_activity
WHERE
(
now() - pg_stat_activity.query_start
) > interval '5 minutes' order by 4 desc;
설명 계획을 검토하여 잘못 작성된 쿼리 식별
EXPLAIN PLAN을 사용하여 잘못 작성된 쿼리를 조사하고 필요한 경우 쿼리를 다시 작성합니다. 원하는 경우 필요한 승인을 받아 다음 명령어로 장기 실행 쿼리를 취소해도 됩니다.
SELECT pg_cancel_backend(<pid>);
VACUUM 활동 모니터링
비활성 튜플을 삭제하는 AUTOVACUUM 활동은 CPU를 많이 사용하는 작업입니다. 인스턴스에서 PostgreSQL 버전 11 이상을 사용하는 경우 다음 쿼리를 사용하여 진행 중인 활성 AUTOVACUUM 또는 VACUUM 활동이 있는지 확인합니다.
SELECT
relid :: regclass,
pid,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count,
max_dead_tuples,
num_dead_tuples
FROM
pg_stat_progress_vacuum;
다음 쿼리를 사용하여 인스턴스에 진행 중인 VACUUM 활동이 있는지 확인합니다.
SELECT
pid,
datname,
usename,
query
FROM
pg_stat_activity
WHERE
query like '%vacuum%';
또한 PostgreSQL에서 VACUUM 작업을 최적화하고 문제를 해결할 수 있습니다.
pg_stat_statements 확장 프로그램 추가
인스턴스 활동에 대한 향상된 딕셔너리 정보를 가져오려면 pg_stat_statements 확장 프로그램을 설정합니다.
잦은 체크포인트
체크포인트를 자주 사용하면 성능이 저하됩니다. PostgreSQL 알림 로그에서 checkpoint occurring too frequently 경고를 보고하는 경우 checkpoint_timeout 플래그를 조정하는 것이 좋습니다.
통계 수집
가장 적합한 쿼리 계획을 선택할 수 있도록 쿼리 플래너에 테이블에 대한 최신 통계가 있어야 합니다. ANALYZE 작업은 데이터베이스의 테이블 콘텐츠에 대한 통계를 수집하고 결과를 pg_statistic 시스템 카탈로그에 저장합니다. 이후 쿼리 플래너가 이러한 통계를 사용하여 쿼리에 가장 효율적인 실행 계획을 결정합니다. AUTOVACUUM 프로세스는 테이블을 주기적으로 자동으로 분석하므로 다음 명령어를 실행하여 모든 테이블이 분석되었고 플래너에 최신 메타데이터가 제공되는지 확인합니다.
SELECT
relname,
last_autovacuum,
last_autoanalyze
FROM
pg_stat_user_tables;
부적절한 시스템 설정
그 밖에도 여러 요인과 플래그 설정 또는 시스템 요인이 쿼리 성능에 영향을 미칩니다. 다른 시스템 설정의 성능에 대한 통계를 얻기 위해 대기 이벤트 및 대기 이벤트 유형을 확인하려면 다음 쿼리를 실행합니다.
SELECT
datname,
usename,
(
case when usename is not null then state else query end
) AS what,
wait_event_type,
wait_event,
backend_type,
count(*)
FROM
pg_stat_activity
GROUP BY
1,
2,
3,
4,
5,
6
ORDER BY
1,
2,
3,
4 nulls first,
5,
6;
결과는 다음과 비슷하게 표시됩니다.
.. | .. | what | wait_event_type | wait_event | .. | count
-..--+-..-+----------------+-----------------+----------------------+-..----+------
..
.. | .. | active | IO | CommitWaitFlush | .. | 750
.. | .. | idle | IO | CommitWaitFlush | .. | 360
.. | .. | active | LWLock | BufferMapping | .. | 191
순차적 스캔 모니터링
행이 수십 개 이상 있는 테이블에서 순차적 스캔이 자주 발생한다면 일반적으로 누락된 색인을 의미합니다. 스캔에서 수천 또는 수십만 개의 행을 처리하면 CPU가 과도하게 사용될 수 있습니다.
행이 수십만 개 있는 테이블에서 순차적 스캔이 자주 발생하면 CPU가 과도하게 사용될 수 있습니다. 필요한 색인을 만들어 이러한 테이블의 순차적 스캔을 방지하세요.
다음 쿼리를 실행하여 모든 테이블에서 순차적 스캔이 시작되는 횟수를 확인합니다.
SELECT
relname,
idx_scan,
seq_scan,
n_live_tup
FROM
pg_stat_user_tables
WHERE
seq_scan > 0
ORDER BY
n_live_tup desc;
마지막으로 CPU가 여전히 높고 쿼리가 타당한 트래픽이라고 생각되면 인스턴스의 CPU 리소스를 늘려 데이터베이스 비정상 종료나 다운타임을 방지하세요.