MySQL Ver. 5.7에서 임시테이블에 대한 성능 개선

1. InnoDB Temporary Table Undo Logs 

MySQL Ver. 5.7.2에서 일반 임시 테이블과 암축 임시테이블 그리고 거기에 연관된 오브젝트들을 위한 새로운 타입의 Undo Log 가 소개되었다.  임시 테이블의 내용은 Crash Recovery에서 사용되지 않기 때문에 redo log가 필요하지 않다. 즉, 임시 테이블의 정보는 서버가 운영 중일때, 롤백해야 하는 상황에서만 필요하다. 리두로그를 만들지 않는 Undo Log는 해당 임시 테이블과 거기에 관련된 오브젝트를 위한 redo logging으로 인해 발생하는 Disk I/O를 피할 수 있기 때문에 성능에 도움을 준다. 임시 테이블에 대한 Undo Log는 임시 테이블 스페이스에 위치한다. 기본으로 생성되는 임시 테이블 스페이스 파일은 ibtmp1 이라는 이름을 가진다. 이것은 따로 지정하지 않으면 Data Directory에 위치하게 되고, 이것은 MySQL이 Startup될때 자동으로 재생성된다. 사용자의 요구에 따라 위치를 변경할 수 있는데 이때 사용하는 시스템 변수는 innodb_temp_data_file_path이다. 

또한, 임시테이블과 그것과 연관된 오브젝트를 위한 변경하는 트랜잭션에서 사용할 Undo log 영역으로 32 rollback segment가 할당되어 있다. 그로 인해 하나의 트랜잭션에서 사용할 수 있는 롤백세그먼트의 갯수가 128에서 96으로 줄었다. 이것은 하나의 트랜잭션에서 동시에 데이터를 수정하는 양이 줄었다는 것을 의미한다. 

2. Internal Temporary Table 설정기능 추가

MySQL 내부에서 쿼리를 실행할때 내부 데이터를 임시 테이블에 넣어서 처리해야 하는 일이 발생할 때, 어떤 스토리지 엔진으로 만들게 할 것인지 선택할 수 있는 기능이 MySQL Ver. 5.7.5에서 추가되었다. 바로, internal_tmp_disk_storage_engine이라는 시스템 변수이다. 

 

internal_tmp_disk_storage_engine 시스템 변수는 MyISAM이나 InnoDB를 선택할 수 있다. 만약, InnoDB를 선택하게 되면, 옵티마이져가 작업 시 필요에 따라 InnoDB 스토리지 엔진으로 Internal 임시 테이블을 생성하는데, 이때, 생성되는 임시 테이블을 intrinsic 테이블이라고 부른다. 이 테이블은 다른 InnoDB테이블과 같이 MVCC나 ACID 는 제공되지만, 다른 테이블과 다르게 UNDO나 REDO 정보는 생성되지 않는다. (참고로, InnoDB 스토리지 엔진으로 만들어지는 모든 임시 테이블은 Redo Log를 생성하지 않는다. ) 이름에서도 알 수 있듯이 intrinsic 테이블은 옵티마이져가 필요하다고 생각하는 경우에 만들어지는 MySQL Internal에서만 생성되어 사용되는 테이블이다. 즉, 일반 사용자는 해당 테이블을 생성하여 사용할 수 없다는 뜻이다. 또한, innodb_temp_table_info 테이블에서도 해당 테이블에 대한 정보는 확인할 수 없다. 

InnoDB Intrinsic 테이블은 다음과 같은 특징을 가지고 있다. 

1. SYS_TABLES에 해당 정보를 저장하거나, InnoDB Dictionary Cache에 저장하는 대신에 모든 정보는 thread의 로컬 캐쉬에 해당 테이블에 대한 정보를 저장한다. 그래서, 세션 레벨에서만 확인이 가능하다. 

2. 테이블은 shared temporary tablespace에서만 생성된다. 이 테이블스페이스는 MySQL이 Startup 될때마다 자동으로 재생성된다. 즉, shutdown되면 모든 테이블은 다 사라진다. 

3. 이 테이블은 롤백을 지원하지 않는다. 그래서, 롤백을 목적으로 사용하는 DB_ROLL_PTR 컬럼은 여기서는 사용되지 않는다. 또한, DB_ROW_ID와 DB_TRX_ID도  이 테이블에서는 사용되지 않는다. 

4. 롤백이 지원되지 않기 때문에 Atomicity는 Row 단위로 제약되지만, 사실 이것은 이 테이블에서 크게 문제되지는 않는다. 내부에서 사용되는 임시 테이블이기 때문이다. 또한 MyISAM과의 Atomicity가 다르지 않아서 옵티마이져 입장에서 좀 더 수월하게 두가지 임시 테이블에 대한 작동을 구현할 수 있게 되었다. 

5. 해당 테이블은 절대 공유되지 않도록 구현되어있기 때문에 좀 더 빠르고 가볍게 구현되었다. 

6. 또한 Intrinsic 테이블에만 추가된 기능들이 있는데 바로 delete_all_rows index와 enable/disable index이다. (?)

7. DB_ROW_ID와 DB_TRX_ID에 대한 관리를 테이블 레벨로 변경하여 글로벌 InnoDB Generator를 사용함으로 인해 발생하는 비용을 줄였다. 즉, 이 테이블의 특성상 불필요한 글로벌 관리 부분을 피하도록 한 것이다. 

8. UPDATE 작업은 내부적으로 DELETE+INSERT로 동작하게 구성되었다. 이것은 불필요한 내부 공간 관리를 줄이기 위함이다. 

9. 내부에서 발생하는 검색은 가장 최근에 진행한 검색 위치를 저장하고, 이전에 접근했던 블럭과 레코드에 커서를 복구함으로서  좀 더 효과적으로 진행될 수 있게 하였다. 

10. 이 테이블은 double write buffer 와 adaptive hash index를 사용하지 않는다. 해당 기능은 이 테이블에서는 불필요한 기능이다. 

3. innodb_temp_data_file_path 시스템 변수의 추가 

MySQL Ver. 5.7.1 부터 innodb_temp_data_file_path 시스템 변수를 추가하여 공통으로 사용할 수 있는 InnoDB 임시 테이블스페이스의 데이터 파일 위치를 지정할 수 있게 하였다. 이 시스템 변수를 사용하여 위치 뿐 아니라 데이터 파일 이름과 사이즈도 지정하여 생성되게 할 수 있다. 이 임시 테이블 스페이스는 압축을 사용하지 않는 InnoDB 테이블에 대한 정보를 담을 때에만 사용할 수 있다. 만약, 압축된 데이터를 가진 임시 테이블을 생성하려고 하면, tmpdir 위치에 file-per-table 형태의 테이블 스페이스 파일이 생성되어 사용된다. 

다음의 글에서 좀 더 정확히 파악할 수 있습니다. 

https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-table-undo-logs.html

http://mysqlserverteam.com/mysql-5-7-innodb-intrinsic-tables/

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_internal_tmp_disk_storage_engine

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path

https://blogs.oracle.com/mysqlinnodb/entry/https_blogs_oracle_com_mysqlinnodb