Aurora MySQL 버전 3의 새로운 임시 테이블 동작 - Amazon Aurora

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 및 InnoDB 스토리지 엔진 중에서 선택합니다.

TempTable 스토리지 엔진을 사용하면 특정 데이터를 처리하는 방법을 추가로 선택할 수 있습니다. 영향을 받은 데이터는 DB 인스턴스의 모든 내부 임시 테이블을 저장하는 메모리 풀을 오버플로합니다.

예를 들어 대형 테이블에 GROUP BY와 같은 집계를 수행하는 동안 이러한 선택은 많은 양의 임시 데이터를 생성하는 쿼리의 성능에 영향을 줄 수 있습니다.

작은 정보

워크로드에 내부 임시 테이블을 생성하는 쿼리가 포함되어 있는 경우 벤치마크를 실행하고 성능 관련 지표를 모니터링하여 애플리케이션이 이 변경으로 수행하는 방법을 확인합니다.

경우에 따라 임시 데이터의 양을 TempTable 메모리 풀 내에 맞추거나 적은 양으로 메모리 풀만 오버플로합니다. 이러한 경우 내부 임시 테이블 및 메모리 매핑 파일에 대한 TempTable 설정을 사용하여 오버플로 데이터를 저장하는 것이 좋습니다. 이게 기본 설정입니다.

TempTable 스토리지 엔진이 기본값입니다. TempTable에서는 테이블당 최대 메모리 제한 대신 이 엔진을 사용하는 모든 임시 테이블에 대한 공통 메모리 풀을 사용합니다. 이 메모리 풀의 크기는 temptable_max_ram 파라미터에서 지정합니다. 메모리가 16GiB 이상인 DB 인스턴스에서는 1GiB, 메모리가 16GiB 미만인 DB 인스턴스에서는 16MB가 기본값으로 설정됩니다. 메모리 풀의 크기는 세션 수준 메모리 소비에 영향을 줍니다.

경우에 따라 TempTable 스토리지 엔진을 사용할 때 임시 데이터가 메모리 풀의 크기를 초과할 수 있습니다. 그렇다면 Aurora MySQL은 보조 메커니즘을 사용하여 오버플로 데이터를 저장합니다.

temptable_max_mmap 파라미터를 설정하여 데이터가 메모리 매핑된 임시 파일에 오버플로될지, 디스크의 InnoDB 내부 임시 테이블에 오버플로될지 선택할 수 있습니다. 이러한 오버플로 메커니즘의 다양한 데이터 형식과 오버플로 기준은 쿼리 성능에 영향을 줄 수 있습니다. 이렇게 하려면 디스크에 기록되는 데이터의 양과 디스크 스토리지 처리량에 대한 수요에 영향을 주면 됩니다.

Aurora MySQL은 선택한 데이터 오버플로 대상과 쿼리가 작성자 또는 리더 DB 인스턴스에서 실행되는지 여부에 따라 오버플로 데이터를 다르게 저장합니다.

  • 작성기 인스턴스에서 InnoDB 내부 임시 테이블로 오버플로되는 데이터는 Aurora 클러스터 볼륨에 저장됩니다.

  • 작성기 인스턴스에서 메모리 매핑된 임시 파일로 오버플로되는 데이터는 Aurora MySQL 버전 3 인스턴스의 로컬 스토리지에 상주합니다.

  • 리더 인스턴스에서 오버플로 데이터는 항상 로컬 스토리지의 메모리 매핑된 임시 파일에 상주합니다. 읽기 전용 인스턴스가 Aurora 클러스터 볼륨에 데이터를 저장할 수 없기 때문입니다.

내부 임시 테이블과 관련된 구성 파라미터는 클러스터의 라이터 및 리더 인스턴스와 다르게 적용됩니다.

  • 리더 인스턴스의 경우 Aurora MySQL은 항상 TempTable 스토리지 엔진을 사용합니다.

  • DB 인스턴스 메모리 크기에 관계없이 라이터 인스턴스와 리더 인스턴스 모두에 대해 temptable_max_mmap의 기본값 크기는 1GiB입니다. 라이터 및 리더 인스턴스 둘 다에서 이 값을 조정할 수 있습니다.

  • temptable_max_mmap0으로 설정하면 라이터 인스턴스에서 메모리 매핑된 임시 파일 사용이 비활성화됩니다.

  • 리더 인스턴스에서는 temptable_max_mmap0으로 설정할 수 없습니다.

참고

temptable_use_mmap 파라미터를 사용하지 않는 것이 좋습니다. 이 파라미터는 사용이 중단되었으며 향후 MySQL 릴리스에서 지원이 제거될 예정입니다.

내부 인 메모리 임시 테이블의 크기 제한

내부(암묵적) 임시 테이블에 대한 스토리지 엔진에서 설명한 바와 같이 temptable_max_ram 및 temptable_max_mmap 설정을 사용하여 임시 테이블 리소스를 전역적으로 제어할 수 있습니다.

또한 tmp_table_size DB 파라미터를 사용하여 개별 내부 인 메모리 임시 테이블의 크기를 제한할 수 있습니다. 이 제한은 개별 쿼리가 과도한 양의 글로벌 임시 테이블 리소스를 소비하는 것을 방지하기 위한 것이며, 이러한 리소스가 필요한 동시 쿼리의 성능에 영향을 미칠 수 있습니다.

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_limitOFF로 설정되었을 때 tmp_table_size는 TempTable 스토리지 엔진이 생성한 내부 인 메모리 임시 테이블에 고려되지 않습니다.

    그러나 글로벌 TempTable 리소스 제한은 계속 적용됩니다. Aurora MySQL은 글로벌 TempTable 리소스 제한에 도달했을 때 다음과 같이 동작합니다.

    • 라이터 DB 인스턴스 - Aurora MySQL이 인 메모리 임시 테이블을 InnoDB 온 디스크 임시 테이블로 자동 변환합니다.

    • 리더 DB 인스턴스 - 쿼리가 오류와 함께 종료됩니다.

      ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_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/#sqlxx_xxx' is full

      이 경우 글로벌 TempTable 리소스 제한과 테이블당 제한이 모두 적용됩니다.

참고

aurora_tmptable_enable_per_table_limit 파라미터는 internal_tmp_mem_storage_engine이 MEMORY로 설정된 경우 아무런 효과가 없습니다. 이 경우 인 메모리 임시 테이블의 최대 크기는 tmp_table_size 또는 max_heap_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_ramtemptable_max_mmap 파라미터를 워크로드의 요구 사항에 맞출 수 있는 결합된 값으로 설정합니다.

temptable_max_ram 파라미터 값을 설정할 때는 주의해야 합니다. 값을 너무 높게 설정하면 데이터베이스 인스턴스에서 사용 가능한 메모리가 줄어들어 메모리 부족 상태가 발생할 수 있습니다. DB 인스턴스의 평균 여유 메모리를 모니터링합니다. 그런 다음, temptable_max_ram에 대한 적절한 값을 결정하여 인스턴스에 합리적인 수준의 여유 메모리가 계속해서 남아 있도록 합니다. 자세한 내용은 Amazon Aurora의 여유 메모리 부족 섹션을 참조하세요.

로컬 스토리지의 크기와 임시 테이블 공간 사용량을 모니터링하는 것도 중요합니다. 인스턴스의 로컬 스토리지 모니터링에 대한 자세한 내용은 AWS 지식 센터 문서 Aurora MySQL 호환 로컬 스토리지에 무엇이 저장되며, 로컬 스토리지 문제를 해결하려면 어떻게 해야 합니까?를 참조하세요.

참고

이 프로시저는 aurora_tmptable_enable_per_table_limit 파라미터가 ON으로 설정된 경우에는 작동하지 않습니다. 자세한 내용은 내부 인 메모리 임시 테이블의 크기 제한 섹션을 참조하세요.

예 1

임시 테이블의 누적 크기가 20GiB까지 늘어난다는 점은 알고 있지만, 메모리 내 임시 테이블을 2GiB로 설정하고 디스크에서 최대 20GiB까지 확장하려고 합니다.

이 경우 temptable_max_ram2,147,483,648로, temptable_max_mmap21,474,836,480으로 설정합니다. 이러한 값은 바이트 단위입니다.

이러한 파라미터 설정을 사용하면 임시 테이블을 누적해서 총 22GiB로 확장할 수 있습니다.

예 2

현재 인스턴스 크기는 16xlarge 이상이고, 필요한 임시 테이블의 전체 크기를 알 수 없습니다. 메모리에서 최대 4GiB를, 디스크에서 사용 가능한 최대 스토리지 크기를 사용할 수 있기를 원합니다.

이 경우 temptable_max_ram4,294,967,296으로, temptable_max_mmap1,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 스토리지 엔진 사용 블로그 게시물을 참조하세요.