[MySQL] 사용자 로그 테이블 - (1) Primary Key가 필요한가?

[MySQL] 사용자 로그 테이블 - (1) Primary Key가 필요한가?

[MySQL] 사용자 로그 테이블 - (2) 파티션 관리

[MySQL] 사용자 로그 테이블 - (3) 자동화된 파티션 관리

로그 테이블과 Primary Key

InnoDB 에 대한 몇 가지 사실을 열거해 봅시다.

• Primary Key는 항상 Clustered Index 이다.
• Primary Key가 없다면 다음 우선 순위에 따라 Clustered Index가 선택된다.
  즉, Clustered Index가 없는 테이블은 존재하지 않는다.
• NOT NULL 속성의 Unique Key (= Unique Index)
• 후보가 없다면, 보이지 않는 컬럼을 내부적으로 추가하여 사용
• auto_increment 속성의 컬럼은 반드시 단일 Key 또는 복합 Key의 첫번째 컬럼이어야 한다.
• 여기서 Key는 Primary Key, Unique Key (=Unique Index), Key (= Index) 모두 가능

이제 본론으로 돌아와서, 로그 테이블에 Primary Key를 만들어야 할까요?

네, 만들어야 합니다.

첫 째로, 맨 위에 열거한 사실로부터 알 수 있는 것처럼 명시적으로 Primary Key를 만들지 않아도 시스템이 식별자로 사용할 보이지 않는 컬럼을 만듭니다. 그리고 이 보이지 않는 컬럼에 Clustered Index가 걸립니다.

테이블에 단 한 개만 생성할 수 있고, 이용했을 때 속도가 가장 빠른 Clustered Index 를 이렇게 낭비하는 것은 바람직하지 않기 때문에 우리는 명시적으로 Clustered Index를 만들어 사용해 주어야 합니다. Clustered Index를 직접 만들 수 있는 방법은? 네, Primary Key를 만드는 것입니다.

둘 째로, 상황에 따라 다르지만 복수의 로그 테이블이 상호 참조 관계를 가지도록 설계해야할 때가 있습니다.

예를 들면 게임의 마스터 로그와 마스터 로그에서 파생된 아이템 로그의 관계와 같은 것이죠.

(더 이상의 자세한 설명은 생략합니다!! ^^)

그럼 어떤 컬럼이 후보가 될 수 있을까요?

1. auto_increment 속성의 컬럼
2. auto_increment 속성의 컬럼 + log_date
3. log_date + auto_increment 속성의 컬럼

선정의 기준은 Index 로서 가치를 가져야 한다는 것인데, 로그 테이블을 조회하는 가장 전형적인 패턴은 날짜 범위 검색입니다.

point 쿼리가 아닌, log_date를 where 조건으로 한 range 쿼리가 주로 사용될 것이기 때문에.. log_date를 Secondary Index로 만드는 것은 부담이 됩니다. 따라서 log_date가 Primary Key의 첫번째 컬럼인 것이 좋겠습니다.

" log_date를 where 조건으로 한 range 쿼리가 주로 사용될 것이기 때문에.. log_date를 Secondary Index로 만드는 것은 부담이 됩니다."

왜???

Secondary Index (또는 non-clustered index)는 point 쿼리에서는 쓸만하지만, range 쿼리에서는 조심하셔야 합니다.

왜냐하면 인덱스 페이지의 leaf 레벨에서 Row에 대한 포인터로.. Row의 주소를 가지고 있는 것이 아니라 clustered index의 키 값을 가지고 있기 때문에, clustered index 키 값으로 다시 row를 찾는.. 소위 lookup - 이건 MS-SQL에서 통용하는 용어인데 MySQL에서는 정확히 어떤 용어를 사용하는지 모르겠네요. - 이 발생합니다. 

즉, secondary 인덱스를 사용하면.. clustered 인덱스 (=PK)로 한 번 더 검색을 하는 것으로 이해하시면 됩니다. (인덱스 커버링은 제외)

그런데 이 작업이 point 쿼리라서 한 번하고 끝내면 괜찮지만, 만약 range로 하게 되면?? (날짜 범위 검색 같은...)

날짜로 한번 찾고, PK로 최종 row를 한번 찾고.. 이 작업을 찾은 날짜 수 만큼 반복하는 것이고 전형적인 랜덤 액세스의 형태이기 때문에 성능 문제가 생깁니다.

참고로 MySQL 5.6 부터 MRR 이라는 기능이 들어 갔는데, 이 기능이 non-clustered index로 range 검색을 할 때 발생하는 랜덤 I/O를 다소 줄여주긴 합니다.

MRR은.. 찾은 PK 값으로 매번 Row를 찾는 것이 아니라 랜덤 버퍼에 PK를 넣어 놨다가 정렬한 후 시퀀셜하게 Row를 찾아 내는 기능인데, 당연한 얘기지만 Clustered Index를 사용하는 것에 비할 바는 못됩니다.

이상이 PK의 순서를 date + id 로 만들어야 하는 이유입니다.

log_date + log_id 로 Primary Key 만들기

CREATE TABLE daily_log (
    log_date datetime(0) NOT NULL,
    log_id bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    log_content json NOT NULL,
    PRIMARY KEY (log_date, log_id)
) ENGINE=InnoDB;

에러가 발생합니다.

Incorrect table definition; there can be only one auto column and it must be defined as a key

auto_increment 속성의 컬럼인 log_id가 단일 키, 또는 복합키의 첫번째 컬럼이어야 하기 때문입니다.

이 문제를 피하기 위해 아래와 같이 6행의 내용을 추가합니다.

CREATE TABLE daily_log (
    log_date datetime(0) NOT NULL,
    log_id bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    log_content json NOT NULL,
    PRIMARY KEY (log_date, log_id),
    INDEX `nix-daily_log-log_id` (log_id)
) ENGINE=InnoDB;

결론적으로 MySQL에서 사용할 수 있는 방법으로는 이 정도가 최선인 것 같습니다.

하지만, 이런 점이 아쉽기는 합니다.

첫째, Primary Key의 크기가 살짝 큽니다. 5 bytes + 8 bytes = 13 bytes

둘째, 별 쓸모가 없는 Secondary Index가 8 bytes + 13 bytes= 21 bytes 를 차지합니다.

그렇다고 log_id를 Primary Key로 하고, log_date를 Secondary Index로 하자니..

Key의 크기는 작아지겠지만, 앞 서 얘기한 것처럼 날짜로 범위 검색할 때 I/O 부담이 심할 것이기 때문에 대안이 되지 못합니다.

만약 42억건을 절대 넘지 않는다는 확신이 있다면, log_id로 int UNSIGNED 를 사용하는 것으로 타협을 해볼 수 있겠군요.

cyclic sequence 를 만들면 어떨까?

이하의 내용은 재미 삼아 읽어 보시기 바랍니다. (서비스에 적용하는 것은 추천하지 않겠습니다.!!!)

궁금해서 구현해 봤는데, 테스트해본 결과 위의 모델에 비해 로그 적재 성능이 많이 떨어지고, 멀티 thread에서 Hot Spot 현상 때문에 오히려 느려지고 심하면 dead lock까지 발생하더군요.

반면교사 삼아 읽어 보시는 것도 나름의 의미가 있을 것 같습니다.

그럼 이야기를 시작합니다.

log_id는 log_date 가 중복일 때 Row를 식별시켜주는 역할만 하면 됩니다.

바꿔 말하면 테이블 전체에서 unique 할 필요가 없습니다. 그냥 1초 동안만 Unique하면 되죠.

만약 1초 동안 적재되는 Rows가 최대 65,535건을 넘지 않는다면 어떨까요?

아마 대부분의 시스템이 이 조건을 만족할 듯 한데요.

log_id의 크기를 2 bytes 로 줄이고, log_id 의 값이 65,535가 넘을 때마다 다시 1로 초기화하는 방법을 생각해 볼 수 있습니다.

MS-SQL이나 Oracle 처럼 sequence 객체가 있다면 좋겠지만, 없으니 직접 만들어야 합니다.

잠깐!!! ALTER TABLE .. AUTO_INCREMENT .. 구문을 사용해 auto_increment 값을 초기화 할 수 있습니다.

언뜻 auto_increment 속성의 컬럼을 cyclic 하게 만들 수 있을 것 같지만, 실제로는 불가능합니다.

테이블에 실재하는 값보다 작은 값으로 초기화되지 않기 때문입니다.

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

log_id에 들어갈 값을 채번하기 위해 아래와 같이 `sequence_log_id` 테이블을 만듭니다.

컬럼은 `log_id` 하나이고, 최초에 값이 0인 Row를 하나 추가해서 초기화해 둡니다.

짐작할 수 있듯이 로그 테이블에 기록할 때 마다 `sequence_log_id` 테이블에서 `log_id`의 값을 1씩 가산하는 방법을 사용합니다.

CREATE TABLE sequence_log_id (
    log_id smallint UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
 
INSERT sequence_log_id (log_id)
VALUES (0);

InnoDB vs. Memory

sequence 테이블을 Heap 으로 만들면, 즉 Memory Storage Engine 을 적용하면, 성능이 많이 향상됩니다.

서두에 이 방법이 성능 문제 때문에 사용할 수 없다고 했지만, Heap이라면 단순 INSERT의 80% (single thread 기준) 까지는 보여줬습니다.

단, AWS Aurora 를 사용한다면 Memory Storage Engine을 Temporary Table에만 사용할 수 있습니다.

이제 로그 테이블도 아래와 같이 변경합니다.

    - log_id에서 auto_increment 속성을 제거

    - log_id의 데이터 유형을 bigint UNSIGNED 에서 smallint UNSIGNED  로 변경

    - log_id에서 index 제거

CREATE TABLE daily_log_with_sequence (
    log_date datetime(0) NOT NULL,
    log_id smallint UNSIGNED NOT NULL,
    log_content json NOT NULL,
    PRIMARY KEY (log_date, log_id)
) ENGINE=InnoDB;

마지막으로 로그 테이블에 기록하는 Stored Procedure 를 작성합니다.

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `usp_add_daily_log_with_sequence`$$
 
CREATE DEFINER=CURRENT_USER() PROCEDURE `usp_add_daily_log_with_sequence`(
      IN pi_dt5_log_date datetime(0) -- 로그 일자
    , IN pi_jsn_log_content json     -- 로그 내용
)
proc_body: BEGIN
    DECLARE v_ins_log_id smallint UNSIGNED;
 
    DECLARE CONTINUE HANDLER FOR 1264
    BEGIN
        UPDATE sequence_log_id
        SET log_id = 0
        WHERE log_id = 65535;
    
        UPDATE sequence_log_id
        SET log_id = LAST_INSERT_ID(log_id + 1);
    END;
 
    UPDATE sequence_log_id
    SET log_id = LAST_INSERT_ID(log_id + 1);
 
    INSERT daily_log_with_sequence (log_date, log_id, log_content)
    VALUES (pi_dt5_log_date, LAST_INSERT_ID(), pi_jsn_log_content);
 
END proc_body$$
 
DELIMITER ;

성능은.. ㅠ.ㅠ

AWS Aurora (MySQL 5.7) t2.small 에서 테스트한 결과 입니다.

- thread 1개

• 단순 로그 INSERT : 초당 약 7,915건 처리
• cyclic sequence 방식 (InnoDB) : 초당 약 2,240건 처리
• cyclic sequence 방식 (Memory) : 초당 약 6,568건 처리

- thread 2개

• 단순 로그 INSERT : 초당 약 8,897건 처리
• cyclic sequence 방식 (InnoDB) : 초당 약 1,914건 처리
• cyclic sequence 방식 (Memory) : Heap 유형의 유저 테이블 생성이 불가능하여 테스트하지 못함

맺으며

Unique 하지 않은 Clustered Index를 만들 수 있거나, auto_increment 컬럼이 Key 가 아니어도 된다면, 로그 테이블 설계가 조금 더 나아지지 않을까 생각합니다.

아니면 성능 좋은 sequence 객체를 지원해 줬더라면.. 하는 아쉬움도 있습니다. ^^

이 정도가 과연 최선인 것인지 여전히 모르겠습니다만, 그게 모델링의 재미이기도 한 것 같습니다.

이 글에서는 로그 테이블의 Key에 대해 다뤄 봤는데요. 이어지는 글에서는 파티션 관리에 대해 적어볼까 합니다.

매일 daily 파티션을 새로 생성하고, 보관 주기가 넘은 파티션을 archiving 하거나 시스템 부담 없이 삭제하는 방법을 정리해 보겠습니다.