운영하는 시스템에서 replication 지연현상이 발생하여 원인 분석 했던 사례 공유드립니다.
포인트는 replication 지연이 발생하는 부분을 찾고 문제되는 쿼리와 그 원인 파악 입니다.
--1. 지연 현상 발생 시점의 Replication 상태
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: TEST2
Master_User: repliUser
Master_Port: 3312
Connect_Retry: 60
Master_Log_File: mysql-bin.000038
Read_Master_Log_Pos: 250526084
Relay_Log_File: relay-log.000038
Relay_Log_Pos: 393111930
Relay_Master_Log_File: mysql-bin.000029
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 393111642
Relay_Log_Space: 6388283173
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 161990
-. Master_Log_File : I/O thread는 Master 노드의 mysql-bin.000038를 읽고있음.
-. Relay_Master_Log_file : SQL-thread는 Master 노드의 mysql-bin.000029 를 반영 중.
-. Exec_Master_log_pos : mysql-bin.000029 의 393111642 Pos를 반영중이고 161990 초가 넘도록 반영중인 것으로 보아 이 부분이 병목현상의 원인으로 파악됨.
-. Seconds_Behind_Master : I/O thread와 SQL Thread의 반영 차이를 초(sec) 로 나타냄.
--2. 병목 부분 확인
mysql-bin.000029 를 확인해보면
# at 393111642 #171115 13:59:24 server id 2 end_log_pos 393111680 GTID 0-2-9707043 /*!100001 SET @@session.gtid_seq_no=9707043*//*!*/; BEGIN /*!*/; # at 393111680 # at 393111799 # at 393112809 # at 393113819 # at 393114831 # at 393115841 . . . . . # at 1651532355 # at 1651533367 # at 1651534379 # at 1651535395 # at 1651536405 #171115 13:58:48 server id 2 end_log_pos 393111799 Table_map: `test`.`si_test` mapped to number 93 #171115 13:58:48 server id 2 end_log_pos 393112809 Update_rows: table id 93 #171115 13:58:48 server id 2 end_log_pos 393113819 Update_rows: table id 93 . . #171115 13:58:48 server id 2 end_log_pos 1651536931 Update_rows: table id 93 flags: STMT_END_F
-. 지연되는 Update 쿼리의 대상 테이블 생성구문
CREATE TABLE "si_test" (
"DIV_CD" varchar(3) COLLATE utf8_bin DEFAULT NULL COMMENT '사업부', "PART_NO" varchar(40) COLLATE utf8_bin DEFAULT NULL COMMENT 'PARTNO', "STOCK_QTY" double DEFAULT NULL COMMENT '사업부재고수량', "WORK_YN" varchar(1) COLLATE utf8_bin DEFAULT NULL COMMENT '작업여부',
"WORK_DATE" datetime DEFAULT NULL COMMENT '인터페이스일시',
"BAM_SEQUENCE_ID" double DEFAULT NULL COMMENT '통합 모니터링 관련 컬럼',
"OLD_GLOBAL_UNIQUE_ID" varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '통합 모니터링 관련 컬럼',
"STEP_CODE" varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT 'step for B2B Server',
"STEP_STATUS_CODE" varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT 'step status for B2B Server',
"EDI_INTERFACE_DATE" datetime DEFAULT NULL COMMENT 'edi interface date for B2B Server',
"GLOBAL_UNIQUE_ID" varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '통합 모니터링 관련 컬럼',
"HIM_WORK_YN" varchar(1) COLLATE utf8_bin DEFAULT 'N' COMMENT 'HIM 작업 여부',
"HIM_WORK_DATE" datetime DEFAULT NULL COMMENT 'HIM 작업 일자',
"ATTRIBUTE1" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE2" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE3" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE4" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE5" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE6" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE7" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE8" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE9" varchar(150) COLLATE utf8_bin DEFAULT NULL,
"ATTRIBUTE10" varchar(150) COLLATE utf8_bin DEFAULT NULL,
KEY "si_test_IX02" ("HIM_WORK_YN")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='사업부재고정보(I/F)'
=> Primary key 없으며 KEY "si_test_IX02" ("HIM_WORK_YN") 인덱스 존재
-. 해당 테이블의 인덱스 생성 구문 및 효율성
MariaDB [(none)]> show index from test.si_test\G;
*************************** 1. row ***************************
Table: si_test
Non_unique: 1
Key_name: si_test_IX02
Seq_in_index: 1
Column_name: HIM_WORK_YN
Collation: A
Cardinality: 166
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
=> table rows 수는 190296711 인데 비하여 cardinality 가 166인 비효율적인 인덱스밖에 없어서 update 시 full scan 발생
-> update 속도 문제 및 Slave로의 복제 시 성능 이슈 유발
-. 병목 유발 쿼리 확인
60079824 ### UPDATE `test`.`si_test` 60079825 ### WHERE 60079826 ### @1='DMZ' 60079827 ### @2='AHA73673401' 60079828 ### @3=3 60079829 ### @4='N' 60079830 ### @5='2017-11-12 09:15:03' 60079831 ### @6=1015 60079832 ### @7=NULL 60079833 ### @8=NULL 60079834 ### @9=NULL 60079835 ### @10=NULL 60079836 ### @11='5DAE334C460B04B2E053A5F4EF879EA3' 60079837 ### @12='N' 60079838 ### @13=NULL 60079839 ### @14='3' 60079840 ### @15=NULL 60079841 ### @16=NULL 60079842 ### @17=NULL 60079843 ### @18=NULL 60079844 ### @19=NULL 60079845 ### @20=NULL 60079846 ### @21=NULL 60079847 ### @22=NULL 60079848 ### @23=NULL 60079849 ### SET 60079850 ### @1='DMZ' 60079851 ### @2='AHA73673401' 60079852 ### @3=3 60079853 ### @4='X' 60079854 ### @5='2017-11-12 09:15:03' 60079855 ### @6=1015 60079856 ### @7=NULL 60079857 ### @8=NULL 60079858 ### @9=NULL 60079859 ### @10=NULL 60079860 ### @11='5DAE334C460B04B2E053A5F4EF879EA3' 60079861 ### @12='N' 60079862 ### @13=NULL 60079863 ### @14='3' 60079864 ### @15=NULL 60079865 ### @16=NULL 60079866 ### @17=NULL 60079867 ### @18=NULL 60079868 ### @19=NULL 60079869 ### @20=NULL 60079870 ### @21=NULL 60079871 ### @22=NULL 60079872 ### @23=NULL
--3. 해결 방법
MariaDB [(none)]> show index from lghirunp.si_divstock\G;
*************************** 1. row ***************************
Table: si_divstock
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: INTERFACE_ID
Collation: A
Cardinality: 190296711
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
=> Primary key 추가하여 update 성능향상
