[MySQL] 삭제 마킹된 데이터를 물리적으로 제거하는 purge thread 와 설정을 위한 innodb_purge_threads 변수 알아보기

[MySQL] 삭제 마킹된 데이터를 물리적으로 제거하는 purge thread 와 설정을 위한 innodb_purge_threads 변수 알아보기

 

l  Version : MySQL 5.5 later

 

MySQL purge thread는 삭제 표시된 레코드를 물리적으로 제거하는 작업을 하는 스레드이다. 쉽게 설명하면 가비지 컬렉션과 같은 역할이다. DELETE 쿼리 등에 의해 삭제되었다고 마킹된 데이터에 대해서 물리적으로 삭제 작업을 한다.

 

 

MySQL의 innodb_purge_threads 변수는 InnoDB 스토리지 엔진에서 제거 작업 전용 스레드 수를 제어하는 ​​데 사용된다. Purge thread는 더 이상 필요하지 않은 행을 삭제하는 프로세스이다. 일반적으로 이러한 상황은 해당 행이 삭제 또는 업데이트되었으며 최신 버전의 행이 존재하기 때문이다.

 

데이터베이스, 특히 데이터 수정(업데이트 및 삭제) 수준이 높은 데이터베이스에서는 이전 버전의 행이 누적될 수 있다. 이로 인해 스토리지 사용량이 증가하고 성능이 저하되며 데이터베이스 복구와 같은 특정 작업에 소요되는 시간이 길어질 수 있다. Purge thread를 2이상 값으로 설정하여 스레드를 사용하면 잠재적으로 이러한 오래된 행을 정리하는 프로세스 속도를 높일 수 있다. 이는 많은 데이터가 정기적으로 수정되는 처리량이 많은 데이터베이스에 특히 유용할 수 있다.

 

MySQL, Percona, MariaDB의 purge 스레드에 대한 설정 정의는 아래와 같다.

Name	MySQL	Percona	MariaDB
Variable	Innodb_purge_threads	Innodb_purge_threads	Innodb_purge_threads
Configuration	Supported	Supported	Supported
Scope	Global, Session	Global, Session	Global, Session
Dynamic	No	No	No
Data Type	Integer	Integer	INT UNSIGNED
Default Value	4	4	4
Minium Value	1	1	1
Maximum Value	32	32	32

 

현재 설정된 innodb_purge_threads 값을 확인하는 방법은 아래 스크립트를 사용하여 확인할 수 있다.

show variables like '%innodb_purge_threads%';

 

 

innodb_purge_threads 변수 설정은 시작 시 사용되는 구성 파일을 사용하여 구성할 수 있다. value를 데이터베이스 요구 사항에 맞는 값으로 수정하여 사용한다. 적용된 변경 사항을 보려면 MySQL 서버를 다시 시작해야 한다.

[mysqld] innodb_purge_threads variable = value

 

 

많은 데이터베이스 구성 설정과 마찬가지로 innodb_purge_threads의 최적 값은 워크로드 및 하드웨어의 세부 사항에 따라 크게 달라질 수 있다. 따라서 일률적인 권장사항을 제공하기는 어렵다. MySQL에서 innodb_purge_threads 변수를 설정하거나 조정할 때 다음 사항을 고려할 수 있도록 한다.

l  작업 부하 특성 – 제거 스레드 수 증가의 영향은 작업 부하에 따라 크게 달라질 수 있다. 데이터베이스의 데이터 수정(삭제 및 업데이트) 비율이 높은 경우 이 값을 늘리면 불필요한 오래된 행 버전을 더 빠르게 제거하여 성능을 향상시킬 수 있다. 그러나 작업 부하가 수정 횟수가 적고 읽기 작업이 많은 경우 이 변수를 늘리면 이점이 줄어들 수 있다.

l  시스템 리소스 – 각 추가 제거 스레드는 더 많은 CPU 및 기타 시스템 리소스를 소비한다. 전체 시스템 성능에 부정적인 영향을 주지 않으면서 서버에 추가 스레드를 처리할 수 있는 충분한 용량이 있는지 확인해야 한다.

l  복구 시간에 대한 영향 – 제거 작업은 실행 취소 로그가 너무 커지는 것을 방지하여 충돌 발생 시 데이터베이스 복구 시간을 단축할 수 있다. 시스템이 행 수정 속도를 따라잡을 수 없는 경우 제거 스레드 수를 늘리면 잠재적으로 복구 시간이 줄어들 수 있다.

 

 

[참고자료]

l   https://releem.com/docs/mysql-performance-tuning/innodb_purge_threads

l   https://www.alibabacloud.com/blog/mysql-engine-features-innodb-based-physical-replication_65220

 

 

 

2023-11-24 / Sungwook Kang / http://sungwookkang.com

 

MySQL, Percona, purge thread, innodb_purge_threads, 퍼지스레드