느린 데이터베이스 성능

이 문제의 전형적인 증상은 다음과 같습니다:

• 높은 디스크 입출력(I/O) 또는 메모리 사용량
• 느린 쿼리

가장 흔한 원인은 느린 쿼리가 조사되고 있기 때문입니다. 다음 단계를 완료하여 해당 사항인지 확인하십시오:

1. 장시간 실행 중인 쿼리를 나열하십시오:

   select age(now(),query_start), pid, client_addr, state, substring(query,0,80) from pg_stat_activity where state != 'idle' order by 1 desc;
   

2. 쿼리 차단 검증:

   select pg_blocking_pids(pid), pid AS blocked_pid, state, query, age(now(),query_start) AS duration from pg_stat_activity where array_length(pg_blocking_pids(pid),1) > 0 order by 1,2;
   

3. 쿼리 실행 계획을 확인하십시오:

   Explain <query>;
   

4. 데이터베이스 통계가 누락되었는지 확인하십시오. 다음 쿼리를 사용하여 유효성 검사: 작업 > 분석 실행 또는 다음 명령을 실행하십시오:

   Analyze <tablename>  select relname, n_live_tup, n_dead_tup, last_vacuum,last_autovacuum,last_analyze,last_autoanalyze, analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='public';
   

5. 테이블에서 인덱스가 누락되었는지 확인하십시오. PostgreSQL B-트리, 해시, GiST,SP-GiST, GIN 및 BRIN과 같은 인덱스 메서드를 제공합니다. 다음 방법 중 하나를 사용하여 색인을 생성합니다.

6. 테이블이 부풀어 올랐는지 확인하십시오. 다음 쿼리를 실행하여 검증하십시오. 작업 > 진공 청소 실행

   또는 다음 문을 사용하십시오:

   SELECT
   relname AS table_name,
   pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
   pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24))) * 100 / pg_relation_size(c.oid)
   END
   ) AS bloat_percentage, pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24)))
   END
   )AS bloat_size
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r' AND n.nspname = 'public' -- Adjust schema if needed
   ORDER BY bloat_size DESC NULLS LAST;
   

7. 데이터베이스 또는 객체가 또는 pg_dump 로 생성되었는지 pg_restore 고려하십시오. 작업 > 통계 업데이트를 사용하여 새로 고침하십시오.

8. 쿼리가 정렬 작업을 완료하는 경우 충분한 메모리가 work_mem 할당되도록 하십시오.

9. 다음 정보를 확인하기 위해 명령문을 pg_stat 사용하십시오:

   • 평균 실행 시간 및 호출 횟수 조회

   • 쿼리별 CPU 사용률

   • 쿼리별 메모리 사용률

pg_stat_activity, pg_stat_bgwriter, 및 pg_buffercache 는 데이터베이스 성능을 모니터링하고 이해하는 데 있어 에서 PostgreSQL 중요한 도구입니다.

배포 모니터링 및 데이터베이스 부하 모니터링

Databases for PostgreSQL 배포는 배포의 리소스 사용량 모니터링을 위한 IBM Cloud Monitoring 서비스와 통합하여 배포의 리소스 사용량을 모니터링할 수 있습니다. 이 서비스를 사용하여 배포(디스크 및 메모리)와 데이터베이스 부하를 모니터링하십시오.

대시보드를 Cloud Databases 사용하여 CPU, 메모리 및 디스크 IOPS 임계값에 대한 경보를 설정하십시오. 디스크 사용량 및 IOPS와 같이 사용 가능한 많은 메트릭은 배포에서 자동 확장을 구성하는 데 유용합니다. 자동 스케일링은 기본적으로 활성화되어 있지 않으므로 수동으로 구성해야 합니다.

사용량 추세를 관찰하고 이러한 추세에 대응하도록 자동 확장을 구성하면 리소스 고갈로 인해 데이터베이스가 불안정해지기 전에 성능 문제를 완화하는 데 도움이 됩니다. 예를 들어, 디스크 I/O 변경이나 CPU 또는 메모리 오류로 인해 발생하는 성능 문제.

디스크 IOPS

초당 입/출력 작업 수(IOPS)는 스토리지 볼륨의 유형과 크기에 따라 제한됩니다. 배포용 Databases for PostgreSQL 스토리지 볼륨은 GB당 10 IOPS 계층의 내구성 Block Storage 볼륨 에 프로비저닝됩니다.

운영 부하가 포화되거나 IOPS 한도를 초과하면 스토리지 하위 시스템이 따라잡을 수 있을 때까지 데이터베이스 요청 및 작업이 지연됩니다. 과부하가 장기간 지속되면 배포가 쿼리를 처리할 수 없게 되어 사실상 사용할 수 없게 될 수 있습니다. 디스크 크기를 늘려 배포에 사용할 수 있는 IOPS 수를 늘릴 수 있습니다. 예를 들어, GB당 10 IOPS 계층의 경우 볼륨 크기를 늘려 IOPS를 증가시킬 수 있습니다.

디스크 사용률이 40~50%에 달하는 상태가 장기간 지속될 경우 데이터베이스 성능에 심각한 부정적 영향을 미칠 수 있습니다. 프로덕션 환경에는 최소 100GB 디스크(1,000 IOPS)를 할당하세요. IOPS = 할당된 GB × 10 (예: 100 GB = 1,000 IOPS)

메모리 사용량

메모리는 데이터에 접근하고 처리하는 가장 빠르고 효율적인 방법이다. 이 때문에 데이터베이스는 거의 항상 디스크에서 데이터를 읽는 것보다 메모리를 사용하는 것이 더 빠르게 동작합니다. 캐시 적중률, 읽은 블록 수, 적중 블록 수 등 살펴보고 고려해야 할 다른 지표들이 있습니다. 메모리 사용량이 100%라는 사실 자체만으로는 우려할 필요가 없습니다. 메모리가 고갈되어 스왑 페이지가 사용되면 성능이 크게 저하될 수 있습니다.

Databases for PostgreSQL 구성에서 shared_buffers를 조정하여 데이터베이스의 공유 버퍼 풀 전용 메모리 양을 설정할 수 있습니다. 최대 권장 값은 배포 전체 메모리의 25%입니다. 공유 버퍼 풀에 너무 많은 메모리를 할당하면 다른 용도의 메모리가 고갈되거나 성능이 저하되거나 심지어 데이터베이스가 비활성화될 수 있습니다.

데이터베이스 부하 모니터링

데이터베이스 부하를 확인하는 두 가지 방법이 있습니다:

{

source logs|filter message.attr.durationMillis>=1000

}

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT username, database, queryid, query_preview, calls, total_exec_time, pct_exec_time, ROUND(SUM(pct_exec_time) OVER (ORDER BY total_exec_time DESC)) AS cum_pct_exec_time, avg_exec_time FROM ( SELECT pu.usename AS username, pd.datname AS database, pss.queryid, LEFT(pss.query, 50) AS query_preview, pss.calls, ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time, ROUND((100.0 * pss.total_exec_time / SUM(pss.total_exec_time) OVER ())::numeric, 2) AS pct_exec_time, ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time FROM pg_stat_statements pss JOIN pg_user pu ON pss.userid = pu.usesysid JOIN pg_database pd ON pss.dbid = pd.oid ) AS subquery ORDER BY total_exec_time DESC LIMIT 25;

SELECT
   username,
   database,
   queryid,
   query_preview,
   calls,
   pct_calls,
   ROUND(SUM(pct_calls) OVER (ORDER BY calls DESC)) AS cum_pct_calls,
   total_exec_time,
   avg_exec_time
FROM (
   SELECT
      pu.usename AS username,
      pd.datname AS database,
      pss.queryid,
      LEFT(pss.query, 50) AS query_preview,
      pss.calls,
      ROUND(100.0 * pss.calls / SUM(pss.calls) OVER (), 2) AS pct_calls,
      ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time,
      ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time
   FROM
      pg_stat_statements pss
      JOIN pg_user pu ON pss.userid = pu.usesysid
      JOIN pg_database pd ON pss.dbid = pd.oid
) AS subquery
ORDER BY
   calls DESC
LIMIT 25;

SELECT query, calls, total_exec_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

성능 향상을 위해 취할 수 있는 추가 조치

성능 문제를 해결하기 위해 다음 조치도 고려해 볼 수 있습니다:

• 느린 쿼리 최적화

  • 느린 쿼리에 대해 EXPLAIN (ANALYZE, BUFFERS)을 실행하십시오. 데이터베이스가 쿼리를 실행하는 방식과 속도가 느린 부분을 보여줍니다. 예를 들어, 다음과 같습니다.

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM student WHERE student_id = 12345;
  

  • 다음을 찾으십시오.

    • 메모리나 디스크 공간을 많이 사용하는 쿼리

    • 필요에 따라 리소스를 IBM Cloud 추가하십시오

    • 디스크/메모리를 확장하여 더 높은 IOPS 및 메모리 성능 달성

• 누락되거나 비효율적인 인덱스는 쿼리 속도 저하의 흔한 원인입니다. EXPLAIN을 사용하여 순차 스캔을 식별하고 인덱스 추가를 고려하십시오.

• 데이터베이스 상태 분석을 위해 VACUUM을 실행하십시오.

• Databases for PostgreSQL 은 Databases for PostgreSQL 데이터베이스에 대한 최대 연결 수를 115개로 설정합니다. 데이터베이스의 상태와 무결성을 유지하기 위해 수퍼유저를 위해 15개의 연결이 예약되어 있으며, 사용자와 애플리케이션을 위해 100개의 연결을 사용할 수 있습니다. 연결 제한에 도달한 후에는 새 연결을 시작하려고 시도하면 오류가 발생합니다. 배치에 연결이 과도하게 많아지지 않도록 하려면 연결 풀링을 사용하거나 배치를 스케일링하고 연결 한계를 늘리십시오. 자세한 내용은 연결 풀 PostgreSQL 관리 항목을 참조하십시오.

• 백업 및 데이터 일괄 업로드 작업을 실행 중인 레시피가 있는지 확인하십시오. 예를 들어: 자동 백업은 매일 완료되며 30일이라는 간단한 보존 기간 일정으로 보관됩니다. 백업이 중단된 경우, 데이터베이스 인스턴스의 클라우드 UI 페이지에서 '사용 가능한 백업' 섹션을 확인하여 중단된 백업을 식별할 수 있습니다.

• 유지보수 관련 IBM Cloud 알림이 있는지 확인하세요. 예를 들어, 데이터베이스 패치 작업.

• 이 문제가 유지보수 등 플랫폼 관련 문제라고 생각되면, 데이터베이스 CRN과 함께 지원팀에 IBM 문의하십시오.