Aurora3.0 Mysql 8.0 TempTable engine 동작 이해

이슈

AWS RDS mariadb -> AWS Aurora MySQL 3.0 으로 마이그레이션 이후, 특정 OLAP 쿼리에서 아래 에러가 발생하였습니다.

java.sql.SQLException: The table '/rdsdbdata/tmp/#sql6f81_38f4982_2' is full

원인파악

Mysql 8.0에선 아래 조건이 만족될 때 내부적으로 임시 테이블을 생성합니다.
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

• Evaluation of [UNION](<https://dev.mysql.com/doc/refman/8.0/en/union.html>) statements, with some exceptions described later.
• Evaluation of some views, such those that use the TEMPTABLE algorithm, [UNION](<https://dev.mysql.com/doc/refman/8.0/en/union.html>), or aggregation.
• Evaluation of derived tables (see Section 15.2.15.8, “Derived Tables”).
• Evaluation of common table expressions (see Section 15.2.20, “WITH (Common Table Expressions)”).
• Tables created for subquery or semijoin materialization (see Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”).
• Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.
• Evaluation of DISTINCT combined with ORDER BY may require a temporary table.
• For queries that use the SQL_SMALL_RESULT modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
• To evaluate [INSERT ... SELECT](<https://dev.mysql.com/doc/refman/8.0/en/insert-select.html>) statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the [SELECT](<https://dev.mysql.com/doc/refman/8.0/en/select.html>), then inserts those rows into the target table. See Section 15.2.7.1, “INSERT ... SELECT Statement”.
• Evaluation of multiple-table [UPDATE](<https://dev.mysql.com/doc/refman/8.0/en/update.html>) statements.
• Evaluation of [GROUP_CONCAT()](<https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat>) or [COUNT(DISTINCT)](<https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count>) expressions.
• Evaluation of window functions (see Section 14.20, “Window Functions”) uses temporary tables as necessary.

TempTable engine은 기존에 임시 테이블에 사용되던 MEMORY engine과 비교하여 아래 장점이 있습니다.
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

• 가변 길이 데이터 타입의 효율적인 스토리지 제공
• 바이너리 오브젝트 타입 서포트
• 글로벌 메모리 영역에서의 관리
• mmap 파일에 의한 overflow

Aurora MySQL이 implicit하게 생성한 임시 테이블은 default TempTable engine이 사용됩니다.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams3-temptable-behavior.html

internal_tmp_mem_storage_engine = MEMORY로 설정하면 MEMORY engine을 사용할 수 있고 아래 옵션을 통해 테이블당 사용가능한 메모리를 설정할 수 있습니다. tmp_table_size, max_heap_table_size

*하지만 Aurora MySQL cluster reader에는 무조건 TempTable engine만 사용할 수 있습니다.

*Aurora specific한 기능: aurora_tmptable_enable_per_table_limit가 ON으로 설정되면 TempTable engine도 위 옵션을 사용하여 전역이 아니라 테이블당 memory size를 설정할 수 있습니다.

In Aurora MySQL version 3.04 and higher, tmp_table_size also defines the maximum size of temporary tables created by the TempTable storage engine when the aurora_tmptable_enable_per_table_limit DB parameter is set to ON

(현재 default off로 사용 중입니다.)

TempTable engine은 각 세션별로 생성하는 임시 테이블 생성을 위한 전역 memory pool을 관리하며 현재 default 1GB 로 설정되어있습니다.  temptable_max_ram

show variables like 'temptable_max_ram'

temptable_max_ram 가 모두 점유 중일 때는, 2가지 중 하나를 선택할 수 있습니다.

1. memory-mapped temporary files로 overflow
2. InnoDB internal temporary tables on disk (memory → disk로 data 복사 발생)

TempTable engine은 마찬가지로 mmap overflow를 위한 전역 변수를 관리하며 library는 default 1GB 로 되어있습니 다. temptable_max_mmap

show variables like 'temptable_max_mmap'

temptable_max_mmap=0으로 설정하면 2. 로 동작합니다.
*하지만 마찬가지로 Aurora MySQL cluster reader에는 temptable_max_mmap=0 설정이 불가능하다고 합니다.
*temptable_max_mmap 값은 운영 중인 인스턴스 사이즈 볼륨의 가용 범위내로 설정해야합니다.
만약 temptable_max_mmap 설정값을 넘어서게 되면 아래 에러가 발생합니다.

ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

TroubleShooting

아래는 실제 사용된 쿼리의 실행계획의 일부입니다.

explain format=json SELECT min(id), max(id) from (
    SELECT
        ~ 
    FROM my_db.my_table1 mt1
        LEFT JOIN (
            SELECT ~ 
            FROM my_db.my_tabl2
                JOIN ~ ON ~ 
                JOIN my_db.my_table1 mt1 ON ~
            GROUP BY ~
        ) ON ~
        LEFT JOIN ~ ON ~
    WHERE '20240314' <= mt1.reg_date and mt1.reg_date < '20240315'
) AS tmp_alias

"materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 3, "cost_info": { "query_cost": "1356821598.29" }

group by를 사용하는 left join의 derived table에서 temp table을 사용되는 것을 확인할 수 있습니다.

Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

이를 해결 하기 위해 가장 첫 번째로 Index를 올바르게 타고있는지 확인하였고, index hint를 이용해 가능한 index 조합을 테스트해보았습니다만 동일했습니다.

그러다 쿼리 실행계획을 다시 유심히 살펴보니, outer where 조건이 subquery(derived table)로 push down되는 방향으로 최적화가 되지 않아, 매우 많은 불필요한 row를 scan하고 있었습니다.

이는 쿼리 작성자가 from 절에 사용되는 subqery에 outer where가 push down 되는 subqery predicate push down과 착각하여 발생한 이슈였고, left join derived table 절에 outer와 동일한 predicate를 추가해준 뒤 문제는 해결 되었습니다.

지금까지 OLAP Query 에서 맞닥뜨린 Temp Table 관련 문제와 이에 대한 동작이해를 다루어보았고, 간단한 해결 경험담을 나누어보았습니다.

감사합니다.