[MsSQL] 마지막 페이지 Latch 경합 (Insert 지연 튜닝) - 2. 테스트 및 적용

Insert 지연 튜닝 - 테스트 및 적용

 

지난번에 원인을 파악했떤 마지막 페이지 경합에 대한, 업무 반영을 하기위해 테스트를 진행하고 결과에 맞게 반영하게 되었다.

[MsSQL] 마지막 페이지 Latch 경합 (Insert 지연 튜닝) - 1. 원인 찾기

 

잘못된 테스트와 정확한 테스트 그리고 새롭게 알아낸 내용 등에 대해서 한번 소개해보도록 하겠다.

 

테스트

어떻게하면 내가 생각한 이슈에 의해 마지막 페이지경합이 발생하며, 해결 방법을 통해 경합이 줄어들었음을 알 수 있을까?

실행된 스레드에서 발생하는 모든 대기에 대한 정보를 반환하는 시스템 관리 뷰를 발견하여,

해당 뷰를 가지고 PAGELATCH_EX 대기의 발생 빈도와 대기시간을 파악하면 되겠구나 싶었다.

select * 
  from SYS.DM_OS_WAIT_STATS 
 where wait_type = 'PAGELATCH_EX'

여기서 waiting_tasks_count 는 발생 수, wait_time_ms는 대기 시간을 뜻한다.

사실 이 시스템 관리 뷰를 사용하여 테스트 한것은 좀 부정확 하였다. 조금 값이 랜덤하고 나오는 느낌이였다.

 

그래서 조금 잘못된 테스트가 되어 잘못 실무에 적용하였고, 추후 정말 해당 오브젝트의 latch 발생에 대한 값을 조회하는 쿼리를 찾게 되었다.

select index_id, 
       (select name 
          from sys.indexes
         where object_id = object_id('TB_LATCH_TEST') 
           and index_id = a.index_id) as index_nm,
       page_latch_wait_count, page_latch_wait_in_ms
  from SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id('BAN_DB'), 
                                          object_id('TB_LATCH_TEST'), 
                                          NULL, 
                                          NULL) a

 

이 쿼리로 보다 정확한 비교를 하고 문제를 파악하여 지연 이슈를 해결 할 수 있었다. 실제 해결에 대해서는 뒤에 다루도록 하겠다.

 

여러가지 상황에서 테스트를 하기위해 데이터를 loop돌며 insert하는 쿼리를 작성하였다.

-- 1. TB_LATCH_TEST 테이블에 5만 건의 랜덤 데이터 삽입
DECLARE @i INT = 0;
DECLARE @batchSize INT = 20;
DECLARE @randomString VARCHAR(200);
DECLARE @randomInt INT;

WHILE @i < 50000
BEGIN
    BEGIN TRANSACTION;

    DECLARE @j INT = 0;
    WHILE @j < @batchSize AND @i < 50000
    BEGIN
        -- 2. 200자 랜덤 문자열 생성
        SET @randomString = '';
        WHILE LEN(@randomString) < 200
        BEGIN
            SET @randomString = @randomString + CHAR(65 + ROUND(RAND() * 25, 0)); -- A-Z 랜덤 문자 추가
        END

        -- 3. 1부터 10000 사이의 랜덤 정수 값 생성
        SET @randomInt = 1 + CAST(RAND() * 9999 AS INT);

        -- 4. 데이터 삽입
        INSERT INTO TB_LATCH_TEST (t_cts, t_cd)
        VALUES (@randomString, @randomInt);

        -- 5. 반복 카운터 증가
        SET @i = @i + 1;
        SET @j = @j + 1;
    END

    COMMIT TRANSACTION;
END;

 

위 쿼리는 총 5만건의 데이터를 총 20개의 Insert 마다 commit 하는 쿼리이다.

해당 쿼리를 총 10개의 세션에서 실행하여 경합이 최대한 일어나게 하여 테스트를 진행하였다.

 

1.  순차 컬럼을 key로 가지는 클러스터 인덱스 테스트

기존의 문제라고 생각되었던 구성을 그대로 해보았다.

순차컬럼으로 클러스터인덱스와 그냥 기본 인덱스 한개를 가지는 테이블을 생성하였다.

create table TB_LATCH_TEST (
    t_no int identity(1, 1),
    t_cts varchar(200),
    t_cd int,
    ins_dtm datetime default getdate()
)

create clustered index CL_TEST on TB_LATCH_TEST (ins_dtm)
create index NCL_TEST on TB_LATCH_TEST (t_cd)

 

insert 실행하기 바로 앞서 wait에 대한 정보를 먼저 조회하였다. 

waiting_tasks_count : 11047713 / wait_time_ms : 2081670

 

insert가 완료된 후 wait에 대한 정보를 조회하고 비교하였다.

waiting_tasks_count : 11323436 / wait_time_ms : 2662527

 

비교한 결과 275723 건의 pagelatch_ex가 발생하였고,  580857ms 만큼의 대기가 발생하였다.

 

각 인덱스 오브젝트 마다의 latch wait에 대해서는 아래와 같다

클러스터인덱스는 299860번의 latch 대기가 발생하였는데, 논클러스터 인덱스는 단 10회 밖에 발생하지않았다.

 

역시 예상했던 대로 순차 컬럼이라 래치가 많이 발생하였고, 클러스터 인덱스다보니 데이터 페이지의 공간을 많이 차지하여, page split이 많이 발생하여 latch가 발생하였을거라 추축되었다.

 

2.  비순차 컬럼을 key로 가지는 클러스터 인덱스 테스트

위의 1번에서 테스트한 내용의 클러스터 인덱스와 논클러스터 인덱스를 서로 바꿔보아 테스트하였다.

 

순차컬럼이 논클러스터이고, 균등 분배되는 클러스터 인덱스일때 과연 얼마나 latch발생이 줄어드는가에 대한 테스트였다.

create table TB_LATCH_TEST (
    t_no int identity(1, 1),
    t_cts varchar(200),
    t_cd int,
    ins_dtm datetime default getdate()
)

create clustered index CL_TEST on TB_LATCH_TEST (t_cd)
create index NCL_TEST on TB_LATCH_TEST (ins_dtm)

 

insert 시작하기 전의 wait 정보는 아래와 같았다.

waiting_tasks_count : 11340988 / wait_time_ms : 2664008

 

insert 가 완료된 후는 아래와 같다

waiting_tasks_count : 11528572 / wait_time_ms : 3093330

 

비교한 결과 187584 건의 pagelatch_ex가 발생하였고, 429322 ms 만큼의 대기가 발생하였다.

테스트 1과 비교했을 때 조금 빨라짐을 확인하였다.

인덱스 별로 latch 대기를 확인해보니,

순차컬럼의 논클러스터 인덱스가 클러스터 인덱스였을 때 보다 확실히 대기를 적게 발생시키나 아직도 많은 양의 대기를 발생시키고있다.

클러스터 인덱스는 균등하게 들어가기에 page split이 덜 발생하였기에 latch가 덜 발생하였나보다.

 

3.  클러스터 인덱스 없이 ndf 파일 분할로 insert 분배

결국 하나의 페이지에 여러 insert가 몰리는게 문제니, ndf파일을 여러개로 분할해보는것은 어떠한가? 라는 생각이 들었다.

새로운 File Group을 만들어주고 File을 3개를 할당하여, 해당 File Group에 테이블과 인덱스를 생성하였다.

create table TB_LATCH_TEST (
    t_no int identity(1, 1),
    t_cts varchar(200),
    t_cd int,
    ins_dtm datetime default getdate()
) on TEST_DATA

create index CL_TEST on TB_LATCH_TEST (t_cd) on TEST_DATA
create index NCL_TEST on TB_LATCH_TEST (ins_dtm) on TEST_DATA

 

이때 순차적으로 적재해야하는 클러스터 인덱스는 제거하였다.

 

insert 시작하기 전의 wait 정보는 아래와 같았다.

waiting_tasks_count : 11528746 / wait_time_ms : 3093332

 

insert 가 완료된 후는 아래와 같다

waiting_tasks_count : 11661555 / wait_time_ms : 3409748

 

비교한 결과 132809 건의 pagelatch_ex가 발생하였고, 316416 ms 만큼의 대기가 발생하였다.

테스트 1,2 와 비교했을 때 조금 빨라짐을 확인하였다.

인덱스 명을 잘못 지정하긴했지만, 위 쿼리와 맞춰져있으니 어떤 인덱스 명인지 확인바란다.

오브젝트 별 대기를 보면 

클러스터 인덱스가 없어지며, 데이터가 쌓이는 힙 영역(index_id = 0) 에 latch대기가 꽤나 많이 생기고

역시나 순차 컬럼을 키로 가지는 인덱스에서 많은 latch 대기가 발생하였다.

 

결국에 문제는 순차컬럼을 없애야 하는가? 이거라고 생각된다.

 

결론

처음에는 조금 잘못된 결과값으로 ndf파일을 나누자로 결론을 지으게 되었다.

결과적으로 ndf파일 4개를 할당하여 분할 insert를 한다고 생각했지만, 결국 순차컬럼을 키로 가지는 인덱스에서 엄청난 latch가 발생하였고, 감소량이 미미하였다고 생각이 든다.

 

결론적으로는 역시 순차컬럼을 index에 사용하지 말아야겠다 라는 생각이 들었으며, 해당 테이블은 시간은 필요가 없고 날짜 단위로만 조회하는 컬럼이라 date컬럼을 새롭게 추가하게 되었다.

create table TB_LATCH_TEST (
    t_no int identity(1, 1),
    t_cts varchar(200),
    t_cd int,
    ins_dtm datetime default getdate(),
    ins_dt date default getdate()
) on TEST_DATA

create index NCL_TEST on TB_LATCH_TEST (t_cd) on TEST_DATA
create index NCL_TEST_dt on TB_LATCH_TEST (ins_dt) on TEST_DATA

 

시간이 안들어가기에 균일한 값을 가지고 분할 insert를 한다고 판단되었다.

위에 테스트 들과 마찬가지로 wati에 대한 정보를 조회해보았다.

 

insert 시작하기 전의 wait 정보는 아래와 같았다.

waiting_tasks_count : 11662708 / wait_time_ms : 3409854

 

insert 가 완료된 후는 아래와 같다.

waiting_tasks_count : 11695932 / wait_time_ms : 3506167

 

비교한 결과 33224건의 pagelatch_ex가 발생하였고, 96313 ms 만큼의 대기가 발생하였다.

위에 테스트들과 비교하였을때 약 1/3로 감소하였다. 

 

오브젝트별 latch발생도 확인해보니, 인덱스에서 발생하는 latch의 빈도가 극격하게 줄었으며, 대체로 힙에 데이터를 쌓는데 발생하였다.