Risparmiate un centesimu nantu à volumi grossi in PostgreSQL

Cuntinuà u tema di arregistramentu grandi flussi di dati risuscitati da articulu precedente nantu à a partizione, In questu avemu da fighjà i modi in quale pudete riduce a dimensione "fisica" di u almacenatu in PostgreSQL, è u so impattu nantu à u rendiment di u servitore.

Parlaremu Paràmetri TOAST è allineamentu di dati. "In media," sti metudi ùn risparmià troppu risorse, ma senza mudificà u codice di l'applicazione.

Tuttavia, a nostra sperienza hè stata assai pruduttiva in questu sensu, postu chì l'almacenamiento di quasi ogni surviglianza per a so natura hè soprattuttu append-solu in termini di dati registrati. È se vi dumandate cumu pudete insignà a basa di dati à scrive à u discu invece 200MB / s a mità di quantu - per piacè sottu cat.

Picculi sicreti di big data

Per prufilu di travagliu u nostru serviziu, volanu regularmente à ellu da e tane pacchetti di testu.

E dapoi cumplessu VLSIchì a basa di dati chì monitoremu hè un pruduttu multi-cumpunente cù strutture di dati cumplessi, dopu dumande per u massimu rendiment diventa abbastanza cusì "multi-volume" cù una logica algoritmica cumplessa. Allora u voluminu di ogni istanza individuale di una dumanda o u pianu d'esekzione risultante in u logu chì vene à noi hè "in media" abbastanza grande.

Fighjemu a struttura di una di e tavule in quale scrivemu dati "crudi" - vale à dì, quì hè u testu originale da l'entrata di log:

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

Un signu tipicu (digià seccionatu, sicuru, cusì hè un mudellu di sezione), induve u più impurtante hè u testu. Calchì volta abbastanza voluminosa.

Ricurdativi chì a dimensione "fisica" di un record in un PG ùn pò micca occupà più di una pagina di dati, ma a dimensione "logica" hè una materia completamente diversa. Per scrive un valore volumetricu (varchar/text/bytea) à un campu, utilizate Tecnulugia TOAST:

PostgreSQL usa una dimensione di pagina fissa (tipicamenti 8 KB), è ùn permette micca tuples per spannu parechje pagine. Dunque, hè impussibile di almacenà direttamente valori di campu assai grande. Per superà sta limitazione, i grandi valori di campu sò cumpressi è / o divisi in parechje linee fisiche. Questu passa inosservatu da l'utilizatore è hà pocu impattu nantu à a maiò parte di u codice di u servitore. Stu metudu hè cunnisciutu cum'è TOAST ...

In fatti, per ogni tavula cù campi "potenzialmente grande", automaticamente hè creatu un tavulinu accoppiatu cù "slicing". ogni record "grande" in segmenti 2KB:

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

Questu hè, s'ellu ci vole à scrive una stringa cù un valore "grande". data, allora a vera registrazione si farà micca solu à a tavola principale è u so PK, ma ancu à TOAST è u so PK.

Reduce l'influenza di TOAST

Ma a maiò parte di i nostri dischi ùn sò micca cusì grande, duverebbe mette in 8KB - Cumu possu risparmià soldi nantu à questu?

Questu hè induve l'attributu vene à u nostru aiutu STORAGE à a colonna di a tavula:

• PROLUNGU permette sia cumpressione è almacenamentu separatu. Questu opzione standard per a maiò parte di i tipi di dati conformi TOAST. Prima prova à fà cumpressione, poi guarda fora di a tavula se a fila hè ancu troppu grande.
• Parigi permette a cumpressione ma micca u almacenamentu separatu. (In fattu, u almacenamentu separatu serà sempre realizatu per tali colonne, ma solu cum'è l'ultimu risorsu, quandu ùn ci hè micca altru modu per riduce a stringa in modu chì si mette in a pagina.)

In fatti, questu hè esattamente ciò chì avemu bisognu per u testu - cumpressà u più pussibule, è s'ellu ùn si mette in tuttu, mette in TOAST. Questu pò esse fattu direttamente nantu à a mosca, cù un cumandamentu:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Cumu valutà l'effettu

Siccomu u flussu di dati cambia ogni ghjornu, ùn pudemu micca paragunà numeri assoluti, ma in termini relative parte più chjuca L'avemu scrittu in TOAST - tantu megliu. Ma ci hè un periculu quì - u più grande u voluminu "fisicu" di ogni registru individuale, u "più largu" diventa l'indici, perchè avemu da copre più pagine di dati.

Sezione prima di cambiamenti:

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

Sezione dopu à cambiamenti:

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

In fatti, noi cuminciò à scrive à TOAST 2 volte menu spessu, chì scaricava micca solu u discu, ma ancu u CPU:

Aghju nutatu chì avemu ancu diventatu più chjucu in "leghje" u discu, micca solu "scrittura" - postu chì quandu inserite un registru in una tavula, avemu ancu "leghje" una parte di l'arburu di ogni indice per determinà u so. pusizioni futura in elli.

Quale pò campà bè nantu à PostgreSQL 11

Dopu avè aghjurnatu à PG11, avemu decisu di cuntinuà "tuning" TOAST è hà nutatu chì partendu da questa versione u paràmetru hè diventatu dispunibule per tuning. toast_tuple_target:

U codice di trasfurmazioni TOAST si spara solu quandu u valore di fila per esse guardatu in a tavula hè più grande di i bytes TOAST_TUPLE_THRESHOLD (di solitu 2 KB). U codice TOAST comprimerà è / o moverà i valori di u campu fora di a tavula finu à chì u valore di a fila diventa menu di TOAST_TUPLE_TARGET byte (valore variabile, ancu di solitu 2 KB) o a dimensione ùn pò esse ridutta.

Avemu decisu chì i dati chì avemu di solitu sò o "assai brevi" o "assai longu", cusì avemu decisu di limità à u minimu valore pussibule:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Videmu cumu i novi paràmetri anu affettatu a carica di discu dopu a ricunfigurazione:

Micca male! Media a fila à u discu hè diminuitu circa 1.5 volte, è u discu "occupatu" hè 20 per centu! Ma forsi questu in qualchì manera hà affettatu u CPU?

Almenu ùn hè micca peghju. Ancu s'ellu hè difficiule di ghjudicà s'ellu ancu tali volumi ùn ponu ancu elevà a carica media di CPU più altu 5%.

Cambiendu i lochi di i termini, a somma... cambia !

Comu sapete, un centesimu salva un rublu, è cù i nostri volumi d'almacenamiento si tratta 10 TB / mese ancu un pocu ottimisazione pò dà un bonu prufittu. Dunque, avemu attentu à a struttura fisica di i nostri dati - cumu esattamente campi "stacked" in u record ognunu di i tavulini.

Perchè per via di allineamentu di dati questu hè ghjustu influenza u voluminu risultatu:

Parechje architetture furnisce l'allineamentu di dati nantu à i limiti di e parolle di a macchina. Per esempiu, in un sistema x32 di 86 bit, i numeri interi (tipu integer, 4 byte) seranu allinati nantu à un limitu di parola di 4 byte, cum'è i numeri di virgola flottante di precisione doppia (puntu flottante di precisione doppia, 8 byte). È nantu à un sistema di 64 bit, i valori doppiu seranu allinati à i limiti di e parolle di 8 byte. Questu hè un altru mutivu di incompatibilità.

A causa di l'allineamentu, a dimensione di una fila di tavula dipende da l'ordine di i campi. Di solitu stu effettu ùn hè micca assai notevuli, ma in certi casi pò purtà à un aumentu significativu di taglia. Per esempiu, se mischiate char (1) è campi integer, ci saranu tipicamente 3 byte perdi trà elli.

Cuminciamu cù mudelli sintetici:

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 байт

Da induve venenu un paru di byte extra in u primu casu? Hè simplice - Smallint di 2 byte allineatu nantu à u cunfini di 4 byte prima di u prossimu campu, è quandu hè l'ultimu, ùn ci hè nunda è ùn ci hè bisognu di allineà.

In teoria, tuttu hè bè è pudete rearrange i campi cum'è vulete. Cuntrollamu nantu à e dati reali cù l'esempiu di una di e tavule, a seccione di ogni ghjornu chì occupa 10-15GB.

Struttura iniziale:

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)

Sezione dopu à cambià l'ordine di a colonna - esattamente listessi campi, solu ordine sfarente:

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)

U voluminu tutale di a rùbbrica hè determinata da u numeru di "fatti" è dipende solu di prucessi esterni, dunque dividimu a dimensione di u munzeddu (pg_relation_size) da u numeru di registri in questu - vale à dì, avemu dimensione media di u registru arregistratu attuale:

Minus 6% volume, Perfettu!

Ma tuttu, sicuru, ùn hè micca cusì rosa - dopu tuttu, in l'indici ùn pudemu micca cambià l'ordine di i campi, è dunque "in generale" (pg_total_relation_size) ...

...ancora quì risparmiatu 1.5%senza cambià una sola linea di codice. Iè, iè !

Aghju nutatu chì l'opzione di sopra per organizà i campi ùn hè micca u fattu chì hè u più ottimali. Perchè ùn vulete micca "strappare" alcuni blocchi di campi per ragioni estetiche - per esempiu, un coppiu (pack, recno), chì hè u PK per sta tabella.

In generale, a determinazione di l'arrangementu "minimu" di i campi hè un compitu abbastanza simplice di "forza bruta". Dunque, pudete ottene risultati ancu megliu da i vostri dati cà i nostri - pruvate!

Source: www.habr.com