Last Updated on 12월 14, 2021 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL Blog에 개재된 "A 250x improvement to tablespace truncation in MySQL Server 8.0.23" 을 번역한 내용을 바탕으로 개선된 truncate 기능에 대해서 살펴보도록 하겠습니다.
Contents
MySQL 8.0.23 an improvement to truncate
MySQL Server 8.0.23 버전에서는 모든 테이블 스페이스에 대해서 더 빠르게 truncate 작업이 수행될 수 있도록 InnoDB가 개선되었습니다.
사실, AHI(Adaptive Hash Indexes)가 비활성화 되어있다면 거의 즉시 완료 될 수도 있습니다.
(Adaptive Hash Indexes 대한 내용은 아래 링크를 참조)
MySQL 8.0.23 에서는 truncate 수행시 버퍼풀의 페이지가 즉시 해제 되는 대신에 지연 해제 되도록 변경/개선 되었습니다.
이러한 내용은 8.0.21에서 소개된 UNDO Tablespace truncate 개선사항과 유사한 변화 입니다
8.0.21 에서의 개선된 UNDO Tablespace truncate 내용은 이전 포스팅을 참조하시면 됩니다.
UNDO Tablespace 의 수정 사항과 유사하게 이 작업은 모든 테이블 스페이스의 삭제 또는 truncate 의 작업 프로세스를 훨씬 빠르게 만들며, 더 중요한 점은 DROP/TRUNCATE 작업을 진행하는 동안에 MySQL 이 멈추거나 정지(성능적인 저하 현상) 되는 원인 을 제거 합니다.
프로세스 3 단계
DROP TABLE/TABLESPACE 의 프로세스에는 세 부분이 있습니다.
• 첫 번째는 InnoDB 버퍼풀 조정
• 두 번째는 AHI(Adaptive Hash Indexes) 인덱스 정리
• 세 번째는 파일시스템에서 파일 삭제와 관련
앞의 두가지가 지연의 원인이며 일반적으로 특히 매우 큰 버퍼 풀을 사용할 경우 또는 Temp Tablespace가 쿼리 수행에서 많이 사용되는 경우 더 큰 오버헤드가 발생되게 됩니다.
변경 사항에서는 이렇게 중요한 첫 번째 부분을 수정하게 되었습니다.
MySQL 사용자는 MySQL Team에게 계속적은 조금 더 빠른 table/tablespace 의 삭제처리를 요구해왔습니다.
이 변경사항은 이러한 작업의 성능을 제공하기 위한 첫 번째이자 가장 큰 단계입니다.
MySQL 8.0.23 버전에서는 아직 AHI 처리에 대한 내용이 포함되어 있지 않습니다. 그러나 AHI 처리 개선은 현재 작업을 진행 중이며 곧 개선/수정 될 예정 입니다.
영향을 받는 작업
이전의 변경사항(포스팅) 에서 언급된 UNDO TABLESPACE 이외 테이블 스페이스에서의 truncate 및 drop 작업은 다음과 같은 명시적 작업에서 실행 됩니다.
• DROP TABLESPACE 로 테이블스페이스 삭제
• file-per-table tablespace 를 사용한 테이블에 대해서 DROP TABLE 및 TRUNCATE TABLE 작업 수행시
(Parameter : innodb_file_per_table)
• 암시적 임시 테이블스페이스의 truncate
이 경우 DROP TEMPORARY TABLE이 아니라 세션 연결 끊긴 다는 사실이 사용자를 놀라게 할 수 있습니다.
예를 들어 내부 임시 테이블스페이스는 좀 더 복잡한 쿼리를 실행하는 동안 옵티마이저가 사용했을 수 있습니다.
이전 MySQL Server 버전에서 볼 수 있는 문제의 종류
테이블스페이스의 삭제 및 truncate 가 진행되는 동안 해당 테이블스페이스에 해당하는 버퍼풀의 페이지를 제거(purge) 하는 것이었습니다.
버퍼 풀이 클수록 더 많은 페이지를 traverse 하고 내부적인 래치(latch)를 더 오래 유지 해야 하며 이 부분은 포그라운드 및 쿼리 스레드에 영향을 미치게 되며, 이것이 지연을 발생시키는 원인이 되게 됩니다.
작업을 계속하기전에 내부 구조 업데이트가 완료 될때 까지 기다려야 하는 것(Any parallel DML transaction)으로 인하여 서버가 몇 초 이상 멈추게 되게 되고, 수백 GB의 메모리를 할당한 큰 버퍼 풀의 경우 이러한 작업이 매우 어려운 작업이었습니다.
중요한 것은 이러한 멈춤 또는 성능저하의 시간 등이 삭제된 테이블스페이스의 크기와 관련이 없다는 것입니다.
크거나 작거나 비어 있는 테이블스페이스를 삭제해도 오래된 페이지(stale pages)를 확인하기 위해 전체 버퍼 풀을 통과해야 하기 때문에 서버의 멈춤이나 성능 저하가 발생하게 됩니다.
버전 8.0.23 에서의 개선 사항
이번 변경으로 인하여 삭제된 페이지를 축출 하기 위해서 버퍼 풀 데이터 구조에 대해서 매우 비싼(Cost) 조회(원문:traversal) 를 수행할 필요가 없어졌습니다
삭제된 테이블스페이스의 페이지는 느리게 처리되며 추가로 다른 쿼리 스레드에서 제거하고 재사용할 페이지를 찾는 LRU 목록을 스캔하게 되면 free page로 하여 재사용 될 수 있습니다.
이 변경으로 인해 DROP/TRUNCATE 작업이 거의 즉각적으로 수행이 되게 됩니다.
InnoDB에서 수행되는 작업은 매우 짧고 작업 속도는 OS에서 실제 데이터 파일을 얼마나 빨리 삭제 할 수 있는지에 달렸습니다.
이 때문에 OS 가 데이터 파일을 삭제 하는 데 약간의 시간이 걸리더라도 AHI 가 관련이 되어 있지 않는 한 지연이 발생하지 않습니다.
성능의 예
이전 버전에서 업데이트가 필요한 LRU 목록 및 Flush 목록의 크기는 다음 쿼리를 통해서 확인 할 수 있습니다.
SELECT sum(pool_size)*16/1024/1024 as "BP size in GB", sum(modified_database_pages) as "flush list size", sum(database_pages) as "LRU list size" FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
MySQL Team 에서 테스트를 진행한 환경과 계획은 아래와 같습니다.
• 환경
버퍼 풀 크기가 32, 64, 128, 256, 450GB인 MySQL Server 인스턴스를 사용
• 테스트 계획
큰 테이블을 만들고 버퍼 풀 크기에 대한 데이터로 채웁니다.
100개의 빈 테이블을 동시에 생성하고 삭제합니다.
테스트를 이전 버전의 MySQL Server에서 수행했을 때 동시성을 의미하는 DROP TABLE 문이 순차적으로 실행되었음을 분명히 알 수 있었습니다. 이 시간 동안 서버가 중단(stalled)되어 DML 쿼리를 수행할 수 없습니다.
8.0.23 버전에서 실행한 동일한 계획은 Buffer Pool의 크기와 상관없이 훨씬 빠르게 완료 되었으며 쿼리가 동시에 실행되는 것처럼 보였습니다.
수집된 데이터가 포함된 차트는 다음과 같습니다.
테스트 결과, 매우 큰 개선 결과를 확인 할 수 있습니다.
아래 그래프는 Y 축 그래프 챠트를 보다 읽기 쉬운 방식으로 표시한 것 입니다.
정리하면, 실제 운영 환경에서 사용되는 MySQL 서버는 위의 테스트보다 훨씬 더 많은 변경된 페이지를 갖고 있을 것이며 그에 따라서 이전 버전에서(8.0.23 이전 버전) 삭제를 하면 훨씬 더 길고 심각한 지연이 발생할 수 있을 수 있습니다.
위의 MySQL Team 에서 진행한 테스트 결과 에서 볼수 있는 개선 사항은 최대 251배 입니다.
Reference
Reference Link
• 250x-improvement-truncate
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
