[DB] 통계 테이블에서 데드락 해결하고 성능 개선하기

insert into B테이블
SELECT UNIX_TIMESTAMP(NOW()) -68, project_key ,segment_key, tenant_id ,
sum(active_user), sum(waiting_user), max(concurrent_user)

from A테이블 

where 
uts>= UNIX_TIMESTAMP(now()) -68 
and uts< UNIX_TIMESTAMP(now()) -8 

group by tenant_id, project_key, segment_key

회사에서 기존에 H2 Database를 사용해서 실제 운영환경에 적용했습니다.

그러다가 새로운 서비스로 마이그레이션 하면서 MariaDB 를 활용하게 되었는데, 기존에 없었던 데드락 문제가 발생했습니다.

데드락이 발생한 쿼리를 찾아보자

데드락 발생 시, 다음 쿼리를 통해 데드락에 대한 로그를 찾아볼 수 있습니다.

(가장 최근의 데드락 로그만 담고 있습니다)

show engine innodb status

위의 쿼리를 실행하면 다음 두 쿼리가 동시에 실행되면서 데드락이 발생되었다고 말해줍니다

INSERT INTO A테이블 (~~~) VALUES (~~~)

insert into B테이블
SELECT UNIX_TIMESTAMP(NOW()) -68, project_key ,segment_key, tenant_id ,
sum(active_user), sum(waiting_user), max(concurrent_user)

from A테이블 

where 
uts>= UNIX_TIMESTAMP(now()) -68 
and uts< UNIX_TIMESTAMP(now()) -8 

group by tenant_id, project_key, segment_key

원인을 생각해보면, MairaDB의 기본 격리수준인 REPEATABLE_READ 에서 insert ~ select 쿼리를 사용하게 되면서 Source Table 이 잠금이 일어나서 발생하는 문제였습니다.

그렇다면 잠금 범위를 최소화하고 실행 시간을 최소화하면 데드락이 줄어들지 않을까요?

성능개선 Step1. Bulk Insert 적용으로 실행시간을 줄여보기

데이터독을 열어보니 해당 요청 로그에 대해서 경고를 해주고 있었습니다.

무려 한 번의 요청에 평균 56번의 쿼리가 나가고 있고, 해당 쿼리가 무엇인지를 보여주고 있습니다.

왜 평균 56번이나 되는 쿼리가 나갔을까요?

로직을 살펴봅시다.

@Override
public void putLogData(List<PutNFDataModel> dataModels) {
    final Long now = TimeUtil.getNowTs();
    List<NFStat> nfStats dataModels.stream()
            .map(dataModel -> dataModel.toNFStat(now))
            .toList();
    nfStatsRepository.saveAll(nfStats);
}

실제로 요청에 들어오는 데이터를 보면 한 번에 5~30개의 데이터가 들어오게 되는데, 해당 쿼리를 saveAll을 통해 호출하고 있습니다.

saveAll 기능은 내부에서 save를 반복문으로 호출하는 기능이기 때문에 N개의 데이터 당 N개의 쿼리가 발생하게 됩니다.

심지어 PK가 AutoIncrement 가 아니기 때문에 select 이후에 insert 가 나가서

2N 개의 쿼리가 나가고 있었습니다.

[위 내용이 자세하게 설명되어 있는 우리 승현쌤의 블로그]

https://imksh.com/113

JPA saveAll이 Bulk INSERT 되지 않았던 이유

실습 환경 MySQL 5.7버전 사용 Windows 10 Entity ID 전략은 IDENTITY Java 11 서론 실무에서 MySQL 5.7 버전을 사용하고 있고, JPA Entity의 ID 전략은 IDENTITY를 사용하고 있는데, 이때 JPARepository를 이용한 saveAll과 sa

imksh.com

하나의 batch insert 쿼리만 나갈 수 있도록 jdbcTemplate.batchUpdate를 사용합니다.

    @Override
    public void saveAllBulk(List<NFStat> nfStats) {
        String sql = "INSERT INTO nf_stat ("timestamp, tenant_id, nfid, ...)
                "VALUES (" +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, ?)";

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                NFStat nfStat = nfStats.get(i);
                preparedStatement.setLong(1, nfStat.getTimeStamp());
                preparedStatement.setString(2, nfStat.getTenantId());
                ...
                ...
                preparedStatement.setInt(53, nfStat.getDynamicVarMax());
                preparedStatement.setInt(54, nfStat.getDynamicIdleTps());
            }

            @Override
            public int getBatchSize() {
                return nfStats.size();
            }
        });
    }

batch insert를 하게 되면 네트워크 비용이 감소하게 되고,

기존 h2 환경에서는 모르겠지만.. 최신 RDBMS 옵티마이저는 bulk insert 최적화가 굉장히 잘되어 있습니다.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bulk_insert_buffer_size

MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables

dev.mysql.com

성능개선 Step2. 인덱스 강제로 태우기 (force index)

MySQL이나 MariaDB는 실제 row 에 잠금을 하는 것이 아닌 인덱스를 대상으로 잠금을 하기 때문에, 인덱스가 최소 범위로 걸리는 것은 매우 중요합니다.

적절한 인덱스를 타서 실행속도가 잘 나오고 있는지, 또 잠금의 범위가 크지는 않을지 고민해보게 되었습니다.

insert into B테이블
SELECT UNIX_TIMESTAMP(NOW()) -68, project_key ,segment_key, tenant_id ,
sum(active_user), sum(waiting_user), max(concurrent_user)

from A테이블 

where 
uts>= UNIX_TIMESTAMP(now()) -68 
and uts< UNIX_TIMESTAMP(now()) -8 

group by tenant_id, project_key, segment_key

이 쿼리가 인덱스를 잘 타고 있을까? 에 대한 의심이 들었습니다.

where 절에 너무 많은 함수들이 들어가있고, 옵티마이저의 판단을 흐리게하는 조건문이라고 생각했었습니다.

그리고 explain으로 조회해보니..

실제로 Possible Key 에는 uts 컬럼에 설정한 인덱스를 후보로 올렸지만, 실질적으로는 풀테이블 스캔을 한 것을 볼 수 있습니다.

그래서 다음과 같이 강제로 인덱스를 태워서 쿼리를 실행하도록 force index 실행 힌트를 제공했습니다.

insert into B테이블
SELECT UNIX_TIMESTAMP(NOW()) -68, project_key ,segment_key, tenant_id ,
sum(active_user), sum(waiting_user), max(concurrent_user)

from nf_stat force index (idx_nf_stat_uts)

where 
uts>= UNIX_TIMESTAMP(now()) -68 
and uts< UNIX_TIMESTAMP(now()) -8 

group by tenant_id, project_key, segment_key

성공적으로 range scan을 하게 되고, 실제로 탐색한 row 도 줄었는 모습을 확인할 수 있었습니다.

성능 개선 및 데드락 결과 확인

그러면 결과를 볼까요?!

오! 효과가 꽤나 드라마틱하게 나타났습니다.

스프링 서버의 latency 입니다.

MariaDB의 latency / 요청수를 볼까요?

성능이 눈에 띄게 개선되고, 데드락 빈도도 줄어들었습니다.

세션 격리수준 Read Committed 적용하기

그렇지만 데드락 빈도가 줄었을 뿐 여전히 간혈적으로 문제가 발생했습니다.

고객사당 3초에 한 번씩 요청에 보내고 있었고, SaaS서비스 오픈 이후 고객사들이 늘어날수록 더 많은 데드락이 발생할 수 밖에 없는 구조였습니다.

그래서 결국 격리수준을 낮춰서 해당 문제를 해결하고자 했습니다.

그렇지만 격리 수준 낮추는 것은 데이터베이스 단위로 영향을 받기 때문에 신중해야하고, 입사한지 얼마 되지 않아서 히스토리를 모르기에 해당 세션만 격리 수준을 낮추는 것으로 판단했습니다.

해당 select insert 쿼리가 나가는 세션에 해당 옵션을 주어서 문제를 해결했습니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITEED;

데드락 해결 결과

REF

https://hoing.io/archives/8067

MySQL CTAS/Insert Select 에서 공유락(Shared Lock) 문제 와 발생 원인 이유

hoing.io

저작자표시 비영리 변경금지

Contents

새소식