아래스크립트는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 서버와는독립적인스토리지계층에서일어난다.
