Aurora MySQL 버전 3의 새로운 임시 테이블 동작
Aurora MySQL 버전 3에서는 이전 Aurora MySQL 버전과 다르게 임시 테이블을 처리합니다. 이 새로운 동작은 MySQL 8.0 커뮤니티 에디션에서 상속되었습니다. Aurora MySQL 버전 3에서 생성할 수 있는 임시 테이블에는 다음과 같이 2가지 유형이 있습니다.
-
내부(또는 암묵적) 임시 테이블 – Aurora MySQL 엔진이 집계 정렬, 파생 테이블 또는 공통 테이블 표현식(CTE)과 같은 작업을 처리하기 위해 생성합니다.
-
사용자 생성(또는 명시적) 임시 테이블 -
CREATE TEMPORARY TABLE
문을 사용할 때 Aurora MySQL 엔진에서 생성합니다.
Aurora 리더 DB 인스턴스의 내부 및 사용자 생성 임시 테이블 모두에 대해 추가로 고려해야 할 점이 있습니다. 다음 섹션에서 이러한 변경 사항에 대해 살펴봅니다.
주제
내부(암묵적) 임시 테이블에 대한 스토리지 엔진
중간 결과 세트를 생성할 때 Aurora MySQL은 처음에 메모리 내 임시 테이블에 쓰기를 시도합니다. 호환되지 않는 데이터 유형이나 구성된 제한으로 인해 실패할 수 있습니다. 그렇다면 임시 테이블은 메모리에 보관되지 않고 온디스크 임시 테이블로 변환됩니다. 이에 대한 자세한 내용은 MySQL 설명서의 MySQL의 내부 임시 테이블 사용
Aurora MySQL 버전 3에서는 내부 임시 테이블이 작동하는 방식이 이전 Aurora MySQL 버전과 다릅니다. 이러한 임시 테이블에 대해 InnoDB와 MyISAM 스토리지 엔진 중에서 선택하지 않고 이제 TempTable
및 MEMORY
스토리지 엔진 중에서 선택합니다.
TempTable
스토리지 엔진을 사용하면 특정 데이터를 처리하는 방법을 추가로 선택할 수 있습니다. 영향을 받은 데이터는 DB 인스턴스의 모든 내부 임시 테이블을 저장하는 메모리 풀을 오버플로합니다.
예를 들어 대형 테이블에 GROUP BY
와 같은 집계를 수행하는 동안 이러한 선택은 많은 양의 임시 데이터를 생성하는 쿼리의 성능에 영향을 줄 수 있습니다.
작은 정보
워크로드에 내부 임시 테이블을 생성하는 쿼리가 포함되어 있는 경우 벤치마크를 실행하고 성능 관련 지표를 모니터링하여 애플리케이션이 이 변경으로 수행하는 방법을 확인합니다.
경우에 따라 임시 데이터의 양을 TempTable
메모리 풀 내에 맞추거나 적은 양으로 메모리 풀만 오버플로합니다. 이러한 경우 내부 임시 테이블 및 메모리 매핑 파일에 대한 TempTable
설정을 사용하여 오버플로 데이터를 저장하는 것이 좋습니다. 이게 기본 설정입니다.
TempTable
스토리지 엔진이 기본값입니다. TempTable
에서는 테이블당 최대 메모리 제한 대신 이 엔진을 사용하는 모든 임시 테이블에 대한 공통 메모리 풀을 사용합니다. 이 메모리 풀의 크기는 temptable_max_ram
경우에 따라 TempTable
스토리지 엔진을 사용할 때 임시 데이터가 메모리 풀의 크기를 초과할 수 있습니다. 그렇다면 Aurora MySQL은 보조 메커니즘을 사용하여 오버플로 데이터를 저장합니다.
temptable_max_mmap
Aurora MySQL은 선택한 데이터 오버플로 대상과 쿼리가 작성자 또는 리더 DB 인스턴스에서 실행되는지 여부에 따라 오버플로 데이터를 다르게 저장합니다.
-
작성기 인스턴스에서 InnoDB 내부 임시 테이블로 오버플로되는 데이터는 Aurora 클러스터 볼륨에 저장됩니다.
-
작성기 인스턴스에서 메모리 매핑된 임시 파일로 오버플로되는 데이터는 Aurora MySQL 버전 3 인스턴스의 로컬 스토리지에 상주합니다.
-
리더 인스턴스에서 오버플로 데이터는 항상 로컬 스토리지의 메모리 매핑된 임시 파일에 상주합니다. 읽기 전용 인스턴스가 Aurora 클러스터 볼륨에 데이터를 저장할 수 없기 때문입니다.
내부 임시 테이블과 관련된 구성 파라미터는 클러스터의 라이터 및 리더 인스턴스와 다르게 적용됩니다.
-
리더 인스턴스의 경우 Aurora MySQL은 항상
TempTable
스토리지 엔진을 사용합니다. -
DB 인스턴스 메모리 크기에 관계없이 라이터 인스턴스와 리더 인스턴스 모두에 대해
temptable_max_mmap
의 기본값 크기는 1GiB입니다. 라이터 및 리더 인스턴스 둘 다에서 이 값을 조정할 수 있습니다. -
temptable_max_mmap
을0
으로 설정하면 라이터 인스턴스에서 메모리 매핑된 임시 파일 사용이 비활성화됩니다. -
리더 인스턴스에서는
temptable_max_mmap
을0
으로 설정할 수 없습니다.
참고
temptable_use_mmap
내부 인 메모리 임시 테이블의 크기 제한
내부(암묵적) 임시 테이블에 대한 스토리지 엔진에서 설명한 바와 같이 temptable_max_ram
또한 tmp_table_size
tmp_table_size
파라미터는 Aurora MySQL 버전 3의 MEMORY
스토리지 엔진이 생성한 임시 테이블의 최대 크기를 정의합니다.
Aurora MySQL 버전 3.04 이상에서는 tmp_table_size
가 aurora_tmptable_enable_per_table_limit
DB 파라미터가 ON
으로 설정되었을 때 TempTable
스토리지 엔진이 생성한 임시 테이블의 최대 크기도 정의합니다. 이 동작은 기본적으로 비활성화(OFF
)되며, 이는 Aurora MySQL 버전 3.03 이하 버전에서와 동일합니다.
-
aurora_tmptable_enable_per_table_limit
이OFF
로 설정되었을 때tmp_table_size
는TempTable
스토리지 엔진이 생성한 내부 인 메모리 임시 테이블에 고려되지 않습니다.그러나 글로벌
TempTable
리소스 제한은 계속 적용됩니다. Aurora MySQL은 글로벌TempTable
리소스 제한에 도달했을 때 다음과 같이 동작합니다.-
라이터 DB 인스턴스 - Aurora MySQL이 인 메모리 임시 테이블을 InnoDB 온 디스크 임시 테이블로 자동 변환합니다.
-
리더 DB 인스턴스 - 쿼리가 오류와 함께 종료됩니다.
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql
xx_xxx
' is full
-
-
aurora_tmptable_enable_per_table_limit
이ON
인 경우 Aurora MySQL은tmp_table_size
제한에 도달했을 때 다음과 같이 동작합니다.-
라이터 DB 인스턴스 - Aurora MySQL이 인 메모리 임시 테이블을 InnoDB 온 디스크 임시 테이블로 자동 변환합니다.
-
리더 DB 인스턴스 - 쿼리가 오류와 함께 종료됩니다.
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sql
xx_xxx
' is full이 경우 글로벌
TempTable
리소스 제한과 테이블당 제한이 모두 적용됩니다.
-
참고
aurora_tmptable_enable_per_table_limit
파라미터는 internal_tmp_mem_storage_engineMEMORY
로 설정된 경우 아무런 효과가 없습니다. 이 경우 인 메모리 임시 테이블의 최대 크기는 tmp_table_size
다음 예시는 라이터 및 리더 DB 인스턴스에 대한 aurora_tmptable_enable_per_table_limit
파라미터의 동작을 보여줍니다.
예 aurora_tmptable_enable_per_table_limit
이 OFF
로 설정된 경우 라이터 DB 인스턴스
인 메모리 임시 테이블이 InnoDB 온 디스크 임시 테이블로 변환되지 않습니다.
mysql> set aurora_tmptable_enable_per_table_limit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap; +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | 0 | 3.04.0 | 0 | 1073741824 | 1073741824 | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ 1 row in set (0.00 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte; +----------+ | max(n) | +----------+ | 60000000 | +----------+ 1 row in set (13.99 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | +-------------------------+-------+ 1 row in set (0.00 sec)
예 aurora_tmptable_enable_per_table_limit
이 ON
으로 설정된 경우 라이터 DB 인스턴스
인 메모리 임시 테이블이 InnoDB 온 디스크 임시 테이블로 변환됩니다.
mysql> set aurora_tmptable_enable_per_table_limit=1; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size; +--------------------+------------------+------------------------------------------+------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size | +--------------------+------------------+------------------------------------------+------------------+ | 0 | 3.04.0 | 1 | 16777216 | +--------------------+------------------+------------------------------------------+------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | +-------------------------+-------+ 1 row in set (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte; +---------+ | max(n) | +---------+ | 6000000 | +---------+ 1 row in set (4.10 sec) mysql> show status like '%created_tmp_disk%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | +-------------------------+-------+ 1 row in set (0.00 sec)
예 aurora_tmptable_enable_per_table_limit
이 OFF
로 설정된 경우 리더 DB 인스턴스
쿼리가 오류 없이 완료되는 이유는 tmp_table_size
가 적용되지 않으며 글로벌 TempTable
리소스 제한에 도달하지 않았기 때문입니다.
mysql> set aurora_tmptable_enable_per_table_limit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap; +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | 1 | 3.04.0 | 0 | 1073741824 | 1073741824 | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte; +----------+ | max(n) | +----------+ | 60000000 | +----------+ 1 row in set (14.05 sec)
예 aurora_tmptable_enable_per_table_limit
이 OFF
로 설정된 경우 리더 DB 인스턴스
이 쿼리는 aurora_tmptable_enable_per_table_limit
이 OFF로 설정된 경우 글로벌 TempTable 리소스 제한에 도달합니다. 리더 인스턴스에 오류가 있는 상태로 쿼리가 종료됩니다.
mysql> set aurora_tmptable_enable_per_table_limit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap; +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ | 1 | 3.04.0 | 0 | 1073741824 | 1073741824 | +--------------------+------------------+------------------------------------------+---------------------+----------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.01 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte; ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
예 aurora_tmptable_enable_per_table_limit
이 ON
로 설정된 경우 리더 DB 인스턴스
tmp_table_size
제한에 도달하면 오류와 함께 쿼리가 종료됩니다.
mysql> set aurora_tmptable_enable_per_table_limit=1; Query OK, 0 rows affected (0.00 sec) mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size; +--------------------+------------------+------------------------------------------+------------------+ | @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size | +--------------------+------------------+------------------------------------------+------------------+ | 1 | 3.04.0 | 1 | 16777216 | +--------------------+------------------+------------------------------------------+------------------+ 1 row in set (0.00 sec) mysql> set cte_max_recursion_depth=4294967295; Query OK, 0 rows affected (0.00 sec) mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte; ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full
Aurora 복제본의 내부 임시 테이블에 대한 완전성 문제 완화
임시 테이블의 크기 제한 문제를 방지하려면 temptable_max_ram
및 temptable_max_mmap
파라미터를 워크로드의 요구 사항에 맞출 수 있는 결합된 값으로 설정합니다.
temptable_max_ram
파라미터 값을 설정할 때는 주의해야 합니다. 값을 너무 높게 설정하면 데이터베이스 인스턴스에서 사용 가능한 메모리가 줄어들어 메모리 부족 상태가 발생할 수 있습니다. DB 인스턴스의 평균 여유 메모리를 모니터링합니다. 그런 다음, temptable_max_ram
에 대한 적절한 값을 결정하여 인스턴스에 합리적인 수준의 여유 메모리가 계속해서 남아 있도록 합니다. 자세한 내용은 Amazon Aurora의 여유 메모리 부족 단원을 참조하십시오.
로컬 스토리지의 크기와 임시 테이블 공간 사용량을 모니터링하는 것도 중요합니다. FreeLocalStorage
에 설명된 Amazon Aurora에 대한 Amazon CloudWatch 지표 Amazon CloudWatch 지표를 사용하여 특정 DB 인스턴스에 사용할 수 있는 임시 스토리지를 모니터링할 수 있습니다.
참고
이 프로시저는 aurora_tmptable_enable_per_table_limit
파라미터가 ON
으로 설정된 경우에는 작동하지 않습니다. 자세한 내용은 내부 인 메모리 임시 테이블의 크기 제한 단원을 참조하십시오.
예 1
임시 테이블의 누적 크기가 20GiB까지 늘어난다는 점은 알고 있지만, 메모리 내 임시 테이블을 2GiB로 설정하고 디스크에서 최대 20GiB까지 확장하려고 합니다.
이 경우 temptable_max_ram
을 2,147,483,648
로, temptable_max_mmap
를 21,474,836,480
으로 설정합니다. 이러한 값은 바이트 단위입니다.
이러한 파라미터 설정을 사용하면 임시 테이블을 누적해서 총 22GiB로 확장할 수 있습니다.
예 2
현재 인스턴스 크기는 16xlarge 이상이고, 필요한 임시 테이블의 전체 크기를 알 수 없습니다. 메모리에서 최대 4GiB를, 디스크에서 사용 가능한 최대 스토리지 크기를 사용할 수 있기를 원합니다.
이 경우 temptable_max_ram
을 4,294,967,296
으로, temptable_max_mmap
를 1,099,511,627,776
으로 설정합니다. 이러한 값은 바이트 단위입니다.
여기서 temptable_max_mmap
을 16xlarge Aurora DB 인스턴스의 최대 로컬 스토리지인 1.2TiB보다 작은 1TiB로 설정합니다.
인스턴스 크기가 이보다 작은 경우 사용 가능한 로컬 스토리지를 채우지 않도록 temptable_max_mmap
의 값을 조정합니다. 예를 들어, 2xlarge 인스턴스에는 160GiB의 로컬 스토리지만 사용할 수 있습니다. 이 경우 값을 160GiB 미만으로 설정하는 것이 좋습니다. DB 인스턴스 크기에 사용할 수 있는 로컬 스토리지에 대한 자세한 내용은 Aurora MySQL에 대한 임시 스토리지 한도 섹션을 참조하세요.
리더 DB 인스턴스의 사용자 생성(명시적) 임시 테이블
CREATE TABLE
문에서 TEMPORARY
키워드를 사용하여 명시적 임시 테이블을 생성할 수 있습니다. Aurora DB 클러스터의 라이터 DB 인스턴스에서 명시적 임시 테이블이 지원됩니다. 리더 DB 인스턴스에서 명시적 임시 테이블을 사용할 수도 있지만, 이 테이블에서 InnoDB 스토리지 엔진을 사용할 수 없습니다.
Aurora 리더 DB 인스턴스에서 명시적 임시 테이블을 생성하는 동안 오류가 발생하지 않게 하려면 모든 CREATE TEMPORARY TABLE
문을 다음 두 방법 중 하나 또는 둘 다를 이용해 실행해야 합니다.
-
ENGINE=InnoDB
절은 지정하지 마십시오. -
SQL 모드를
NO_ENGINE_SUBSTITUTION
으로 설정하지 마십시오.
임시 테이블 생성 오류 및 완화
수신하는 오류는 일반 CREATE TEMPORARY TABLE
문 또는 변형 CREATE TEMPORARY TABLE AS SELECT
를 사용하는지에 따라 다릅니다. 다음 예에서는 서로 다른 형식의 오류를 보여줍니다.
이 임시 테이블 동작은 읽기 전용 인스턴스에만 적용됩니다. 이 첫 번째 예에서는 세션이 연결된 인스턴스의 종류임을 확인합니다.
mysql>
select @@innodb_read_only;+--------------------+ | @@innodb_read_only | +--------------------+ | 1 | +--------------------+
일반 CREATE TEMPORARY TABLE
문에서 명령문은 NO_ENGINE_SUBSTITUTION
SQL 모드가 켜져 있는 경우 실패합니다. NO_ENGINE_SUBSTITUTION
이 해제되면(기본값) 적절한 엔진 교체가 이루어지며, 임시 테이블 생성이 성공합니다.
mysql>
set sql_mode = 'NO_ENGINE_SUBSTITUTION';mysql>
CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB;ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).
mysql>
SET sql_mode = '';mysql>
CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB;mysql>
SHOW CREATE TABLE tt4\G*************************** 1. row *************************** Table: tt4 Create Table: CREATE TEMPORARY TABLE `tt4` ( `id` int DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TEMPORARY TABLE AS SELECT
문에서 명령문은 NO_ENGINE_SUBSTITUTION
SQL 모드가 켜져 있는 경우 실패합니다. NO_ENGINE_SUBSTITUTION
이 해제되면(기본값) 적절한 엔진 교체가 이루어지며, 임시 테이블 생성이 성공합니다.
mysql>
set sql_mode = 'NO_ENGINE_SUBSTITUTION';mysql>
CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1;ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).
mysql>
SET sql_mode = ''; mysql> show create table tt3;+-------+----------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------+ | tt3 | CREATE TEMPORARY TABLE `tt3` ( `id` int DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------+ 1 row in set (0.00 sec)
Aurora MySQL 버전 3에서 임시 테이블의 스토리지 측면 및 성능 영향에 대한 자세한 내용은 Amazon RDS for MySQL 및 Amazon Aurora MySQL의 TempTable 스토리지 엔진 사용