아래스크립트는3초이상실행된쿼리목록을확인하여, WHERE 부분을수정하여사용자에필요한시간으로변경하여사용할수있다. 조회결과는오래수행된쿼리의내림차순으로표시되며, S3 spectrum을사용할경우외부테이블에대한사용정보도함께나태낸다. 쿼리결과에서elapsed_time은전체쿼리가수행에걸린시간을나타내므로해당시간이클수록느린쿼리라고판단할수있다.
select a.query_id, a.database_name, a.query_type, a.start_time, a.end_time, (a.elapsed_time * 1.0)/ 1000 / 1000 as elapsed_time_sec, (a.execution_time * 1.0)/ 1000 / 1000 as execution_time_sec, a.returned_rows, a.returned_bytes, a.query_text, b.source_type,-- Spectrum : S3, 연합쿼리 : PG b.duration as external_query_duration, b.total_partitions as s3_partition, b.qualified_partitions as s3_scan_partiton, b.scanned_files as s3_scan_file, b.returned_rows as s3_returned_rows, b.returned_bytes as s3_returned_bytes, b.file_formatas s3_file_formant, b.file_location as s3_file_location, b.external_query_text as external_query_text, a.result_cache_hit, (a.queue_time * 1.0)/ 1000 /1000as queue_time_sec, a.status, a.error_message, a.user_id, a.transaction_id, a.session_id from SYS_QUERY_HISTORY as a leftouterjoin SYS_EXTERNAL_QUERY_DETAIL as b on a.query_id = b.query_id where (a.elapsed_time * 1.0)/ 1000 / 1000 > 3 -- 마이크로세컨을 세컨으로 계산하도록 변경. 숫자 변경하여 사용 and a.statusin('success','canceled')-- 사용자 쿼리가 성공 또는 사용자에 의한 취소 쿼리만 조회 /* 사용 가능 status value planning, queued, running, returning, failed, canceled, success */ and a.start_time >='2023-01-11 01:00'-- UTC 시간 and a.end_time <='2023-01-11 23:00'-- UTC 시간 orderby elapsed_time desc
Select trunc(start_time) "Day", sum(compute_seconds)/60/60 as sum_compute_seconds, (sum(compute_seconds)/60/60) * 0.438 as price -- <Price for 1 RPU> from sys_serverless_usage group by trunc(start_time) order by 1
Redshift Serverless는사용한만큼비용이발생하는구조이지만, 쿼리가실패하였을경우에는비용이발생하지않는다. 하지만사용자요청에의해취소되는작업에대해서는취소될때까지사용된리소스에대해서는비용이발생한다. 따라서, 실행중인쿼리를캔슬하거나쿼리타임아웃으로인해쿼리가취소되지않도록시간을적절히조절하여의도하지않은불필요한비용이발생하지않도록한다.
-- DB 전체 full vacuum vacuum full analyze; -- DB 전체간단하게실행 vacuum verbose analyze; -- 특정테이블만간단하게실행 vacuum analyze [테이블명]; -- 특정테이블만 full vacuum vacuum full [테이블명];
SELECT n.nspname AS schema_name, c.relname AS table_name, pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple, pg_stat_get_live_tuples(c.oid) AS live_tuple, pg_stat_get_dead_tuples(c.oid) AS dead_tupple, round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate, round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate, pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size, pg_size_pretty(pg_relation_size(c.oid)) as relation_size FROM pg_class AS c JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE pg_stat_get_live_tuples(c.oid) > 0 AND c.relname NOT LIKE 'pg_%' ORDER BY dead_tupple DESC;
아래쿼리는Vacuum 통계정보를확인한다.
SELECT * FROM pg_stat_all_tables ORDER BY schemaname, relname;
Vacuum FULL 실행시pg_class의relfilenode 값이변경된다. 아래쿼리는relfilenode의물리적인파일위치를확인한다.
SELECT oid, pg_relation_filepath(oid), relname, relfilenode FROM pg_class LIMIT 10;
아래쿼리는현재실행중인Vacuum세션정보를확인할수있다.
SELECT datname, usename, pid, CURRENT_TIMESTAMP - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
SELECT relname AS TableName ,n_live_tup AS LiveTuples ,n_dead_tup AS DeadTuples ,last_autovacuum AS Autovacuum ,last_autoanalyze AS Autoanalyze FROM pg_stat_user_tables;
테이블에서Vacuum을실행하려면Autovacuum 프로세스가 SHARE UPDATE EXCLUSIVE 잠금을획득해야하는데, 이는두트랜잭션이동시에 SHARE UPDATE EXCLUSIVE 잠금을보유할수없기때문에다른잠금과충돌한다. 이는 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 및 ACCESS EXCLUSIVE와같은다른잠금모드에서도동일하다.
SHARE UPDATE EXCLUSIVE 잠금은 SELECT, UPDATE, INSERT 또는 DELETE를차단하지않으며아래잠금이있는트랜잭션만차단한다.
lSHARE UPDATE EXCLUSIVE – Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, and certain ALTER INDEX and ALTER TABLE variants.
lSHARE – Acquired by CREATE INDEX (without CONCURRENTLY).
lSHARE ROW EXCLUSIVE – Acquired by CREATE TRIGGER and some forms of ALTER TABLE.
lEXCLUSIVE – Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.
lACCESS EXCLUSIVE – Acquired by DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of ALTER INDEX and ALTER TABLE also acquire a lock at this level.
Autovacuum action skipped due long-running transactions
PostgreSQL은 MVCC 개념을기반으로하기때문에하나이상의트랜잭션이오래된버전의데이터에액세스하는경우Autovacuum 프로세스는데드튜플을정리하지않는다. 데이터가삭제되거나업데이트되기전에생성된데이터의스냅샷에서트랜잭션이작업중인경우Autovacuum은해당데드튜플을건너뛰고해당데드튜플은다음반복에서Vacuum 된다. 이런케이스는일반적으로데이터베이스의장기실행트랜잭션에서발생한다. 데이터베이스에서장기실행트랜잭션을찾으려면아래쿼리를실행한다. 예제쿼리는5분이상실행되고있는쿼리를나타낸다.
SELECT now()-query_start as Running_Since, pid, datname, usename, application_name, client_addr , left(query,60) FROM pg_stat_activity WHERE state in ('active','idle in transaction') AND (now() - query_start) > interval '5 minutes';
Autovacuum은데드튜플을건너뛰게할수있으므로모니터링의일부로트랜잭션세션의유휴상태(idle in transaction)를포함하는것이좋다.
*주의* Aurora MySQL 병렬쿼리의아키텍처는다른데이터베이스시스템에서이름이유사한기능의아키텍처와다르다. Aurora MySQL 병렬쿼리는SMP(Symmetric Multi Processing)를포함하지않아, 데이터베이스의CPU 용량에의존하지않는다. 병렬처리는쿼리조정자역할을하는Aurora MySQL 서버와는독립적인스토리지계층에서일어난다.
[AWS Aurora] Aurora Global Database를사용한Cross-Region 장애조치
lVersion : AWS Aurora
Amazon Aurora Global Database는여러AWS 리전에걸쳐있고대기시간이짧은글로벌읽기를지원하기때문에, 전세계지역에서실행되는애플리케이션들은가까운리전의Aurora Global Database에서서비스를제공받기때문에안정적이며빠른서비스를제공할수있다. 또한Aurora Global Database는데이터를마스터링하는하나의기본AWS 리전및최대5개의읽기전용보조AWS 리전으로구성되기때문에, AWS 리전에서장애발생시신속하게다른리전으로복구할수있다. Aurora Global Database는아래와같은장점을가지고있다.
l로컬대기시간으로글로벌읽기: 전세계에지사를두고있는경우, Aurora Global Database를사용하여기본 AWS 리전에서정보의주요소스를최신상태로유지할수있다. 다른리전의사무실은로컬지연시간을사용하여해당리전의정보에액세스할수있다.
l관리형계획장애조치: 기본 DB 클러스터를 Aurora 글로벌데이터베이스의보조리전중하나로재배치한다. 이기능을사용하면 RPO가 0(데이터손실없음)이고다른변경을수행하기전에보조 DB 클러스터를기본클러스터와동기화한다. 이자동화된프로세스의 RTO는일반적으로수동장애조치의 RTO보다적다.
AWS에는리전이라는개념이있다. AWS가전세계에서데이터센터를클러스터링하는물리적위치를리전이라고한다. 논리적데이터센터의각그룹을가용영역이라고하는데, 각 AWS 리전은지리적영역내에서격리되고물리적으로분리된여러개의 AZ로구성된다. 각 AZ는독립된전원, 냉각및물리적보안을갖추고있으며지연시간이매우짧은중복네트워크를통해연결된다.
(아래그림에서설명은현재와맞지않으므로그림만참고한다.)
AWS는북미, 남미, 유럽, 중국, 아시아태평양, 남아프리카및중동의리전을포함하여여러지리적리전을유지관리하고있다. 리전은추가또는변경될수있으므로최신의리전정보는아래링크를참고한다.
DevOps Guru for RDS를사용하기위해서는Amazon Console에서RDS 성능개선도우미(Performance Insight)를활성화후DevOps Guru 콘솔로이동하여활성화한다.
RDS용 DevOps Guru는데이터베이스로드(DB Load) 성능메트릭에서이상감지를사용하여문제를감지한다. DB 로드는 AAS(Average Active Sessions) 단위로측정된다. DB 로드는데이터베이스의활동수준을측정하므로DB 부하가높으면성능문제가발생할수있다. 이메트릭은가상 CPU(vCPU) 수와비교할수있으며, DB 부하가이수보다높으면문제가발생할수있다.
아래그림은DevOps Guru for RDS리포트결과로, 그래프는 AAS에서대부분이테이블또는 CPU에대한액세스를기다리고있음을보여준다. 대기이벤트는현재실행중인 SQL이기다리고있는상태로가장일반적인이유는 CPU를기다리거나읽기또는쓰기를기다리거나잠긴리소스를기다리는상태이다. Top SQL 차원은 DB 로드에가장많이기여하는쿼리를보여준다.
DevOps Guru for RDS 분석페이지에서는문제의원인과해결을위한몇가지권장사항도보여주는데위메트릭에서의이상징후는높은로드대기이벤트와 CPU 용량초과라는두가지문제가감지되었다. 그리고아래와같은분석결과는나타내었다.
lIO 및 CPU 대기유형에대한27개의 AAS가있는고부하대기이벤트를가있으며전체 DB 로드의 99%이다.
l실행중인작업이 6개프로세스를초과했음을알려준다. 이데이터베이스에는 2개의 vCPU만있으며권장되는실행프로세스수는최대 4개(vCPU 2개)여야한다.
대시보드화면의데이터베이스로드(Database load) 차트에서는병목현상에대한정보를확인할수있다. 어떤데이터베이스로그가최대CPU(Max CPU) 선을상회하는지확인할수있고어떤작업이DB 부하를차지하는지보여준다. 아래그림에서는로그파일동기화대기시간이대부분의 DB 부하를차지한다. 그리고LGWR all worker groups 대기시간도높다. TOP SQL 차트는로그파일동기화대기의원인에사용된SQL 구문인COMMIT 문을보여준다.
TOP SQL 에서는데이터베이스로드에영향을미치는상위SQL 쿼리가표시된다. TOP SQL 탭에서는SQL 통계(SQL Statistics) 대기별로드(AAS), SQL 정보, 환경설정정보등을확인할수있다.
