빌더를 위한 B2B 서비스 예시를 활용한 데이터베이스 쿼리 최적화

보다 생산적인 서버로 이동하지 않고 데이터베이스에 대한 쿼리 수를 10배 늘리고 시스템 기능을 유지하려면 어떻게 해야 합니까? 데이터베이스 성능 저하를 어떻게 처리했는지, 컴퓨팅 리소스 비용을 늘리지 않고 최대한 많은 사용자에게 서비스를 제공하기 위해 SQL 쿼리를 최적화한 방법을 알려 드리겠습니다.

저는 건설회사의 비즈니스 프로세스를 관리하는 서비스를 만들고 있습니다. 약 3개 회사가 우리와 협력하고 있습니다. 10명이 넘는 사람들이 매일 4~10시간 동안 우리 시스템을 사용하고 있습니다. 계획, 알림, 경고, 검증 등 다양한 문제를 해결합니다. 우리는 PostgreSQL 9.6을 사용합니다. 데이터베이스에는 약 300개의 테이블이 있으며 매일 최대 200억 개의 쿼리(10개의 다른 쿼리)가 수신됩니다. 평균적으로 초당 3~4개의 요청이 있으며, 가장 활동적인 순간에는 초당 10개 이상의 요청이 있습니다. 대부분의 쿼리는 OLAP입니다. 추가, 수정 및 삭제가 훨씬 적으므로 OLTP 로드가 상대적으로 적습니다. 저는 귀하가 우리 프로젝트의 규모를 평가하고 우리 경험이 귀하에게 얼마나 유용한지 이해할 수 있도록 이 모든 수치를 제공했습니다.

그림 1. 서정적

우리가 개발을 시작했을 때, 우리는 데이터베이스에 어떤 종류의 부하가 걸릴지, 서버가 풀링을 중단하면 어떻게 할지에 대해 실제로 생각하지 않았습니다. 데이터베이스를 설계할 때 우리는 일반적인 권장 사항을 따르고 발에 총을 쏘지 않으려고 노력했지만 "패턴을 사용하지 마십시오"와 같은 일반적인 조언을 넘어섰습니다. 엔터티 속성 값 우리는 들어 가지 않았습니다. 우리는 정규화 원칙을 기반으로 설계하여 데이터 중복을 피하고 특정 쿼리 속도를 높이는 데 신경 쓰지 않았습니다. 첫 번째 사용자가 도착하자마자 성능 문제가 발생했습니다. 늘 그렇듯이 우리는 이에 대해 전혀 준비가 되어 있지 않았습니다. 첫 번째 문제는 간단했습니다. 원칙적으로 새 색인을 추가하면 모든 것이 해결되었습니다. 그러나 간단한 패치가 작동하지 않는 때가 왔습니다. 경험이 부족하고 문제의 원인을 이해하는 것이 점점 더 어려워지고 있음을 깨닫고 전문가를 고용하여 서버를 올바르게 설정하고 모니터링을 연결하고 어디서 얻을 수 있는지 알려주었습니다. 통계.

그림 2. 통계

따라서 우리 데이터베이스에서는 하루에 약 10개의 서로 다른 쿼리가 실행됩니다. 이 10만개 중에 평균 실행 시간이 2~3ms로 0.1~0.3만 번 실행되는 몬스터가 있고, 하루에 30번 호출되는 평균 실행 시간이 100초인 쿼리도 있다.

10개의 쿼리를 모두 최적화하는 것은 불가능했기 때문에 데이터베이스의 성능을 올바르게 향상시키기 위해 노력을 어디로 기울여야 할지 파악하기로 결정했습니다. 여러 번의 반복 후에 우리는 요청을 유형으로 나누기 시작했습니다.

TOP 요청

이는 가장 많은 시간(총 시간)이 소요되는 가장 무거운 쿼리입니다. 이는 매우 자주 호출되는 쿼리이거나 실행하는 데 매우 오랜 시간이 걸리는 쿼리입니다(길고 빈번한 쿼리는 속도를 위한 싸움의 첫 번째 반복에서 최적화되었습니다). 결과적으로 서버는 실행에 가장 많은 시간을 소비합니다. 또한 상위 요청을 총 실행 시간별로 분리하고 IO 시간별로 별도로 분리하는 것이 중요합니다. 이러한 쿼리를 최적화하는 방법은 약간 다릅니다.

모든 회사의 일반적인 관행은 TOP 요청을 처리하는 것입니다. 쿼리 하나만 최적화해도 5~10%의 리소스를 확보할 수 있습니다. 그러나 프로젝트가 성숙해짐에 따라 TOP 쿼리를 최적화하는 것이 점점 더 중요해지고 있습니다. 모든 간단한 방법은 이미 해결되었으며 가장 "무거운" 요청에는 리소스의 "단지" 3-5%만 사용됩니다. 전체 TOP 쿼리에 소요되는 시간이 30~40% 미만이라면 해당 쿼리가 빠르게 작동하도록 이미 노력을 기울였을 가능성이 높으며 이제 다음 그룹의 쿼리 최적화로 넘어갈 때입니다.
이 그룹에 얼마나 많은 상위 쿼리를 포함해야 하는지에 대한 질문에 답하는 것이 남아 있습니다. 저는 보통 최소 10개, 최대 20개를 받습니다. TOP 그룹의 첫 번째와 마지막의 시간이 10배 이상 차이가 나지 않도록 노력합니다. 즉, 쿼리 실행 시간이 1위에서 10위로 급격하게 감소하면 TOP-10을 선택하고, 감소가 점진적이라면 그룹 크기를 15 또는 20으로 늘립니다.

중농

이는 마지막 5~10%를 제외하고 TOP 바로 뒤에 오는 모든 요청입니다. 일반적으로 이러한 쿼리를 최적화하면 서버 성능을 크게 향상시킬 수 있습니다. 이러한 요청의 비중은 최대 80%에 달할 수 있습니다. 하지만 점유율이 50%를 넘어섰다고 해도 좀 더 주의 깊게 살펴봐야 할 때다.

꼬리

언급한 대로 이러한 쿼리는 마지막에 제공되며 시간의 5~10%가 소요됩니다. 자동 쿼리 분석 도구를 사용하지 않는 경우에만 잊어버릴 수 있으며 최적화하는 것도 저렴할 수 있습니다.

각 그룹을 어떻게 평가하나요?

PostgreSQL에 대한 평가를 수행하는 데 도움이 되는 SQL 쿼리를 사용합니다(다른 많은 DBMS에도 유사한 쿼리를 작성할 수 있다고 확신합니다).

TOP-MEDIUM-TAIL 그룹의 크기를 추정하는 SQL 쿼리

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

쿼리 결과는 세 개의 열로 구성되며, 각 열에는 이 그룹의 쿼리를 처리하는 데 걸리는 시간의 백분율이 포함됩니다. 요청 안에는 한 그룹의 요청을 다른 그룹의 요청과 구분하는 두 개의 숫자(제 경우에는 20과 800)가 있습니다.

최적화 작업이 시작된 시점과 현재의 요청 비율을 대략적으로 비교하면 다음과 같습니다.

다이어그램은 TOP 요청의 비율이 급격히 감소했지만 "중간 농민"이 증가했음을 보여줍니다.
처음에 TOP 요청에는 노골적인 실수가 포함되었습니다. 시간이 지남에 따라 소아 질환이 사라지고 TOP 요청 비율이 감소했으며 어려운 요청을 가속화하기 위해 점점 더 많은 노력을 기울여야 했습니다.

요청 텍스트를 얻으려면 다음 요청을 사용합니다.

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

TOP 쿼리 속도를 높이는 데 도움이 되는 가장 일반적으로 사용되는 기술 목록은 다음과 같습니다.

• 시스템 재설계(예: 데이터베이스에 대한 정기적인 쿼리 대신 메시지 브로커를 사용하여 알림 논리 재작업)
• 색인 추가 또는 변경
• ORM 쿼리를 순수 SQL로 다시 작성
• 지연 데이터 로딩 로직 재작성
• 데이터 비정규화를 통한 캐싱. 예를 들어 배달 -> 송장 -> 요청 -> 신청이라는 테이블 연결이 있습니다. 즉, 각 전달은 다른 테이블을 통해 애플리케이션과 연결됩니다. 각 요청의 모든 테이블을 연결하지 않기 위해 Delivery 테이블의 요청에 대한 링크를 복제했습니다.
• 참고 서적이 포함된 정적 테이블을 캐싱하고 프로그램 메모리에서 테이블을 거의 변경하지 않습니다.

때때로 변경 사항은 인상적인 재설계에 달했지만 시스템 부하의 5-10%를 제공했으며 정당했습니다. 시간이 지남에 따라 배기 장치는 점점 더 작아졌고 점점 더 심각한 재설계가 필요했습니다.

그런 다음 우리는 두 번째 요청 그룹인 중농 그룹에 관심을 돌렸습니다. 그 안에 쿼리가 더 많아지고 그룹 전체를 분석하려면 시간이 많이 걸릴 것 같았습니다. 그러나 대부분의 쿼리는 최적화하기가 매우 간단한 것으로 나타났으며 많은 문제가 다양한 변형으로 수십 번 반복되었습니다. 다음은 수십 개의 유사한 쿼리에 적용한 몇 가지 일반적인 최적화의 예이며, 최적화된 쿼리의 각 그룹은 데이터베이스를 3~5% 언로드했습니다.

• COUNT와 Full Table Scan으로 레코드 유무를 확인하는 대신 EXISTS를 사용하기 시작했습니다.
• DISTINCT를 제거했습니다(일반적인 방법은 없지만 요청 속도를 10~100배 높여 쉽게 제거할 수 있음).

  예를 들어, 큰 배달 테이블에서 모든 운전자를 선택하는 쿼리 대신(DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  상대적으로 작은 테이블 PERSON에 대해 쿼리를 수행했습니다.

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  상관 하위 쿼리를 사용한 것처럼 보이지만 속도가 10배 이상 향상됩니다.

• 많은 경우 COUNT가 완전히 폐기되었으며
  대략적인 값의 계산으로 대체됨
• 대신

  UPPER(s) LIKE JOHN%’ 
  

  사용

  s ILIKE “John%”
  

각 특정 요청의 속도가 3~1000배 빨라지는 경우도 있었습니다. 인상적인 성능에도 불구하고 처음에는 완료하는 데 10ms가 걸리고, 3번째로 무거운 쿼리 중 하나이며, 전체 데이터베이스 로드 시간의 XNUMX분의 XNUMX%를 차지하는 쿼리를 최적화하는 데 아무런 의미가 없는 것처럼 보였습니다. 그러나 동일한 유형의 쿼리 그룹에 동일한 방법을 적용함으로써 우리는 몇 퍼센트의 이익을 얻었습니다. 수백 개의 쿼리를 모두 수동으로 검토하는 데 시간을 낭비하지 않기 위해 정규식을 사용하여 동일한 유형의 쿼리를 찾는 몇 가지 간단한 스크립트를 작성했습니다. 결과적으로 쿼리 그룹을 자동으로 검색함으로써 우리는 약간의 노력으로도 성능을 더욱 향상시킬 수 있었습니다.

그 결과 우리는 지금까지 30년 동안 동일한 하드웨어를 사용하여 작업해 왔습니다. 일일 평균 부하는 약 70%이며, 피크 때는 10%에 이릅니다. 요청 건수와 사용자 수는 약 XNUMX배 증가했습니다. 그리고 이 모든 것은 동일한 TOP-MEDIUM 요청 그룹을 지속적으로 모니터링하는 덕분입니다. TOP 그룹에 새로운 요청이 나타나면 즉시 분석하여 속도를 높이려고 노력합니다. 쿼리 분석 스크립트를 사용하여 일주일에 한 번 MEDIUM 그룹을 검토합니다. 최적화 방법을 이미 알고 있는 새로운 쿼리를 발견하면 신속하게 변경합니다. 때때로 우리는 여러 쿼리에 동시에 적용할 수 있는 새로운 최적화 방법을 찾습니다.

우리의 예측에 따르면 현재 서버는 사용자 수가 3~5배 더 증가해도 견딜 수 있을 것입니다. 사실, 우리에게는 또 하나의 비결이 있습니다. 권장되는 대로 아직 SELECT 쿼리를 미러로 전송하지 않았습니다. 그러나 우리는 "중포"를 켜기 전에 먼저 "스마트" 최적화의 가능성을 완전히 소진하기를 원하기 때문에 이것을 의식적으로 수행하지 않습니다.
완료된 작업을 비판적으로 살펴보면 수직 확장을 사용하는 것이 좋습니다. 전문가의 시간을 낭비하는 대신 더 강력한 서버를 구입하십시오. 서버 비용은 그리 높지 않을 수 있습니다. 특히 아직 수직적 확장의 한계를 벗어나지 않았기 때문입니다. 그러나 요청 건수만 10배 증가했습니다. 몇 년이 지나면서 시스템의 기능이 향상되어 이제는 더 많은 유형의 요청이 발생하고 있습니다. 캐싱 덕분에 기존 기능이 더 적은 요청으로 더 효율적으로 수행됩니다. 이는 실제 가속 계수를 얻기 위해 안전하게 5를 더 곱할 수 있음을 의미합니다. 따라서 가장 보수적으로 추정하면 가속도가 50배 이상이었다고 할 수 있습니다. 서버를 수직으로 흔들면 비용이 50배 더 ​​많이 듭니다. 특히 한 번 최적화를 수행하면 항상 작동하며, 임대 서버에 대한 비용이 매달 청구된다는 점을 고려하면 더욱 그렇습니다.

출처 : habr.com