PostgreSQL로 대용량을 구매하면 비용을 절감할 수 있습니다.

계속해서 발생하는 대규모 데이터 스트림 기록 주제 파티셔닝에 관한 이전 기사, 여기서는 귀하가 할 수 있는 방법을 살펴보겠습니다. 저장된 데이터의 "물리적" 크기를 줄입니다. PostgreSQL의 서버 성능에 미치는 영향.

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

그러나 우리의 경험은 이와 관련하여 매우 생산적인 것으로 나타났습니다. 왜냐하면 거의 모든 모니터링의 저장은 본질적으로 대부분 추가 ​​전용 기록된 데이터로 보면. 그리고 데이터베이스가 대신 디스크에 쓰도록 가르칠 수 있는 방법이 궁금하다면 200MB / s의 절반 - 고양이 밑으로 부탁드립니다.

빅데이터의 작은 비밀

직업 프로필별 우리의 서비스, 그들은 정기적으로 은신처에서 그에게 날아갑니다. 텍스트 패키지.

이후 VLSI 복합체우리가 모니터링하는 데이터베이스는 복잡한 데이터 구조를 가진 다중 구성 요소 제품이고 쿼리는 최대 성능을 위해 꽤 이렇다 복잡한 알고리즘 로직을 갖춘 "다중 볼륨". 따라서 요청의 각 개별 인스턴스 볼륨이나 우리에게 전달되는 로그의 결과 실행 계획은 "평균적으로" 상당히 큰 것으로 나타났습니다.

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

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%.

용어의 위치를 ​​변경하면 합계가... 변경됩니다!

아시다시피, XNUMX페니는 루블을 절약하며 저장량은 대략 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바이트 경계에 정렬된 4바이트 smallint 다음 필드 앞과 마지막 필드인 경우 아무것도 없으며 정렬할 필요도 없습니다.

이론적으로는 모든 것이 정상이며 원하는 대로 필드를 다시 정렬할 수 있습니다. 일일 섹션이 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