Amazon Aurora MySQL 개요

Aurora MySQL 버전 3에서의 새로운 임시 테이블 동작 과정

Estimated reading: 6 minutes 35 views

원본 문서: New temporary table behavior in Aurora MySQL version 3

Aurora MySQL 버전 3에서는 임시 테이블(temporary table)을 기존 버전들과 다르게 처리합니다. 이러한 새로운 동작 방식은 MySQL 8.0 커뮤니티 에디션에서 가져온 것입니다. Aurora MySQL 버전 3에서는 다음 두 가지 유형의 임시 테이블을 생성할 수 있습니다:

  • 내부(또는 묵시적) 임시 테이블 – 정렬, 집계, 파생 테이블, 공통 테이블 표현식(CTE)과 같은 작업을 처리하기 위해 Aurora MySQL 엔진에 의해 생성됩니다.
  • 사용자 생성(또는 명시적) 임시 테이블 – 사용자가 CREATE TEMPORARY TABLE 문을 사용하였을 때에 Aurora MySQL 엔진에 의해 생성됩니다.

이러한 내부 및 사용자 생성 임시 테이블 각각에 대하여 Aurora 리더 DB 인스턴스에서 추가 고려해야할 사항이 있습니다. 다음 섹션에서 이러한 변경 사항에 대해 설명합니다.

내부(묵시적) 임시 테이블의 스토리지 엔진

Aurora MySQL은 중간 결과 집합을 생성할 때 처음에는 메모리 내 임시 테이블에 쓰려고 시도합니다. 그러나 데이터 유형이 호환되지 않거나 설정된 제한을 초과하면 이 작업이 실패할 수 있습니다. 이 경우 임시 테이블은 메모리에 유지되는 대신 디스크상의 임시 테이블로 변환됩니다. 자세한 내용은 MySQL 다큐멘테이션의 MySQL에서 내부 임시 테이블 사용을 참조하세요.

Aurora MySQL 버전 3에서는 내부 임시 테이블이 작동하는 방식이 이전 Aurora MySQL 버전과 다릅니다. 기존처럼 임시 테이블에 대해 InnoDB 및 MyISAM 스토리지 엔진 중에서 선택하는 것이 아니라, 이제 TempTableMEMORY 스토리지 엔진 중에서 선택하게 됩니다.

TempTable 스토리지 엔진을 사용하면 특정 데이터를 처리하는 방법에 대해 추가적인 선택을 할 수 있습니다. 여기서 특정 데이터라는 것은, DB 인스턴스의 모든 내부 임시 테이블에서 사용하기 위한 공통 메모리 풀이 있는데, 이 메모리 풀의 크기를 넘어선(overflow) 데이터를 말합니다.

즉, 이때의 추가적인 선택은, 큰테이블에서 GROUP BY와 같은 집계를 수행하는 것과 같이 대량의 임시 데이터를 생성하는 쿼리의 성능에 영향을 줄 수 있는 것입니다.


워크로드에 내부 임시 테이블을 생성하는 쿼리가 포함된 경우, 벤치마크를 실행하고 성능 관련 지표를 모니터링하여 애플리케이션이 이러한 변경 사항에 어떻게 반응하는지 확인하세요.
일부 경우, 임시 데이터 양이 TempTable 메모리 풀 내에 적합하거나 메모리 풀을 약간 초과하는 정도에 불과합니다. 이 경우, 내부 임시 테이블을 위한 TempTable 설정과 메모리 맵 파일을 사용하여 초과 데이터를 보관하는 것을 권장합니다. 이 설정이 기본값입니다.

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

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

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

Aurora MySQL은 데이터 오버플로 대상 및 쿼리가 실행되는 DB 인스턴스 유형이 라이터인지 리더인지에 따라 오버플로 데이터를 다르게 저장합니다.

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

클러스터 내 라이터, 리더 인스턴스에 따라 내부 임시 테이블과 관련된 구성 파라미터가 다르게 적용됩니다:

  • 리더 인스턴스에서는, Aurora MySQL이 항상 TempTable 스토리지 엔진을 사용합니다.
  • temptable_max_mmap의 기본값은 DB 인스턴스 메모리 크기와 관계없이 라이터 및 리더 인스턴스 모두에서 1 GiB입니다. 이 값을 라이터 및 리더 인스턴스 모두에서 조정할 수 있습니다.
  • 라이터 인스턴스에서는, temptable_max_mmap 값을 0으로 설정하면 메모리 매핑된 임시 파일 사용이 비활성화됩니다.
  • 리더 인스턴스에서는, temptable_max_mmap 값을 0으로 설정할 수 없습니다.
참고
temptable_use_mmap 파라미터는 더 이상 사용을 권장하지 않으며, 이미 사용 중단 상태(deprecated)이고, 향후 MySQL 릴리스에서 완저히 지원이 중단될 예정입니다.

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

내부(묵시적) 임시 테이블의 스토리지 엔진에서 설명한 것처럼, temptable_max_ramtemptable_max_mmap 설정을 통해 전역적으로 임시 테이블 리소스를 제어할 수 있습니다.

또한, tmp_table_size DB 파라미터를 사용하여 개별 내부 메모리 임시 테이블의 크기를 제한할 수도 있습니다. 이 제한을 통해 개별 쿼리가 글로벌 임시 테이블 리소스를 과도하게 사용하는 것을 방지하여, 이러한 리소스를 동시에 필요로 하는 서로 다른 쿼리들의 성능에 영향을 미치지 않도록 합니다.

tmp_table_size 파라미터는 Aurora MySQL 버전 3에서 MEMORY 스토리지 엔진으로 생성된 임시 테이블의 최대 크기를 정의합니다.

Aurora MySQL 버전 3.04 이상에서는 aurora_tmptable_enable_per_table_limit DB 파라미터가 ON으로 설정된 경우, tmp_table_sizeTempTable 스토리지 엔진으로 생성된 임시 테이블의 최대 크기를 정의합니다. 이 동작은 기본적으로 OFF로 설정되어 있으며, 이는 Aurora MySQL 버전 3.03 이하 버전과 동일한 동작입니다.

  • aurora_tmptable_enable_per_table_limitOFF인 경우, TempTable 스토리지 엔진으로 생성된 내부 메모리 임시 테이블에는 tmp_table_size가 적용되지 않습니다.
    그러나 글로벌 TempTable 리소스 제한은 여전히 적용됩니다. 글로벌 TempTable 리소스 제한에 도달할 경우, Aurora MySQL은 다음과 같이 동작합니다:
    • 라이터 인스턴스 – 메모리 내 임시 테이블이 자동으로 InnoDB 디스크 기반 임시 테이블로 변환됩니다.
    • 리더 인스턴스 – 쿼리가 오류와 함께 종료됩니다.
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full
  • aurora_tmptable_enable_per_table_limitON인 경우, tmp_table_size 제한에 도달할 때 Aurora MySQL의 동작은 다음과 같습니다:
    • 라이터 인스턴스 – 메모리 내 임시 테이블이 자동으로 InnoDB 디스크 기반 임시 테이블로 변환됩니다.
    • 리더 인스턴스 – 쿼리가 오류와 함께 종료됩니다.
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

이 경우 전역 TempTable 리소스 제한과 테이블당 제한이 둘다 모두 적용됩니다.

참고
internal_tmp_mem_storage_engineMEMORY로 설정된 경우에 aurora_tmptable_enable_per_table_limit 파라미터는 아무 효과가 없습니다. 이 경우, 메모리 내 임시 테이블의 최대 크기는 tmp_table_size 또는 max_heap_table_size 값 중 작은 값으로 결정됩니다.

다음 예제는 쓰기 및 리더 DB 인스턴스에서 aurora_tmptable_enable_per_table_limit 파라미터가 설정된 경우의 동작을 보여줍니다.

aurora_tmptable_enable_per_table_limit가 OFF로 설정된 라이터 인스턴스 예시

메모리 내 임시 테이블이 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으로 설정된 라이터 인스턴스 예시

메모리 내 임시 테이블이 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로 설정된 리더 인스턴스 예시

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로 설정된 리더 인스턴스 예시

이 쿼리는 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으로 설정된 리더 인스턴스 예시

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에서 사용 가능한 메모리 문제 해결을 참조하세요.

또한 로컬 스토리지 크기와 임시 테이블 공간 소비량을 모니터링하는 것도 중요합니다. 특정 DB 인스턴스의 사용 가능한 임시 스토리지를 모니터링하려면 Amazon CloudWatch의 FreeLocalStorage 지표를 사용하세요.

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

예제 1

임시 테이블이 최대 20 GiB까지 증가할 수 있다고 가정합시다. 메모리 내 임시 테이블 크기를 2 GiB로 설정하고 디스크에서 최대 20 GiB까지 확장되도록 설정하려 합니다.

temptable_max_ram2,147,483,648로 설정하고, temptable_max_mmap21,474,836,480로 설정합니다. 이 값은 바이트 단위입니다.

이러한 파라미터 설정을 통해 임시 테이블이 누적 총 22GiB까지 증가할 수 있습니다.

예제 2

현재 인스턴스 크기는 16xlarge 이상입니다. 필요한 임시 테이블의 총 크기는 알 수 없습니다. 메모리에서 최대 4 GiB를 사용하고 디스크에서 최대 사용 가능한 스토리지 크기까지 확장하려 합니다.

temptable_max_ram4,294,967,296로 설정하고, temptable_max_mmap1,099,511,627,776로 설정합니다. 이 값은 바이트 단위입니다.

여기서 temptable_max_mmap을 1TiB로 설정하는데, 이는 16xlarge Aurora DB 인스턴스에서 사용 가능한 최대 로컬 스토리지 사이즈인 1.2TiB보다 작습니다.

더 작은 크기의 인스턴스의 경우 사용 가능한 로컬 스토리지를 초과하지 않도록 temptable_max_mmap 값을 조정해야 합니다. 예를 들어, 2xlarge 인스턴스에는 160 GiB의 로컬 스토리지가 제공됩니다. 따라서 값을 160 GiB 이하로 설정하는 것이 좋습니다. DB 인스턴스 크기에 따른 로컬 스토리지에 대한 자세한 정보는 Aurora MySQL 임시 스토리지 제한을 참조하세요.

리더 DB 인스턴스에서의 사용자 생성(명시적) 임시 테이블

CREATE TABLE 문에서 TEMPORARY 키워드를 사용하여 명시적 임시 테이블을 생성할 수 있습니다. 명시적 임시 테이블은 Aurora DB 클러스터의 라이터 DB 인스턴스에서 지원됩니다. 리더 DB 인스턴스에서도 명시적 임시 테이블을 사용할 수 있지만, InnoDB 스토리지 엔진을 강제로 사용할 수는 없습니다.

Aurora MySQL 리더 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 모드가 켜져 있으면 문이 실패합니다. 이 모드를 꺼둔 경우(기본값)에는 적절한 엔진으로 대체되어 임시 테이블 생성이 성공합니다.

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 스토리지 엔진 사용을 참조하세요.

Leave a Comment



이 문서 공유

Aurora MySQL 버전 3에서의 새로운 임시 테이블 동작 과정

링크 복사

CONTENTS