Заштедете денар на големи количини во 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 користи фиксна големина на страница (обично 8 KB) и не дозволува торките да опфаќаат повеќе страници. Затоа, невозможно е директно да се складираат многу големи вредности на полето. За да се надмине ова ограничување, големите вредности на полето се компресираат и/или се делат на повеќе физички линии. Ова се случува незабележано од корисникот и има мало влијание врз повеќето кодови на серверот. Овој метод е познат како ТОСТ...

Всушност, за секоја табела со „потенцијално големи“ полиња, автоматски се креира спарена табела со „сечење“. секој „голем“ запис во сегменти од 2KB:

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

Тоа е, ако треба да напишеме низа со „голема“ вредност data, тогаш ќе се случи вистинското снимање не само на главната маса и нејзината ПК, туку и на ТОСТ и нејзиниот ПК.

Намалување на влијанието на ТОАСТ

Но, повеќето од нашите рекорди сè уште не се толку големи, треба да се вклопи во 8KB - Како можам да заштедам пари на ова?..

Тука ни доаѓа атрибутот на помош STORAGE во колоната на табелата:

• ИЗВРШЕНО овозможува и компресија и посебно складирање. Ова стандардна опција за повеќето типови на податоци во согласност со TOAST. Прво се обидува да изврши компресија, а потоа го складира надвор од табелата ако редот е сè уште преголем.
• ГЛАВНИ овозможува компресија, но не и посебно складирање. (Всушност, посебно складирање сè уште ќе се врши за такви колони, но само како последно средство, кога нема друг начин да се намали низата за да се вклопи на страницата.)

Всушност, токму тоа ни треба за текстот - компресирајте го што е можно повеќе, а ако воопшто не ви одговара ставете го во ТОСТ. Ова може да се направи директно во лет, со една команда:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Како да се оцени ефектот

Бидејќи протокот на податоци се менува секој ден, не можеме да споредуваме апсолутни бројки, туку во релативна смисла помал удел Го запишавме во ТОСТ - толку подобро. Но, тука постои опасност - колку е поголем „физичкиот“ волумен на секој поединечен запис, толку индексот станува „поширок“, затоа што треба да покриеме повеќе страници со податоци.

Дел пред промените:

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

Дел по промените:

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

Всушност, ние почна да пишува на ТОСТ 2 пати поретко, кој го растовари не само дискот, туку и процесорот:

Ќе забележам дека станавме помали и во „читањето“ на дискот, а не само во „пишувањето“ - бидејќи кога вметнуваме запис во табела, треба да „прочитаме“ и дел од дрвото на секој индекс за да го одредиме неговиот идната позиција во нив.

Кој може да живее добро на PostgreSQL 11

По ажурирањето на PG11, решивме да продолжиме со „штимање“ TOAST и забележавме дека почнувајќи од оваа верзија параметарот toast_tuple_target:

Кодот за обработка на TOAST се активира само кога вредноста на редот што треба да се зачува во табелата е поголема од TOAST_TUPLE_THRESHOLD бајти (обично 2 KB). Кодот TOAST ќе ги компресира и/или ќе ги помести вредностите на полињата надвор од табелата додека вредноста на редот не стане помала од TOAST_TUPLE_TARGET бајти (променлива вредност, исто така обично 2 KB) или големината не може да се намали.

Решивме дека податоците што обично ги имаме се или „многу кратки“ или „многу долги“, па решивме да се ограничиме на минималната можна вредност:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ајде да видиме како новите поставки влијаеле на вчитувањето на дискот по реконфигурацијата:

Не е лошо! Просечна редот до дискот е намален приближно 1.5 пати, а дискот „зафатен“ е 20 проценти! Но, можеби ова некако влијаеше на процесорот?

Барем не стана полошо. Иако, тешко е да се процени дали дури и таквите волумени сè уште не можат да го подигнат просечното оптоварување на процесорот повисоко 5%.

Со менување на местата на поимите се менува збирот...!

Како што знаете, еден денар заштедува рубља, а со нашите волумени за складирање станува збор 10 ТБ/месечно дури и мала оптимизација може да даде добар профит. Затоа, обрнавме внимание на физичката структура на нашите податоци - како точно „наредени“ полиња во записот секоја од табелите.

Бидејќи поради усогласување на податоците ова е директно напред влијае на добиениот волумен:

Многу архитектури обезбедуваат усогласување на податоците за границите на машинските зборови. На пример, на 32-битен x86 систем, цели броеви (тип на цел број, 4 бајти) ќе бидат порамнети на граница на збор од 4 бајти, како што ќе се удвојат прецизните броеви со подвижна запирка (двојна прецизна подвижна запирка, 8 бајти). И на 64-битен систем, двојните вредности ќе бидат порамнети со границите на зборовите од 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 бајти пред следното поле, а кога е последното, нема ништо и нема потреба да се порамнува.

Во теорија, сè е во ред и можете да ги преуредите полињата како што сакате. Ајде да го провериме на вистински податоци користејќи го примерот на една од табелите, чиј дневен дел зафаќа 10-15 GB.

Почетна структура:

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 за оваа табела.

Општо земено, одредувањето на „минималниот“ распоред на полињата е прилично едноставна задача за „брутална сила“. Затоа, можете да добиете уште подобри резултати од вашите податоци од нашите - обидете се!

Извор: www.habr.com