Sublight에서 PostgreSQL로 작성: 호스트 1개, 1일, 1TB

최근에 표준 레시피를 사용하여 방법을 알려 드렸습니다. SQL 읽기 쿼리 성능 향상 PostgreSQL 데이터베이스에서. 오늘 우리는 방법에 대해 이야기 할 것입니다 녹음을 더욱 효율적으로 할 수 있습니다 구성에서 "비틀림"을 사용하지 않고 데이터베이스에서 간단히 데이터 흐름을 올바르게 구성하면 됩니다.

#1. 단면화

정리할 가치가 있는 방법과 이유에 대한 기사 "이론적으로" 분할 적용 이미 그랬으므로 여기서는 우리 내에서 몇 가지 접근 방식을 적용하는 관행에 대해 이야기하겠습니다. 수백 대의 PostgreSQL 서버에 대한 모니터링 서비스.

"지나간 일들..."

처음에는 다른 MVP와 마찬가지로 우리 프로젝트도 상당히 가벼운 부하로 시작되었습니다. 모니터링은 가장 중요한 XNUMX개의 서버에 대해서만 수행되었으며 모든 테이블은 상대적으로 작았습니다... 그러나 시간이 지남에 따라 모니터링되는 호스트의 수가 점점 더 많아졌습니다. , 다시 한 번 우리는 다음 중 하나를 사용하여 작업을 시도했습니다. 테이블 크기 1.5TB, 이렇게 계속 살아갈 수는 있지만 매우 불편하다는 것을 깨달았습니다.

시대는 거의 엄청난 시대와 같았고, PostgreSQL 9.x의 다양한 버전이 관련되어 있었기 때문에 모든 파티셔닝은 "수동"으로 수행되어야 했습니다. 테이블 상속 및 트리거 동적 라우팅 EXECUTE.

결과 솔루션은 모든 테이블로 변환될 수 있을 만큼 보편적인 것으로 나타났습니다.

• 모든 내용을 설명하는 빈 "헤더" 상위 테이블이 선언되었습니다. 필요한 인덱스 및 트리거.
• 클라이언트 입장에서 기록은 “루트” 테이블에 작성되었으며, 내부적으로는 라우팅 트리거 BEFORE INSERT 레코드가 필수 섹션에 "물리적으로" 삽입되었습니다. 아직 그러한 것이 없다면 예외를 포착하고...
• … 사용하여 CREATE TABLE ... (LIKE ... INCLUDING ...) 상위 테이블의 템플릿을 기반으로 생성되었습니다. 원하는 날짜에 제한이 있는 구간따라서 데이터를 검색할 때 해당 데이터에서만 읽기가 수행됩니다.

PG10: 첫 번째 시도

그러나 상속을 통한 분할은 역사적으로 활성 쓰기 스트림이나 다수의 하위 파티션을 처리하는 데 적합하지 않았습니다. 예를 들어, 필요한 섹션을 선택하는 알고리즘이 XNUMX차 복잡도, 100개 이상의 섹션에서 작동한다는 점을 직접 이해하실 수 있습니다...

PG10에서는 지원을 구현하여 이러한 상황이 크게 최적화되었습니다. 네이티브 파티셔닝. 그래서 저희도 스토리지 마이그레이션 후 바로 적용해보려고 했으나...

매뉴얼을 살펴본 후 밝혀진 바와 같이, 이 버전에서 기본적으로 파티션된 테이블은 다음과 같습니다.

• 인덱스 설명을 지원하지 않습니다.
• 트리거를 지원하지 않습니다
• 누구의 '후손'도 될 수 없다
• 지원하지 않습니다 INSERT ... ON CONFLICT
• 섹션을 자동으로 생성할 수 없습니다.

갈퀴로 이마에 고통스러운 타격을 가한 우리는 응용 프로그램을 수정하지 않으면 불가능하다는 것을 깨달았고 추가 연구를 XNUMX 개월 동안 연기했습니다.

PG10: 두 번째 기회

그래서 우리는 발생한 문제를 하나씩 해결하기 시작했습니다.

1. 왜냐하면 트리거와 ON CONFLICT 여기저기서 아직 필요하다고 판단되어 이를 해결하기 위한 중간 단계를 만들었습니다. 프록시 테이블.
2. "라우팅"을 제거했습니다. 트리거에서 - 즉, EXECUTE.
3. 따로 꺼내놓으셨어요 모든 인덱스가 포함된 템플릿 테이블그래서 그들은 프록시 테이블에도 존재하지 않습니다.

마지막으로, 우리는 기본 테이블을 기본적으로 분할했습니다. 새로운 섹션을 생성하는 것은 여전히 ​​애플리케이션의 양심에 달려 있습니다.

"톱질" 사전

다른 분석 시스템과 마찬가지로 우리는 또한 "사실"과 "컷" (사전). 우리의 경우 그들은 이러한 자격으로 다음과 같이 행동했습니다. 템플릿 본문 유사한 느린 쿼리 또는 쿼리 자체의 텍스트.

“Facts”는 이미 오랫동안 날짜별로 구분되어 있었기 때문에 우리는 오래된 부분을 침착하게 삭제했고 그것들은 우리를 괴롭히지 않았습니다(로그!). 그런데 사전에 문제가 있었네요...

많이 있었다는 것은 아니지만 대략 100TB의 "사실"로 인해 2.5TB의 사전이 탄생했습니다.. 그러한 테이블에서는 무엇이든 편리하게 삭제할 수 없고, 적절한 시간 내에 압축할 수 없으며, 쓰기 속도가 점차 느려졌습니다.

사전처럼... 그 안에는 각 항목이 정확히 한 번만 표시되어야 합니다... 그리고 이것이 맞습니다. 하지만!... 아무도 우리가 하는 것을 막지 않습니다. 매일 별도의 사전! 예, 이는 특정 중복성을 제공하지만 다음을 허용합니다.

• 더 빠르게 쓰기/읽기 섹션 크기가 작기 때문에
• 더 적은 메모리를 소비합니다 보다 컴팩트한 인덱스로 작업하여
• 더 적은 양의 데이터 저장 오래된 것을 빠르게 제거할 수 있는 능력 때문에

전체적인 조치의 결과로 CPU 로드가 ~30% 감소하고 디스크 로드가 ~50% 감소했습니다.:

동시에 우리는 더 적은 부하로 데이터베이스에 정확히 동일한 내용을 계속 기록했습니다.

#2. 데이터베이스 진화 및 리팩토링

그래서 우리는 우리가 가진 것을 결정했습니다 매일 자신의 섹션이 있습니다 데이터로. 실제로, CHECK (dt = '2018-10-12'::date) — 그리고 레코드가 특정 섹션에 속하기 위한 분할 키와 조건이 있습니다.

당사 서비스의 모든 보고서는 특정 날짜의 맥락에서 작성되므로 "분할되지 않은 시간" 이후 해당 보고서에 대한 인덱스는 모든 유형이었습니다. (섬기는 사람, 날짜, 계획 템플릿), (섬기는 사람, 날짜, 계획 노드), (날짜, 오류 클래스, 서버), ...

하지만 이제 그들은 모든 구역에 살고 있어요 당신의 사본 각 인덱스... 그리고 각 섹션 내에서 날짜는 일정합니다... 이제 우리는 그러한 각 색인에 있음이 밝혀졌습니다 단순히 상수를 입력하세요 필드 중 하나로 볼륨과 검색 시간이 모두 증가하지만 결과는 나오지 않습니다. 그들은 갈퀴를 그들 자신에게 맡겼습니다. 이런...

최적화 방향은 분명합니다. - 단순합니다. 모든 색인에서 날짜 필드를 제거하십시오. 파티션된 테이블에서. 우리의 거래량을 감안할 때 이익은 약 1TB/주!

이제 이 테라바이트는 어떻게든 기록되어야 한다는 점에 주목해 보겠습니다. 즉, 우리도 이제 디스크의 로드가 줄어듭니다! 이 사진은 우리가 일주일 동안 집중한 청소를 통해 얻은 효과를 명확하게 보여줍니다.

#삼. 최대 부하를 "확산"

로드된 시스템의 가장 큰 문제 중 하나는 중복 동기화 필요하지 않은 일부 작업. 때로는 "그들이 눈치채지 못했기 때문에", 때로는 "그게 더 쉬웠어요"라고 하지만 조만간 제거해야 합니다.

이전 사진을 확대하여 디스크가 있는지 살펴보겠습니다. 이중 진폭의 부하에서 "펌프" 이는 분명히 "통계적으로" 이러한 여러 작업에서 발생해서는 안 되는 인접 샘플 사이의 작업입니다.

이것은 달성하기 매우 쉽습니다. 이미 모니터링을 시작했습니다 거의 1000개의 서버, 각각은 별도의 논리적 스레드에 의해 처리되며 각 스레드는 다음과 같이 특정 빈도로 데이터베이스에 전송되도록 축적된 정보를 재설정합니다.

setInterval(sendToDB, interval)

여기서 문제는 바로 다음과 같은 사실에 있습니다. 모든 스레드는 거의 동시에 시작됩니다., 따라서 전송 시간은 거의 항상 "요점까지" 일치합니다. 앗 #2...

다행히도 이 문제는 해결하기가 매우 쉽습니다. "무작위" 런업 추가 시간별:

setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))

#4. 필요한 것을 캐시합니다.

세 번째 전통적인 고부하 문제는 다음과 같습니다. 캐시 없음 그가 어디에 있는지 할 수 있었다 그렇다.

예를 들어, 우리는 계획 노드(이 모든 것) 측면에서 분석을 가능하게 했습니다. Seq Scan on users), 그러나 그들은 대부분 동일하다고 즉시 생각합니다. 그들은 잊어버렸습니다.

아니요, 물론 데이터베이스에는 아무 것도 다시 기록되지 않습니다. 이렇게 하면 다음과 같은 트리거가 차단됩니다. INSERT ... ON CONFLICT DO NOTHING. 하지만 이 데이터는 여전히 데이터베이스에 도달하므로 불필요합니다. 충돌을 확인하기 위해 읽기 해야 할. 앗 #3...

캐싱이 활성화되기 전/후에 데이터베이스로 전송되는 레코드 수의 차이는 명백합니다.

그리고 이는 그에 따른 스토리지 로드 감소입니다.

전체로

"일일 테라바이트"라는 말은 정말 무섭게 들립니다. 모든 일을 올바르게했다면 이것은 단지 2^40바이트 / 86400초 = ~12.5MB/s데스크탑 IDE 나사도 고정되어 있습니다. 🙂

그러나 심각하게 말하면 하루 동안 로드가 XNUMX배나 "편향"되어도 최신 SSD의 기능을 쉽게 충족할 수 있습니다.

출처 : habr.com