Continuando il tema della registrazione di grandi flussi di dati, sollevato , in questo esamineremo i metodi attraverso i quali è possibile ridurre la dimensione "fisica" dei dati salvati in PostgreSQL e il loro impatto sulle prestazioni del server.
Parleremo di impostazioni TOAST e allineamento dei dati. "In media", questi metodi consentiranno di risparmiare non troppi risorse, ma — senza modificare il codice dell'applicazione.
Tuttavia, la nostra esperienza si è rivelata piuttosto produttiva in questo senso, poiché lo storage di quasi qualsiasi monitoraggio è per sua natura per lo più append-only per quanto riguarda i dati registrati. E se siete interessati a come si può insegnare al database a scrivere su disco invece di 200MB/s la metà — vi invito sotto.
Piccoli segreti dei big data
Per il profilo di lavoro , riceve regolarmente dai log pacchetti di testo.
E poiché , i cui database monitoriamo, è un prodotto multi-componente con strutture dati complesse, le query per raggiungere le massime prestazioni risultano piuttosto simili . Quindi, anche il volume di ciascun singolo esemplare della richiesta o del piano di esecuzione risultante nel nostro log risulta essere «in media» piuttosto grande.
Esaminiamo la struttura di una delle tabelle in cui scriviamo i dati «grezzi» — cioè il testo originale della registrazione del log:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- chiave di sezione
date
, data -- la cosa più importante
text
, PRIMARY KEY(pack, recno)
);Una tabella tipica (già partizionata, naturalmente, quindi questo è un modello di sezione), dove la cosa più importante è il testo. A volte piuttosto voluminoso.
Ricordiamo che la dimensione «fisica» di una registrazione in PG non può occupare più di una pagina di dati, mentre la dimensione «logica» è un'altra questione. Per memorizzare un valore voluminoso nel campo (varchar/text/bytea) si utilizza :
PostgreSQL utilizza una dimensione fissa delle pagine (di solito 8 KB) e non consente che le tuple occupino più pagine. Pertanto, non è possibile memorizzare direttamente valori di campo molto grandi. Per superare questa limitazione, i grandi valori di campo vengono compressi e/o suddivisi in più righe fisiche. Questo avviene in modo trasparente per l'utente e influisce in modo marginale sulla maggior parte del codice del server. Questo metodo è noto come TOAST …
Infatti, per ogni tabella con campi "potenzialmente grandi" viene automaticamente di ogni registrazione "grande" segmentata in pezzi da 2KB:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
); Cioè, se dobbiamo scrivere una riga con un valore "grande" data, la scrittura reale avverrà non solo nella tabella principale e nel suo PK, ma anche in TOAST e nel suo PK.
Riduciamo l'impatto del TOAST
Ma la maggior parte delle registrazioni non è poi così grande, dovrebbero rientrare in 8KB — come possiamo risparmiare su questo?..
Qui ci aiuta l'attributo della colonna della tabella:
- EXTENDED consente sia la compressione che la memorizzazione separata. Questo è l'opzione standard per la maggior parte dei tipi di dati compatibili con TOAST. Inizialmente viene tentata la compressione, quindi viene eseguita la memorizzazione al di fuori della tabella se la riga è ancora troppo grande.
- MAIN consente la compressione, ma non la memorizzazione separata. (In effetti, la memorizzazione separata verrà comunque eseguita per tali colonne, ma solo come ultima risorsa, quando non ci sono altri modi per ridurre la riga in modo che possa entrare nella pagina.)
In effetti, è esattamente ciò di cui abbiamo bisogno per il testo — massimizzare la compressione e se proprio non ci sta — spostarlo in TOAST. Questo può essere fatto direttamente "al volo", con un solo comando:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Come valutare l'effetto
Poiché il flusso di dati cambia ogni giorno, non possiamo confrontare cifre assolute, ma in termini relativi più piccola parte abbiamo registrato in TOAST — meglio è. Ma qui c'è un rischio: più grande è il nostro volume "fisico" di ciascun singolo record, più "ampio" diventa l'indice, perché è necessario coprire un numero maggiore di pagine di dati.
Sezione prima delle modifiche:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Sezione dopo le modifiche:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)In effetti, noi abbiamo iniziato a scrivere in TOAST il 50% in meno, il che ha alleggerito non solo il disco, ma anche la CPU:
Noterò che abbiamo anche iniziato a "leggere" meno il disco, non solo a "scrivere" — poiché quando si inserisce un record in una tabella, è necessario "leggere" anche parte dell'albero di ogni indice, per determinare la sua futura posizione in essi.
A chi sta bene utilizzare PostgreSQL 11
Dopo l'aggiornamento a PG11, abbiamo deciso di continuare a "ottimizzare" TOAST e abbiamo notato che a partire da questa versione è disponibile il parametro configurabile :
Il codice di gestione TOAST viene attivato solo quando il valore della riga, che deve essere memorizzato nella tabella, supera la dimensione di TOAST_TUPLE_THRESHOLD byte (solitamente 2 KB). Il codice TOAST comprimerà e/o sposterà i valori del campo al di fuori della tabella finché il valore della riga non scende sotto i byte di TOAST_TUPLE_TARGET (un valore variabile, anch'esso generalmente di 2 KB) o se non è possibile ridurre ulteriormente il volume.
Abbiamo deciso che i dati che abbiamo di solito sono o "davvero brevi" oppure "molto lunghi", quindi abbiamo deciso di limitarci al valore minimo possibile:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Vediamo come le nuove impostazioni hanno influito sul caricamento del disco dopo la riconfigurazione:
Non male! La media la coda al disco si è ridotta di circa 1,5 volte, e il tasso di 'occupazione' del disco è diminuito di circa il 20%! Ma potrebbe aver influito sul CPU?
Almeno non è andata peggio. Anche se, è difficile da giudicare, se persino questi volumi non riescono a sollevare il caricamento medio del CPU sopra 5%.
Cambiando l'ordine degli addendi, la somma… cambia!
Come è noto, un centesimo risparmia un rublo, e con i nostri volumi di archiviazione di circa 10TB/mese anche una piccola ottimizzazione può portare a un buon profitto. Per questo motivo, abbiamo prestato attenzione alla struttura fisica dei nostri dati — come specificamente i campi sono 'disposti' all'interno della registrazione di ciascuna delle tabelle.
Perché a causa di questo influisce direttamente :
Molte architetture prevedono l'allineamento dei dati sui confini delle parole macchina. Ad esempio, in un sistema x86 a 32 bit, gli interi (tipo integer, 4 byte) vengono allineati sul confine delle parole da 4 byte, così come i numeri in virgola mobile a doppia precisione (tipo double precision, 8 byte). In un sistema a 64 bit, i valori double saranno allineati sul confine delle parole da 8 byte. Questa è un'altra ragione per cui si possono avere incompatibilità.
A causa dell'allineamento, la dimensione di una riga di tabella dipende dall'ordine in cui sono disposti i campi. Di solito, questo effetto non è molto evidente, ma in alcuni casi può portare a un aumento significativo delle dimensioni. Ad esempio, se si mescolano i campi di tipo char(1) e integer, di solito ci saranno 3 byte vuoti tra di loro.
Iniziamo con modelli sintetici:
SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 byte
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 byteDa dove provengono questi 2 byte in più nel primo caso? È semplice — il smallint a 2 byte si allinea sul confine da 4 byte prima del campo successivo, mentre se è l'ultimo non c'è niente da allineare e non ce n'è bisogno.
In teoria, va tutto bene e si possono spostare i campi come si vuole. Verifichiamo con dati reali usando uno degli esempi di tabella, la cui sezione giornaliera occupa circa 10-15 GB.
Struttura originale:
CREATE TABLE public.plan_20190220
(
-- Eredita da table plan: pack uuid NOT NULL,
-- Eredita da table plan: recno smallint NOT NULL,
-- Eredita da table plan: host uuid,
-- Eredita da table plan: ts timestamp with time zone,
-- Eredita da table plan: exectime numeric(32,3),
-- Eredita da table plan: duration numeric(32,3),
-- Eredita da table plan: bufint bigint,
-- Eredita da table plan: bufmem bigint,
-- Eredita da table plan: bufdsk bigint,
-- Eredita da table plan: apn uuid,
-- Eredita da table plan: ptr uuid,
-- Eredita da 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 dopo il cambio dell'ordine delle colonne: esattamente gli stessi campi, solo con un ordine diverso:
CREATE TABLE public.plan_20190221
(
-- Eredità dalla tabella plan: dt date NOT NULL,
-- Eredità dalla tabella plan: ts timestamp with time zone,
-- Eredità dalla tabella plan: pack uuid NOT NULL,
-- Eredità dalla tabella plan: recno smallint NOT NULL,
-- Eredità dalla tabella plan: host uuid,
-- Eredità dalla tabella plan: apn uuid,
-- Eredità dalla tabella plan: ptr uuid,
-- Eredità dalla tabella plan: bufint bigint,
-- Eredità dalla tabella plan: bufmem bigint,
-- Eredità dalla tabella plan: bufdsk bigint,
-- Eredità dalla tabella plan: exectime numeric(32,3),
-- Eredità dalla tabella 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) Il volume totale della sezione è determinato dal numero di "fatti" ed è influenzato solo dai processi esterni, quindi divideremo la dimensione dell'heap (pg_relation_size) per il numero di registrazioni in essa — quindi otteniamo la dimensione media della registrazione memorizzata:
Meno il 6% del volume, ottimo!
Ma non è tutto roseo — infatti non possiamo cambiare l'ordine dei campi negli indici, e quindi nel "complesso" (pg_total_relation_size)…
… e qui comunque abbiamo risparmiato l'1.5%, senza cambiare una riga di codice. Esatto!
Voglio sottolineare che la disposizione dei campi sopra riportata non è necessariamente la più ottimale. Infatti, alcuni blocchi di campi non vogliamo «spezzarli» per motivi estetici — ad esempio, una coppia (pack, recno), che costituisce la PK per questa tabella.
Nel complesso, definire una disposizione «minimale» dei campi è un compito relativamente semplice di «esplorazione». Pertanto, puoi ottenere risultati anche migliori dei nostri con i tuoi dati — prova!
Fonte: habr.com
