현상
Aurora DB 관련하여 large-scale 쿼리 or 배치를 동작할 때 tmp full 에러가 발생합니다.
error code [xxxx]; The table '/rdsdbdata/tmp/#sqlxxxx_yyyy_z' is full 과 같은 형식의 에러 메시지가 발생하는데, 이러한 에러를 방지하는 방법이 있나요?
설명
일반적으로 해당 에러 코드는 MySQL 8.x 버전에서 발생하는 이슈입니다.
https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_use.html
해당 오류에 관해 설명해 드리면,
MySQL 8.0에는 TempTable이라는 새로운 스토리지 엔진이 도입되었으며, 이전에는 모든 내부 Temp Table이 Memory엔진으로 생성되었습니다. TempTable 도입으로 인해 ‘internal_tmp_mem_storage_engine’ 이라는 새로운 변수가 생성되었으며 이 변수의 기본값은 TempTable입니다.
이 TempTable 엔진은 ‘temptable_max_ram’ 변수를 사용하여 정의할 수 있는 최대 RAM 용량을 사용할 때 메모리 매핑된 임시 파일을 생성하여 디스크 공간을 차지합니다. 여기서 문제는 디스크의 이러한 메모리 매핑 파일이 /tmp 디렉토리에 생성되는데, 이 파티션이 가득 차면 쿼리가 실패하고 “table is full error”가 표시됩니다.
아래는 해당 버그 관련 MySQL 공식 문서입니다.
[+]https://bugs.mysql.com/bug.php?id=99100
해결방법
이 오류를 해결하기 위해 권장되는 해결 방법은 변수 ‘internal_tmp_mem_storage_engine’ 을 “TempTable”에서 “MEMORY”로 설정하는 것입니다.
(해당 변수를 변경함으로써 발생할 수 있는 문제를 최소화하기 위해서, 먼저 테스트 환경에서 테스트를 진행하신 후 프로덕션 환경에 배포해주시기 바랍니다.)‘temptable_max_ram’ 변수를 더 높은 값으로 변경하는 것도 고려할 수 있습니다. ‘temptable_max_ram’ 변수(Dynamic)는 TempTable 스토리지 엔진이 메모리 매핑된 임시 파일 또는 InnoDB on disk 내부 임시 테이블 형식으로 디스크에서 공간을 할당하기 전에 점유할 수 있는 최대 RAM 양을 정의합니다. 기본 ‘temptable_max_ram’ 설정은 1GiB입니다.
MEMORY 스토리지 엔진을 사용하실 때 메모리 내 임시 테이블의 최대 크기는 ‘tmp_table_size’ 또는 ‘max_heap_table_size’ 값으로 정의됩니다. 그러나 메모리 내 임시 테이블이 제한을 초과하면 MySQL은 자동으로 디스크 내 임시 테이블로 변환합니다. 각 변수 internal_tmp_mem_storage_engine[1], tmp_table_size[2] 및 max_heap_table_size[3]에 대한 자세한 설명은 References 참조 부탁드립니다. MySQL 임시 테이블[4]에 대한 추가 설명도 확인 가능하십니다.
위에서 MEMORY 설정으로 변수를 변경할 때 사용하고 계신 인스턴스에 사용 가능한 메모리가 충분한지 확인 후 진행 부탁드립니다. 그래야 메모리 부족으로 인한 다른 문제가 발생하지 않습니다.
세션이 닫히면 임시 테이블이 삭제되므로 이전 임시 테이블을 볼 수 없지만, 해당 오류가 다시 발생할 경우 아래 쿼리를 실행하여 임시 테이블에 사용된 공간을 확인하실 수 있습니다.
Run the following query to find internal temporary tables:
mysql> SELECT * FROM information_schema.innodb_tables WHERE name LIKE '%#%';
Run the following query to find the InnoDB system tablespace:
mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';
Run the following query to find the InnoDB temporary tablespace:
mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';
References
[1] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_internal_tmp_mem_storage_engine
[2] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size
[3] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_heap_table_size
[4] https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html