PostgreSQL 쿼리의 대량 최적화. 키릴 보로비코프(텐서)

이 보고서는 다음을 허용하는 몇 가지 접근 방식을 제시합니다. 하루에 수백만 개의 SQL 쿼리가 있을 때 SQL 쿼리의 성능을 모니터링합니다., 모니터링되는 PostgreSQL 서버가 수백 대 있습니다.

이러한 대량의 정보를 효율적으로 처리할 수 있는 기술 솔루션은 무엇이며, 이것이 일반 개발자의 삶을 어떻게 더 쉽게 만들어 줄까요?

누가 관심이 있나요? 특정 문제 분석 및 다양한 최적화 기법 SQL 쿼리 및 PostgreSQL의 일반적인 DBA 문제 해결 - 다음 작업도 가능합니다. 일련의 기사를 읽으십시오 이 주제에.


내 이름은 Kirill Borovikov입니다. 텐서 회사. 특히 저는 회사에서 데이터베이스 작업을 전문으로 하고 있습니다.

오늘은 단일 쿼리의 성능을 "분리"할 필요 없이 문제를 한꺼번에 해결하는 경우 쿼리를 최적화하는 방법을 알려 드리겠습니다. 수백만 건의 요청이 있고 일부를 찾아야 하는 경우 솔루션에 대한 접근 방식 이 큰 문제.

일반적으로 백만 명의 고객을 위한 Tensor는 VLSI는 우리의 애플리케이션입니다: 기업 소셜 네트워크, 비디오 커뮤니케이션용 솔루션, 내부 및 외부 문서 흐름용 솔루션, 회계 및 창고용 회계 시스템... 즉, 통합 비즈니스 관리를 위한 "메가 컴바인"으로서 100개 이상의 다양한 솔루션이 있습니다. 내부 프로젝트.

모든 기능이 정상적으로 작동하고 개발되도록 보장하기 위해 우리는 전국에 10개의 개발 센터를 두고 있으며 더 많은 개발 센터가 있습니다. 개발자 1000명.

우리는 2008년부터 PostgreSQL을 사용해 왔으며 클라이언트 데이터, 통계, 분석, 외부 정보 시스템의 데이터 등 우리가 처리하는 많은 양의 데이터를 축적해 왔습니다. 400TB 이상. 프로덕션에만 약 250개의 서버가 있고, 우리가 모니터링하는 데이터베이스 서버는 총 1000개 정도입니다.

SQL은 선언적 언어입니다. 무언가가 작동해야 하는 "방법"이 아니라 달성하려는 "무엇"을 설명합니다. DBMS는 JOIN을 만드는 방법, 즉 테이블을 연결하는 방법, 적용할 조건, 인덱스를 통과할 항목, 통과하지 않을 항목 등을 더 잘 알고 있습니다.

일부 DBMS는 "아니요, 이 두 테이블을 이러한 대기열에 연결하세요"라는 힌트를 허용하지만 PostgreSQL은 이를 수행할 수 없습니다. 이것이 선도적인 개발자들의 의식적인 입장입니다. “개발자들에게 어떤 종류의 힌트를 사용하도록 허용하기보다는 쿼리 최적화 프로그램을 끝내고 싶습니다.”

그러나 PostgreSQL은 "외부"가 스스로를 제어하는 ​​것을 허용하지 않음에도 불구하고 완벽하게 허용합니다. 그 사람 안에서 무슨 일이 일어나는지 봐쿼리를 실행할 때와 문제가 있는 위치.

일반적으로 [DBA에 대한] 개발자는 일반적으로 어떤 고전적인 문제에 직면합니까? “여기서 우리는 요청을 이행했고 우리에겐 모든 게 느려, 모든 것이 중단되고, 무슨 일이 일어나고 있습니다... 어떤 종류의 문제가 발생했습니다!”

이유는 거의 항상 동일합니다.

• 비효율적인 쿼리 알고리즘
  개발자: "이제 나는 JOIN을 통해 그에게 SQL로 10개의 테이블을 제공할 것입니다..." - 그리고 그의 조건이 기적적으로 효과적으로 "풀려"지고 모든 것을 빨리 얻을 것이라고 기대합니다. 그러나 기적은 일어나지 않으며 이러한 가변성(하나의 FROM에 테이블 10개)이 있는 시스템에서는 항상 일종의 오류가 발생합니다. [기사]
• 오래된 통계
  이 점은 특히 PostgreSQL과 매우 관련이 있습니다. 서버에 대규모 데이터세트를 "부어" 요청하면 태블릿이 "섹스캐니트"됩니다. 어제는 10개의 레코드가 있었고 오늘은 10만 개의 레코드가 있지만 PostgreSQL은 아직 이를 인식하지 못하므로 이에 대해 알려줘야 합니다. [기사]
• 리소스에 "플러그"
  디스크, 메모리 또는 프로세서 성능이 충분하지 않은 약한 서버에 크고 로드가 많은 데이터베이스를 설치했습니다. 그리고 그게 전부입니다. 어딘가에는 더 이상 뛰어넘을 수 없는 성능 한계가 있습니다.
• 차단
  이것은 어려운 점이지만 다양한 수정 쿼리(INSERT, UPDATE, DELETE)와 가장 관련이 있습니다. 이것은 별도의 큰 주제입니다.

계획 세우기

...그리고 그 밖의 모든 것에 대해서는 우리가 계획이 필요해! 서버 내부에서 무슨 일이 일어나고 있는지 확인해야 합니다.

PostgreSQL의 쿼리 실행 계획은 쿼리 실행 알고리즘을 텍스트로 표현한 트리입니다. 기획자의 분석 결과 가장 효과적인 것으로 판명된 것은 바로 이 알고리즘이다.

각 트리 노드는 테이블이나 인덱스에서 데이터 검색, 비트맵 작성, 두 테이블 결합, 선택 항목 결합, 교차 또는 제외 등의 작업입니다. 쿼리 실행에는 이 트리의 노드를 살펴보는 작업이 포함됩니다.

쿼리 계획을 얻으려면 가장 쉬운 방법은 명령문을 실행하는 것입니다. EXPLAIN. 모든 실제 속성을 얻으려면, 즉 실제로 기본에서 쿼리를 실행하려면 - EXPLAIN (ANALYZE, BUFFERS) SELECT ....

나쁜 부분: 실행하면 "지금 여기"에서 발생하므로 로컬 디버깅에만 적합합니다. 데이터 변경의 흐름이 활발하고 로드가 많은 서버를 사용하면 다음과 같은 메시지가 표시됩니다. “오! 여기서는 실행 속도가 느립니다.캠핑 요구." XNUMX분 전, XNUMX시간 전 - 실행하고 로그에서 이 요청을 받아 서버로 다시 가져오는 동안 전체 데이터세트와 통계가 변경되었습니다. 디버깅을 위해 실행하면 빠르게 실행됩니다! 그리고 넌 왜, 왜인지 이해할 수 없어 있었다 느리게.

서버에서 요청이 실행되는 순간 정확히 무슨 일이 일어났는지 이해하기 위해 똑똑한 사람들은 다음과 같이 썼습니다. auto_explain 모듈. 거의 모든 가장 일반적인 PostgreSQL 배포판에 존재하며 구성 파일에서 간단히 활성화할 수 있습니다.

일부 요청이 지정한 제한보다 오래 실행되고 있음을 인식하면 이 요청 계획의 "스냅샷"을 생성하고 로그에 함께 기록합니다..

이제 모든 것이 괜찮은 것 같습니다. 로그로 가서 거기를 확인합니다... [텍스트 족보]. 하지만 실행하는 데 11ms가 걸렸기 때문에 훌륭한 계획이라는 사실 외에는 아무 말도 할 수 없습니다.

모든 것이 괜찮은 것 같지만 실제로 무슨 일이 일어났는지는 확실하지 않습니다. 일반적인 시간 외에는 실제로 아무것도 볼 수 없습니다. 왜냐하면 이러한 일반 텍스트의 "양"을 보는 것은 일반적으로 시각적이지 않기 때문입니다.

하지만 명확하지 않더라도, 불편하더라도 더 근본적인 문제가 있습니다.

• 노드는 다음을 나타냅니다. 전체 하위 트리의 리소스 합계 그 밑에. 즉, 특정 인덱스 스캔 아래에 중첩된 조건이 있는 경우 이 특정 인덱스 스캔에 소요된 시간을 알 수 없습니다. 내부에 "자식"과 조건 변수, CTE가 있는지 동적으로 확인하고 "우리 마음 속에" 있는 이 모든 것을 빼야 합니다.
• 두 번째 포인트: 노드에 표시된 시간은 단일 노드 실행 시간. 예를 들어 테이블 레코드를 여러 번 반복한 결과 이 ​​노드가 실행된 경우 계획에서 루프(이 노드의 주기) 수가 증가합니다. 그러나 원자 실행 시간 자체는 계획 측면에서 동일하게 유지됩니다. 즉, 이 노드가 총 얼마나 오래 수행되었는지 이해하려면 한 항목을 다른 항목으로 곱해야 합니다. 즉, "머리 속"입니다.

그러한 상황에서는 “가장 약한 고리가 누구인가?”를 이해하십시오. 거의 불가능한. 따라서 개발자 자신도 "매뉴얼"에 다음과 같은 내용을 작성합니다. “계획을 이해하는 것은 배우고 경험해야 하는 예술입니다...”.

하지만 우리 개발자는 1000명인데, 이 경험을 그들 각자에게 전달할 수는 없습니다. 나, 너, 그 사람은 알고 있지만 저기 있는 누군가는 더 이상 모른다. 어쩌면 그는 배울 수도 있고 아닐 수도 있지만 지금은 일해야 합니다. 그리고 그는 어디서 이 경험을 얻을 수 있을까요?

계획 시각화

그러므로 우리는 이러한 문제를 해결하기 위해서는 다음과 같은 조치가 필요하다는 것을 깨달았습니다. 계획의 좋은 시각화. [기사]

우리는 먼저 "시장을 통과"했습니다. 인터넷을 통해 무엇이 존재하는지 살펴보겠습니다.

그러나 어느 정도 개발 중인 상대적으로 "실시간" 솔루션은 거의 없다는 것이 밝혀졌습니다. 말 그대로 단 하나뿐입니다. explain.depesz.com 휴베르트 루바체프스키(Hubert Lubaczewski). 계획의 텍스트 표현인 "피드" 필드를 입력하면 구문 분석된 데이터가 포함된 테이블이 표시됩니다.

• 노드 자체 처리 시간
• 전체 하위 트리에 대한 총 시간
• 통계적으로 예상되는 검색된 레코드 수
• 노드 본체 자체

이 서비스에는 링크 아카이브를 공유하는 기능도 있습니다. 당신은 거기에 당신의 계획을 집어넣고 이렇게 말했습니다: "안녕, Vasya, 여기 링크가 있습니다. 거기에 뭔가 문제가 있습니다."

그러나 작은 문제도 있습니다.

첫째, 엄청난 양의 "복사-붙여넣기"입니다. 통나무 조각을 가져다가 거기에 붙이고, 계속해서 붙이세요.

둘째, 읽은 데이터의 양을 분석하지 않음 — 출력과 동일한 버퍼 EXPLAIN (ANALYZE, BUFFERS), 여기에는 표시되지 않습니다. 그는 단순히 그것들을 분해하고 이해하고 작업하는 방법을 모릅니다. 많은 양의 데이터를 읽고 디스크와 메모리 캐시를 잘못 할당했을 수 있다는 것을 알게 되면 이 정보는 매우 중요합니다.

세 번째 부정적인 점은 이 프로젝트의 개발이 매우 약하다는 것입니다. 커밋은 매우 작습니다. XNUMX개월에 한 번씩 코드가 Perl에 있으면 좋습니다.

하지만 이것은 모두 "가사"입니다. 우리는 어떻게 든 이것으로 살아갈 수 있지만이 서비스에서 우리를 크게 멀어지게 만든 한 가지가 있습니다. 이는 CTE(Common Table Expression) 및 InitPlan/SubPlan과 같은 다양한 동적 노드 분석의 오류입니다.

이 그림을 믿는다면 각 개별 노드의 총 실행 시간은 전체 요청의 총 실행 시간보다 길다는 것을 알 수 있습니다. 간단 해 - 이 CTE의 생성 시간은 CTE 스캔 노드에서 차감되지 않았습니다.. 따라서 CTE 스캔 자체에 소요되는 시간에 대한 정답을 더 이상 알 수 없습니다.

그런 다음 우리는 이제 우리 자신의 글을 쓸 시간이라는 것을 깨달았습니다. 만세! 모든 개발자는 "이제 직접 작성해 보겠습니다. 매우 쉬울 것입니다!"라고 말합니다.

우리는 웹 서비스에 일반적인 스택을 사용했습니다. Node.js + Express 기반 코어, 아름다운 다이어그램을 위해 Bootstrap 및 D3.js를 사용했습니다. 그리고 우리의 기대는 완전히 충족되었습니다. 우리는 2주 만에 첫 번째 프로토타입을 받았습니다.

• 사용자 정의 계획 파서
  즉, 이제 PostgreSQL에서 생성된 계획에서 모든 계획을 구문 분석할 수 있습니다.
• 동적 노드의 올바른 분석 - CTE 스캔, InitPlan, SubPlan
• 버퍼 분포 분석 - 데이터 페이지를 메모리에서 읽는 곳, 로컬 캐시에서 읽는 곳, 디스크에서 읽는 곳
• 명확성을 얻었습니다
  따라서 로그에서 이 모든 것을 "파헤치는" 것이 아니라 그림에서 "가장 약한 링크"를 바로 볼 수 있습니다.

구문 강조가 포함된 이와 같은 결과가 나왔습니다. 그러나 일반적으로 개발자는 더 이상 계획을 완전히 표현하지 않고 더 짧은 계획으로 작업합니다. 결국 우리는 이미 모든 숫자를 구문 분석하여 왼쪽과 오른쪽으로 던졌고 중간에는 어떤 기호에 따라 CTE 스캔, CTE 생성 또는 Seq 스캔과 같은 노드의 종류인 첫 번째 줄만 남겼습니다.

이것이 우리가 부르는 단축 표현입니다. 계획 템플릿.

또 무엇이 편리할까요? 총 시간 중 어느 부분이 어느 노드에 할당되었는지 확인하고 옆에 "붙여" 두는 것이 편리할 것입니다. 파이 차트.

노드를 가리키면 Seq Scan이 전체 시간의 3/4 미만이 걸렸고 나머지 XNUMX/XNUMX는 CTE Scan이 차지한 것으로 나타났습니다. 공포! 이는 쿼리에 CTE 스캔을 적극적으로 사용하는 경우 CTE 스캔의 "발사 속도"에 대한 작은 참고 사항입니다. 그다지 빠르지는 않습니다. 일반 테이블 스캐닝보다 열등합니다. [기사] [기사]

그러나 일반적으로 그러한 다이어그램은 우리가 즉시 세그먼트를 가리키고 예를 들어 일부 Seq 스캔이 "먹은" 시간의 절반 이상을 볼 때 더 흥미롭고 더 복잡합니다. 게다가 내부에는 일종의 필터가 있었고 그에 따라 많은 레코드가 삭제되었습니다... 이 사진을 개발자에게 직접 던지고 다음과 같이 말할 수 있습니다. “Vasya, 여기서는 모든 것이 좋지 않습니다! 알아내세요. 보세요. 뭔가 잘못되었습니다!”

당연히 약간의 "갈퀴"가 관련되었습니다.

우리가 가장 먼저 접한 것은 반올림 문제였습니다. 계획의 각 개별 노드의 시간은 1μs의 정확도로 표시됩니다. 예를 들어 노드 주기 수가 1000을 초과하면 PostgreSQL을 실행한 후 "정확도 내"로 나눈 다음 다시 계산할 때 총 시간이 "0.95ms에서 1.05ms 사이"가 됩니다. 카운트가 마이크로초로 가면 괜찮지만 이미 [밀리]초가 되면 "누가 누구에게서 얼마나 소비했는지" 계획의 노드에 리소스를 "풀" 때 이 정보를 고려해야 합니다.

두 번째로 더 복잡한 점은 동적 노드 간에 리소스(해당 버퍼)를 배포하는 것입니다. 프로토타입의 첫 2주와 추가로 4주가 소요되었습니다.

이런 종류의 문제가 발생하는 것은 매우 쉽습니다. 우리는 CTE를 수행하고 아마도 그 안에 있는 내용을 읽을 것입니다. 실제로 PostgreSQL은 "스마트"하며 거기에서 직접 아무것도 읽지 않습니다. 그런 다음 여기에서 첫 번째 레코드를 가져오고 동일한 CTE에서 XNUMX번째 레코드를 가져옵니다.

우리는 계획을 보고 이해합니다. 이상합니다. Seq 스캔에서는 3개의 버퍼(데이터 페이지)가 "소비"되고, CTE 스캔에서는 1개가 더 있고, 두 번째 CTE 스캔에서는 2개가 더 있습니다. 즉, 단순히 모든 것을 합산하면 6이 나오지만 태블릿에서는 3만 읽습니다! CTE Scan은 어디에서든 아무것도 읽지 않고 프로세스 메모리와 직접 작동합니다. 즉, 여기에는 분명히 잘못된 것이 있습니다!

실제로 여기에는 Seq Scan에서 요청한 3페이지의 데이터가 모두 포함되어 있으며, 처음 1페이지는 첫 번째 CTE 스캔을 요청하고 그 다음 두 번째, 1페이지가 더 읽어졌습니다. 2페이지가 아닌 2페이지를 읽은 데이터입니다.

그리고 이 그림을 통해 우리는 계획 실행이 더 이상 트리가 아니라 일종의 비순환 그래프라는 사실을 이해하게 되었습니다. 그리고 우리는 "애초에 무엇이 어디서 왔는지"를 이해할 수 있도록 이와 같은 다이어그램을 얻었습니다. 즉, 여기서는 pg_class에서 CTE를 생성하고 이를 두 번 요청했으며, 두 번째 요청에서는 거의 모든 시간을 브랜치에서 소비했습니다. 2번째 항목을 읽는 것이 태블릿에서 첫 번째 항목을 읽는 것보다 훨씬 더 많은 비용이 든다는 것은 분명합니다.

우리는 잠시 숨을 내쉬었습니다. 그들은 이렇게 말했습니다. “자, 네오, 당신은 쿵푸를 알고 있군요! 이제 우리의 경험이 바로 화면에 나타납니다. 이제 사용하시면 됩니다." [기사]

로그 통합

1000명의 개발자들은 안도의 한숨을 쉬었습니다. 그러나 우리는 수백 개의 "전투" 서버만 보유하고 있으며 개발자 측의 이 모든 "복사하여 붙여넣기"가 전혀 편리하지 않다는 것을 이해했습니다. 우리는 그것을 스스로 수집해야 한다는 것을 깨달았습니다.

일반적으로 통계를 수집할 수 있는 표준 모듈이 있지만 구성에서도 활성화해야 합니다. pg_stat_statements 모듈. 그러나 그는 우리에게 적합하지 않았습니다.

첫째, 동일한 데이터베이스 내에서 다른 체계를 사용하여 동일한 쿼리에 할당합니다. 다른 QueryId. 즉, 먼저 해보면 SET search_path = '01'; SELECT * FROM user LIMIT 1;그리고 나서 SET search_path = '02'; 동일한 요청이 있는 경우 이 모듈의 통계는 서로 다른 기록을 갖게 되며 구성표를 고려하지 않고는 이 요청 프로필의 맥락에서 구체적으로 일반 통계를 수집할 수 없습니다.

우리가 그것을 사용하지 못하게 하는 두 번째 점은 계획이 부족하다. 즉, 계획은 없고 요청 자체만 있을 뿐입니다. 우리는 무엇이 느려지고 있는지 알지만 그 이유를 이해하지 못합니다. 그리고 여기서 우리는 빠르게 변화하는 데이터 세트의 문제로 돌아갑니다.

그리고 마지막 순간 - "사실"의 부족. 즉, 쿼리 실행의 특정 인스턴스를 처리할 수 없습니다. 아무 것도 없고 집계된 통계만 있습니다. 이것으로 작업하는 것은 가능하지만 매우 어렵습니다.

그래서 우리는 복사 붙여넣기와 싸우기로 결심하고 글쓰기를 시작했습니다. 수집가.

수집기는 SSH를 통해 연결하고 인증서를 사용하여 데이터베이스와 서버에 대한 보안 연결을 설정합니다. tail -F 로그 파일에 "고착"됩니다. 그래서 이번 세션에서는 전체 로그 파일의 완전한 "미러"를 얻습니다., 서버가 생성합니다. 서버 자체의 로드는 최소화됩니다. 서버에서 아무 것도 구문 분석하지 않고 트래픽만 미러링하기 때문입니다.

우리는 이미 Node.js에서 인터페이스 작성을 시작했기 때문에 계속해서 그 안에 콜렉터를 작성했습니다. 그리고 이 기술은 그 자체로 정당화되었습니다. 왜냐하면 JavaScript를 사용하여 약하게 형식화된 텍스트 데이터, 즉 로그를 작업하는 것이 매우 편리하기 때문입니다. 그리고 백엔드 플랫폼인 Node.js 인프라 자체를 사용하면 네트워크 연결은 물론 모든 데이터 스트림에 대해 쉽고 편리하게 작업할 수 있습니다.

따라서 우리는 두 가지 연결을 "확장"합니다. 첫 번째는 로그 자체를 "듣고"우리 자신에게 가져오고 두 번째는 주기적으로 기지에 요청합니다. "그런데 로그에는 oid 123이라는 표시가 차단되어 있다고 나오네요." 하지만 이는 개발자에게는 아무 의미가 없고, 데이터베이스에 "그나저나 OID = 123이 뭐죠?"라고 물어보는 게 좋을 것 같습니다. 그래서 우리는 우리 자신에 대해 아직 모르는 것이 무엇인지 주기적으로 기지에 묻습니다.

“당신이 고려하지 않은 것이 하나 있습니다. 코끼리 같은 벌이 있다는 것입니다!..” 우리는 10대의 서버를 모니터링하고 싶었을 때 이 시스템을 개발하기 시작했습니다. 처리하기 어려운 몇 가지 문제가 발생한 부분을 이해하는 데 가장 중요합니다. 그러나 XNUMX분기 동안 우리는 모니터링을 위해 XNUMX건을 받았습니다. 시스템이 작동했기 때문에 모두가 원했고 모두가 편안했습니다.

이 모든 것을 합산해야 하며 데이터 흐름은 크고 활동적입니다. 실제로 우리가 모니터링하는 것, 처리할 수 있는 것이 바로 우리가 사용하는 것입니다. 또한 PostgreSQL을 데이터 저장소로 사용합니다. 그리고 운영자보다 데이터를 "쏟는" 속도가 더 빠른 것은 없습니다. COPY 아직 아님.

하지만 단순히 데이터를 '쏟는' 것은 실제로 우리의 기술이 아닙니다. 왜냐하면 50개의 서버에서 초당 약 100개의 요청이 있는 경우 하루에 150-XNUMXGB의 로그가 생성되기 때문입니다. 따라서 우리는 베이스를 조심스럽게 "절단"해야 했습니다.

첫째로, 우리는 그랬습니다 일별로 나누기, 대체로 아무도 요일 간의 상관 관계에 관심이 없기 때문입니다. 오늘 밤에 애플리케이션의 새 버전과 이미 새로운 통계를 출시했다면 어제와 어떤 차이가 있습니까?

둘째, 우리는 배웠다(강요당했다) 다음을 사용하여 작성하는 것이 매우 매우 빠릅니다. COPY. 즉, 뿐만 아니라 COPY그 사람보다 빠르니까. INSERT, 그리고 훨씬 더 빠릅니다.

세 번째 요점 - 해야 했어요 트리거와 외래 키를 각각 포기합니다.. 즉, 참조 무결성이 전혀 없습니다. 한 쌍의 FK가 있는 테이블이 있고 데이터베이스 구조에서 "여기에 FK에서 참조하는 로그 레코드가 있습니다. 예를 들어 레코드 그룹에 대해"라고 말하면 이를 삽입하면 PostgreSQL이 남은 게 없고 어떻게 받아들이고 솔직하게 해야 하는지 SELECT 1 FROM master_fk1_table WHERE ... 삽입하려는 식별자로 이 레코드가 존재하는지, 삽입 시 이 외래 키를 "분리"하지 않는지 확인하기 위한 것입니다.

대상 테이블과 해당 인덱스에 대한 하나의 레코드 대신 참조하는 모든 테이블에서 읽는 추가 이점을 얻습니다. 하지만 우리는 이것이 전혀 필요하지 않습니다. 우리의 임무는 최소한의 부하로 최대한 빨리 녹음하는 것입니다. 그래서 FK - 다운!

다음 요점은 집계와 해싱입니다. 처음에는 이를 데이터베이스에 구현했습니다. 결국 레코드가 도착하면 일종의 태블릿에서 즉시 수행하는 것이 편리합니다. 트리거 오른쪽에 "+ XNUMX"이 표시됩니다.. 편리하지만 나쁜 점은 하나입니다. 하나의 레코드를 삽입했지만 다른 테이블에서 다른 항목을 읽고 써야 한다는 것입니다. 게다가 읽고 쓰는 것뿐만 아니라, 매번 하는 일도 합니다.

이제 특정 호스트를 통과한 요청 수를 간단히 계산하는 테이블이 있다고 상상해 보세요. +1, +1, +1, ..., +1. 원칙적으로는 이것이 필요하지 않습니다. 모두 가능합니다 수집기의 메모리에 있는 합계 한 번에 데이터베이스로 전송 +10.

예, 일부 문제가 발생하면 논리적 무결성이 "무너질" 수 있지만 이는 거의 비현실적인 경우입니다. 일반 서버가 있고 컨트롤러에 배터리가 있고 트랜잭션 로그가 있고 서버에 로그가 있기 때문입니다. 파일 시스템... 일반적으로 그럴 가치가 없습니다. 트리거/FK 실행으로 인한 생산성 손실은 발생하는 비용만큼 가치가 없습니다.

해싱도 마찬가지입니다. 특정 요청이 귀하에게 도착하면 데이터베이스에서 특정 식별자를 계산하여 데이터베이스에 기록한 다음 모든 사람에게 알립니다. 녹음할 때 같은 것을 녹음하고 싶어하는 두 번째 사람이 당신에게 올 때까지는 모든 것이 괜찮습니다. 그리고 당신은 차단되고 이것은 이미 나쁩니다. 따라서 일부 ID 생성을 클라이언트(데이터베이스 관련)로 전송할 수 있다면 이렇게 하는 것이 더 좋습니다.

텍스트(요청, 계획, 템플릿 등)에서 MD5를 사용하는 것이 완벽했습니다. 수집기 측에서 이를 계산하고 기성 ID를 데이터베이스에 "붓습니다". MD5의 길이와 일일 파티셔닝을 통해 충돌 가능성에 대해 걱정할 필요가 없습니다.

하지만 이 모든 것을 빠르게 녹음하려면 녹음 절차 자체를 수정해야 했습니다.

보통 어떻게 데이터를 쓰시나요? 우리는 일종의 데이터 세트를 가지고 있으며 이를 여러 테이블로 분할한 다음 이를 복사합니다. 먼저 첫 번째, 두 번째, 세 번째로... 하나의 데이터 스트림을 세 단계로 작성하는 것 같아서 불편합니다. 순차적으로. 불쾌한. 더 빨리 할 수 ​​​​있습니까? 할 수 있다!

이렇게 하려면 이러한 흐름을 서로 병렬로 분해하는 것만으로도 충분합니다. 오류, 요청, 템플릿, 차단 등이 별도의 스레드로 이동하며 모두 병렬로 작성되는 것으로 나타났습니다. 이걸로 충분해 각 개별 대상 테이블에 대해 COPY 채널을 지속적으로 열어 두십시오..

즉, 콜렉터에서 항상 흐름이 있어, 여기에 필요한 데이터를 쓸 수 있습니다. 하지만 데이터베이스가 이 데이터를 보고 누군가가 이 데이터가 기록될 때까지 기다리지 않도록 하려면 COPY는 특정 간격으로 중단되어야 합니다.. 우리에게 가장 효과적인 기간은 약 100ms였습니다. 이를 닫았다가 즉시 동일한 테이블에 다시 엽니다. 그리고 일부 피크 동안 하나의 흐름이 충분하지 않으면 특정 한도까지 풀링합니다.

또한 이러한 로드 프로필의 경우 레코드를 일괄적으로 수집하는 집계는 좋지 않다는 사실도 발견했습니다. 고전적인 악은 INSERT ... VALUES 그리고 추가로 1000개의 레코드가 있습니다. 그 시점에 미디어에 최대 쓰기가 발생하고 디스크에 무언가를 쓰려는 다른 모든 사람이 기다리고 있을 것이기 때문입니다.

이러한 이상 현상을 제거하려면 아무 것도 집계하지 마세요. 전혀 버퍼링하지 마십시오. 그리고 디스크에 대한 버퍼링이 발생하는 경우(다행히 Node.js의 Stream API를 통해 확인할 수 있음) 이 연결을 연기하세요. 다시 무료라는 이벤트를 받으면 누적된 대기열에서 이벤트에 씁니다. 바쁜 동안 풀에서 다음 무료 항목을 가져와서 작성하세요.

데이터 기록에 이 접근 방식을 도입하기 전에는 약 4K 쓰기 작업이 있었고 이러한 방식으로 로드가 4배 ​​감소했습니다. 이제 새로운 모니터링 데이터베이스로 인해 최대 6MB/s까지 100배 더 성장했습니다. 그리고 이제 우리는 모든 개발자가 단 3개월 안에 모든 문제를 해결할 수 있기를 바라면서 지난 10개월 동안의 로그를 약 15-XNUMXTB의 볼륨에 저장합니다.

우리는 문제를 이해합니다

그러나 단순히 이 모든 데이터를 수집하는 것은 훌륭하고 유용하며 관련성이 있지만 충분하지는 않습니다. 이해가 필요합니다. 하루에 수백만 개의 서로 다른 계획이 있기 때문입니다.

하지만 수백만 달러는 관리하기 어렵습니다. 먼저 "더 작게" 해야 합니다. 그리고 우선, 이 "더 작은" 것을 어떻게 정리할 것인지 결정해야 합니다.

우리는 세 가지 핵심 사항을 확인했습니다.

• 누구 이 요청을 보냈습니다
  즉, 웹 인터페이스, 백엔드, 결제 시스템 등 어떤 애플리케이션에서 "도착"했는지입니다.
• 어디에서 그런 일이 일어났어
  어떤 특정 서버에서? 하나의 응용 프로그램 아래에 여러 개의 서버가 있는데 갑자기 하나가 "멍청해집니다"("디스크가 썩었습니다", "메모리 누출", 기타 문제로 인해). 그러면 서버를 구체적으로 처리해야 합니다.
• как 문제는 어떤 식으로든 나타났다

"누가" 요청을 보냈는지 이해하기 위해 표준 도구를 사용하여 세션 변수를 설정합니다. SET application_name = '{bl-host}:{bl-method}'; — 요청이 들어오는 비즈니스 로직 호스트의 이름과 요청을 시작한 메서드 또는 애플리케이션의 이름을 보냅니다.

요청의 "소유자"를 전달한 후에는 로그에 출력되어야 합니다. 이를 위해 변수를 구성합니다. log_line_prefix = ' %m [%p:%v] [%d] %r %a'. 관심 있으신 분들은 아마도 설명서를 봐그게 다 무슨 뜻이야? 로그에 다음과 같은 내용이 표시됩니다.

• 시간
• 프로세스 및 트랜잭션 식별자
• 데이터베이스 이름
• 이 요청을 보낸 사람의 IP
• 및 메소드 이름

그러다가 우리는 서로 다른 서버 간의 하나의 요청에 대한 상관 관계를 살펴보는 것이 그다지 흥미롭지 않다는 것을 깨달았습니다. 하나의 애플리케이션이 여기저기서 동일하게 문제를 일으키는 상황은 자주 발생하지 않습니다. 하지만 동일하더라도 이 서버 중 하나를 살펴보세요.

자 여기 컷이 있습니다 "하나의 서버 - 하루" 어떤 분석에도 충분하다는 것이 밝혀졌습니다.

첫 번째 분석 섹션은 동일합니다. "견본" - 모든 수치 지표가 삭제된 계획 제시의 축약된 형태입니다. 두 번째 컷은 적용이나 방법이고, 세 번째 컷은 우리에게 문제를 일으킨 특정 계획 노드입니다.

특정 인스턴스에서 템플릿으로 전환하면 한 번에 두 가지 이점을 얻을 수 있습니다.

• 분석 대상 수의 다중 감소
  더 이상 수천 개의 쿼리나 계획이 아닌 수십 개의 템플릿을 통해 문제를 분석해야 합니다.
• 타임라인
  즉, 특정 섹션 내의 '사실'을 요약하여 낮 동안의 모습을 표시할 수 있습니다. 그리고 여기서는 예를 들어 한 시간에 한 번 발생하는 패턴이 있지만 하루에 한 번 발생해야 하는 경우 무엇이 잘못되었는지, 즉 원인이 누구이며 왜 여기에 있어야 하는지 생각해야 한다는 것을 이해할 수 있습니다. 해서는 안됩니다. 이것은 또 다른 비수치적, 순전히 시각적인 분석 방법입니다.

나머지 방법은 계획에서 추출한 지표(이러한 패턴이 발생한 횟수, 전체 및 평균 시간, 디스크에서 읽은 데이터의 양, 메모리에서 읽은 데이터의 양)를 기반으로 합니다.

예를 들어 호스트에 대한 분석 페이지를 방문했을 때 디스크에서 무언가가 너무 많이 읽기 시작했습니다. 서버의 디스크가 이를 처리할 수 없습니다. 누가 이를 읽습니까?

그리고 모든 열을 기준으로 정렬하고 지금 처리할 항목(프로세서나 디스크의 로드 또는 총 요청 수)을 결정할 수 있습니다. 우리는 그것을 정렬하고 "상위" 항목을 살펴보고 수정한 다음 새로운 버전의 애플리케이션을 출시했습니다.
[동영상강의]

그리고 즉시 다음과 같은 요청에서 동일한 템플릿과 함께 제공되는 다양한 애플리케이션을 볼 수 있습니다. SELECT * FROM users WHERE login = 'Vasya'. 프론트엔드, 백엔드, 처리... 그리고 사용자가 상호 작용하지 않는데 처리가 사용자를 읽는 이유가 무엇인지 궁금합니다.

반대 방법은 애플리케이션에서 수행하는 작업을 즉시 확인하는 것입니다. 예를 들어 프런트엔드는 this, this, this, this로 한 시간에 한 번(타임라인이 도움이 됩니다)입니다. 그리고 즉시 질문이 떠오릅니다. 한 시간에 한 번씩 뭔가를 하는 것이 프론트엔드의 일이 아닌 것 같습니다...

얼마 후 우리는 집계가 부족하다는 것을 깨달았습니다. 계획 노드별 통계. 우리는 테이블 자체의 데이터로 작업을 수행하는 노드(인덱스로 읽기/쓰기 여부)만 계획에서 격리했습니다. 사실, 이전 사진에 비해 한 가지 측면만 추가되었습니다. 이 노드가 우리에게 얼마나 많은 기록을 가져왔나요?및 삭제된 수(필터에 의해 제거된 행).

플레이트에 적합한 인덱스가 없고 요청을 하면 인덱스를 지나서 Seq Scan에 들어가고... 하나를 제외한 모든 레코드를 필터링했습니다. 하루에 100억 개의 필터링된 레코드가 필요한 이유는 무엇입니까? 인덱스를 롤업하는 것이 더 낫지 않습니까?

모든 계획을 노드별로 분석한 결과 계획에 의심스러워 보일 가능성이 높은 몇 가지 일반적인 구조가 있음을 깨달았습니다. 그리고 개발자에게 "친구여, 먼저 색인으로 읽은 다음 정렬하고 잘라냅니다"라고 말하는 것이 좋을 것입니다. 원칙적으로 하나의 기록이 있습니다.

쿼리를 작성한 모든 사람은 아마도 "Vasya의 마지막 주문, 날짜를 알려주십시오."라는 패턴을 접했을 것입니다. 그리고 날짜별 색인이 없거나 사용한 색인에 날짜가 없다면 똑같은 "갈퀴"를 밟으세요.

그러나 우리는 이것이 "갈퀴"라는 것을 알고 있습니다. 따라서 개발자에게 무엇을 해야 하는지 즉시 알려주는 것은 어떨까요? 따라서 지금 계획을 열 때 우리 개발자는 팁이 포함된 아름다운 그림을 즉시 보고 "여기 저기에 문제가 있지만 이 방법 저 방법으로 해결됩니다."라고 즉시 말합니다.

그 결과 처음에는 문제를 해결하는 데 필요했던 경험량이 지금은 크게 줄었습니다. 이것이 우리가 가지고 있는 종류의 도구입니다.

출처 : habr.com