[MySQL] InnoDB에서 Alter Table되는 진행상황을 Peformance Schema를 이용하여 모니터링 하기

성능 스키마를 사용하여 InnoDB 테이블에 대한 ALTER TABLE 진행 상황을 모니터링 할 수 있습니다.

 

ALTER TABLE의 여러 단계를 나타내는 7개의 스테이지 이벤트가 있습니다. 각 단계 이벤트는 다른 단계를 통해 진행될 때 전체 ALTER TABLE 작업에 대해 WORK_COMPLETED 및 WORK_ESTIMATED의 누계를보고합니다. WORK_ESTIMATED는 ALTER TABLE이 수행하는 모든 작업을 고려하는 공식을 사용하여 계산되며 ALTER TABLE 처리중에 수정될 수 있습니다. WORK_COMPLETED 및 WORK_ESTIMATED 값은 ALTER TABLE에서 수행한 모든 작업의 추상적인 표현입니다.

 

발생 순서에 따라 ALTER TABLE 단계 이벤트에는 다음이 포함됩니다.

+ stage/innodb/alter 테이블 (PK 읽기 및 내부 정렬) : 이 단계는 ALTER TABLE이 기본키 읽기 단계에 있을때 활성화 됩니다. WORK_COMPLETED=0으로 시작하고 WORK_ESTIMATED는 기본키의 예상 페이지 수로 설정됩니다. 단계가 완료되면 WORK_ESTIMATED가 기본 키의 실제 페이지 수로 업데이트됩니다.

+ stage/innodb/alter table (병합 정렬) : ALTER TABLE 연산에 의해 추가 된 각 인덱스에 대해이 단계가 반복됩니다.

+ stage/innodb/alter table (insert) : ALTER TABLE 연산에 의해 추가 된 각 인덱스에 대해이 단계가 반복됩니다.

+ stage/innodb/alter 테이블 (로그 적용 인덱스) : ALTER TABLE이 실행되는 동안 생성 된 DML 로그를 적용하는 단계입니다.

+ stage/innodb/alter table (flush) : 이 단계가 시작되기 전에 WORK_ESTIMATED는 플러시 목록의 길이를 기준으로 더 정확한 추정치로 업데이트됩니다.

+ stage/innodb/alter 테이블 (로그 적용 테이블) : ALTER TABLE이 실행되는 동안 생성된 동시 DML 로그를 적용하는 단계입니다. 이 단계의 기간은 테이블 변경 범위에 따라 다릅니다. 이 단계는 테이블에서 동시 DML이 실행되지 않은 경우 즉시 수행됩니다.

+ stage/innodb /alter table (end) : ALTER TABLE이 실행되는 동안 테이블에서 실행된 DML을 다시 적용하는 것과 같이 플러시 단계 이후에 나타난 나머지 작업을 포함합니다.

 

참고사항

InnoDB ALTER TABLE 스테이지 이벤트는 현재 공간 인덱스 추가를 고려하지 않습니다.

 

▶︎ 성능 스키마를 사용한 ALTER TABLE 모니터링 예

다음 예는 stage/innodb/alter table% 스테이지 이벤트 계측기 및 관련 소비자 테이블을 활성화하여 ALTER TABLE 진행 상황을 모니터링하는 방법을 보여줍니다.

 

1. stage/innodb/alter% 기기를 활성화합니다.

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES'
       WHERE NAME LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

 

2. events_stages_current, events_stages_history 및 events_stages_history_long을 포함하는 스테이지 이벤트 소비자 테이블을 활성화합니다.

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

 

3. ALTER TABLE 작업을 실행합니다. 이 예에서 middle_name 컬럼은 직원 샘플 데이터베이스의 직원 테이블에 추가됩니다.

mysql> ALTER TABLE employees.employees ADD COLUMN middle_name varchar(14) AFTER first_name;
Query OK, 0 rows affected (9.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

4. 성능 스키마 events_stages_current 테이블을 쿼리하여 ALTER TABLE 작업의 진행 상황을 확인합니다. 표시되는 스테이지 이벤트는 현재 진행중인 ALTER TABLE 단계에 따라 다릅니다. WORK_COMPLETED 열에는 완료된 작업이 표시됩니다. WORK_ESTIMATED 열은 남은 작업의 추정치를 제공합니다.

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |            280 |           1245 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.01 sec)

 

events_stages_current 테이블은 ALTER TABLE 작업이 완료된 경우 빈 집합을 반환합니다. 이 경우 events_stages_history 테이블을 확인하여 완료된 작업에 대한 이벤트 데이터를 볼 수 있습니다. 예를 들면 :

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_history;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |            886 |           1213 |
| stage/innodb/alter table (flush)                     |           1213 |           1213 |
| stage/innodb/alter table (log apply table)           |           1597 |           1597 |
| stage/innodb/alter table (end)                       |           1597 |           1597 |
| stage/innodb/alter table (log apply table)           |           1981 |           1981 |
+------------------------------------------------------+----------------+----------------+
5 rows in set (0.00 sec)

 

위와 같이 ALTER TABLE 처리 중에 WORK_ESTIMATED 값이 수정되었습니다. 초기 단계 완료 후 예상 작업량은 1213입니다. ALTER TABLE 처리가 완료되면 WORK_ESTIMATED가 실제 값인 1981로 설정되었습니다.