Last Updated on 3월 2, 2024 by Jade(정현호)
안녕하세요.
이번 포스팅에서는 PK가 없는 테이블을 사용하는 환경에서의 복제 지연 현상과 8.0 버전에서 새로 추가된 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능에 대해서 확인해보려고 합니다.
테스트 테이블 생성 및 데이터 적재
테스트를 위해서 테이블을 하나 생성 후 데이터를 적재하도록 하겠습니다.
포스팅에서 사용한 MySQL 버전은 8.0.25 이며, binlog_format 은 ROW입니다.
테이블 생성
테이블에는 별도의 인덱스가 없는 상태입니다.
create table tb_test_repl ( id int , tb_content varchar(200), tb_writer varchar(200), tb_regdate datetime);
데이터 적재
데이터는 500만건을 적재하도록 하겠습니다. 데이터 적재 시 빠른 적재(그리고 테스트임으로) 위해서 redo 로그를 비활성화 후 적재하도록 하겠습니다.
-- REDO 비활성화
alter instance disable innodb redo_log;
-- PROCEDUER 수행하여 500만건 입력
DELIMITER $$
DROP PROCEDURE IF EXISTS loopInsert$$
CREATE PROCEDURE loopInsert()
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION;
WHILE i <= 5000000 DO
INSERT INTO tb_test_repl(id , tb_content, tb_writer , tb_regdate)
VALUES(i, concat('content',i), 'admin', now());
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
CALL loopInsert;
적재가 완료되었다면 다시 REDO 로그를 활성화합니다.
alter instance enable innodb redo_log;
Note
MySQL 8.0.21 버전 부터는 수동으로 리두(Redo) 로그를 활성화/비활성화 할 수 있는 기능이 추가되었습니다.
데이터를 복구하거나 인스턴스 생성 후 초기에 대량의 데이터를 적재(마이그레이션) 등의 작업을 할 경우 REDO 를 비활성화 후 작업을 수행할 수 있으며, 작업 수행 시간이 단축할 수 있습니다.
생성된 테이블은 아래와 같은 유형으로 되어 있습니다.
+------+------------+-----------+---------------------+ | id | tb_content | tb_writer | tb_regdate | +------+------------+-----------+---------------------+ | 1 | content1 | admin | 2022-04-15 02:15:32 | | 2 | content2 | admin | 2022-04-15 02:15:32 | | 3 | content3 | admin | 2022-04-15 02:15:32 | | 4 | content4 | admin | 2022-04-15 02:15:32 | | 5 | content5 | admin | 2022-04-15 02:15:32 | | 6 | content6 | admin | 2022-04-15 02:15:32 | | 7 | content7 | admin | 2022-04-15 02:15:32 | | 8 | content8 | admin | 2022-04-15 02:15:32 | | 9 | content9 | admin | 2022-04-15 02:15:32 | | 10 | content10 | admin | 2022-04-15 02:15:32 | +------+------------+-----------+---------------------+
auto_increment 가 주로 사용될 것으로 보이는 id 컬럼은 현재 PK 또는 Secondary Index 가 생성되지 않은 상태 입니다
(테이블에 인덱스가 존재하지 않음)
데이터 삭제
위에서 생성한 테스트 테이블에 대해서 데이터를 삭제하도록 하겠습니다.
데이터는 300만건을 삭제할 것이며, id 컬럼에서 between 을 사용하여 삭제하도록 하겠습니다.
데이터 삭제
-- 300만건 삭제 delete from tb_test_repl where id between 1 and 3000000; Query OK, 3000000 rows affected (24.93 sec)
완료되는데 약 25초가량 소요되었습니다.
autocommit=ON(DEFAULT) 임으로 DELETE 문장이 완료된 다음 COMMIT 이 수행이 되게 되게 됩니다.
그 다음 Replica 인스턴스에서는 바로 복제 지연이 발생이 되게 됩니다.
아래와 같이 show replica status(또는 show slave status) 로 조회를 하면 Seconds_Behind_Source(또는 Seconds_Behind_Master) 값은 계속 늘어나게 되고, Relay_Log_File 와 Relay_Log_Pos 의 변화는 상당히 느릴 것입니다.
show replica status\G
******************* 1. row *******************
Source_Log_File: node1.000012
Read_Source_Log_Pos: 262902737
Relay_Log_File: node2-relay-bin.000012
Relay_Log_Pos: 164593574
Relay_Source_Log_File: node1-bin.000012
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Exec_Source_Log_Pos: 164593363
Relay_Log_Space: 262903201
Seconds_Behind_Source: 302
<!!-- 복제 지연은 계속 늘어남
Replica 인스턴스에서 지연이 발생되는 이유는 DELETE 문장 수행 시 사용할 적절한 인덱스가 없기 때문입니다.
포스팅 예제에서 id 컬럼에 PK 가 아닌 Secondary Index 라도 있었다면 위와 같이 지연이 계속 적으로 늘어나지는 않을 것입니다.
왜냐면 예제 에서의 id 컬럼은 auto_increment 로 생성한 것 같이 while loop 문으로 순차적으로 입력한 값 이기 때문에 변별력(카디널리티)가 좋은 컬럼입니다. 그래서 만약 Secondary index 가 있었다면 문제가 발생되지는 않게 됩니다.
하지만 예를 들어 WHERE 절의 조건에 USE_YN 과 같은 Y 와 N 값만 들어 있는 카디널리티가 좋지 못한 컬럼이 WHERE 절 조건으로 사용되었을 경우 해당 컬럼이 Secondary Index 가 있더라도 위와 같은 지연 현상은 발생할 수 있게 됩니다.
지연되는 과정에서 relaylog 파일을 mysqlbinlog 유틸리티을 통해 확인하면 아래와 같은 SQL 문장이 수행되는 것을 확인할 수 있습니다.
### DELETE FROM `sysbench`.`tb_test_repl` ### WHERE ### @1=3 /* INT meta=0 nullable=1 is_null=0 */ ### @2='content3' /* VARSTRING(800) meta=800 nullable=1 is_null=0 */ ### @3='admin' /* VARSTRING(800) meta=800 nullable=1 is_null=0 */ ### @4='2022-04-15 05:04:05' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
* binlog_format=ROW 인 환경에서의 로그 내용임
테이블에 모든 컬럼이 WHERE 절 비교 대상으로 해서 DELETE 문이 진행 중이고 예제에서는 사용할 수 있는 인덱스 또는 카디널리티가 높은 인덱스가 없기 때문에 1개의 DELETE 문장을 Replica 인스턴스에서 처리하는데 많은 시간이 소요되고 있는 것입니다.
PK 가 있었을 경우 id 컬럼을 의미하는 @1=3 조건절에서 PK 를 통해서 DELETE 가 되었을 것입니다.
InnoDB 의 Clustered Index 테이블의 경우 사용자가 테이블 생성시 명시적으로 PK 를 지정하지 않아도 생성시 내부적인 PK가 생성은 되지만, 이러한 쿼리 레벨에서 직접적으로 사용할 수는 없습니다.
문제 해결
이와 같이 DELETE 문이나 UPDATE 문이 Master(Primary) 에서 수행에 소요된 시간 보다 더 많이, 이상하게 더딘 처리속도와 지연이 발생된다면 테이블의 구조를 확인하여 PK 가 있는지 여부를 먼저 살펴보는 것도 좋을 것 같습니다.
지금과 같은 케이스에서 복제 지연 시간이 계속 늘어나고 있는 상황에 대해서 해결할 수 있는 방법은 여러가지가 있겠으며, 가장 쉬운 방법으로는 PK 를 생성하는 것 입니다.
PK 는 Replica 인스턴스 부터 생성을 하면 되며, 복제 중지 -> PK 생성 -> 복제 시작 순으로 진행을 합니다.
-- 복제 중지(stop) stop replica; 또는 stop slave; -- PK 생성 alter table tb_test_repl add primary key(id); -- 복제 시작 start replica; 또는 start slave;
PK 생성 완료 후, PK 를 사용할 수 있는 시점이 되면 그때 부터 매우 빠르게 Seconds_Behind_Source(또는 Seconds_Behind_Master) 수치가 줄어드는 것을 확인할 수 있으며, 실행되어야 하는 레코드 건수에 따라 다르겠지만, 생각 보다 매우 빠르게 복제 지연이 해소되는 것을 확인할 수 있습니다.
이 작업은 복제 지연이 발생되고 있는 Replica 인스턴스에서 작업을 하는 것이며, Primary 에서는 별도의 작업 시간(PM) 에 하거나 binary 로그를 남기지 않고 pt-online-schema-change 를 사용하여 PK 을 생성하거나, 기타 여러 좋은 방법으로 Primary 인스턴스에서도 PK 를 생성을 진행하면 됩니다.
pt-online-schema-change 에 대해서는 아래 포스팅을 참고하시면 됩니다.
Note
MySQL 8.0.22 버전 부터 start slave 에서 start replica 로 , stop slave 에서 stop replica 로 커맨드 가 변경 되었습니다.
REQUIRE_TABLE_PRIMARY_KEY_CHECK
위와 같은 문제가 발생될 수 있는 경우를 대비하여 MySQL 8.0.20 버전에서 부터 추가된 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능을 활용하는 방법도 한가지 좋은 방법이라고 생각됩니다.
REQUIRE_TABLE_PRIMARY_KEY_CHECK 는 Primary 인스턴스에서 테이블 생성 후 Replica 인스턴스에서 복제 내용을 적용(수행) 과정에서 PK 존재 여부를 확인 또는 체크하는 기능입니다.
MySQL 8.0.20 버전에서 추가된 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능 이전에 sql_require_primary_key 시스템 변수가 MySQL 8.0.13 버전에서 먼저 추가 되었고 REQUIRE_TABLE_PRIMARY_KEY_CHECK 는 sql_require_primary_key 시스템 변수를 사용하는 기능입니다.
sql_require_primary_key 시스템 변수는 MySQL 8.0.13 버전에서 추가된 시스템 변수로 기본값은 OFF 이며 ON 으로 활성 후에 테이블을 생성시 PK 가 없이 생성할 경우 에러를 발생하는 기능으로 테이블 생성시 PK 를 명시적으로 설정을 강제화 하는 역할을 하게 됩니다.
그래서 sql_require_primary_key 활성화 하게 되면 아래와 같은 룰이 적용되게 됩니다.
- 기본 키가 없는 새 테이블을 만들려는 시도는 오류와 함께 실패합니다.
- 여기에는 CREATE TABLE ... LIKE가 포함됩니다.
- CREATE TABLE 부분에 기본 키 정의가 포함되지 않는 한 CREATE TABLE ... SELECT도 포함됩니다.
- 동일한(하나의) ALTER TABLE 문에서 기본 키를 삭제하고 기본 키를 추가하는 것이 허용된다는 점을 제외하고 기존 테이블에서 기본 키를 삭제하려는 시도는 오류와 함께 실패합니다.
Note
AWS Aurora MySQL 에서는 sql_require_primary_key 과 require_table_primary_key_check 두개 파라미터 모두 사용 불가 합니다.
RDS for MySQL 8.0 에서 sql_require_primary_key 사용 가능하고 require_table_primary_key_check 은 없습니다.
REQUIRE_TABLE_PRIMARY_KEY_CHECK 는 Replica 인스턴스(또는 Replica 인스턴스 운영자) 입장에서 Primary 서버를 제어할 수 없거나 또는 설정 내역에 대해서 신뢰할 수 없을 경우 Replica 인스턴스 측면에서 PK 포함 여부를 체크하는 기능입니다.
REQUIRE_TABLE_PRIMARY_KEY_CHECK 를 3개를 선택할 수 있으며 아래와 같은 기능을 의미합니다.
- ON: 복제 채널은 복제 작업에서 항상 sql_require_primary_key 시스템 변수에 대해 ON 값을 사용하므로 모든 테이블 생성 및 변경 작업에 기본 키가 필요합니다.
- OFF: 복제 채널은 복제 작업에서 항상 sql_require_primary_key 시스템 변수에 대해 OFF 값을 사용하므로 기본 키가 이러한 제한을 적용하더라도 테이블을 생성하거나 변경할 때 기본 키가 필요하지 않습니다.
- 스트림: 기본값; 복제 채널은 각 트랜잭션에 대해 기본에서 복제된 값을 사용합니다. 이렇게 하면 이전 서버 동작이 유지됩니다.
기본 값은 스트림이며, ON 으로 사용시에는 위의 설명과 같이 Replica 인스턴스에서 sql_require_primary_key=ON 이 설정된 것처럼 동작하게 됩니다.
그래서 REQUIRE_TABLE_PRIMARY_KEY_CHECK=ON 을 통해서 Replication 설정(복제 구성) 을 하였을 경우 Primary 인스턴스에서 PK 가 없는 테이블을 생성시에는 Replica 인스턴스에서 복제 에러가 발생되게 됩니다.
대부분의 운영 환경에서 show replica status(또는 show slave status) 값을 모니터링 할 것임으로 테이블 생성시 PK 가 누락되어 복제에 문제가 발생하여 감지(SMS 나 메신저 등)가 되었을 경우 위의 포스팅의 예제와 같이 PK가 없어서 문제되는 상황은 사전에 방지할 수 있을 것으로 생각됩니다.
복제 설정시 옵션
REQUIRE_TABLE_PRIMARY_KEY_CHECK 는 시스템 변수가 아닌 CHANGE MASTER TO 나 CHANGE REPLICATION SOURCE TO 와 같은 복제 설정 명령어의 추가된 옵션 값입니다.
아래와 같이 복제를 설정하는 과정에서도 할 수 있으며, 기존의 사용중인 복제 설정내역에서 추가 도 할 수 있습니다.
-- 복제 설정시 -- MySQL 8.0.23 이상 - CHANGE REPLICATION SOURCE CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON, source_host='xxx.xxx.xxx.xxx', source_port=nnnn, source_user='nnnn',source_password='nnnn', source_log_file='bin.000012',source_log_pos=656141280; -- 단일 채널 복제일 경우 STOP REPLICA SQL_THREAD; CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON; START REPLICA SQL_THREAD FOR CHANNEL 'channel_1'; -- MSR 일 경우 STOP REPLICA SQL_THREAD FOR CHANNEL 'channel_1'; CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON FOR CHANNEL 'channel_1'; START REPLICA SQL_THREAD FOR CHANNEL 'channel_1';
위와 같이 REQUIRE_TABLE_PRIMARY_KEY_CHECK 를 설정 후 Primary 인스턴스에서 PK 없이 테이블을 생성하면 복제에 에러가 발생됩니다. 그리고 아래와 같이 Replica 인스턴스 복제 정보에서 에러 내역을 확인할 수 있습니다.
## show replica status(또는 show slave status) 출력 결과 중 내용 입니다. Last_SQL_Error: Error 'Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.' on query. Default database: 'nnnn'. Query: 'create table tb_test_repl2 ( id int , tb_content varchar(200), tb_writer varchar(200), tb_regdate datetime)' Replicate_Ignore_Server_Ids:
이와 같이 에러가 발생하게 되면 알람 통지 등을 통해서 Primary 인스턴스에서 PK 가 없는 테이블이 생성되었다고 인지(판단) 하시면 됩니다.
해결 방법으로는 여러가지 방법이 있을 수 있으며, 간단하게는 REQUIRE_TABLE_PRIMARY_KEY_CHECK 를 비활성화 -> Primary 인스턴스에서 PK 를 추가 -> REQUIRE_TABLE_PRIMARY_KEY_CHECK 활성화 순으로 진행할 수 있습니다.
Replica 인스턴스에서 REQUIRE_TABLE_PRIMARY_KEY_CHECK=OFF 로 변경 합니다.
-- 단일 채널 복제일 경우 STOP REPLICA SQL_THREAD; CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = OFF; START REPLICA SQL_THREAD;
그 다음 Primary 인스턴스에서 PK 를 추가합니다
-- Primary 인스턴스에서 PK를 생성 alter table tb_test_repl2 add primary key(id);
Replica 인스턴스에서 테이블에 PK 생성 내역이 복제가 되어 같이 생성되었는지를 확인합니다.
-- Replica 인스턴스에서 확인
mysql> show create table tb_test_repl2\G
*************************** 1. row ***************************
Table: tb_test_repl2
Create Table: CREATE TABLE `tb_test_repl2` (
`id` int NOT NULL,
`tb_content` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`tb_writer` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
`tb_regdate` datetime DEFAULT NULL,
PRIMARY KEY (`id`) <!!--- PK 생성 된 내역이 복제 되었음
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
다시 REQUIRE_TABLE_PRIMARY_KEY_CHECK 를 활성화 합니다.
-- 단일 채널 복제일 경우 STOP REPLICA SQL_THREAD; CHANGE REPLICATION SOURCE TO REQUIRE_TABLE_PRIMARY_KEY_CHECK = ON; START REPLICA SQL_THREAD;
물론 복제가 문제가 생기기 전에 Primary 인스턴스 레벨에서 부터 에러가 발생되어서 사용자로 하여금 PK 가 누락되었고 PK 를 포함하여 테이블 생성이 필요하다는 것을 인지시킬 수도 있습니다.
그럴 경우에 sql_require_primary_key 시스템 파라미터를 설정하여 Primary 인스턴스에서 애초에 테이블 생성 부분 부터 체크되고 에러가 발생될 수 있도록 하는 것도 좋은 한가지 방법이라고 생각 합니다.
Reference
Reference URL
• dev.mysql.com/enforce-primary-key
• dev.mysql.com/group-replication-requirements
• dev.mysql.com/sql_require_primary_key
관련된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
