이 글은 Database 블로그의 Monitor query plans for Amazon Aurora PostgreSQL 을 한국어 번역 및 편집하였습니다.
Amazon Aurora PostgreSQL 호환 버전이 한층 더 강화되었습니다. 이제 사용자들은 쿼리 플랜을 자유롭게 관리할 수 있게 되었습니다. 이 기능을 통해 현재 데이터베이스 부하에 기여하는 쿼리 플랜을 식별하고, 시간이 지남에 따라 쿼리 플랜의 성능 통계를 추적할 수 있습니다.
이 글에서는 최적의 데이터베이스 성능을 유지하기 위해 쿼리 플랜을 모니터링하는 방법을 보여주고 쿼리 플랜 모니터링 기능의 주요 사용 사례를 논의합니다.
효율적인 쿼리 플랜은 데이터베이스 성능의 중요한 부분이기 때문에 Amazon Aurora PostgreSQL 호환 버전은 위한 Amazon RDS Performance Insights 지원과 같은 기능을 추가했습니다. 이는 데이터베이스 모니터링 기능을 제공하며, Aurora PostgreSQL 쿼리 플랜 관리는 최적의 데이터베이스 성능을 유지하기 위한 안정성과 적응성을 제공합니다.
솔루션 개요
Amazon Aurora PostgreSQL 버전 14.10, 15.5 및 그 이상의 버전에서는 쿼리 플랜 모니터링 기능을 사용할 수 있습니다. 이 기능은 지원되는 버전에서 기본적으로 활성화되어 있으며, aurora_compute_plan_id 파라미터로 제어됩니다. 기본값은 1 (ON)이며, 이를 0 (OFF)로 설정하여 비활성화할 수 있습니다. 또한, 쿼리 플랜을 추적하려면 pg_stat_statements가 shared_preload_libraries에 포함되어 있어야 합니다.
새로운 뷰를 살펴보기 전에 플랜 식별자에 대해 이해하는 것이 중요합니다. 쿼리 식별자와는 별도로, 플랜 식별자는 sql_hash와 플랜의 해시로 구성됩니다. 이는 쿼리가 실행되는 방식을 보다 구체적으로 파악할 수 있게 해줍니다.
다음 함수를 사용하여 쿼리 플랜을 확인하고 모니터링할 수 있습니다:
- aurora_stat_activity – 현재 실행 중인 쿼리의 플랜 식별자를 포함하여 프로세스의 현재 활동에 대한 정보를 제공합니다.
- aurora_stat_plans – 쿼리 식별자에 대한 쿼리 플랜 및 기타 중요한 통계를 반환합니다.
이 함수들과 그 사용법에 대한 자세한 내용은 Aurora PostgreSQL 함수 레퍼런스를 참고하세요.
DB 파라미터 그룹의 다양한 파라미터를 사용하여 쿼리 플랜을 모니터링할 수 있습니다. 자세한 내용은 Aurora PostgreSQL 쿼리 실행 플랜에 대한 파라미터 레퍼런스를 확인하세요.
사전 준비사항
이 글을 따라 실습을 진행하려면 다음과 같은 사전 준비가 필요합니다:
- Aurora PostgreSQL 클러스터가 없는 경우, 하나를 생성하세요. 생성 방법에 대한 자세한 내용은 Aurora PostgreSQL DB 클러스터 생성을 참조하세요.
- Aurora PostgreSQL 인스턴스에 접근하기 위해 PostgreSQL 클라이언트를 설치할 Amazon Elastic Compute Cloud (Amazon EC2) 인스턴스를 생성하세요. EC2 리소스를 생성하고 EC2 인스턴스를 시작하는 방법에 대한 자세한 내용은 EC2 리소스 생성 및 EC2 인스턴스 시작을 참조하세요. 또는 RDS 데이터베이스와 EC2 컴퓨팅 인스턴스 간의 연결을 한 번의 클릭으로 설정할 수 있습니다.
- Amazon Linux 2023의 경우, 모든 SQL 문을 실행하기 위해 psql 명령어 도구를 다운로드 하려면 다음 명령어를 사용합니다:
sudo dnf install postgresql15 postgresql15-server postgresql15-contrib
- 설치가 된 이후 psql 을 사용하여 Aurora PostgreSQL 에 연결할 수 있습니다. 다음 psql 명령을 사용하여 데이터베이스에 연결합니다:
psql --host=<your-db-endpoint> --port=5432 --username=<your-db-username> --dbname=<your-db-name>
여기서 your-db-endpoint, your-db-username, your-db-name을 실제 값으로 대체하면 데이터베이스 비밀번호를 입력하라는 메시지가 표시됩니다.
연결 정보는 Amazon RDS 콘솔의“Connectivity & security” 탭에서 Aurora PostgreSQL 작성자 인스턴스의 엔드포인트와 포트 번호를 확인할 수 있습니다.
그리고 설치한 Aurora PostgreSQL 인스턴스의 default dbname 은 postgres, 그리고 사용자 정보는 AWS Secrets Manager 에서 아래와 같이 확인가능합니다.
만일, Self-Managed 계정을 사용하고싶은 경우에는 RDS 콘솔에서 데이터베이스 인스턴스를 선택 후, 아래와 같이 인스턴스 수정을 통해 Credentials management 에서도 직접 패스워드 설정이 가능합니다.
- 다음과 같이 확인되면 데이터베이스에 연결이 성공된 것입니다:
[ec2-user@ip-yourip~]$ psql --host=<your hostname> --port=5432 --username=<your username> --dbname=<your dbname>
Password for user postgres:
psql (15.8, server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=>
쿼리 플랜 모니터링
이제 Aurora PostgreSQL 의 쿼리 플랜을 모니터링하는 프로세스를 더 자세히 알아보겠습니다. 다음은 전체 과정을 단계별로 설명하여 이해를 돕기 위한 종합적인 가이드입니다:
- 쿼리 플랜을 추적하려면
pg_stat_statements가 shared_preload_libraries에 포함되어 있는지 확인하세요. pg_stat_statements가 초기화되었는지 확인하려면 다음 명령어를 실행합니다:
pg155db=> SELECT setting ~ 'pg_stat_statements'
FROM pg_catalog.pg_settings
WHERE name = 'shared_preload_libraries';
만약 pg_stat_statements 가 shared_preload_libraries 에 포함되어있다면, 다음과 같은 내용이 반환될 것입니다:
aurora_compute_plan_id 파라미터가 default 인 ON으로 세팅되어있는지 확인합니다:
Default 값은 on 으로 설정이 되어있습니다.aurora_stat_plans.with_costs 와 aurora_stat_plans.with_timing 파라미터가 모두 ON 으로 설정되어있는지 확인합니다:
- 클러스터 파라미터 그룹에서 필요한
aurora_stat_plans.with* 파라미터를 ON 이나 1로 세팅합니다:
- pgbench 를 활용하여 샘플 데이터를 로드하세요. 이 글에서는
pgbench를 스케일 팩터 10으로 초기화합니다. 다음 명령어를 사용하여 pgbench 테이블을 생성하고 데이터를 로드 할 수 있습니다:
pgbench -i -h <cluster endpoint> -d <database name> -s 10 -U <user name>
- 다음은
pgbench를 사용하여 120초 동안 64개의 병렬 세션으로 TCP-B 테스트를 실행하는 방법입니다:
pgbench -h aurora-xxxxx.us-east-1.rds.amazonaws.com --protocol=simple -P 30 --time=120 --client=64 --jobs=16 -b tpcb-like pg155db > results_apg_plans.out
pgbench 가 실행되는 동안, 다음 쿼리를 활용하여 다른 세션에서의 aurora_stat_activity 를 확인합니다:
SELECT
count(*),
query_id,
plan_id,
substr(query, 1, 20)
FROM
aurora_stat_activity ()
WHERE
state = 'active'
AND pid <> pg_backend_pid()
GROUP BY
query_id,
plan_id,
substr(query, 1, 20)
ORDER BY
4 DESC;
본 쿼리를 실행하면 다음과 같은 결과가 나옵니다:
count | query_id | plan_id | substr
-------+----------------------+-------------+-----------------------------------------------
16 | -1978808372860228080 | -1987991358 | UPDATE pgbench_tellers SET tbalance = tbalanc
21 | -3571162383692102920 | 1506018018 | UPDATE pgbench_branches SET bbalance = bbalan
1 | 1171103359827119145 | 300482084 | INSERT INTO pgbench_history (tid, bid, aid, d
1 | -7810315603562552972 | | END;
(4 rows)
이처럼 각 쿼리와 쿼리에 할당된 plan_id의 세션 수를 확인할 수 있습니다. 이러한 정보는 현재 실행 중인 쿼리에 대한 쿼리 플랜을 찾는 데 유용합니다. aurora_stat_plans 함수를 사용하여 동일한 plan_id에 대한 설명 플랜을 확인하세요.
postgres=> SELECT calls, query, planid, plan_captured_time, explain_plan, total_exec_time
,min_exec_time
,max_exec_time
,mean_exec_time
FROM aurora_stat_plans(true) WHERE planid=-815866029;
calls | query | planid | plan_captured_time | explain_plan | total_exec_time | min_exec_time | max_exec_time | mean_exec_time
-------+------------------------------------------+------------+------------------------------+--------------+-------------------+---------------+---------------+-------------------
82444 | UPDATE pgbench_tellers SET tbalance = tb.| -815866029 | 2024-10-05 03:40:53.86516+00 | Update on pg.| 2420416.282274987 | 0.026806 | 1170.815968 | 29.35830724218842
|.alance + $1 WHERE tid = $2 | | |.bench_teller.| | | |
| | | |.s (cost=0.1.| | | |
| | | |.4..8.16 rows.| | | |
| | | |.=0 width=0) .| | | |
| | | |.(actual time.| | | |
| | | |.=0.073..0.07.| | | |
| | | |.3 rows=0 loo.| | | |
| | | |.ps=1) +| | | |
| | | | Buffers: s.| | | |
| | | |.hared hit=5 +| | | |
| | | | -> Index .| | | |
| | | |.Scan using p.| | | |
| | | |.gbench_telle.| | | |
| | | |.rs_pkey on p.| | | |
| | | |.gbench_telle.| | | |
| | | |.rs (cost=0..| | | |
| | | |.14..8.16 row.| | | |
| | | |.s=1 width=10.| | | |
| | | |.) (actual ti.| | | |
| | | |.me=0.016..0..| | | |
| | | |.016 rows=1 l.| | | |
| | | |.oops=1) +| | | |
| | | | Inde.| | | |
| | | |.x Cond: (tid.| | | |
| | | |. = 63) +| | | |
| | | | Buff.| | | |
| | | |.ers: shared .| | | |
| | | |.hit=2 | | | |
(1 row)
이는 쿼리가 실행된 횟수, 모든 실행의 총 실행 시간, 쿼리의 최소 및 최대 실행 시간을 포함한 쿼리 플랜을 보여줍니다.
사용 사례 1 : QPM 자동 캡처와의 통합
대부분의 경우, 쿼리 플랜 관리(QPM) 기능은 자동 플랜 캡처로 설정되어 있으며, 이는 두 번 이상 실행된 모든 SQL 문에 대한 플랜을 캡처합니다. 그러나 특정 SQL 문 집합을 수동으로 지정하여 플랜을 캡처할 수도 있습니다. 이를 위해 클러스터 파라미터 그룹에서 capture_plan_baselines를 off로 설정하고, 플랜을 수동으로 캡처할 세션에서는 이를 수동으로 설정해야 합니다. QPM이 변경으로 인해 다른 플랜을 식별하면, 이 플랜은 사용 승인을 받아야 합니다. 승인 후, 새로운 플랜은 자동으로 aurora_stat_plans에 통합됩니다. 이 사용 사례를 테스트 하려면 다음 단계를 완료하세요:
- 사용자 지정 DB 클러스터 파라미터 그룹을 열고
rds.enable_plan_management 파라미터를 1로 설정합니다.
- DB 인스턴스에 대해
apg_plan_mgmt 확장을 생성합니다. Aurora PostgreSQL 쿼리 플랜 관리를 활성화하는 방법에 대한 자세한 내용은 관련 문서를 참조하세요:
postgres=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
postgres=> SELECT extname,extversion FROM pg_extension WHERE extname='apg_plan_mgmt';
extname | extversion
---------------+------------
apg_plan_mgmt | 2.7
(1 row)
- 간단한 테이블을 생성하고 데이터를 삽입합니다:
postgres=> CREATE table test_table (id int);
CREATE TABLE
postgres=> INSERT INTO test_table SELECT n FROM generate_series(1, 100000) as n;
INSERT 0 100000
- 쿼리 플랜을 확인하고 Seq Scan으로 이동하는지 확인합니다:
postgres=> EXPLAIN verbose SELECT * FROM test_table WHERE id = 1;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on public.test_table (cost=0.00..1693.00 rows=1 width=4)
Output: id
Filter: (test_table.id = 1)
Query Identifier: 7869618859695600219
(4 rows)
- 승인된 플랜이 있는지 확인하기 위해
apg_plan_mgmt.dba_plans 뷰를 확인합니다:
postgres=> select sql_hash, plan_hash, status, plan_outline from apg_plan_mgmt.dba_plans;
sql_hash | plan_hash | status | plan_outline
----------+-----------+--------+--------------
(0 rows)
- 플랜을 캡처하고 사용하기 위해 다음 파라미터를 설정합니다:
postgres=> SET apg_plan_mgmt.capture_plan_baselines = ON;
SET
postgres=> SET apg_plan_mgmt.use_plan_baselines = ON;
SET
- 쿼리를 실행하고 플랜이 캡처되었는지 확인합니다:
postgres=> SELECT * from test_table where id = 1;
id
----
1
(1 row)
postgres=> SELECT sql_hash, queryid, plan_hash, status, plan_outline from apg_plan_mgmt.dba_plans;
sql_hash | queryid | plan_hash | status | plan_outline
-----------+---------------------+------------+----------+--------------------------
919644975 | 7869618859695600219 | 1046053951 | Approved | { +
| | | | "Fmt": "04.00", +
| | | | "Outl": { +
| | | | "Op": "SScan", +
| | | | "QB": 1, +
| | | | "S": "public", +
| | | | "Plel": "F", +
| | | | "Tbl": "test_table",+
| | | | "Rid": 1 +
| | | | } +
| | | | }
(1 row)
aurora_stat_plans에서 플랜이 선택되었는지 확인합니다:
postgres=> SELECT queryid, planid, calls, explain_plan FROM aurora_stat_plans(true) WHERE queryid = 7869618859695600219;
queryid | planid | calls | explain_plan
---------------------+------------+-------+--------------------------------------------------------------------------------
7869618859695600219 | 1046053951 | 1 | Seq Scan on test_table (cost=0.00..1693.00 rows=1 width=4) (actual time=0.015.
| | |...7.128 rows=1 loops=1) +
| | | Filter: (id = 1) +
| | | Rows Removed by Filter: 99999 +
| | | Buffers: shared hit=443
(1 row)
- 쿼리를 가속화하기 위해 인덱스를 생성합니다:
postgres=> CREATE INDEX test_table_idx1 ON test_table(id);
CREATE INDEX
- 쿼리를 다시 실행하고 새로운 플랜이 캡처되어
Unapproved 상태인지 확인합니다:
postgres=> SELECT * FROM test_table WHERE id = 1;
id
----
1
(1 row)
postgres=> SELECT sql_hash, queryid, plan_hash, status, plan_outline FROM apg_plan_mgmt.dba_plans WHERE queryid = 7869618859695600219;
sql_hash | queryid | plan_hash | status | plan_outline
------------+---------------------+------------+------------+-------------------------------
919644975 | 7869618859695600219 | 1046053951 | Approved | { +
| | | | "Fmt": "04.00", +
| | | | "Outl": { +
| | | | "Op": "SScan", +
| | | | "QB": 1, +
| | | | "S": "public", +
| | | | "Plel": "F", +
| | | | "Tbl": "test_table", +
| | | | "Rid": 1 +
| | | | } +
| | | | }
2073510322 | 7869618859695600219 | 1046053951 | Approved | { +
| | | | "Fmt": "04.00", +
| | | | "Outl": { +
| | | | "Op": "SScan", +
| | | | "QB": 1, +
| | | | "S": "public", +
| | | | "Plel": "F", +
| | | | "Tbl": "test_table", +
| | | | "Rid": 1 +
| | | | } +
| | | | }
919644975 | 7869618859695600219 | 86597673 | Unapproved | { +
| | | | "Fmt": "04.00", +
| | | | "Outl": { +
| | | | "Op": "IOScan", +
| | | | "QB": 1, +
| | | | "S": "public", +
| | | | "Plel": "F", +
| | | | "Idx": "test_table_idx1",+
| | | | "Tbl": "test_table", +
| | | | "Rid": 1 +
| | | | } +
| | | | }
(3 rows)
aurora_stat_plans가 여전히 Approved 플랜을 사용하고 있는지 확인합니다:
postgres=> SELECT queryid, planid, calls, explain_plan FROM aurora_stat_plans(true) WHERE queryid = 7869618859695600219;
queryid | planid | calls | explain_plan
---------------------+------------+-------+--------------------------------------------------------------------------------
7869618859695600219 | 1046053951 | 2 | Seq Scan on test_table (cost=0.00..1693.00 rows=1 width=4) (actual time=0.015.
| | |...7.128 rows=1 loops=1) +
| | | Filter: (id = 1) +
| | | Rows Removed by Filter: 99999 +
| | | Buffers: shared hit=443
(1 row)
- 새 플랜을 승인하고 이전 플랜을 거부합니다:
postgres=> SELECT apg_plan_mgmt.set_plan_status(919644975, 86597673, 'Approved');
set_plan_status
-----------------
0
(1 row)
postgres=> SELECT apg_plan_mgmt.set_plan_status (919644975, 1046053951, 'Rejected');
set_plan_status
-----------------
0
(1 row)
postgres=> SELECT apg_plan_mgmt.set_plan_status (2073510322, 1046053951, 'Rejected');
set_plan_status
-----------------
0
(1 row)
postgres=> SELECT * from test_table where id = 1;
id
----
1
(1 row)
postgres=> SELECT sql_hash, queryid, plan_hash, status, plan_outline FROM apg_plan_mgmt.dba_plans WHERE queryid = 7869618859695600219;
sql_hash | queryid | plan_hash | status | plan_outline
------------+---------------------+------------+----------+-------------------------------
919644975 | 7869618859695600219 | 86597673 | Approved | { +
| | | | "Fmt": "04.00", +
| | | | "Outl": { +
| | | | "Op": "IOScan", +
| | | | "QB": 1, +
| | | | "S": "public", +
| | | | "Plel": "F", +
| | | | "Idx": "test_table_idx1",+
| | | | "Tbl": "test_table", +
| | | | "Rid": 1 +
| | | | } +
| | | | }
919644975 | 7869618859695600219 | 1046053951 | Rejected | { +
| | | | "Fmt": "04.00", +
| | | | "Outl": { +
| | | | "Op": "SScan", +
| | | | "QB": 1, +
| | | | "S": "public", +
| | | | "Plel": "F", +
| | | | "Tbl": "test_table", +
| | | | "Rid": 1 +
| | | | } +
| | | | }
2073510322 | 7869618859695600219 | 1046053951 | Rejected | { +
| | | | "Fmt": "04.00", +
| | | | "Outl": { +
| | | | "Op": "SScan", +
| | | | "QB": 1, +
| | | | "S": "public", +
| | | | "Plel": "F", +
| | | | "Tbl": "test_table", +
| | | | "Rid": 1 +
| | | | } +
| | | | }
(3 rows)
- aurora_stat_plans 가 새롭게 승인된 플랜을 반영했는지 확인합니다:
postgres=> SELECT queryid, planid, calls, explain_plan FROM aurora_stat_plans(true) WHERE queryid=7869618859695600219 and planid=86597673;
queryid | planid | calls | explain_plan
---------------------+----------+-------+----------------------------------------------------------------------------------
7869618859695600219 | 86597673 | 2 | Index Only Scan using test_table_idx1 on test_table (cost=0.29..4.31 rows=1 wid.
| | |.th=4) (actual time=0.043..0.045 rows=1 loops=1) +
| | | Index Cond: (id = 1) +
| | | Heap Fetches: 0 +
| | | Buffers: shared hit=3
(1 row)
사용 사례 2: QPM 수동 캡처와의 통합
때때로 쿼리 최적화 프로그램이 최상의 쿼리 플랜을 생성하지 못할 수 있습니다. 이러한 경우, 애플리케이션 코드에서 쿼리 힌트를 사용하는 방법도 있지만, 유지보수 문제로 인해 권장되지 않는 경우가 많습니다. 대신 PostgreSQL에서는 pg_hint_plan 확장을 사용할 수 있습니다. 이를 통해 “scan method”, “join method”, “join order” 등과 같은 지시 사항을 QPM에 저장하여, 애플리케이션 코드에 힌트를 사용하지 않고도 최적화 프로그램이 원하는 플랜을 적용할 수 있도록 합니다.
이 섹션에서는 aurora_stat_activity와 aurora_stat_plans 뷰를 효과적으로 사용하는 방법을 설명하며, 현재 쿼리의 플랜을 식별하고 이를 QPM과 교차 참조하여 잠재적으로 최적화된 플랜을 찾는 방법을 탐구하고자 합니다.
- 다음 테이블을 생성하고 데이터를 삽입합니다:
postgres=> CREATE TABLE join_tab1 (
id int,
name varchar
);
CREATE TABLE
postgres=> INSERT INTO join_tab1
VALUES (generate_series(1, 10), 'test' || generate_series(1, 10));
INSERT 0 10
postgres=> CREATE TABLE join_tab2 (
id int,
name varchar
);
CREATE TABLE
Postgres=> INSERT INTO join_tab2
VALUES (generate_series(1, 100000), 'test' || generate_series(1, 100000));
INSERT 0 100000
- 다음 명령어를 활용하여
pgbench_join_simple_query.sql 파일을 생성합니다:
postgres=> SELECT * FROM
join_tab1 b
JOIN
join_tab2 a
ON b.id=a.id
ORDER BY
a.id;
id | name | id | name
----+--------+----+--------
1 | test1 | 1 | test1
2 | test2 | 2 | test2
3 | test3 | 3 | test3
4 | test4 | 4 | test4
5 | test5 | 5 | test5
6 | test6 | 6 | test6
7 | test7 | 7 | test7
8 | test8 | 8 | test8
9 | test9 | 9 | test9
10 | test10 | 10 | test10
(10 rows)
[shell]
cat << EOF > pgbench_join_simple_query.sql
SELECT * FROM
join_tab1 b
JOIN
join_tab2 a
ON b.id=a.id
ORDER BY
a.id;
EOF
- 다음 pgbench 명령어를 실행합니다:
pgbench -U <your user name> -h <your hostname> -p 5432 --time=120 --client=16 --jobs=4 -f pgbench_join_simple_query.sql <your dbname> > results_apg_plans.out
aurora_stat_plans 와 aurora_stats_activity 뷰를 확인하여 쿼리 실행 및 플랜을 점검합니다:
postgres=> SELECT count(*), query_id, plan_id, query
FROM aurora_stat_activity()
WHERE state = 'active'
AND pid <> pg_backend_pid()
GROUP BY query_id, plan_id, query
ORDER BY 1 DESC;
count | query_id | plan_id | query
-------+----------------------+-------------+----------------------
12 | -3160533748248474733 | -1446031810 | SELECT * FROM +
| | | join_tab1 b +
| | | JOIN +
| | | join_tab2 a +
| | | ON b.id=a.id+
| | | ORDER BY +
| | | a.id;
(1 row)
postgres=> SELECT queryid, calls, query , planid, plan_captured_time, substr(explain_plan,1,350) as explain_plan, total_exec_time
,min_exec_time
,max_exec_time
,mean_exec_time from aurora_stat_plans(true)
WHERE queryid=-3160533748248474733;
-[ RECORD 1 ]------+---------------------------------------------------------------------------------------------------------
queryid | -3160533748248474733
calls | 63622
query | SELECT * FROM +
| join_tab1 b +
| JOIN +
| join_tab2 a +
| ON b.id=a.id +
| ORDER BY +
| a.id
planid | -1446031810
plan_captured_time | 2024-10-05 06:11:29.22112+00
explain_plan | Sort (cost=2896.63..2899.81 rows=1270 width=49) (actual time=41.355..41.357 rows=10 loops=1) +
| Sort Key: b.id +
| Sort Method: quicksort Memory: 25kB +
| Buffers: shared hit=545 +
| -> Hash Join (cost=2791.00..2831.16 rows=1270 width=49) (actual time=41.320..41.327 rows=10 loops=1)+
| Hash Cond: (b.id = a.id) +
| Buffers: shared hit=542 +
|
total_exec_time | 3348671.502599981
min_exec_time | 25.484836
max_exec_time | 134.976206
mean_exec_time | 52.633860969475954
- 이 쿼리에 대한
dba_plans에서 승인된 플랜을 확인하세요. 이는 aurora_stat_plans 및 aurora_stat_activity와 일치해야 합니다.
postgres=> SELECT sql_hash,
plan_hash,
status,
enabled,
estimated_total_cost "cost",
sql_text
FROM apg_plan_mgmt.dba_plans
WHERE queryid=8276924454695828566;
sql_hash | plan_hash | status | enabled | cost | sql_text
-----------+-------------+----------+---------+------------------+----------------------
300806424 | -1446031810 | Approved | t | 2899.80989116354 | SELECT * FROM +
| | | | | join_tab1 b +
| | | | | JOIN +
| | | | | join_tab2 a +
| | | | | ON b.id=a.id+
| | | | | ORDER BY +
| | | | | a.id;
(1 row)
- 쿼리 플랜을 확인하고
pg_hint_plan을 사용하여 힌트를 통해 더 나은 플랜을 생성합니다:
postgres=> EXPLAIN (hashes true)
SELECT
*
FROM
join_tab1 b
JOIN join_tab2 a ON b.id = a.id
ORDER BY
a.id;
QUERY PLAN
--------------------------------------------------------------------------------------
Sort (cost=2896.63..2899.81 rows=1270 width=49)
Sort Key: b.id
-> Hash Join (cost=2791.00..2831.16 rows=1270 width=49)
Hash Cond: (b.id = a.id)
-> Seq Scan on join_tab1 b (cost=0.00..22.70 rows=1270 width=36)
-> Hash (cost=1541.00..1541.00 rows=100000 width=13)
-> Seq Scan on join_tab2 a (cost=0.00..1541.00 rows=100000 width=13)
SQL Hash: -1844833537, Plan Hash: -1446031810
(8 rows)
- 다음 쿼리를 사용하여
pg_hint_plan.enable_hint 파라미터를 활성화하고 힌트를 사용하여 플랜을 확인합니다:
postgres=> SET pg_hint_plan.enable_hint = true;
SET
postgres=> SHOW pg_hint_plan.enable_hint;
pg_hint_plan.enable_hint
--------------------------
true
(1 row)
- 사용하려는 힌트와 함께 쿼리에 대해 EXPLAIN을 사용하세요. 다음 예에서는
NestLoop (a, b) 힌트를 사용하여 최적화 프로그램이 테이블 a와 테이블 b를 조인할 때 중첩 루프를 사용하도록 지시합니다:
ANALYZE join_tab1;
ANALYZE join_tab2;
postgres=> /*+ NestLoop(a b) */ EXPLAIN (ANALYZE, VERBOSE, BUFFERS, hashes true)
SELECT
*
FROM
join_tab1 b
JOIN join_tab2 a ON b.id = a.id
ORDER BY
a.id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2792.40..2792.43 rows=10 width=23) (actual time=26.835..26.838 rows=10 loops=1)
Output: b.id, b.name, a.id, a.name
Sort Key: b.id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=542
-> Hash Join (cost=2791.00..2792.24 rows=10 width=23) (actual time=26.819..26.827 rows=10 loops=1)
Output: b.id, b.name, a.id, a.name
Hash Cond: (b.id = a.id)
Buffers: shared hit=542
-> Seq Scan on public.join_tab1 b (cost=0.00..1.10 rows=10 width=10) (actual time=0.007..0.008 rows=10 loops=1)
Output: b.id, b.name
Buffers: shared hit=1
-> Hash (cost=1541.00..1541.00 rows=100000 width=13) (actual time=26.783..26.784 rows=100000 loops=1)
Output: a.id, a.name
Buckets: 131072 Batches: 1 Memory Usage: 5506kB
Buffers: shared hit=541
-> Seq Scan on public.join_tab2 a (cost=0.00..1541.00 rows=100000 width=13) (actual time=0.003..8.556 rows=100000 loops=1)
Output: a.id, a.name
Buffers: shared hit=541
Query Identifier: -3160533748248474733
Planning:
Buffers: shared hit=90
Planning Time: 0.760 ms
Execution Time: 26.921 ms
Note: An Approved plan was used instead of the minimum cost plan.
SQL Hash: 1277910231, Plan Hash: -1446031810, Minimum Cost Plan Hash: -1446031370
(26 rows)
- 플랜 ID 1283399430이 캡처되었는지 확인하고 플랜의 상태를 기록합니다:
postgres=> SELECT sql_hash, plan_hash, status, enabled, sql_text
FROM apg_plan_mgmt.dba_plans
WHERE sql_hash = 1277910231;
sql_hash | plan_hash | status | enabled | sql_text
------------+-------------+------------+---------+-----------------------------------------
1277910231 | -1446031810 | Approved | t | SELECT +
| | | | * +
| | | | FROM +
| | | | join_tab1 b +
| | | | JOIN join_tab2 a ON b.id = a.id+
| | | | ORDER BY +
| | | | a.id;
1277910231 | -1446031370 | Unapproved | t | SELECT +
| | | | * +
| | | | FROM +
| | | | join_tab1 b +
| | | | JOIN join_tab2 a ON b.id = a.id+
| | | | ORDER BY +
| | | | a.id;
(2 rows)
- 새로운 플랜을 승인하고 이전 플랜을 거부합니다:
postgres=> SELECT apg_plan_mgmt.set_plan_status (1277910231, -1446031370, 'Approved');
set_plan_status
-----------------
0
(1 row)
postgres=> SELECT apg_plan_mgmt.set_plan_status (1277910231, -1446031810, 'Rejected');
set_plan_status
-----------------
0
(1 row)
postgres=> SELECT sql_hash,
plan_hash,
status,
enabled,
sql_text
FROM apg_plan_mgmt.dba_plans
WHERE queryid=-3160533748248474733;
sql_hash | plan_hash | status | enabled | sql_text
-------------+-------------+----------+---------+-----------------------------------------
1277910231 | -1446031810 | Rejected | t | SELECT +
| | | | * +
| | | | FROM +
| | | | join_tab1 b +
| | | | JOIN join_tab2 a ON b.id = a.id+
| | | | ORDER BY +
| | | | a.id;
1277910231 | -1446031370 | Approved | t | SELECT +
| | | | * +
| | | | FROM +
| | | | join_tab1 b +
| | | | JOIN join_tab2 a ON b.id = a.id+
| | | | ORDER BY +
| | | | a.id;
(2 rows)
- 다음 쿼리를 실행하고
aurora_stat_plans가 해당 플랜을 반영했는지 확인합니다:
postgres=> SELECT
*
FROM
join_tab1 b
JOIN join_tab2 a ON b.id = a.id
ORDER BY
a.id;
id | name | id | name
----+--------+----+--------
1 | test1 | 1 | test1
2 | test2 | 2 | test2
3 | test3 | 3 | test3
4 | test4 | 4 | test4
5 | test5 | 5 | test5
6 | test6 | 6 | test6
7 | test7 | 7 | test7
8 | test8 | 8 | test8
9 | test9 | 9 | test9
10 | test10 | 10 | test10
(10 rows)
postgres=> SELECT queryid, planid, calls, explain_plan FROM aurora_stat_plans(true) WHERE planid=-1446031370;
-[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------------------
queryid | -3160533748248474733
planid | -1446031370
calls | 1
explain_plan | Sort (cost=1917.49..1917.52 rows=10 width=23) (actual time=19.650..19.653 rows=10 loops=1) +
| Sort Key: b.id +
| Sort Method: quicksort Memory: 25kB +
| Buffers: shared hit=542 +
| -> Hash Join (cost=1.23..1917.32 rows=10 width=23) (actual time=0.030..19.635 rows=10 loops=1) +
| Hash Cond: (a.id = b.id) +
| Buffers: shared hit=542 +
| -> Seq Scan on join_tab2 a (cost=0.00..1541.00 rows=100000 width=13) (actual time=0.010..8.236 rows=100000 loops=1)+
| Buffers: shared hit=541 +
| -> Hash (cost=1.10..1.10 rows=10 width=10) (actual time=0.011..0.012 rows=10 loops=1) +
| Buckets: 1024 Batches: 1 Memory Usage: 9kB +
| Buffers: shared hit=1 +
| -> Seq Scan on join_tab1 b (cost=0.00..1.10 rows=10 width=10) (actual time=0.003..0.004 rows=10 loops=1) +
| Buffers: shared hit=1
pgbench를 다시 실행하고 aurora_stat_plans에서 새로운 플랜이 반영되었는지 확인하세요:
postgres=> SELECT queryid, planid, calls FROM aurora_stat_plans(true) WHERE queryid=-3160533748248474733;
queryid | planid | calls
----------------------+-------------+-------
-3160533748248474733 | -1446031810 | 63624
-3160533748248474733 | -1446031370 | 13915
postgres=> SELECT queryid, planid, calls FROM aurora_stat_plans(true) WHERE queryid=-3160533748248474733;
queryid | planid | calls
----------------------+-------------+-------
-3160533748248474733 | -1446031810 | 63624
-3160533748248474733 | -1446031370 | 47985
ID -1446031370을 가진 새로운 플랜에 대한 호출 수가 증가한 것을 볼 수 있습니다.
리소스 정리
불필요한 비용을 방지하기 위해 테스트 후 다음 단계를 완료하세요:
요약
이 글에서는 Amazon Aurora PostgreSQL의 새로운 쿼리 모니터링 기능을 사용하여 쿼리 플랜을 모니터링하는 방법을 설명했습니다. 이 기능은 데이터베이스 성능을 개선하기 위한 중요한 정보를 제공하며, 사용자가 실시간으로 쿼리 플랜을 분석하여 성능 문제를 빠르게 식별하고 해결할 수 있도록 돕습니다. 이러한 접근 방식은 지연 시간을 줄이고 자원 사용을 최적화하며, 다운타임을 최소화하여 전반적인 생산성을 향상시킬 수 있습니다.