[MySQL - Online DDL] part 2. 실습

Online DDL 작업 정보 계측 memory

 

※ 사전 조건

performance_schema=ON

MariaDB [(none)]> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

 

 

■ Performance_schmea.setup_instruments 조회

mysql> select substring_index(name,'/',1) as cate, count(*) as cnt from performance_schema.setup_instruments group by substring_index(name,'/',1);
+-------------+-----+
| cate        | cnt |
+-------------+-----+
| wait        | 393 |
| idle        |   1 |
| stage       | 131 |
| statement   | 213 |
| transaction |   1 |
| memory      | 497 |
| error       |   1 |
+-------------+-----+
7 rows in set (0.00 sec)

 

 

■ sample data insert

mysql> create table test_tbl ( a int primary key auto_increment, b varchar(100), c varchar(100));

mysql> insert into test_tbl select null, uuid(),uuid() from information_schema.columns as a, information_schema.columns as b limit 1000000;

 

 

■ 해당 테이블에 alter 사항을 모니터링 하기 위한 performance schema 셋팅 정보 변경

-- alter 작업을 위한 setup_instruments 를 Enable 시킨다.
mysql> update performance_schema.setup_instruments set enabled='YES' where name like 'stage/innodb/alter%';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 8  Changed: 0  Warnings: 0


-- events_stages_%의 setup_consumers를 Enable시켜 각각의 alter stage를 모니터링 한다.
mysql> update performance_schema.setup_consumers set enabled='YES' where name like 'events_stage%';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

 

 

■ aleter 작업 새로운 컬럼 추가 

mysql> ALTER TABLE test_tbl ADD COLUMN x INT;
Query OK, 0 rows affected (6.063 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test_tbl DROP COLUMN x;
Query OK, 0 rows affected (12.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

 

■ 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;

*************************** 1. row ***************************
                THREAD_ID: 45
                 SQL_TEXT: ALTER TABLE test_tbl DROP COLUMN x
               EVENT_NAME: stage/innodb/alter table (read PK and internal sort)
        완료 작업 수: 12834
        전체 작업 수: 28849
                진척률: 44.49%
            실행 시간: 7.27 s
            남은 시간: 9.07 s
     종료 예상 시간: 2022년 08 월 17 일 16:31:11
초 당 처리 작업 수: 1765
1 row in set, 3 warnings (0.00 sec)

 

 

■ 작업 완료 이후 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;
+----------+------------------+------------------------------------+------------------------------------------------------+------------+---------------+
| EVENT_ID | NESTING_EVENT_ID | SQL_TEXT                           | EVENT_NAME                                           | 작업 수     | 작업 시간     |
+----------+------------------+------------------------------------+------------------------------------------------------+------------+---------------+
|       36 |               34 | ALTER TABLE test_tbl DROP COLUMN x | stage/innodb/alter table (read PK and internal sort) |      28849 | 12.34 s       |
|       37 |               34 | ALTER TABLE test_tbl DROP COLUMN x | stage/innodb/alter table (end)                       |      28849 | 2.44 us       |
|       38 |               34 | ALTER TABLE test_tbl DROP COLUMN x | stage/innodb/alter table (read PK and internal sort) |          0 | 81.2 us       |
|       39 |               34 | ALTER TABLE test_tbl DROP COLUMN x | stage/innodb/alter table (end)                       |          0 | 4.12 us       |
|       40 |               34 | ALTER TABLE test_tbl DROP COLUMN x | stage/innodb/alter table (log apply table)           |        384 | 192.01 us     |
|       41 |               34 | ALTER TABLE test_tbl DROP COLUMN x | stage/innodb/alter table (end)                       |        384 | 47.45 us      |
|       42 |               34 | ALTER TABLE test_tbl DROP COLUMN x | stage/innodb/alter table (log apply table)           |        768 | 16.32 ms      |
+----------+------------------+------------------------------------+------------------------------------------------------+------------+---------------+
7 rows in set (0.01 sec)

 

 

■ 현재 사용중인 메모리 양 확인

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;

+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/vio                | 2.23 KiB      |
| memory/temptable          | 2.00 MiB      |
| memory/sql                | 11.89 MiB     |
| memory/performance_schema | 223.62 MiB    |
| memory/mysys              | 8.91 MiB      |
| memory/mysqlx             | 3.10 KiB      |
| memory/mysqld_openssl     | 277.54 KiB    |
| memory/myisam             |  728 bytes    |
| memory/innodb             | 196.44 MiB    |
| memory/csv                |  120 bytes    |
| memory/blackhole          |  120 bytes    |
+---------------------------+---------------+
11 rows in set (0.00 sec)

 

 

 

Index operation

■ Index 생성 과 동시에 새로운 세션에서 DML발생 (알고리즘 : Inplace)

## session 1
mysql> create index idx_test on test_tbl(a) ALGORITHM=inplace LOCK=none;
Query OK, 0 rows affected (2.81 sec)
Records: 0  Duplicates: 0  Warnings: 0



## session 2
INSERT INTO kim.test_tbl (b, c) VALUES (null, 'test1');
SELECT * FROM kim.test_tbl ORDER BY a DESC LIMIT 1;
UPDATE kim.test_tbl SET b='test2' WHERE a=1;
SELECT * FROM kim.test_tbl WHERE a=1;
DELETE FROM kim.test_tbl WHERE a=2;
SELECT * FROM kim.test_tbl WHERE a=2;

 

 

-> Index 생성이 완료되지 않아도 해당 쿼리들이 LOCK없이 정상적으로 진행됩니다.

mysql> SELECT * FROM kim.test_tbl ORDER BY a DESC LIMIT 1;
+---------+------+-------+
| a       | b    | c     |
+---------+------+-------+
| 1048565 | NULL | test1 |
+---------+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM kim.test_tbl WHERE a=1;
+---+-------+--------------------------------------+
| a | b     | c                                    |
+---+-------+--------------------------------------+
| 1 | test2 | 726b2d88-1dfe-11ed-a65d-f220cd459773 |
+---+-------+--------------------------------------+
1 row in set (0.00 sec)

 

Online DDL 시 진행된 DML은 Row Log Buffer에서 진행됩니다.  최대 사이즈 변수의 값을 넘어 버리면 오류가 발생되어 DDL이 실패하게 됩니다.

mysql> show global variables like '%online%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+

 

■ Index 삭제 (알고리즘 : Inplace)

mysql> drop index idx_test on kim.test_tbl ALGORITHM=inplace LOCK=none;

 

■ Index 이름 변경 (알고리즘 : Inplace)

mysql> ALTER TABLE test_tbl RENAME INDEX test_tbl TO test_tbl_new, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

 

 

Column Opertaions

■ column 삭제 (알고리즘 : Inplace)

mysql> ALTER TABLE test_tbl DROP COLUMN c, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (18.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

■ column 이름 변경 (알고리즘 : Inplace)

mysql> ALTER TABLE test_tbl change b c VARCHAR(100), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

■ column Default value 추가 및 변경 (알고리즘 : Instant)

mysql> ALTER TABLE test_tbl MODIFY c VARCHAR(100) DEFAULT '', ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

■ column Data Type 변경 (알고리즘 : copy)

Data Type 변경은 무조건 Copy 알고리즘만 사용이 가능합니다. 또한 Shared Lock 이하만 지원되기 때문에 CUD는 불가합니다.

mysql> UPDATE test_tbl SET c=0;

mysql> ALTER TABLE test_tbl MODIFY c INT DEFAULT 0, ALGORITHM=COPY, LOCK=SHARED;
Query OK, 1000004 rows affected (7.04 sec)
Records: 1000004  Duplicates: 0  Warnings: 0

 

■ column varchar Data Type 크기 확장 (알고리즘 : Inplace)

Varchar Type 일 시에 크기를 확장만 가능하고 축소는 불가합니다.

mysql> ALTER TABLE test_tbl CHANGE c c VARCHAR(300), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.01 sec)

 

■ column Default value 삭제 (알고리즘 : Instant)

mysql> ALTER TABLE test_tbl ALTER c DROP DEFAULT, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

 

참고

 

https://yhjin.tistory.com/80?category=1014637 

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