MySQL Online DDL(2) [작업 정보 계측 and Memory]

Mysql의 innoDB는 Buffer 메모리를 사용하여 IO반영 전 작업을 처리하거나 Temporary 작업들을 저장하고 있다.

해당 Buffer를 사용하는 작업 중 사용하는 메모리를 조회하는 방법인 Mysql 5.7부터 추가된 Performance_schema 에서 해당 내용을 설정 확인할 수 있다.

정보를 수집하는 instruments와 수집한 정보를 저장하는 consumers로 설정하여 이용하는 구조이다.

 

정보 수집

Performance_schema.setup_instruments 을 조회해 보면 다음과 같은 컬럼으로 이루어 진 것을 확인할 수 있다.

1.      NAME : 수집 가능한 값으로 wait/synch/mutex/pfs/LOCK_pfs_share_list 와 같이 분류를 /를 구분하여 Group화 하고 있다.

2.      ENABLED : 해당 값을 수집을 하는지 여부

3.      TIMED

A.     YES, NO, NULL로 값을 설정할 수 있다.

B.      시스템이 기본적으로 제공하는 값을 수정할 수 없다.

C.      NULL은 기본적으로 TIMER를 지원하지 않는 수집값이고 해당 값을 변경하더라도 영향을 받지 않는다.

D.     해당 이벤트는 NULL, TIMER_START, TIMER_END, TIMER_WAIT 값으로 남는다.

4.      PROPERTIES : 상세 설정 값으로 5개의 정보를 가진다.

A.     global_statistics : 전역 정보만을 요약함

B.      mutable : 다른 값으로 치환되고 해당 값은 명목상 값임

C.      progress : 진행 상태 정보를 제공할 수 있다. 해당 정보는 Stage만 가능

D.     singleton : 단일 인스턴스의 정보임

E.      user : 시스템이 아닌 사용자가 제작한 워크로드의 값임

5.      VOLATILITY : LOW, HIGH로 설정 가능한 수집 우선 순위

6.      DOCUMENTATION : 해당 값에 대한 설명

위에서 설명했듯이 name에서 분류를 가지고 있기 때문에 /로 파싱하여 조회하면 대 분류로 어떠한 정보를 수집할 수 있는지 알 수 있다.

select substring_index(name, '/', 1) as cate, count(*) as cnt

from performance_schema.setup_instruments 

group by substring_index(name, '/', 1);

 

해당과 같이 조회하면 정보 조회가 가능한 것을 확인할 수 있다.

정보 수집 이벤트 설정

Performance_schema.setup_consumers 는 위에서 설정된 수집 정보는 어떤 상황이 발생되었을 시 수집할지에 대한 정보이다.

해당 테이블을 조회에 보면 다음과 같은 컬럼으로 이루어져 있다.

1.      NAME

A.     수집 상황에 대한 정보 이름이다.

B.      구조는 상위 발생 상황에서 다음 발생 상황이나 내용으로 이어지는 구조이다.

C.      예를 들면 events_stages_current è stage에 발생된 Event의 현재 상황 정보 이다.

2.      ENABLED : 해당 정보를 수집하는지에 대한 여부

 

수집 정보 조회

위의 테이블에서 수집 정보를 설정하고 쿼리를 통해 해당 수집 정보를 조회할 수 있다.

아래는 Alter table 예제를 통해 해당 수집 정보를 조회하는 것을 설명 하고자 한다.

 

우선 테스트를 위한 sample Data를 생성한다.

-- Sample Data 생성

create table alter_test_tbl 

(

    a int primary key auto_increment,

    b varchar(100),

    c varchar(100)

);

 

insert into alter_test_tbl 

select null, uuid(), uuid() 

from information_schema.columns as a, 

    information_schema.columns as b

limit 10000000;

 

해당 테이블에 Alter 사항을 모니터링 하기 위한 Performance_schema 셋팅 정보를 변경한다.

-- alter 작업을 위한 setup_instruments 를 Enable 시킨다.

UPDATE performance_schema.setup_instruments 

SET ENABLED = 'YES' 

WHERE NAME LIKE 'stage/innodb/alter%';

 

-- events_stages_%의 setup_consumers를 Enable시켜 각각의 alter stage를 모니터링 한다.

UPDATE performance_schema.setup_consumers 

SET ENABLED = 'YES' 

WHERE NAME LIKE 'events_stages%';

 

다음과 같이 새로운 컬럼을 추가하는 alter 작업을 시도한다.

-- Alter 작업을 진행한다.

ALTER TABLE alter_test_tbl ADD COLUMN x INT;

ALTER TABLE alter_test_tbl DROP COLUMN x;

 

alter 작업이 진행 중에는 events_stages_current 테이블에서 확인이 가능하다.

SELECT esc.THREAD_ID

 , esc.SQL_TEXT

 , estc.EVENT_NAME

 , estc.WORK_COMPLETED AS '완료 작업 수'

 , estc.WORK_ESTIMATED AS '전체 작업 수' 

 , CONCAT(

     ROUND(

         @PROGRESS := estc.WORK_COMPLETED/WORK_ESTIMATED * 100,

         2),

      "%") AS "진척률"

 , sys.format_time(

     @ELAPSED := (estc.TIMER_END - estc.TIMER_START)

     ) AS '실행 시간'

 , sys.format_time(

     @REMAIN := FLOOR(@ELAPSED * (100/@PROGRESS) - @ELAPSED)

     ) AS '남은 시간'

 , DATE_FORMAT(

     NOW() + INTERVAL @REMAIN/POWER(10,12)  SECOND, 

     '%Y년 %m 월 %d 일 %H:%i:%s'

     )  AS  '종료 예상 시간'

 , ROUND(estc.WORK_COMPLETED / (@ELAPSED/POWER(10,12)),0)  AS  '초 당 처리 작업 수' 

FROM performance_schema.events_statements_current AS esc

INNER JOIN performance_schema.events_stages_current AS estc

ON estc.THREAD_ID = esc.THREAD_ID

WHERE estc.NESTING_EVENT_ID = esc.EVENT_ID

\G;

 

모든 작업이 완료된 후에는 events_stages_history_long 에서 해당 작업의 순서대로 작업 및 시간을 확인할 수 있다.

SELECT sesh.EVENT_ID

 , sesh.NESTING_EVENT_ID

 , esh.SQL_TEXT

 , sesh.EVENT_NAME

 , sesh.WORK_ESTIMATED AS '작업 수' 

 , sys.format_time(sesh.TIMER_WAIT) AS '작업 시간'

FROM performance_schema.events_stages_history_long AS sesh

INNER JOIN performance_schema.events_statements_history AS esh

ON sesh.NESTING_EVENT_ID = esh.EVENT_ID

ORDER BY sesh.NESTING_EVENT_ID, sesh.TIMER_START;

 

메모리 정보 수집

memory 카테고리의 instruments는 계측하는 구조가 없기 때문에 위의 사용법으로는 계측하기가 어렵다.

하지만 sys.x$memory_global_by_current_bytes 를 제공해 주기 때문에 메모리의 사용양 등을 확인할 수 있다.

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area

 , FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUBSTRING_INDEX(event_name,'/',2) DESC;

 

위의 커리를 통해 현재 사용 중인 메모리 양을 확인 할 수 있다.

 

따라서 2가지 정보를 통해 online alter 작업의 예상 종료 시간 및 변경 중 DML을 저장하는 row buffer pool 사용양 정보를 대략적으로 예측해 볼 수 있을 것이다.

 

 

 

참조

https://engineering.linecorp.com/ko/blog/mysql-research-performance-schema-instruments/

http://minsql.com/mysql/alterTableProgress/

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-setup-instruments-table.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-setup-consumers-table.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-consumer-filtering.html