postgres shared_buffers

요약

 

PostgreSQL은 shared buffer (shared memory 영역)가 비워지지 않도록 스케줄러를 사용했습니다.

근데 밑에 글은 그 내용보다는 인덱스 튜닝에 더 가까운 것 같음

상황

고객사에서 이용할 api 응답속도가 37초정도의 응답속도를 보이고 있어서 인덱스 튜닝을 통해서 해당 문제를 해결하려 하였습니다.

SELECT
  cc.id,
  cc.channel_name,
  ccd.follower_cnt,
  ccd.contents_cnt,
  ccd.created_at,
  ccd.date AS latest_scrap_date,
  f.saved_url
FROM channel_creator cc
  JOIN LATERAL (
    SELECT *
    FROM channel_creator_daily d
    WHERE d.channel_creator_id = cc.id
    ORDER BY d.date DESC
    LIMIT 1
  ) ccd ON TRUE
  JOIN file f ON f.id = cc.profile_image_id
WHERE cc.created_by_system = true
  AND cc.main_lang = 'ko'
GROUP BY cc.id, cc.channel_name, ccd.follower_cnt,
         ccd.contents_cnt, ccd.created_at, ccd.date, f.saved_url
ORDER BY cc.updated_at DESC
LIMIT 20 OFFSET 0;

 

상단의 쿼리와 같은 형태의 크레에이터 조회 (실제 쿼리를 간략화 함) 쿼리에

아래와 같은 인덱스를 추가하였습니다.

CREATE INDEX idx_channel_creator_system_lang
  ON channel_creator (created_by_system, main_lang, updated_at DESC);

 해당 api로 응답해야 하는 크리에이터의 조건에는 중복도가 낮다고 할만한 컬럼이 딱히 없어서 정렬의 최적화가 목적이었습니다.

 

해당 인덱스 추가 후 응답속도가 8~9초 정도로 개선 되었지만, 여전히 너무 느리다고 생각하여 인덱스 튜닝이 아니라 쿼리 자체를 고치자고 생각하여 다음 날 추가적인 개선을 해보기 위해서 다시 쿼리를 분석하려 하였습니다.

 

문제

다음 날 동일 api를 호출 해보니 처음 요청이 다시 37초 정도의 응답으로 늘어났고, 2번째 요청부터 9초정도의 응답을 보이는 것을 확인하였습니다.  

이를 통해서 인덱스를 통한 개선이 진행된게 아니라, db의 캐시 관련 설정이 적용되어 두번째부터는 응답이 빠른거겠구나 하는 추측을 할 수 있었습니다.

 

1. 응답 속도 자체의 개선

EXPLAIN (ANALYZE, BUFFERS)

1) loop 관련
->  Limit  (cost=0.42..1918.31 rows=1 width=64) (actual time=0.339..0.339 rows=1 loops=26545)
      ->  Index Scan Backward using channel_creator_daily_pkey on channel_creator_daily d  
            Index Cond: (channel_creator_id = cc.id)
            
            
2) shared buffer 관련
Buffers: shared hit=2318906 read=10

- 1 부분을 통해서 해당 쿼리가 왜 느린지
- 2 부분을 통해서 shared buffer를 hit할 때 응답속도가 빨라지는 점 

 

실행 계획을 확인해 본 결과 위와 같은 점들을 확인할 수 있었습니다.

 

• loops=26545 → channel_creator 26,545건마다 daily 테이블 인덱스 역순 스캔 1번씩 실행
• 즉 channel_creator_daily 최신 1건을 뽑으려고, creator 개수만큼 반복 (N+1 패턴)

gpt를 통해서 해당 쿼리를 postgres의 distinct on 문법을 이용해서 개선할 수 있었습니다.

 

PostgreSQL의 DISTINCT ON

• Postgres는 확장 기능으로 DISTINCT ON (col1, col2 …) 제공
• ORDER BY와 함께 쓰면 그룹별로 첫 번째 행만 뽑을 수 있음

SELECT DISTINCT ON (d.channel_creator_id) ...
ORDER BY d.channel_creator_id, d.date DESC

 

JOIN LATERAL 부분을 distinct on 절로 수정 후 실행 결과, api 응답을 0.3~ 0.5초 이하의 응답속도로 개선할 수 있었습니다.

 

2. 캐시가 비워지는 문제

고객사에서 사용하려는 api가 캐시가 없을 경우 응답속도가 일정하지 않다면 문제가 될 수 있을거라 판단하여 고민하다가,

주기적으로 스케줄러가 api를 찌르게 하여 해당 문제를 해결하였습니다.