PostgreSQL에서 대용량 볼륨에 1페니 절약

대용량 데이터 스트림을 기록하는 주제를 계속해서 제기했습니다. 섹션에 대한 이전 기사, 여기서 우리는 당신이 할 수 있는 방법을 고려할 것입니다 저장된 "물리적" 크기를 줄이세요 PostgreSQL에서의 차이점과 서버 성능에 미치는 영향에 대해 설명합니다.

우리는 ~에 대해 이야기 할 것이다 TOAST 설정 및 데이터 정렬. "평균적으로" 이러한 방법을 사용해도 많은 리소스를 절약할 수는 없지만 애플리케이션 코드를 전혀 수정하지 않고도 리소스를 절약할 수 있습니다.

그러나 거의 모든 모니터링의 저장은 본질적으로 다음과 같은 점에서 우리의 경험이 매우 생산적임이 입증되었습니다. 대부분 추가 ​​전용 기록되는 데이터의 관점에서. 그리고 디스크에 쓰기 위해 데이터베이스를 어떻게 가르칠 수 있는지에 관심이 있다면 200MB / s의 두 배로 줄었습니다. 아래를 참조하세요.

빅데이터의 작은 비밀

직업 프로필별 우리 서비스의그는 정기적으로 로그에서 메시지를 받습니다. 텍스트 패키지.

이후 SBIS 복합체우리가 모니터링하는 데이터베이스는 복잡한 데이터 구조를 갖춘 다중 구성 요소 제품이며 쿼리도 마찬가지입니다. 최대 성능을 달성하기 위해 그들은 꽤 그런 식으로 나온다 복잡한 알고리즘 논리를 갖춘 "다권" 책. 따라서 로그에 기록되는 각 개별 쿼리 인스턴스의 크기나 그 결과 실행 계획은 "평균적으로" 매우 큰 것으로 나타났습니다.

"원시" 데이터를 쓰는 테이블 중 하나의 구조를 살펴보겠습니다. 즉, 로그 항목의 원본 텍스트는 다음과 같습니다.

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

이와 같은 전형적인 표(물론 이미 섹션이 나뉘어 있으므로 이는 섹션 템플릿입니다)에서 가장 중요한 것은 텍스트입니다. 때로는 매우 방대합니다.

PG에서 한 레코드의 "물리적" 크기는 한 페이지 이상의 데이터를 차지할 수 없지만 "논리적" 크기는 전혀 다른 문제입니다. 필드에 볼륨 값(varchar/text/bytea)을 쓰려면 다음을 사용하세요. 토스트 기술:

PostgreSQL은 고정된 페이지 크기(보통 8KB)를 사용하며, 튜플이 여러 페이지에 걸쳐 있는 것을 허용하지 않습니다. 따라서 매우 큰 필드 값을 직접 저장하는 것은 불가능합니다. 이러한 제한을 극복하기 위해 큰 필드 값은 압축되거나 여러 개의 물리적 행에 분할됩니다. 이 작업은 사용자에게 투명하게 이루어지며 대부분의 서버 코드에 거의 영향을 미치지 않습니다. 이 방법은 TOAST…라고 불립니다.

실제로 "잠재적으로 큰" 필드가 있는 모든 테이블의 경우 자동으로 "잘라내기"를 통해 쌍을 이루는 테이블이 생성됩니다. 각 "대형" 레코드는 2KB 세그먼트로 구성됩니다.

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

즉, "큰" 값을 갖는 줄을 작성해야 하는 경우 data그러면 실제 녹음이 진행됩니다. 메인 테이블과 그 PK뿐만 아니라 TOAST와 그 PK에도 적용됩니다..

TOAST 영향 감소

하지만 우리의 기록 대부분은 그렇게 크지 않습니다. 8KB에 맞아야 합니다 - 어떻게 하면 돈을 아낄 수 있을까요?

여기서 속성이 우리에게 도움이 됩니다. STORAGE 테이블 열에 대해:

• 확장 압축과 별도 저장이 모두 가능합니다. 이것 표준 옵션 대부분의 TOAST 호환 데이터 유형에 대해. 먼저 압축을 시도한 다음, 행이 여전히 너무 클 경우 테이블에서 저장을 시도합니다.
• 메인 압축은 가능하지만 별도로 저장할 수는 없습니다. (실제로 이러한 열에 대해서는 별도의 저장이 계속 수행되지만 최후의 수단으로(페이지에 맞게 줄을 줄일 수 있는 다른 방법이 없는 경우)

사실, 이것은 바로 우리가 텍스트에 필요로 하는 것입니다. 최대한 압축하고, 전혀 안맞으면 TOAST에 넣어주세요. 다음 명령 하나로 바로 이 작업을 수행할 수 있습니다.

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

효과를 평가하는 방법

데이터 흐름은 매일 바뀌기 때문에 절대적인 수치를 비교할 수는 없지만 상대적인 수치를 비교할 수 있습니다. 더 작은 몫 우리는 그것을 TOAST에 적어 두었습니다. 그게 더 좋았습니다. 하지만 여기에는 위험이 있습니다. 각 개별 레코드의 "물리적" 볼륨이 클수록 인덱스는 더 "넓어집니다". 왜냐하면 인덱스가 다루어야 할 데이터 페이지 수가 더 많아지기 때문입니다.

섹션 변경 전:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

섹션 변경 후:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

사실, 우리는 TOAST에 글을 쓰는 빈도가 2배 줄었습니다.디스크뿐만 아니라 CPU도 언로드합니다.

또한, 디스크를 "쓰기"만 하는 것이 아니라 "읽기"도 덜 하게 되었다는 점에 유의하시기 바랍니다. 어떤 테이블에 레코드를 삽입할 때, 각 인덱스의 트리 일부를 "읽어서" 해당 인덱스의 미래 위치를 결정해야 하기 때문입니다.

PostgreSQL 11에서 잘 사는 사람

PG11로 업데이트한 후 TOAST를 "조정"하기로 결정했고 이 버전부터 매개변수를 구성에 사용할 수 있게 되었다는 것을 알았습니다. toast_tuple_target:

TOAST 처리 코드는 테이블에 저장될 행 값이 TOAST_TUPLE_THRESHOLD 바이트(일반적으로 2KB)보다 큰 경우에만 트리거됩니다. TOAST 코드는 행 값이 TOAST_TUPLE_TARGET 바이트(변수, 일반적으로 2KB)보다 작거나 크기를 줄일 수 없을 때까지 테이블에서 필드 값을 압축하거나 테이블 밖으로 이동합니다.

우리는 데이터가 일반적으로 "매우 짧거나" "매우 길다"고 판단하여 가능한 최소값으로 제한하기로 했습니다.

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

재구성 후 새로운 설정이 디스크 로딩에 어떤 영향을 미치는지 살펴보겠습니다.

나쁘지 않네요! 평균 디스크 대기열이 줄었습니다 약 1.5배이고 디스크 "점유율"은 20%입니다! 하지만 어쩌면 CPU에 영향을 미쳤을 수도 있을까?

적어도 확실히 더 나빠지지는 않았어요. 그러나 그러한 볼륨조차도 여전히 평균 CPU 부하를 더 높일 수 없는지 판단하기는 어렵습니다. 5%.

항의 순서를 바꾸면 합계가 바뀌죠!

아시다시피, 절약한 1페니는 벌어들인 1페니와 같으며, 우리의 저장 용량에는 주문이 있습니다. 10TB/월 조금만 최적화해도 좋은 수익을 낼 수 있습니다. 그래서 우리는 데이터의 물리적 구조를 살펴보았습니다. 정확히 어떻게 레코드 내부의 "스택" 필드 각 테이블

때문에 데이터 정렬 그것은 직선이다 결과 볼륨에 영향을 미칩니다:

많은 아키텍처는 머신 단어 경계에 따른 데이터 정렬을 제공합니다. 예를 들어, 32비트 x86 시스템에서 정수(정수형, 4바이트)는 4바이트 단어 경계에 정렬되고, 배정밀도 부동 소수점 숫자(배정밀도형, 8바이트)도 마찬가지입니다. 64비트 시스템에서는 double 값이 8바이트 단어 경계에 정렬됩니다. 이는 비호환성의 또 다른 이유입니다.

정렬로 인해 테이블 ​​행의 크기는 필드 순서에 따라 달라집니다. 일반적으로 이러한 효과는 크게 눈에 띄지 않지만 어떤 경우에는 크기가 상당히 증가할 수 있습니다. 예를 들어, char(1) 필드와 정수 필드를 섞으면 일반적으로 두 필드 사이에 3바이트가 낭비됩니다.

합성 모델부터 시작해 보겠습니다.

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

첫 번째 경우에 추가된 몇 바이트는 어디서 나왔나요? 간단합니다. 2바이트 smallint는 4바이트 경계에 정렬됩니다. 다음 필드 전, 그리고 마지막 필드일 때는 아무것도 없으므로 정렬할 필요가 없습니다.

이론상으로는 모든 것이 괜찮으며 원하는 대로 필드를 재배열할 수 있습니다. 예시로 테이블 중 하나를 사용하여 실제 데이터에서 확인해 보겠습니다. 이 테이블의 일일 섹션은 10~15GB를 차지합니다.

원래 구조:

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

열 순서를 변경한 후의 섹션은 짝수입니다. 같은 필드, 다만 순서만 다를 뿐:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

섹션의 총 볼륨은 "팩트"의 수에 따라 결정되며 외부 프로세스에만 의존하므로 힙의 크기를 나눕니다(pg_relation_size) 그 안에 있는 레코드의 수에 따라 - 즉, 우리는 얻습니다. 실제 저장된 레코드의 평균 크기:

마이너스 6% 볼륨, 엄청난!

하지만 물론 모든 것이 그렇게 장밋빛인 것은 아닙니다. 인덱스에서는 필드 순서를 변경할 수 없습니다., 따라서 "일반적으로"(pg_total_relation_size) ...

…그리고 여기서도 결국 1.5% 절약코드 한 줄도 변경하지 않고도. 물론이죠!

위의 필드 배열 옵션이 반드시 가장 최적은 아니라는 점을 알려드리고 싶습니다. 일부 필드 블록은 미학적 이유로 "찢어지는" 것을 원하지 않기 때문에 - 예를 들어, (pack, recno)이 테이블의 PK는 입니다.

일반적으로 필드의 "최소" 배열을 결정하는 것은 상당히 간단한 "열거" 작업입니다. 따라서 귀하는 당사보다 더 나은 데이터 결과를 얻을 수 있습니다. 시도해 보세요!

출처 : habr.com