[MySQL] pidstat를 이용한 쿼리 CPU 점유율 모니터링

pidstat를 이용한 쿼리 CPU 점유율 모니터링

OS : CentOS 7.9 64bit

MySQL : 8.0.25 - commercial

Server : Localhost

---

소개

MySQL은 PotgreSQL, Orcale 과 같은 프로세스 기반 DB가 아니라, 쓰레드 기반 DBMS이기에, DB의 어떤 세션(쓰레드)이 리소스를 점유하고 있는지 OS의 툴에서 확인하기 힘들었습니다.

하지만, MySQL 5.7 부터 PERFORMANCE_SCHEMA.THREADS 테이블에 thread_os_id 컬럼이 추가되면서, 이를 확인할 수 있습니다.

pidstat 프로그램은, 특정 pid에 대한 CPU, 메모리 및 디스크 사용량을 실시간으로 출력할 수 있습니다.

~]$ pidstat -t -p `/sbin/pidof mysqld` 5

→ mysqld 프로세스의 thread만 5초 간격으로 확인합니다.

수행시간이 긴 쿼리를 수행하기 위해, 특정 테이블에 대량의 데이터를 넣어보겠습니다.

~]$ pidstat -t -p `/sbin/pidof mysqld` 5

Thread ID 14488번이 CPU 자원을 점유하고 있는 것을 확인할 수 있습니다.

위와 같은 쿼리문에 TID 조건을 걸어 해당 쿼리가 무엇인지 확인할 수 있습니다.

---

쉘 스크립트 적용

이와 같은 TID 확인은 실시간으로 확인해야 하기에 정확한 감지가 힘들 수 있습니다.

그리하여, 해당 과정을 쉘 스크립트로 개발하여 로깅파일을 만들어낼 수 있습니다.

2 : CPU 점유율을 지정할 수 있습니다.

3 : 파일명으로 사용되며, 위 예시는 YYYYmmddHH 형식입니다.

4 : logging을 위한 디렉토리 입니다. 위 예시는 스크립트와 동일한 경로이므로 사용되지 않습니다.

6 : 쿼리문을 수행하기 위한 mysql 콘솔 경로입니다.

7, 8 : MySQL 접속에 필요한 계정정보 입니다.

10 : logging file 이름입니다.

12 : 실행 시 ${logFile} 이름으로 빈 파일이 생성됩니다.

15 : 정규표현식(regex)에 매칭 될 패턴입니다. 숫자로만 이루어진, 즉 TID만 매칭이 되어야 한다는 뜻입니다.

17 : ${cpuUsage} 이상의 CPU를 점유중인 TID를 찾아내는 커맨드입니다.

18, 19 : target 배열에 담겨진 정보들을 출력합니다.

20 ~ 26 : ${target[i]}가 숫자(TID)일 경우, tidList에 저장합니다.

28, 29 : tidList 배열에 담겨진 정보들을 출력합니다.

30 : tidList의 중복된 TID를 제거합니다. (17번째 행에서 같은 TID가 등록될 수 있습니다.)

31 ~ 34 : map에 등록된 TID를 기준으로, 쿼리문을 수행하여 ${logFile}에 저장합니다.

---

실습

다른 세션 2개에서 수행시간이 오래 걸리는 쿼리문을 수행합니다.

mysql> insert into test.a (select * from test.a);

mysql> insert into test.b (select * from test.a);

수행 된 스크립트에서 지정한 cpuUsage (현재 15) 이상의 TID를 감지합니다.

로깅된 파일의 내용을 확인합니다.

---

회고

쿼리 CPU 모니터링 점유율을 확인할 수 있는 방법을 찾고싶었는데, 모니터링 툴 및 다른 솔루션에선 찾아볼 수 없었습니다. 구글링 도중... pidstat를 찾았고, 테스트를 했습니다.

하지만.. 그저 실시간으로 탐지되는 것이라 어느정도 로깅이 필요한 쉘 스크립트 작성이 필요했습니다.

쉘 스크립트 작성이 어색하지만, 기능을 만들어내니 좋은 경험이였던 것 같습니다.

기능 추가예정 내용은 아래와 같습니다.

• Logging 파일 단순화 ( 불필요한 정보가 너무 많음 )
• CPU 점유율 저장
• 쿼리문 결과의 PROCESSLIST_TIME 순 정렬
• pidstat 결과의 CPU 점유율 순 정렬
• pidstat 수행 커맨드 최적화
• 메모리 점유율 모니터링 개발