Estalvieu un cèntim en grans volums a PostgreSQL

Continuant amb el tema de l'enregistrament de grans fluxos de dades plantejats per article anterior sobre la partició, en això veurem les maneres en què podeu reduir la mida "física" de l'emmagatzemat a PostgreSQL i el seu impacte en el rendiment del servidor.

En parlarem Configuració de TOAST i alineació de dades. "De mitjana", aquests mètodes no estalviaran massa recursos, però sense modificar el codi de l'aplicació.

No obstant això, la nostra experiència va resultar ser molt productiva en aquest sentit, ja que l'emmagatzematge de gairebé qualsevol monitorització per la seva naturalesa és majoritàriament només per adjuntar pel que fa a les dades registrades. I si us pregunteu com podeu ensenyar a la base de dades a escriure al disc 200MB / s la meitat - si us plau, sota el gat.

Petits secrets del big data

Per perfil laboral el nostre servei, volen regularment cap a ell des dels caus paquets de text.

I des de llavors complex VLSIla base de dades de la qual supervisem és un producte de diversos components amb estructures de dades complexes i després consultes per al màxim rendiment resultar bastant així "multi-volum" amb lògica algorítmica complexa. Així, el volum de cada instància individual d'una sol·licitud o el pla d'execució resultant al registre que ens arriba resulta ser "de mitjana" força gran.

Vegem l'estructura d'una de les taules en què escrivim dades "crues", és a dir, aquí teniu el text original de l'entrada del registre:

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

Un rètol típic (ja seccionat, és clar, així que es tracta d'una plantilla de secció), on el més important és el text. De vegades bastant voluminós.

Recordeu que la mida "física" d'un registre en un PG no pot ocupar més d'una pàgina de dades, però la mida "lògica" és una qüestió completament diferent. Per escriure un valor volumètric (varchar/text/bytea) en un camp, feu servir Tecnologia TOAST:

PostgreSQL utilitza una mida de pàgina fixa (normalment 8 KB) i no permet que les tuples abastin diverses pàgines. Per tant, és impossible emmagatzemar directament valors de camp molt grans. Per superar aquesta limitació, els valors de camp grans es comprimeixen i/o es divideixen en diverses línies físiques. Això passa desapercebut per l'usuari i té poc impacte en la majoria del codi del servidor. Aquest mètode es coneix com TOAST...

De fet, per a cada taula amb camps "potencialment grans", automàticament es crea una taula aparellada amb "slicing". cada registre "gran" en segments de 2 KB:

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

És a dir, si hem d'escriure una cadena amb un valor "gran". data, llavors es produirà l'enregistrament real no només a la taula principal i el seu PK, sinó també a TOAST i el seu PK.

Reducció de la influència TOAST

Però la majoria dels nostres registres encara no són tan grans, hauria d'encaixar en 8 KB - Com puc estalviar diners en això?...

Aquí és on l'atribut ens ajuda STORAGE a la columna de la taula:

• AMPLIAT permet compressió i emmagatzematge separat. Això opció estàndard per a la majoria de tipus de dades compatibles amb TOAST. Primer intenta realitzar compressió i després l'emmagatzema fora de la taula si la fila encara és massa gran.
• PRINCIPAL permet la compressió però no l'emmagatzematge separat. (De fet, encara es farà un emmagatzematge separat per a aquestes columnes, però només com a últim recurs, quan no hi ha cap altra manera de reduir la cadena perquè encaixi a la pàgina.)

De fet, això és exactament el que necessitem per al text: comprimiu-lo al màxim, i si no encaixa gens, poseu-lo a TOAST. Això es pot fer directament sobre la marxa, amb una ordre:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Com avaluar l'efecte

Com que el flux de dades canvia cada dia, no podem comparar nombres absoluts, sinó en termes relatius quota menor Ho vam escriure a TOAST, tant millor. Però aquí hi ha un perill: com més gran sigui el volum "físic" de cada registre individual, més "ampli" serà l'índex, perquè hem de cobrir més pàgines de dades.

Secció abans dels canvis:

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

Secció després dels canvis:

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

De fet, nosaltres va començar a escriure a TOAST 2 vegades menys sovint, que va descarregar no només el disc, sinó també la CPU:

Observaré que també ens hem fet més petits a l'hora de "llegir" el disc, no només d'"escriure", ja que en inserir un registre en una taula també hem de "llegir" part de l'arbre de cada índex per tal de determinar-ne posició futura en ells.

Qui pot viure bé amb PostgreSQL 11

Després d'actualitzar a PG11, vam decidir continuar “sintonitzant” TOAST i vam observar que a partir d'aquesta versió el paràmetre toast_tuple_target:

El codi de processament TOAST només s'activa quan el valor de fila que s'ha d'emmagatzemar a la taula és més gran que TOAST_TUPLE_THRESHOLD bytes (normalment 2 KB). El codi TOAST comprimirà i/o mourà els valors dels camps fora de la taula fins que el valor de la fila sigui inferior a TOAST_TUPLE_TARGET bytes (valor variable, també normalment 2 KB) o la mida no es pot reduir.

Vam decidir que les dades que tenim habitualment són "molt curtes" o "molt llargues", així que vam decidir limitar-nos al valor mínim possible:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Vegem com la nova configuració va afectar la càrrega del disc després de la reconfiguració:

No està malament! Mitjana la cua al disc ha disminuït aproximadament 1.5 vegades, i el disc "ocupat" és un 20 per cent! Però potser això va afectar d'alguna manera la CPU?

Almenys no va empitjorar. Tot i que, és difícil jutjar si fins i tot aquests volums encara no poden augmentar la càrrega mitjana de la CPU 5%.

Canviant els llocs dels termes, la suma... canvia!

Com sabeu, un cèntim estalvia un ruble, i amb els nostres volums d'emmagatzematge es tracta 10 TB/mes fins i tot una petita optimització pot donar un bon benefici. Per tant, vam prestar atenció a l'estructura física de les nostres dades: com exactament camps "apilats" dins del registre cadascuna de les taules.

Perquè a causa de alineació de dades això és senzill afecta el volum resultant:

Moltes arquitectures proporcionen l'alineació de dades als límits de les paraules de la màquina. Per exemple, en un sistema x32 de 86 bits, els nombres enters (tipus enter, 4 bytes) s'alinearan en un límit de paraula de 4 bytes, igual que els nombres de coma flotant de doble precisió (coma flotant de doble precisió, 8 bytes). I en un sistema de 64 bits, els valors dobles s'alinearan amb els límits de paraules de 8 bytes. Aquest és un altre motiu d'incompatibilitat.

A causa de l'alineació, la mida d'una fila de taula depèn de l'ordre dels camps. En general, aquest efecte no és molt notable, però en alguns casos pot provocar un augment significatiu de la mida. Per exemple, si barregeu camps char(1) i enters, normalment hi haurà 3 bytes perduts entre ells.

Comencem amb models sintètics:

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

D'on van sortir un parell de bytes addicionals en el primer cas? És fàcil - Smallint de 2 bytes alineat al límit de 4 bytes abans del següent camp, i quan és l'últim, no hi ha res ni necessitat d'alinear.

En teoria, tot està bé i pots reordenar els camps com vulguis. Comprovem-ho amb dades reals utilitzant l'exemple d'una de les taules, la secció diària de la qual ocupa 10-15 GB.

Estructura inicial:

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)

Secció després de canviar l'ordre de les columnes, exactament mateixos camps, només un ordre diferent:

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)

El volum total de la secció està determinat pel nombre de "fets" i depèn només de processos externs, així que dividim la mida del munt (pg_relation_size) pel nombre de registres que hi ha, és a dir, obtenim mida mitjana del registre emmagatzemat real:

Menys 6% de volum, Genial!

Però tot, per descomptat, no és tan rosat, després de tot, als índexs no podem canviar l'ordre dels camps, i per tant "en general" (pg_total_relation_size) ...

... encara aquí també estalviat un 1.5%sense canviar ni una sola línia de codi. Sí sí!

Tinc en compte que l'opció anterior per organitzar camps no és el fet que sigui la més òptima. Perquè no voleu "esquinçar" alguns blocs de camps per motius estètics, per exemple, un parell (pack, recno), que és el PK d'aquesta taula.

En general, determinar la disposició "mínima" dels camps és una tasca de "força bruta" força senzilla. Per tant, podeu obtenir resultats encara millors amb les vostres dades que les nostres: proveu-ho!

Font: www.habr.com