MySQL 8.0 부터 Resource Group 기능이 추가되었다.
Resource Group 의 생성과 관리 기능을 지원하고, 서버에서 실행되는 스레드를 특정 그룹에 할당 함으로써
그룹이 사용할 수 있는 자원에 따라 스레드가 작업을 실행한다.
그룹 속성으로 자원을 제어 하고, 그룹 내 스레드의 자원 소비를 활성화 하거나 제한 한다.
DBA 는 여러 작업의 부하에 맞게 이러한 특성을 수정할 수 있다.
사용 가능한 가상 CPU 수를 결정하고, 적절한 권한을 가진 데이터베이스 관리자는 해당 CPU 를 RESOURCE GROUP 과 연결하고 스레드를 그룹에 할당할 수 있다.
CPU 시간은 CPU 코어, 하이퍼스레드, 하드웨어 스레드 등을 포함하는 용어로 "가상 CPU" 라는 개념으로 관리 가능한 리소스이다.
vCPU 의 리소스를 서로 다른 그룹에서 각각의 우선순위로 할당할 수 있게 되었다.
RESOURCE GROUP 은 아래와 같은 제한 사항이 있다.
thread pool plugin 을 설치했을 경우 사용 불가
macOS 에서 사용 불가 (스레드에 CPU binding API 제공하지 않기 때문)
FreeBSD, Solaris 서버에서 resource group thread 우선순위 설정은 무시된다
Linux 에서 CAP_SYS_NICE 가용이 설정되어 있지 않는 한 resource group thread 가 무시된다
Linux 에서 MySQL 패키지 설치 시 반드시 기능을 설정해야 한다
압축된 tar 파일 바이너리 배포판이나 소스를 사용하여 설치하는 경우 setcap 명령을 사용해서 mysqld 를 pathname 을 명시하여 수동으로 CAP_SYS_NICE 가용성을 설정할 수 있다.
- Windows 에서 5개의 우선 순위 레벨 중 하나로 thread 가 수행된다.
Priority Range | Windows Priority Level |
-20 to -10 | THREAD_PRIORITY_HIGHEST |
-9 to -1 | THREAD_PRIORITY_ABOVE_NORMAL |
0 | THREAD_PRIORITY_NORMAL |
1 to 10 | THREAD_PRIORITY_BELOW_NORMAL |
11 to 19 | THREAD_PRIORITY_LOWEST |
- resource group management 는 로컬 서버에서 수행된다.
Resource Group SQL 구문과 resource_group data dictionary 테이블에 변경 사항은 binary log 에 작성되지 않고, 복제 되지 않는다.
OLTP workload 가 높을 때, batch job 들을 수행할 때
동일한 인스턴스에 대해 느린 분석 쿼리나, 집계 쿼리 들이 문제가 된다.
이 경우 보통 Slave 인스턴스로 작업을 옮기는데, 하드웨어 자원을 사용하고 slave DB 로 접속하도록 설정하는데 시간이 소요된다.
또한 일괄 작업을 일관성 있게 수행하거나 배치 작업이 write 를 수행해야 해야하는 경우 Slave 를 사용하지 못한다.
이 경우 Resource group 은 좋은 대안이 될 수 있다.
RESOURCE GROUP 관리는 CREATE, ALTER, DROP 명령을 수행할 수 있다.
명령을 수행하려면 RESOURCE_GROUP_ADMIN 권한이 필요하다.
현재 아래와 같은 두 기본 그룹이 존재한다. ( 이 그룹은 삭제/변경 불가 )
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS ;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default | USER | 1 | 0-31 | 0 |
| SYS_default | SYSTEM | 1 | 0-31 | 0 |
+---------------------+---------------------+------------------------+----------+-----------------+
-- 32 core 로 VCPU_IDS 값이 0-31 이다.
$ cat /proc/cpuinfo | grep 'core id' | wc -l
32
RESOURCE GROUP TYPE 중 SYSTEM , USER 가 존재한다.
RESOURCE_GROUP_TYPE : 그룹에 할당할 수 있는 우선순위 값의 범위에 영향을 준다.
허용된 우선 순위의 차이와 함께 시스템 스레드를 식별하여 사용자 스레드에 대한 CPU 자원의 경합으로 부터 시스템 스레드를 보호한다.
아래는 사용 가능한 우선순위 범위와 각 그룹 유형 설명이다.
SYSTEM resource group 이면 허용되는 우선 범위는 -20 ~ 0 이다.
사용자 group 의 경우 허용되는 우선 순위 범위는 0 - 19 이다.
스레드 우선 순위는 RESOURCE GROUP 에 할당 된 스레드의 실행 우선 순위이다.
SYSTEM, USER 그룹의 기본 우선순위는 0이다.
SYSTEM 그룹은 USER 그룹보다 우선순위가 높아서, USER 스레드가 SYSTEM 스레드보다 높은 우선순위를 갖지 않도록 한다.
8 개의 CPU 가 있을 경우를 가정하여 아래와 같이 테스트한다.
mysql> CREATE RESOURCE GROUP Reporting
-> TYPE = USER
-> VCPU = 5-6
-> THREAD_PRIORITY = 10 ;
Query OK, 0 rows affected (0.05 sec)
-- 만일 아래와 같은 warning 이 발생하면, CAP_SYS_NICE 이 설정 되지 않은 것이다.
( 또한 FreeBSD 나 Solaris 에서는 우선순위가 무시된다. )
mysql> show warnings ;
+---------+------+-------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------+
| Warning | 3659 | Attribute thread_priority is ignored (using default value). |
+---------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)
-- CAP_SYS_NICE 수동 설정하기 systemctl start mysqld -- os root user 로 실행 $ setcap cap_sys_nice+ep /db/mysql/bin/mysqld $ getcap /db/mysql/bin/mysqld /db/mysql/bin/mysqld = cap_sys_nice+ep 위 내용을 설정하면 mysqld 를 root 로 수행해야 library 를 참조한다. (에러구문) /db/mysql/bin/mysqld: error while loading shared libraries: libssl.so.1.0.0: cannot open shared object file: No such file or directory systemctl stop mysqld |
mysql> CREATE RESOURCE GROUP Batch_job
-> TYPE = USER
-> VCPU = 7-8
-> THREAD_PRIORITY = 8 ;
Query OK, 0 rows affected (0.05 sec)
Group 이 정상적으로 생성되었는지 확인한다.
mysql> SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS ;
+---------------------+---------------------+------------------------+----------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+----------+-----------------+
| USR_default | USER | 1 | 0-31 | 0 |
| SYS_default | SYSTEM | 1 | 0-31 | 0 |
| Reporting | USER | 1 | 5-6 | 10 |
| Batch_job | USER | 1 | 7-8 | 8 |
+---------------------+---------------------+------------------------+----------+-----------------+
아래와 같은 상황을 가정한다.
- 현재 서버에서 실행되는 heavy 쿼리로 인해 많은 리소스를 소비하고 모든 것이 느려짐. 이 경우 다음과 같이 수행할 수 있다.
- Performance schema 의 threads 테이블에서 쿼리의 thread_id 를 찾아 특정 resource group 에 할당한다.
thread-1
mysql> select count(*) from test_tbl1 t1 , test_tbl2 t2 where t1.table_name = t2.table_name ;
thread-2
mysql> select * from performance_schema.threads \G
THREAD_ID: 45
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 12
PROCESSLIST_USER: jmhong
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: hong
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 40
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: select count(*) from test_tbl1 t1 , test_tbl2 t2 where t1.table_name = t2.table_name
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 84932
RESOURCE_GROUP: USR_default
// USR_default group 으로 실행했을 때 elapsed time
6373484 rows in set (2 min 46.06 sec)
// Reporting group 으로 실행했을 때 elapsed time
mysql> SET RESOURCE GROUP Reporting FOR 45 ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.threads \G
THREAD_ID: 45
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 12
PROCESSLIST_USER: jmhong
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: hong
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 77
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: select count(*) from test_tbl1 t1 , test_tbl2 t2 where t1.table_name = t2.table_name
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 84932
RESOURCE_GROUP: Reporting
힌트를 사용하여 특정 RESOURCE GROUP 을 지정하여 단일 쿼리를 수행할 수도 있다.
SELECT /*+ RESOURCE_GROUP(Batch_job) */ count(*) from test_tbl1 t1 , test_tbl2 t2 where t1.table_name = t2.table_name ;
mysql> select * from performance_schema.threads \G
THREAD_ID: 45
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 12
PROCESSLIST_USER: jmhong
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: hong
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 2
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: SELECT /*+ RESOURCE_GROUP(Batch_job) */ count(*) from test_tbl1 t1 , test_tbl2 t2 where t1.table_name = t2.table_name
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 84932
RESOURCE_GROUP: Batch_job
group 에 할당된 스레드는 해당 자원과 함께 실행되며, 원하는 대로 수정이 가능하다.
결과적으로 우선순위를 낮춰보고 RESOURCE GROUP 에 더 적은 CPU 리소스를 할당해야 한다.
mysql> ALTER RESOURCE GROUP Reporting
VCPU = 1
THREAD_PRIORITY = 19 ;
현재 접속한 스레드에서 쿼리를 수행할 때 명시적으로 RESOURCE GROUP 을 지정할 수 있다.
mysql> SET RESOURCE GROUP Batch_job ;
Query OK, 0 rows affected (0.00 sec)
시스템 load 가 높을 경우 CPU 할당 개수를 줄이고 우선순위를 낮춘다.
ALTER RESOURCE GROUP Batch VCPU = 3 THREAD_PRIORITY = 19;
시스템 load 가 적을 경우 CPU 할당 개수를 늘리고 우선순위를 높인다.
ALTER RESOURCE GROUP Batch VCPU = 0-3 THREAD_PRIORITY = 0;
언제 사용할 수 있을까 ?
- heavy query 로 인해 장애가 발생중일 때 특정 쿼리를 kill 하지 않고 낮은 우선순위로 변경하여 리소스 사용을 줄일 수 있다.
- replication delay 를 줄이기 위해 replication thread 의 우선순위를 높인다.
- scheduler 의 우선순위를 높인다
- 분석 쿼리의 우선순위를 낮춘다
- 단점 : 특정 account 를 지정할 수는 없음 / thread 로 지정만 가능하다
참고 link
https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html
https://www.percona.com/blog/2018/01/25/mysql-8-0-resource_group-overview/
