Last Updated on 1월 27, 2024 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 8.0.27 버전에서 새로 기능이 추가된 innodb parallel threads dll 기능에 대해서 확인해보도록 하겠습니다.
MySQL 8.0.27 새로운 기능 추가
MySQL 8.0.27 에서 InnoDB가 보조 인덱스(Secondary Index) 를 생성(정렬 및 구축)하는 데 사용할 수 있는 최대 병렬 스레드를 제어하는 새로운 시스템 변수인 innodb_ddl_threads 를 도입하였습니다.
이 새로운 시스템 변수(innodb_ddl_threads) 는 다른 새로운 시스템 변수인 innodb_ddl_buffer_size 와 같이 사용됩니다.
기능의 이름은 InnoDB Parallel Threads Online DDL Opertaions 이지만 현재 확인되는 버전(8.0.27~28) 에서는 보조 인덱스(Secondary Index) 에서만 해당 기능을 사용할 수 있으며, 빠른 스토리지와 여러 CPU 코어가 있는 경우 이러한 시스템 변수를 조정하면 보조 인덱스(Secondary Index) 생성 속도를 높일 수 있습니다.
포스팅 테스트 환경
• OS : CentOS 7.9
• CPU : Vcpu 4
• MySQL : 8.0.13 , 8.0.27
기능 테스트
테스트 테이블은 sysbench 를 통해 생성하였습니다.
$ sysbench --mysql-host=localhost --mysql-port=3306 ... ... --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench \ --table-size=7000000 --tables=1 \ /usr/local/sysbench/share/sysbench/oltp_read_only.lua prepare
테스트 테이블 건수는 7,000,000건 으로 1개 테이블을 생성하였습니다.
sysbench 에 대한 자세한 내용은 아래 포스팅을 참조하시면 됩니다.
생성된 테스트 테이블에 먼저 이전 버전은 8.0.13 버전에서 보조 인덱스 생성 테스트를 진행하도록 하겠습니다.
mysql> alter table sbtest1 add index idx_01_c(c); Query OK, 0 rows affected (1 min 37.48 sec) Records: 0 Duplicates: 0 Warnings: 0
포스팅 시스템에서는 1분 37초 가량 으로 확인됩니다.
이제 MySQL 8.0.27 버전에서 테스트해보도록 하겠습니다. 인덱스 생성 전 시스템 변수를 변경 후 진행하였습니다.
-- 시스템 변수 변경 mysql> set local innodb_ddl_threads = 8; mysql> set local innodb_parallel_read_threads = 8; mysql> set local innodb_ddl_buffer_size = 1048576000; -- 인덱스 생성 mysql> alter table sbtest1 add index idx_01_c(c), ALGORITHM=INPLACE; Query OK, 0 rows affected (42.87 sec) Records: 0 Duplicates: 0 Warnings: 0
포스팅 환경의 8.0.27 버전에서는 약 42.87 정도 소요되었으며, 차이로는 약 55초 정도 차이가 발생되었습니다.
위의 테스트 결과와 같이 이제 보조 인덱스 생성시 병렬 스레드 처리로 예전 보다 더 빠르게 수행이 가능해진 것을 확인할 수 있습니다.
Note
1) 위와 같은 테스트 결과는 시스템의 스펙(CPU or 스토리지 등등) 과 시스템 변수 설정 값에 따라서 달라 지게 됩니다.
2) Creating or adding a secondary index 는 ALGORITHM=INSTANT 를 지원하지 않습니다.
해당 기능과 관계 있는 시스템 변수는 아래 3개입니다.
- innodb_ddl_threads(기본값 4)
- innodb_parallel_read_threads(기본값 4)
- innodb_ddl_buffer_size(기본값 1048576 bytes (approximately 1 MB))
innodb_parallel_read_threads 파라미터는 8.0.14 버전에서 추가된 "InnoDB now supports parallel clustered index reads" 기능에 해당하는 시스템 변수이며, 해당 기능은 인덱스 생성에서 데이터를 조회하는 역할을 하게 되며, "InnoDB now supports parallel clustered index reads" 기능은 PK인 Clustered Index 에서만 사용할 수 있는 기능입니다
관련 내용은 기존 포스팅을 참조하시면 됩니다.
innodb_ddl_buffer_size 는 정의된 모든 innodb_ddl_threads 간에 공유됩니다. 쓰레드의 양을 늘린다면 버퍼 사이즈도 함께 늘리는 것이 권장됩니다.
시스템 변수 설정 값 별 개선 내용
아래 표는 InnoDB Team 에서 테스트한 결과입니다.
다음은 이러한 시스템변수의 영향을 보기 위한 표 요약입니다.
| ddl buffer size | ddl threads | parallel read theads | execution time |
| 1048576 | 4 | 4 | 9 min 0.6838 sec |
| 104857600 | 8 | 8 | 4 min 8.3601 sec |
| 1048576000 | 8 | 8 | 3 min 9.1862 sec |
| 1048576000 | 16 | 16 | 3 min 7.4079 sec |
| 1048576000 | 16 | 8 | 3 min 4.1161 sec |
| 1048576000 | 12 | 4 | 3 min 8.7854 sec |
| 1048576000 | 4 | 12 | 3 min 5.9497 sec |
| 1048576000 | 4 | 4 | 3 min 12.2435 sec |
| 2097152000 | 4 | 4 | 2 min 43.6280 sec |
| 2097152000 | 8 | 8 | 2 min 44.6516 sec |
InnoDB Team은 내부 시스템의 데이터에서 InnoDB DDL 스레드에 사용되는 버퍼 크기를 늘리면 최상의 성능을 얻을 수 있음을 알 수 있습니다.
그렇다면 오직 버퍼 크기를 늘리기만 하면 될까요? 물론 데이터의 특성에 따라 다르겠지만, 테스트시 오직 1개의 스레드 와 버퍼 2GB를 설정한 경우 실행 결과는 약 3분 22초 가 소용되었습니다.
이와 같이 버퍼 사이즈를 늘린다고 무조건 성능이 향상되는 것은 아닙니다.
새롭게 추가된 이 기능은 alter(index 추가) 명령어에서 동일 테이블에 대해서 여러 인덱스를 병렬로 빌드 할 수 있는 기능이 추가가 되었습니다.
-- 실행 예시 myql> alter table sbtest1 add index idx_02_k(k), add index idx_03_pad(k);
InnoDB에서 온라인 DDL을 처리하면서 이 새로운 방법 병렬 스레드 실행은 매우 좋은 개선 사항이라고 생각됩니다.
MySQL 8.0 버전 에서는 버전 업데이트 되면서 병렬 처리 기능이 조금 씩 추가되고 있는 것 같습니다.
갈수록 더 많은 병렬 처리 기능이 추가될 것으로 예측됩니다.
추가로 8.0.27 버전의 변경 내용은 Release Notes 를 참조해보시면 좋습니다.
Reference
Reference Link
• blogs.oracle.com/parallel-threads
• dev.mysql.com/news-8-0-27
연관된 다른 글
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
