대규모 쿼리 최적화 1차 시도 (IN 방지..) (24.05.21)

Trace와 Span 상황

효율적 탐색 질의 :

1. Trace와 Span 데이터가 존재. Trace의 PK를 Span이 FK로 가지는 1:N 매핑 관계
2. '다수'의 Trace를 조회하고, 이와 연관된 '다수'의 Span을 매핑하여 가져와야 함.
3. 이를 위해 Trace를 IO를 통해 한번 조회하고, 이것의 Id Set을 통해 IN 연산을 사용하여 Span들을 가져오려고 했음.
4. 하지만 3번의 경우 대용량 데이터라 상당히 많은 시간이 소요될 것 같음. 이를 극복하기 위해 다음 방법이 유효한지?

: '다른 조건'을 통해 Span의 모집단을 먼저 줄이고, 이후 Join이나 In 연산을 사용.

ex)Trace id set (1, 2, 3 .. N) Spans in (traceId)를 바로 사용함 (많은 시간) Spans where time.goe(..) and (일차로 모집단 감소) and traceId in (traceId);

접근 방법

1. 1차 조건을 통한 Span 모집단 감소: 특정 조건(예: 시간 조건)을 사용하여 Span의 초기 모집단을 줄인다. 이로 인해 쿼리에서 처리해야 할 데이터 양이 감소하게 된다.
2. 2차로 Trace ID를 통한 필터링: 1차 조건으로 걸러진 Span 데이터에서 Trace ID를 기준으로 필요한 데이터를 필터링한다.

구체적인 쿼리 예시

다음은 설명한 방법을 SQL 쿼리로 나타낸 예시이다.

Step 1: Trace 데이터를 조회하여 필요한 ID를 얻음

SELECT id
FROM tbl_trace
WHERE some_condition;  -- 필요한 Trace 데이터를 필터링하는 조건

이 쿼리의 결과로 Trace ID 목록을 얻는다.

Step 2: 1차 조건을 사용하여 Span 데이터를 필터링하고, Trace ID를 통해 최종 필터링

SELECT *
FROM tbl_span
WHERE time >= :some_time_threshold  -- 초기 모집단을 줄이는 조건
  AND trace_id IN (
      SELECT id
      FROM tbl_trace
      WHERE some_condition  -- 동일한 조건으로 Trace 데이터를 필터링
  );

이 방법은 다음과 같은 이점을 제공한다:

1. 초기 데이터 양 감소: 1차 조건을 통해 Span 테이블에서 처리할 데이터 양을 줄인다.
2. 효율적인 필터링: IN 연산을 통해 필요한 Trace ID에 해당하는 Span 데이터를 효율적으로 필터링한다.

JPA 및 QueryDSL을 사용한 구현 예시

JPA 및 QueryDSL을 사용하여 위의 로직을 구현하는 방법은 다음과 같다.

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class SpanService {

    @Autowired
    private JPAQueryFactory jpaQueryFactory;

    private static final QSpan qSpan = QSpan.span;
    private static final QTrace qTrace = QTrace.trace;

    public List<SpanDto> getFilteredSpans(long timeThreshold, List<Long> traceIds) {
        return jpaQueryFactory.select(Projections.constructor(SpanDto.class,
                        qSpan.id,
                        qSpan.scopeName,
                        qSpan.scopeVersion,
                        qSpan.traceId,
                        qSpan.spanId,
                        qSpan.parentSpanId,
                        qSpan.flag,
                        qSpan.name,
                        qSpan.kind,
                        qSpan.startTimeUnixNano,
                        qSpan.endTimeUnixNano,
                        qSpan.statusIsError,
                        qSpan.attributes,
                        qSpan.hasEvent
                ))
                .from(qSpan)
                .where(qSpan.startTimeUnixNano.goe(timeThreshold)
                        .and(qSpan.traceId.in(traceIds)))
                .fetch();
    }
}

성능 최적화 팁

1. 인덱스 사용: **startTimeUnixNano**와 **traceId**에 적절한 인덱스를 생성하여 조회 성능을 향상시킨다.
2. 배치 처리: 대량의 데이터를 처리할 경우, 페이징이나 배치 처리를 통해 한번에 너무 많은 데이터를 로드하지 않도록 한다.
3. 쿼리 계획 분석: **EXPLAIN ANALYZE**를 사용하여 쿼리 계획을 분석하고, 쿼리가 어떻게 실행되는지 확인하여 병목 지점을 최적화한다.

시간 범주를 먼저 SORT 하고 IN, 혹은 Join 사용

일반적으로 시간 범주로 먼저 필터링한 후 PK 리스트를 이용해 데이터를 검색하는 접근 방식이 더 나은 성능을 제공할 수 있다. 이는 데이터의 크기가 커질수록 특히 더 유리해질 수 있다. 그 이유는 인덱스를 효과적으로 사용하여 검색 범위를 좁히고, 이후에 필요한 데이터만 처리하기 때문이다.

시간 범주로 먼저 필터링하는 접근 방식

시간 범주로 필터링한 후 IN 조건 사용:

SELECT *
FROM your_table
WHERE timestamp_column BETWEEN startTime AND endTime
  AND id IN (pk1, pk2, pk3, ...)
ORDER BY timestamp_column;

 

시간 범주로 필터링한 후 JOIN 사용:

SELECT your_table.*
FROM your_table
INNER JOIN (
    VALUES (pk1), (pk2), (pk3), ...
) AS pk_list(id) ON your_table.id = pk_list.id
WHERE your_table.timestamp_column BETWEEN startTime AND endTime
ORDER BY your_table.timestamp_column;

 

일반적인 성능 이점

1. 효율적인 인덱스 사용:
   • 시간 범주(timestamp_column)와 관련된 인덱스가 있다면, 데이터베이스는 시간 범주를 먼저 필터링하여 큰 데이터셋을 신속하게 줄일 수 있다.
   • 이후에 PK 리스트(id에 대한 인덱스)를 사용하여 적은 데이터셋에서 필요한 데이터를 빠르게 검색할 수 있다.
2. I/O 감소:
   • 시간 범주로 먼저 필터링하면 검색 범위가 줄어들어, 전체 데이터셋을 스캔하는 대신 필요한 부분만 읽게 된다.
   • 이로 인해 디스크 I/O가 감소하고, 메모리 사용량도 줄어들게 된다.
3. 정렬 비용 절감:
   • 시간 범주로 먼저 필터링하고 정렬하면, 정렬해야 할 데이터의 양이 줄어들어 전체적인 정렬 비용이 절감된다.

인덱스 설정

효과적인 인덱스 설정은 성능을 극대화하는 데 중요하다. 다음과 같이 인덱스를 설정할 수 있다:

1. 단일 인덱스:
   • timestamp_column과 id에 대해 각각 인덱스를 생성한다.

   
   CREATE INDEX idx_your_table_timestamp ON your_table(timestamp_column);
   CREATE INDEX idx_your_table_id ON your_table(id);

2. 복합 인덱스:
   • timestamp_column과 id를 결합한 복합 인덱스를 생성하여 쿼리 성능을 더욱 향상시킬 수 있다.

   
   CREATE INDEX idx_your_table_timestamp_id ON your_table(timestamp_column, id);

결론

일반적인 경우, 시간 범주로 먼저 필터링한 후에 PK 리스트를 이용해 데이터를 검색하는 방식이 더 나은 성능을 제공할 가능성이 높다. 이는 특히 대규모 데이터셋에서 효율적으로 작동하며, 인덱스를 잘 활용하여 디스크 I/O와 정렬 비용을 줄일 수 있기 때문이다. 따라서, 데이터 크기에 관계없이 시간 범주로 먼저 필터링하는 접근 방식을 권장한다.

실제 성능 측정하기

인덱스 지우고 생성하고.. 이렇게 성능측정하기 쉽지 않을듯 하다.

테스트 서버 가져와야 함.

서버를 재시작하지 않고 인덱스 생성 전후의 성능을 비교할 수 있는 몇 가지 방법이 있다. 이 방법들은 데이터베이스 서버의 캐시를 무효화하지 않지만, 캐시의 영향을 최소화하면서 성능을 비교할 수 있다.

방법 1: EXPLAIN (ANALYZE, BUFFERS) 사용

EXPLAIN 명령어에 BUFFERS 옵션을 추가하여 쿼리 실행 시 디스크 I/O와 버퍼 캐시 사용 정보를 포함할 수 있다. 이를 통해 인덱스 생성 전후의 쿼리 성능을 더 자세히 비교할 수 있다.

-- 인덱스 생성 전 성능 측정
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';

-- 인덱스 생성
CREATE INDEX idx_start_end_time ON tbl_span(start_time, end_time);

-- 인덱스 생성 후 성능 측정
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';

방법 2: pg_prewarm 확장 모듈 사용

PostgreSQL의 pg_prewarm 확장 모듈을 사용하여 인덱스 데이터를 미리 캐시할 수 있다. 이를 통해 캐시의 영향을 줄이고 성능을 비교할 수 있다.

sql코드 복사
-- pg_prewarm 확장 모듈 설치
CREATE EXTENSION pg_prewarm;

-- 인덱스 생성 후, 인덱스 데이터를 미리 로드
CREATE INDEX idx_start_end_time ON tbl_span(start_time, end_time);
SELECT pg_prewarm('idx_start_end_time');

-- 성능 측정
EXPLAIN ANALYZE SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';

방법 3: 동일한 쿼리를 여러 번 실행하여 캐시 효과 최소화

쿼리를 여러 번 실행하여 캐시 효과를 최소화한 후, 인덱스 생성 전후의 성능을 비교할 수 있다. 첫 번째 실행은 캐시를 로드하는 역할을 하고, 이후 실행에서는 더 정확한 성능을 측정할 수 있다.

-- 인덱스 생성 전
EXPLAIN ANALYZE SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';
-- 동일 쿼리를 여러 번 실행하여 캐시 효과 최소화
SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';
SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';
SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';

-- 인덱스 생성
CREATE INDEX idx_start_end_time ON tbl_span(start_time, end_time);

-- 인덱스 생성 후
EXPLAIN ANALYZE SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';
-- 동일 쿼리를 여러 번 실행하여 캐시 효과 최소화
SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';
SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';
SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';

방법 4: 실행 계획 비교 도구 사용

실행 계획을 비교하는 도구를 사용하여 인덱스 생성 전후의 실행 계획을 비교할 수 있다. 이를 통해 인덱스의 효과를 더 명확하게 파악할 수 있다.

-- 인덱스 생성 전 실행 계획 저장
EXPLAIN (FORMAT JSON) SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';

-- 인덱스 생성
CREATE INDEX idx_start_end_time ON tbl_span(start_time, end_time);

-- 인덱스 생성 후 실행 계획 저장
EXPLAIN (FORMAT JSON) SELECT * FROM tbl_span WHERE start_time >= '2023-01-01' AND end_time <= '2023-12-31';

저장된 실행 계획을 JSON 형식으로 비교 도구를 사용하여 분석할 수 있다.

이러한 방법을 통해 서버를 재시작하지 않고도 인덱스 생성 전후의 성능을 비교할 수 있다.