Continuando il tema della registrazione di grandi flussi di dati sollevato , in questo esamineremo i modi in cui è possibile ridurre le dimensioni "fisiche" dei dati memorizzati in PostgreSQL e il loro impatto sulle prestazioni del server.
Si parlerà di impostazioni TOAST e allineamento dei dati. "In media", questi metodi permetteranno un risparmio non troppo significativo di risorse, ma — senza modificare il codice dell'applicazione.
Tuttavia, la nostra esperienza si è rivelata piuttosto produttiva in questo senso, poiché lo storage di quasi ogni monitoraggio è per sua natura per lo più append-only dal punto di vista dei dati scritti. E se siete interessati a sapere come si può insegnare al database a scrivere su disco invece di 200MB/s in metà — vi invito a continuare a leggere.
Piccole segrete sui grandi dati
Per il profilo di lavoro riceve regolarmente dai log pacchetti di testo.
E poiché le cui basi dati monitoriamo, è un prodotto multi-componente con strutture dati complesse, le richieste per raggiungere prestazioni massime risultano piuttosto Quindi anche il volume di ciascun singolo esempio di richiesta o del piano di esecuzione risultante nel log che ci arriva risulta "in media" piuttosto grande.
Diamo un'occhiata alla struttura di una delle tabelle in cui scriviamo i "dati grezzi" — ovvero il testo originale registrato nel 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, ovviamente, quindi questo è un modello di sezione), dove la cosa più importante è il testo. A volte è abbastanza voluminoso.
Ricordiamo che la dimensione "fisica" di una registrazione in PG non può occupare più di una pagina di dati, ma la dimensione "logica" è tutta un'altra cosa. Per scrivere un valore voluminoso in un campo (varchar/text/bytea) si utilizza :
PostgreSQL utilizza una dimensione fissa della pagina (di solito 8 KB) e non consente ai tuple di occupare più pagine. Pertanto, non è possibile memorizzare direttamente valori molto grandi nei campi. Per superare questa limitazione, i valori grandi dei campi vengono compressi e/o suddivisi in più righe fisiche. Questo avviene senza che l'utente se ne accorga e influisce in modo insignificante sulla maggior parte del codice del server. Questo metodo è noto come TOAST …
In effetti, per ogni tabella con campi "potenzialmente grandi" viene automaticamente di ogni record "grande" in segmenti 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 registrazione reale avverrà non solo nella tabella principale e nel suo PK, ma anche in TOAST e nel suo PK.
Riduciamo l'influenza di TOAST
Ma la maggior parte dei record non è comunque così grande, devono rimanere entro 8KB — come possiamo risparmiare su questo?..
Qui ci viene in aiuto l'attributo per la 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 si tenta la compressione, poi si procede al salvataggio 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 adattarsi alla pagina.)
In effetti, questo è esattamente ciò di cui abbiamo bisogno per il testo — massimamente comprimere, e se proprio non riesce a stare — 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é ogni giorno il flusso di dati cambia, non possiamo confrontare cifre assolute, ma in termini relativi, quanto minore è la quota che abbiamo registrato in TOAST — tanto meglio. Ma qui c'è un rischio: maggiore è il nostro volume "fisico" di ogni registrazione, tanto più "ampio" diventa l'indice, perché dobbiamo coprire un maggior numero 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, abbiamo iniziato a scrivere in TOAST la metà delle volte in meno, che ha alleggerito non solo il disco, ma anche la CPU:
Osserverò che abbiamo anche iniziato a "leggere" meno il disco, non solo a "scrivere" - poiché durante l'inserimento di una riga in una tabella è necessario "leggere" anche parte dell'albero di ciascun indice per determinare la sua futura posizione in essi.
A chi vive bene con PostgreSQL 11
Dopo l'aggiornamento a PG11 abbiamo deciso di continuare a "ottimizzare" TOAST e abbiamo notato che, a partire da questa versione, è stato reso disponibile per la configurazione il parametro :
Il codice di gestione di TOAST si attiva solo quando il valore della riga da memorizzare nella tabella supera il limite di TOAST_TUPLE_THRESHOLD byte (di solito 2 KB). Il codice TOAST comprimerà e/o sposterà i valori del campo al di fuori della tabella fino a quando il valore della riga non scenderà al di sotto di TOAST_TUPLE_TARGET byte (una dimensione variabile, anch'essa di solito 2 KB) o ridurre il volume diventerà impossibile.
Abbiamo deciso che i nostri dati sono di solito "totalmente brevi" o "veramente lunghi", quindi abbiamo deciso di limitarci al valore minimo possibile:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Diamo un'occhiata a come le nuove impostazioni hanno influito sul caricamento del disco dopo la riconfigurazione:
Non male! La media della coda verso il disco è diminuita circa di 1,5 volte, e la "occupazione" del disco - di circa il 20%! Ma forse questo ha influito anche sulla CPU?
Almeno, di certo non è peggiorato. Anche se, è difficile giudicare se anche questi volumi non riescono a sollevare il carico medio della CPU oltre 5%.
Dal cambio di posti 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. Pertanto, abbiamo prestato attenzione alla struttura fisica dei nostri dati - come sono concretamente "stratificati" i campi all'interno di ogni registrazione di ciascuna delle tabelle.
Perché a causa di questo influisce direttamente :
Molte architetture prevedono l'allineamento dei dati sui confini delle parole del computer. Ad esempio, su un sistema a 32 bit x86, i numeri interi (tipo intero, che occupa 4 byte) saranno allineati al confine delle parole da 4 byte, così come i numeri in virgola mobile a doppia precisione (tipo doppia precisione, 8 byte). E su un sistema a 64 bit i valori doppi saranno allineati al confine delle parole da 8 byte. Questa è un'altra ragione per l'incompatibilità.
A causa dell'allineamento, la dimensione della riga della 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 notevole aumento della dimensione. Ad esempio, se si mescolano campi di tipo char(1) e integer, tra di essi di solito andranno persi 3 byte senza motivo.
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 sono arrivati quei due byte extra nel primo caso? È semplice — un smallint di 2 byte si allinea su un confine di 4 byte prima del campo successivo, e quando è l'ultimo, non c'è niente da allineare e non serve farlo.
In teoria, va tutto bene e si possono spostare i campi come si vuole. Verifichiamo con dati reali usando un esempio di una delle tabelle, la cui sezione giornaliera occupa da 10 a 15 GB.
Struttura originale:
CREATE TABLE public.plan_20190220
(
-- Eredita dalla tabella plan: pack uuid NOT NULL,
-- Eredita dalla tabella plan: recno smallint NOT NULL,
-- Eredita dalla tabella plan: host uuid,
-- Eredita dalla tabella plan: ts timestamp with time zone,
-- Eredita dalla tabella plan: exectime numeric(32,3),
-- Eredita dalla tabella plan: duration numeric(32,3),
-- Eredita dalla tabella plan: bufint bigint,
-- Eredita dalla tabella plan: bufmem bigint,
-- Eredita dalla tabella plan: bufdsk bigint,
-- Eredita dalla tabella plan: apn uuid,
-- Eredita dalla tabella plan: ptr uuid,
-- Eredita dalla tabella 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 aver modificato l'ordine delle colonne — esattamente gli stessi campi, solo con un ordine diverso:
CREATE TABLE public.plan_20190221
(
-- Eredita dalla tabella plan: dt date NOT NULL,
-- Eredita dalla tabella plan: ts timestamp with time zone,
-- Eredita dalla tabella plan: pack uuid NOT NULL,
-- Eredita dalla tabella plan: recno smallint NOT NULL,
-- Eredita dalla tabella plan: host uuid,
-- Eredita dalla tabella plan: apn uuid,
-- Eredita dalla tabella plan: ptr uuid,
-- Eredita dalla tabella plan: bufint bigint,
-- Eredita dalla tabella plan: bufmem bigint,
-- Eredita dalla tabella plan: bufdsk bigint,
-- Eredita dalla tabella plan: exectime numeric(32,3),
-- Eredita 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 complessivo della sezione è determinato dal numero di «fatti» e dipende solo dai processi esterni, quindi dividiamo la dimensione dell'heap (pg_relation_size) al numero di registrazioni in esso — quindi otterremo la dimensione media della registrazione reale memorizzata:
Meno il 6% del volume, ottimo!
Ma tutto, naturalmente, non è così roseo — infatti non possiamo cambiare l'ordine dei campi negli indici, e quindi «in generale» (pg_total_relation_size)…
… comunque anche qui abbiamo risparmiato l'1,5%, senza modificare una riga di codice. Proprio così!
Nota che la disposizione dei campi sopra riportata non è detto che sia la più ottimale. Perché alcuni blocchi di campi non vogliamo «spezzarli» per motivi estetici — ad esempio, una coppia (pack, recno), che è la PK per questa tabella.
In generale, la definizione di una disposizione «minimale» dei campi è un compito piuttosto semplice di «permutazione». Pertanto, puoi ottenere risultati anche migliori sui tuoi dati rispetto a noi — prova!
Fonte: habr.com
