# Aurora PostgreSQL에서 쿼리 성능 최적화 쿼리 성능을 최적화하면 더 적은 리소스를 사용하면서 데이터베이스가 더 빠르고 효율적으로 실행되어 사용자 경험이 향상되고 운영 비용이 절감되므로 이는 매우 중요합니다. Amazon Aurora PostgreSQL은 PostgreSQL 워크로드의 쿼리 성능을 최적화하는 데 도움이 되는 몇 가지 기능을 제공합니다. **Topics** + [Aurora 최적화된 읽기로 Aurora PostgreSQL 쿼리 성능 개선](AuroraPostgreSQL.optimized.reads.md) + [Aurora PostgreSQL에서 상관관계가 있는 하위 쿼리 최적화](apg-correlated-subquery.md) + [적응형 조인을 사용하여 쿼리 성능 개선](user-apg-adaptive-join.md) + [공유 계획 캐시 사용](apg-shared-plan-cache.md) # Aurora 최적화된 읽기로 Aurora PostgreSQL 쿼리 성능 개선 Aurora 최적화된 읽기로 Aurora PostgreSQL의 쿼리 처리 속도를 높일 수 있습니다. Aurora 최적화된 읽기를 사용하는 Aurora PostgreSQL DB 인스턴스는 DB 인스턴스의 메모리 용량을 초과하는 대규모 데이터 세트를 보유한 애플리케이션에서 쿼리 지연 시간이 최대 8배 개선되고 비용이 최대 30% 절감됩니다. **Topics** + [PostgreSQL의 Aurora 최적화된 읽기 개요](#AuroraPostgreSQL.optimized.reads.overview) + [Aurora 최적화된 읽기 사용](#AuroraPostgreSQL.optimized.reads.using) + [Aurora 최적화된 읽기 사용 사례](#AuroraPostgreSQL.optimized.reads.usecases) + [Aurora 최적화된 읽기를 사용하는 DB 인스턴스 모니터링](#AuroraPostgreSQL.optimized.reads.monitoring) + [Aurora 최적화된 읽기 모범 사례](#AuroraPostgreSQL.optimized.reads.bestpractices) ## PostgreSQL의 Aurora 최적화된 읽기 개요 Aurora 최적화된 읽기는 비휘발성 메모리 익스프레스(NVMe) 스토리지와 함께 Graviton 기반 R6gd, R8gd 및 Intel 기반 R6id 인스턴스를 사용하는 DB 클러스터를 생성할 때 기본적으로 사용할 수 있습니다. 다음 PostgreSQL 버전에서 사용할 수 있습니다. + R8gd 인스턴스의 경우 14.12 이상 버전, 15.7 이상 버전, 16.3 이상 버전, 17.4 이상 버전 + R6gd 및 R6id 인스턴스의 경우 14.9 이상 버전, 15.4 이상 버전, 16.1 이상 버전 Aurora 최적화된 읽기는 계층형 캐시와 임시 객체라는 두 가지 기능을 지원합니다. **최적화된 읽기가 활성화된 계층형 캐시** - 계층형 캐시를 사용하면 DB 인스턴스 캐싱 용량을 인스턴스 메모리의 최대 5배까지 확장할 수 있습니다. 이렇게 하면 트랜잭션 측면에서 가장 최근의 일관성 있는 데이터를 포함하도록 캐시가 자동으로 유지되므로 애플리케이션은 외부 결과 집합 기반 캐싱 솔루션의 데이터 흐름을 관리하는 오버헤드에서 벗어날 수 있습니다. 이전에 Aurora 스토리지에서 데이터를 가져오던 쿼리에 대해 최대 8배 더 개선된 지연 시간을 제공합니다. Aurora에서 기본 파라미터 그룹의 `shared_buffers` 값은 일반적으로 사용 가능한 메모리의 약 75%로 설정됩니다. 하지만 r8gd, r6gd 및 r6id 인스턴스 유형의 경우 Aurora는 최적화된 읽기 캐시에 대한 메타데이터를 호스팅하기 위해 `shared_buffers` 공간을 4.5% 줄입니다. **최적화된 읽기가 활성화된 임시 객체** - 임시 객체를 사용하면 PostgreSQL에서 생성된 임시 파일을 로컬 NVMe 스토리지에 배치하여 보다 빠르게 쿼리를 처리할 수 있습니다. 이렇게 하면 네트워크를 통해 Elastic Block Storage(EBS)로 전송되는 트래픽이 줄어듭니다. DB 인스턴스에서 사용할 수 있는 메모리 용량을 초과하는 대량의 데이터를 정렬, 조인 또는 병합하는 고급 쿼리에 대해 최대 2배 더 개선된 지연 시간과 처리량을 제공합니다. Aurora I/O 최적화 클러스터에서 최적화된 읽기는 계층형 캐시와 NVMe 스토리지의 임시 객체를 모두 사용합니다. 최적화된 읽기가 활성화된 계층형 캐시 기능을 통해 Aurora는 인스턴스 메모리의 2배를 임시 객체에 할당하고, 스토리지의 약 10%는 내부 작업으로, 나머지 스토리지는 계층형 캐시로 할당합니다. Aurora Standard 클러스터에서 최적화된 읽기는 임시 객체만 사용합니다. Aurora I/O 최적화 클러스터를 사용하면 인스턴스 수준에서 `aurora_temp_space_size` 동적 파라미터를 사용하여 최적화된 읽기 지원 임시 객체에 할당된 공간의 크기를 조정할 수 있습니다. 이 크기 조정 기능은 다음 PostgreSQL 버전에서 사용할 수 있습니다. + 16.8 이상의 모든 버전 + 15.12 이상의 15 버전 + 14.17 이상의 14 버전 이 파라미터를 사용하면 데이터베이스 엔진을 다시 시작할 필요 없이 인스턴스 메모리의 2배에서 최대 6배까지 용량을 조정할 수 있습니다. 임시 객체 공간을 확장하면 동시 워크로드에 관계없이 변경 사항이 즉시 적용됩니다. 그러나 공간을 줄이면 임시 객체에 새 크기 요청을 수용할 수 있는 충분한 미사용 공간이 있는 경우에만 조정이 완료됩니다. 최적화된 읽기 지원 임시 객체의 크기를 조정하면 계층형 캐시가 사용 가능한 공간을 사용하도록 자동으로 조정됩니다. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.optimized.reads.html) **참고** NVMe 기반 DB 인스턴스 클래스에서 IO 최적화 클러스터와 Standard 클러스터 사이를 전환하면 데이터베이스 엔진이 즉시 재시작됩니다. Aurora PostgreSQL에서는 임시 객체가 저장되는 테이블 공간을 구성하는 데 `temp_tablespaces` 파라미터를 사용합니다. 임시 객체가 구성되었는지 확인하려면 다음 명령을 사용합니다. ``` postgres=> show temp_tablespaces; temp_tablespaces --------------------- aurora_temp_tablespace (1 row) ``` `aurora_temp_tablespace`는 NVMe 로컬 스토리지를 가리키는 Aurora에서 구성한 테이블스페이스입니다. 이 파라미터는 수정하거나 Amazon EBS 스토리지로 다시 전환할 수 없습니다. 최적화된 읽기 캐시가 켜져 있는지 확인하려면 다음 명령을 사용합니다. ``` postgres=> show shared_preload_libraries; shared_preload_libraries -------------------------------------------------------- rdsutils,pg_stat_statements,aurora_optimized_reads_cache ``` ## Aurora 최적화된 읽기 사용 NVMe 기반 DB 인스턴스 중 하나를 사용하여 Aurora PostgreSQL DB 인스턴스를 프로비저닝하면 DB 인스턴스는 자동으로 Aurora 최적화된 읽기를 사용합니다. Aurora Optimized Read를 켜려면 다음 중 하나를 수행합니다. + NVMe 기반 DB 인스턴스 클래스 중 하나를 사용하여 Aurora PostgreSQL DB 클러스터를 생성합니다. 자세한 내용은 [Amazon Aurora DB 클러스터 생성](Aurora.CreateInstance.md) 섹션을 참조하세요. + NVMe 기반 DB 인스턴스 클래스 중 하나를 사용하여 기존 Aurora PostgreSQL DB 클러스터를 수정합니다. 자세한 내용은 [Amazon Aurora DB 클러스터 수정](Aurora.Modifying.md) 섹션을 참조하세요. Aurora 최적화된 읽기는 로컬 NVMe SSD 스토리지가 있는 DB 인스턴스 클래스 중 하나 이상이 지원되는 모든 AWS 리전에서 사용 가능합니다. 자세한 내용은 [Amazon AuroraDB 인스턴스 클래스](Concepts.DBInstanceClass.md) 섹션을 참조하세요. 최적화되지 않은 읽기 Aurora 인스턴스로 다시 전환하려면 Aurora 인스턴스의 DB 인스턴스 클래스를 데이터베이스 워크로드에 대해 NVMe 임시 스토리지가 없는 유사한 인스턴스 클래스로 수정하세요. 예를 들어, 현재 DB 인스턴스 클래스가 db.r6gd.4xlarge인 경우 db.r6g.4xlarge를 선택하여 다시 전환합니다. 자세한 내용은 [Aurora DB 인스턴스 수정](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.DBInstance.Modifying.html)을 참조하세요. ## Aurora 최적화된 읽기 사용 사례 **최적화된 읽기가 활성화된 계층형 캐시** 계층형 캐시가 있는 최적화된 읽기가 도움이 될 수 있는 몇 가지 사용 사례입니다. + 엄격한 성능 SLA가 있는 결제 처리, 청구, 전자 상거래와 같은 인터넷 규모의 애플리케이션 + 지표/데이터 수집을 위해 수백 개의 포인트 쿼리를 실행하는 실시간 보고 대시보드 + 수백 개의 벡터 임베딩에서 정확한 이웃 또는 가장 가까운 이웃을 검색하기 위해 pgvector 확장을 갖춘 생성형 AI 애플리케이션 **최적화된 읽기가 활성화된 임시 객체** 다음은 임시 객체가 있는 최적화된 읽기가 도움이 될 수 있는 몇 가지 사용 사례입니다. + CTE(Common Table Expression), 파생된 테이블, 그룹화 작업을 포함하는 분석 쿼리. + 애플리케이션의 최적화되지 않은 쿼리를 처리하는 읽기 전용 복제본. + 항상 적절한 인덱스를 사용할 수 없는 GROUP BY 및 ORDER BY 같은 복잡한 작업이 포함된 온디맨드 또는 동적 보고 쿼리. + 정렬을 위한 `CREATE INDEX` 또는 `REINDEX` 작업입니다. + 내부 임시 테이블을 사용하는 기타 워크로드. ## Aurora 최적화된 읽기를 사용하는 DB 인스턴스 모니터링 최적화된 읽기가 활성화된 계층형 캐시를 사용하는 쿼리는 다음 예와 같이 EXPLAIN 명령을 사용하여 모니터링할 수 있습니다. ``` Postgres=> EXPLAIN (ANALYZE, BUFFERS) SELECT c FROM sbtest15 WHERE id=100000000 QUERY PLAN -------------------------------------------------------------------------------------- Index Scan using sbtest15_pkey on sbtest15 (cost=0.57..8.59 rows=1 width=121) (actual time=0.287..0.288 rows=1 loops=1) Index Cond: (id = 100000000) Buffers: shared hit=3 read=2 aurora_orcache_hit=2 I/O Timings: shared/local read=0.264 Planning: Buffers: shared hit=33 read=6 aurora_orcache_hit=6 I/O Timings: shared/local read=0.607 Planning Time: 0.929 ms Execution Time: 0.303 ms (9 rows) Time: 2.028 ms ``` **참고** 계획 설명의 `Buffers` 섹션에 있는 `aurora_orcache_hit` 및 `aurora_storage_read` 필드는 최적화된 읽기가 켜져 있고 값이 0보다 큰 경우에만 표시됩니다. 읽기 필드는 `aurora_orcache_hit` 필드와 `aurora_storage_read` 필드의 합계입니다. 다음 CloudWatch 지표를 사용하여 Aurora 최적화된 읽기를 사용하는 DB 인스턴스를 모니터링할 수 있습니다. + `AuroraOptimizedReadsCacheHitRatio` + `FreeEphemeralStorage` + `ReadIOPSEphemeralStorage` + `ReadLatencyEphemeralStorage` + `ReadThroughputEphemeralStorage` + `WriteIOPSEphemeralStorage` + `WriteLatencyEphemeralStorage` + `WriteThroughputEphemeralStorage` 이러한 지표는 사용 가능한 인스턴스 스토어 스토리지, IOPS 및 처리량(throughput)에 대한 데이터를 제공합니다. 이러한 지표에 대한 자세한 내용은 섹션을 참조하세요.[Amazon Aurora에 대한 인스턴스 수준 지표](Aurora.AuroraMonitoring.Metrics.md#Aurora.AuroraMySQL.Monitoring.Metrics.instances) `pg_proctab` 확장을 사용하여 NVMe 스토리지를 모니터링할 수도 있습니다. ``` postgres=>select * from pg_diskusage(); major | minor | devname | reads_completed | reads_merged | sectors_read | readtime | writes_completed | writes_merged | sectors_written | writetime | current_io | iotime | totaliotime ------+-------+---------------------+-----------------+--------------+--------------+----------+------------------+---------------+-----------------+-----------+------------+---------+------------- | | rdstemp | 23264 | 0 | 191450 | 11670 | 1750892 | 0 | 24540576 | 819350 | 0 | 3847580 | 831020 | | rdsephemeralstorage | 23271 | 0 | 193098 | 2620 | 114961 | 0 | 13845120 | 130770 | 0 | 215010 | 133410 (2 rows) ``` ## Aurora 최적화된 읽기 모범 사례 Aurora 최적화된 읽기에 대한 다음 모범 사례를 따릅니다. + CloudWatch 지표 `FreeEphemeralStorage`를 사용하여 인스턴스 스토어에서 사용 가능한 스토리지 공간을 모니터링합니다. DB 인스턴스의 워크로드로 인해 인스턴스 스토어가 한도에 도달하면 임시 객체를 많이 사용하는 동시성 및 쿼리를 조정하거나 더 큰 DB 인스턴스 클래스를 사용하도록 수정합니다. + 최적화된 읽기 캐시 적중률에 대한 CloudWatch 지표를 모니터링합니다. VACUUM과 같은 작업은 많은 수의 블록을 매우 빠르게 수정합니다. 이로 인해 적중률이 일시적으로 떨어질 수 있습니다. `pg_prewarm` 확장을 사용하여 데이터를 버퍼 캐시에 로드하면 Aurora가 이러한 블록 중 일부를 최적화된 읽기 캐시에 선제적으로 기록할 수 있습니다. + 클러스터 캐시 관리(CCM)를 사용하여 장애 조치 대상으로 사용될 Tier-0 리더의 버퍼 캐시 및 계층형 캐시를 워밍업할 수 있습니다. CCM이 활성화되면 버퍼 캐시를 정기적으로 스캔하여 제거 가능한 페이지를 계층형 캐시에 기록합니다. CCM에 대한 자세한 내용은 [장애 조치 후 Aurora PostgreSQL용 클러스터 캐시 관리를 통한 신속한 복구](AuroraPostgreSQL.cluster-cache-mgmt.md) 섹션을 참조하세요. # Aurora PostgreSQL에서 상관관계가 있는 하위 쿼리 최적화 상관관계가 있는 하위 쿼리는 외부 쿼리의 표 열을 참조합니다. 외부 쿼리에서 반환된 모든 행에 대해 한 번씩 평가됩니다. 다음 예에서는 하위 쿼리가 표 ot의 열을 참조합니다. 이 표는 하위 쿼리의 FROM 절에는 포함되지 않지만, 외부 쿼리의 FROM 절에서는 참조됩니다. 표 ot의 행이 100만 개이면 하위 쿼리를 100만 번 평가해야 합니다. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` **참고** 하위 쿼리 변환 및 하위 쿼리 캐시는 버전 16.8부터 Aurora PostgreSQL에서 사용할 수 있으며 Babelfish for Aurora PostgreSQL은 4.2.0부터 이러한 기능을 지원합니다. Babelfish for Aurora PostgreSQL 버전 4.6.0 및 5.2.0부터 다음 파라미터가 이러한 기능을 제어합니다. babelfishpg\$1tsql.apg\$1enable\$1correlated\$1scalar\$1transform babelfishpg\$1tsql.apg\$1enable\$1subquery\$1cache 기본적으로 두 파라미터 모두 켜져 있습니다. ## 하위 쿼리 변환을 사용하여 Aurora PostgreSQL 쿼리 성능 개선 Aurora PostgreSQL은 상관관계가 있는 하위 쿼리를 상응하는 외부 조인으로 변환하여 해당 하위 쿼리를 가속화할 수 있습니다. 이 최적화는 다음과 같은 두 유형의 상관관계가 있는 하위 쿼리에 적용됩니다. + 단일 집계 값을 반환하고 SELECT 목록에 나타나는 하위 쿼리입니다. ``` SELECT ot.a, ot.b, (SELECT AVG(it.b) FROM it WHERE it.a = ot.a) FROM ot; ``` + 단일 집계 값을 반환하고 WHERE 절에 나타나는 하위 쿼리입니다. ``` SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); ``` ### 하위 쿼리에서 변환 활성화 상관관계가 있는 하위 쿼리를 동등한 외부 조인으로 변환할 수 있도록 하려면 `apg_enable_correlated_scalar_transform` 파라미터를 `ON`으로 설정합니다. 이 파라미터의 기본값은 `OFF`입니다. 클러스터 또는 인스턴스 파라미터 그룹을 수정하여 파라미터를 설정할 수 있습니다. 자세한 내용은 [Amazon Aurora의 파라미터 그룹](USER_WorkingWithParamGroups.md)를 참조하세요. 또는 다음 명령을 사용하여 현재 세션에 대한 설정만 구성할 수도 있습니다. ``` SET apg_enable_correlated_scalar_transform TO ON; ``` ### 변환 확인 EXPLAIN 명령을 사용하여 상관관계가 있는 하위 쿼리가 쿼리 계획에서 외부 조인으로 변환되었는지 확인합니다. 변환이 활성화되면 상관관계가 있는 관련 하위 쿼리 부분이 외부 조인으로 변환됩니다. 예제: ``` postgres=> CREATE TABLE ot (a INT, b INT); CREATE TABLE postgres=> CREATE TABLE it (a INT, b INT); CREATE TABLE postgres=> SET apg_enable_correlated_scalar_transform TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); QUERY PLAN -------------------------------------------------------------- Hash Join Hash Cond: (ot.a = apg_scalar_subquery.scalar_output) Join Filter: ((ot.b)::numeric < apg_scalar_subquery.avg) -> Seq Scan on ot -> Hash -> Subquery Scan on apg_scalar_subquery -> HashAggregate Group Key: it.a -> Seq Scan on it ``` GUC 파라미터를 `OFF`로 설정해도 동일한 쿼리는 변환되지 않습니다. 이 계획에는 외부 조인이 없고, 대신 하위 계획이 있습니다. ``` postgres=> SET apg_enable_correlated_scalar_transform TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT AVG(it.b) FROM it WHERE it.a = ot.a); QUERY PLAN ---------------------------------------- Seq Scan on ot Filter: ((b)::numeric < (SubPlan 1)) SubPlan 1 -> Aggregate -> Seq Scan on it Filter: (a = ot.a) ``` ### 제한 사항 + 하위 쿼리는 SELECT 목록 또는 where 절의 조건 중 하나에 있어야 합니다. 그렇지 않으면 변환되지 않습니다. + 하위 쿼리는 집계 함수를 반환해야 합니다. 사용자 정의 집계 함수는 변환에 지원되지 않습니다. + 반환 표현식이 단순 집계 함수가 아닌 하위 쿼리는 변환되지 않습니다. + 하위 쿼리 WHERE 절의 상관관계가 있는 조건은 단순한 열 참조여야 합니다. 그렇지 않으면 변환되지 않습니다. + 절이 일반 등식 조건자여야 하는 하위 쿼리의 상관관계가 있는 조건입니다. + 하위 쿼리에는 HAVING 또는 GROUP BY 절을 포함할 수 없습니다. + 하위 쿼리의 where 절에는 AND와 결합된 하나 이상의 조건자가 포함될 수 있습니다. **참고** 변환의 성능 영향은 스키마, 데이터 및 워크로드에 따라 달라집니다. 변환과 관련된 하위 쿼리 실행은 외부 쿼리에서 생성된 행 수가 증가함에 따라 성능을 크게 개선할 수 있습니다. 프로덕션 환경에서 활성화하기 전에 실제 스키마, 데이터 및 워크로드를 사용하여 비프로덕션 환경에서 이 기능을 테스트하는 것이 좋습니다. ## 하위 쿼리 캐시를 사용하여 Aurora PostgreSQL 쿼리 성능 향상 Aurora PostgreSQL은 하위 쿼리 캐시를 지원하여 상관관계가 있는 하위 쿼리의 결과를 저장합니다. 이 기능은 하위 쿼리 결과가 이미 캐시에 있는 경우 상관관계가 있는 반복 하위 쿼리 실행을 건너뜁니다. ### 하위 쿼리 캐시 이해 PostgreSQL의 Memoize 노드는 하위 쿼리 캐시의 핵심 부분입니다. Memoize 노드는 로컬 캐시에 해시 표를 유지하여 입력 파라미터 값을 쿼리 결과 행으로 매핑합니다. 해시 표의 메모리 제한은 work\$1mem과 hash\$1mem\$1multiplier의 곱입니다. 자세한 내용은 [리소스 소비](https://www.postgresql.org/docs/16/runtime-config-resource.html)를 참조하세요. 쿼리 실행 중에 하위 쿼리 캐시는 캐시 적중률(CHR)을 사용하여 캐시가 쿼리 성능을 개선하는지를 추정하고 쿼리 런타임 시 캐시를 계속 사용할지를 결정합니다. CHR은 총 요청 수에 대한 캐시 적중 횟수의 비율입니다. 예를 들어, 상관관계가 있는 하위 쿼리를 100번 실행해야 하고 해당 실행 결과 중 70개를 캐시에서 검색할 수 있는 경우 CHR은 0.7입니다. apg\$1subquery\$1cache\$1check\$1interval 캐시 누락 횟수가 발생할 때마다 CHR이 apg\$1subquery\$1cache\$1hit\$1rate\$1threshold보다 큰지 확인하여 하위 쿼리 캐시의 이점을 평가합니다. 이보다 작으면 캐시가 메모리에서 삭제되고 쿼리 실행은 캐시되지 않은 원래 하위 쿼리 재실행 상태로 돌아갑니다. ### 하위 쿼리 캐시 동작을 제어하는 파라미터 다음 표에는 하위 쿼리 캐시 동작을 제어하는 파라미터가 나열되어 있습니다. | 파라미터 | 설명 | 기본값 | 허용됨 | | --- | --- | --- | --- | | apg\$1enable\$1subquery\$1cache | 상관관계가 있는 스칼라 하위 쿼리에 캐시를 사용할 수 있도록 지원합니다. | OFF | ON, OFF | | apg\$1subquery\$1cache\$1check\$1interval | 하위 쿼리 캐시 적중률을 평가할 빈도(캐시 누락 횟수)를 설정합니다. | 500 | 0–2147483647 | | apg\$1subquery\$1cache\$1hit\$1rate\$1threshold | 하위 쿼리 캐시 적중률의 임계값을 설정합니다. | 0.3 | 0.0\$11.0 | **참고** `apg_subquery_cache_check_interval` 값이 클수록 CHR 기반 캐시 이점 추정의 정확도는 향상될 수 있지만, 캐시 표에 `apg_subquery_cache_check_interval` 행이 있을 때까지 CHR이 평가되지 않으므로 캐시 오버헤드가 증가합니다. `apg_subquery_cache_hit_rate_threshold` 값이 클수록 하위 쿼리 캐시를 포기하고 캐시되지 않은 원래 하위 쿼리 재실행으로 돌아가는 경향이 있습니다. 클러스터 또는 인스턴스 파라미터 그룹을 수정하여 파라미터를 설정할 수 있습니다. 자세한 내용은 [Amazon Aurora의 파라미터 그룹](USER_WorkingWithParamGroups.md)를 참조하세요. 또는 다음 명령을 사용하여 현재 세션에 대한 설정만 구성할 수도 있습니다. ``` SET apg_enable_subquery_cache TO ON; ``` ### Aurora PostgreSQL에서 하위 쿼리 캐시 켜기 하위 쿼리 캐시가 활성화되면 Aurora PostgreSQL은 캐시를 적용하여 하위 쿼리 결과를 저장합니다. 그러면 쿼리 계획의 SubPlan 아래에 Memoize 노드가 생깁니다. 예를 들어, 다음 명령 시퀀스는 하위 쿼리 캐시 없이 상관관계가 있는 단순 하위 쿼리의 예상 쿼리 실행 계획을 보여줍니다. ``` postgres=> SET apg_enable_subquery_cache TO OFF; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Seq Scan on it Filter: (a = ot.a) ``` `apg_enable_subquery_cache`를 활성화한 후 쿼리 계획의 SubPlan 노드 아래에 Memoize 노드가 포함되며, 이는 하위 쿼리가 캐시를 사용할 계획임을 나타냅니다. ``` postgres=> SET apg_enable_subquery_cache TO ON; SET postgres=> EXPLAIN (COSTS FALSE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ------------------------------------ Seq Scan on ot Filter: (b < (SubPlan 1)) SubPlan 1 -> Memoize Cache Key: ot.a Cache Mode: binary -> Seq Scan on it Filter: (a = ot.a) ``` 실제 쿼리 실행 계획에는 캐시 적중 및 캐시 누락을 포함하여 하위 쿼리 캐시에 대한 자세한 정보가 포함되어 있습니다. 다음 출력은 표에 일부 값을 삽입한 후의 위 예제 쿼리의 실제 쿼리 실행 계획을 보여줍니다. ``` postgres=> EXPLAIN (COSTS FALSE, TIMING FALSE, ANALYZE TRUE) SELECT ot.a, ot.b FROM ot WHERE ot.b < (SELECT it.b FROM it WHERE it.a = ot.a); QUERY PLAN ----------------------------------------------------------------------------- Seq Scan on ot (actual rows=2 loops=1) Filter: (b < (SubPlan 1)) Rows Removed by Filter: 8 SubPlan 1 -> Memoize (actual rows=0 loops=10) Cache Key: ot.a Cache Mode: binary Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on it (actual rows=0 loops=6) Filter: (a = ot.a) Rows Removed by Filter: 4 ``` 총 캐시 적중 수는 4이고 총 캐시 누락 수는 6입니다. 총 적중 및 누락 수가 Memoize 노드의 루프 수보다 적으면 CHR 평가를 통과하지 못했고 캐시가 어느 시점에서 정리되고 폐기되었음을 의미합니다. 이에 따라 하위 쿼리 실행이 캐시되지 않은 원래 재실행으로 되돌아갔습니다. ### 제한 사항 하위 쿼리 캐시는 특정 패턴의 상관관계가 있는 하위 쿼리를 지원하지 않습니다. 이러한 유형의 쿼리는 하위 쿼리 캐시가 설정되어 있더라도 캐시 없이 실행됩니다. + IN/EXISTS/ANY/ALL 상관관계가 있는 하위 쿼리 + 비결정적 함수를 포함하는 상관관계가 있는 하위 쿼리. + 해싱 또는 등식 연산을 지원하지 않는 데이터 유형이 있는 외부 테이블 열을 참조하는 상관관계가 있는 하위 쿼리입니다. # 적응형 조인을 사용하여 쿼리 성능 개선 ## 개요 적응형 조인은 쿼리 성능을 개선하는 데 도움이 되는 Aurora PostgreSQL 17.4의 미리 보기 기능입니다. 이 기능은 기본적으로 비활성화되어 있지만 글로벌 사용자 구성(GUC) 파라미터를 사용하여 활성화할 수 있습니다. 이 기능은 미리 보기 기능이므로 기본 파라미터 값이 변경될 수 있습니다. 활성화된 경우 적응형 조인은 런타임에 중첩 루프 조인에서 해시 조인으로 동적으로 전환하여 쿼리 성능을 최적화하는 데 도움이 됩니다. 이 전환은 부정확한 카디널리티 추정치로 인해 PostgreSQL 최적화 프로그램이 중첩 루프 조인을 잘못 선택한 경우 발생합니다. ## 적응형 조인 구성 다음 세 가지 GUC 파라미터를 사용하여 적응형 조인을 제어할 수 있습니다. **적응형 조인 구성 파라미터** | GUC 파라미터 | 설명 | 기본 및 구성 옵션 | | --- | --- | --- | | apg\$1adaptive\$1join\$1crossover\$1multiplier | 이 승수는 행 교차점과 함께 작동하여 중첩 루프에서 해시 조인으로 전환할 시기를 결정합니다. 행 교차점은 SQL 최적화 프로그램이 중첩 루프 및 해시 조인 작업의 비용이 동일하다고 추정하는 지점입니다. 승수 값이 높을수록 적응형 조인이 해시 조인으로 전환될 가능성이 줄어듭니다. | 적응형 조인이 활성화되었는지 여부를 제어합니다. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1adaptive\$1join\$1cost\$1threshold | 이 파라미터는 최소 쿼리 비용 임계값을 설정합니다. 적응형 조인은 이 임계값 미만의 쿼리에 대해 자동으로 비활성화됩니다. 이렇게 하면 적응형 조인을 계획하는 데 드는 비용이 중첩 루프에서 해시 조인으로 전환하는 이익을 초과할 수 있는 단순 쿼리의 성능 오버헤드를 방지할 수 있습니다. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) | | apg\$1enable\$1parameterized\$1adaptive\$1join | 이 파라미터는 활성화된 경우 적응형 조인 기능을 파라미터화된 중첩 루프 조인으로 확장합니다. 기본적으로 적응형 조인은 파라미터화되지 않은 중첩 루프 조인에서만 작동합니다. 해시 조인으로 전환하면 이점을 얻을 가능성이 높기 때문입니다. 파라미터화된 중첩 루프 조인은 일반적으로 성능이 더 좋으므로 해시 조인으로 전환할 필요성이 덜합니다. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/user-apg-adaptive-join.html) 먼저 `apg_adaptive_join_crossover_multiplier`를 활성화해야 합니다. | # 공유 계획 캐시 사용 ## 개요 Aurora PostgreSQL은 사용자별 프로세스 모델을 사용하여, 클라이언트 연결마다 전용 백엔드 프로세스가 생성됩니다. 각 백엔드 프로세스는 준비된 문에 대해 자체 로컬 계획 캐시를 유지합니다. 이러한 캐시는 프로세스 간에 공유할 수 없으므로 준비된 문을 많이 사용하는 애플리케이션은 여러 백엔드 프로세스에서 중복 캐시를 생성하여 메모리 사용량이 증가할 수 있습니다. Aurora PostgreSQL 버전 17.6 이상 및 16.10 이상에는 공유 계획 캐시 기능이 도입되었습니다. 이 기능을 활성화하면 백엔드 프로세스가 일반 계획을 공유할 수 있으므로 중복 계획 생성을 방지하여 메모리 사용량을 줄이고 성능을 개선할 수 있습니다. 공유 계획 캐시는 다음 구성 요소를 캐시 키로 사용합니다. + 쿼리 문자열(설명 포함) + 플래너 관련 GUC 파라미터(`search_path` 포함) + 사용자 ID + 데이터베이스 ID 인스턴스가 재시작되면 공유 캐시가 재설정됩니다. ## Parameters 다음 표에서는 공유 계획 캐시 기능을 제어하는 파라미터를 설명합니다. | 파라미터 | 설명 | 기본값 | 허용됨 | | --- | --- | --- | --- | | apg\$1shared\$1plan\$1cache.enable | 공유 계획 캐시를 켜거나 끕니다. | 0(꺼짐) | 0, 1 | | apg\$1shared\$1plan\$1cache.max | 캐시 항목의 최대 개수 | 200\$11000(인스턴스 크기에 따라 다름) | 100–50000 | | apg\$1shared\$1plan\$1cache.min\$1size\$1per\$1entry | 공유 캐시에 저장할 최소 계획 크기. 규모가 작은 계획은 로컬 캐시를 사용하여 OLTP 성능을 최적화합니다. | 16KB | 0\$132768(KB) | | apg\$1shared\$1plan\$1cache.max\$1size\$1per\$1entry | 공유 캐시의 최대 계획 크기. 규모가 큰 계획은 비용 정보만 저장합니다. | 256KB\$14MB(인스턴스 크기에 따라 다름) | 0\$132768(KB) | | apg\$1shared\$1plan\$1cache.idle\$1generic\$1plan\$1release\$1timeout | 유휴 세션에서 로컬 일반 계획이 해제되기까지 걸리는 시간입니다. 값이 낮을수록 메모리가 절약되고 값이 높을수록 성능이 향상될 수 있습니다. | 10초 | 0\$12147483647(ms) | **참고** 재시작하지 않고도 모든 파라미터를 수정할 수 있습니다. ## 보기 및 함수 모니터링 + `apg_shared_plan_cache()` - 세부 캐시 항목 정보(적중 횟수, 유효성, 타임스탬프)를 표시합니다. + `apg_shared_plan_cache_stat()` - 인스턴스 수준의 통계(제거, 무효화)를 표시합니다. + `apg_shared_plan_cache_reset()` - `apg_shared_plan_cache()` 및 `apg_shared_plan_cache_stat()`의 모든 항목을 제거합니다. + `apg_shared_plan_cache_remove(cache_key)` - 항목이 `cache_key`와 일치하는 경우 `apg_shared_plan_cache()`에서 해당 항목을 제거합니다. ## 제한 사항 + 준비된 문에서만 작동하며 PL/pgSQL 문은 캐싱하지 않음 + 임시 테이블 또는 카탈로그 테이블이 포함된 쿼리는 캐싱하지 않음 + RLS(Row-Level Security)에 의존하는 쿼리는 캐싱하지 않음 + 각 복제본이 자체 캐시를 유지함(교차 복제본 공유 없음)