Print
카테고리: [ MariaDB ]
조회수: 5079

운영하는 시스템에서 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 성능향상