Risparmia un centesimo su grandi volumi in PostgreSQL

Continuando il tema della registrazione di grandi flussi di dati sollevati da articolo precedente sul partizionamento, in questo esamineremo i modi in cui puoi farlo ridurre la dimensione “fisica” dello storage in PostgreSQL e il loro impatto sulle prestazioni del server.

Ne parleremo Impostazioni TOAST e allineamento dei dati. “In media”, questi metodi non faranno risparmiare troppe risorse, ma senza modificare affatto il codice dell’applicazione.

Tuttavia, la nostra esperienza si è rivelata molto produttiva in questo senso, poiché per sua natura l'archiviazione di quasi tutti i monitoraggi lo è per lo più solo in aggiunta in termini di dati registrati. E se ti stai chiedendo come insegnare invece al database a scrivere su disco 200MB / s la metà - per favore sotto cat.

Piccoli segreti dei big data

Per profilo professionale nostro servizio, volano regolarmente da lui dalle tane pacchetti di testo.

E da allora Complesso VLSIil cui database che monitoriamo è un prodotto multicomponente con strutture dati complesse, quindi interroga per le massime prestazioni risultare proprio così “multivolume” con logica algoritmica complessa. Quindi il volume di ogni singola istanza di una richiesta o del conseguente piano di esecuzione nel log che ci arriva risulta essere “in media” piuttosto grande.

Diamo un'occhiata alla struttura di una delle tabelle in cui scriviamo i dati "grezzi", ovvero ecco il testo originale della voce di registro:

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

Un tipico cartello (già sezionato, ovviamente, quindi questo è un modello di sezione), dove la cosa più importante è il testo. A volte piuttosto voluminoso.

Ricordiamo che la dimensione “fisica” di un record in un PG non può occupare più di una pagina di dati, ma la dimensione “logica” è una questione completamente diversa. Per scrivere un valore volumetrico (varchar/text/bytea) in un campo, utilizzare Tecnologia TOAST:

PostgreSQL utilizza una dimensione di pagina fissa (tipicamente 8 KB) e non consente alle tuple di estendersi su più pagine. Pertanto, è impossibile memorizzare direttamente valori di campo molto grandi. Per superare questa limitazione, i valori di campo di grandi dimensioni vengono compressi e/o suddivisi su più linee fisiche. Ciò avviene senza che l'utente se ne accorga e ha un impatto minimo sulla maggior parte del codice del server. Questo metodo è noto come TOAST...

Infatti, per ogni tabella con campi "potenzialmente grandi", automaticamente viene creata una tabella abbinata con “slicing”. 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 stringa con un valore “grande”. data, avrà luogo la registrazione vera e propria non solo al tavolo principale e al suo PK, ma anche a TOAST e al suo PK.

Ridurre l'influenza del TOAST

Ma la maggior parte dei nostri dischi non sono ancora così grandi, dovrebbe contenere 8KB - Come posso risparmiare su questo?..

È qui che l'attributo ci viene in aiuto STORAGE nella colonna della tabella:

• EXTENDED consente sia la compressione che l'archiviazione separata. Questo opzione standard per la maggior parte dei tipi di dati conformi a TOAST. Innanzitutto tenta di eseguire la compressione, quindi la memorizza all'esterno della tabella se la riga è ancora troppo grande.
• PRINCIPALI consente la compressione ma non l'archiviazione separata. (In effetti, per tali colonne verrà comunque eseguita un'archiviazione separata, ma solo come ultima opzione, quando non c'è altro modo per ridurre la stringa in modo che si adatti alla pagina.)

In effetti, questo è esattamente ciò di cui abbiamo bisogno per il testo: comprimilo il più possibile e, se non ci entra affatto, mettilo in TOAST. Questo può essere fatto direttamente al volo, con un 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 numeri assoluti, ma in termini relativi quota minore Lo abbiamo scritto in TOAST: tanto meglio. Ma qui c'è un pericolo: maggiore è il volume "fisico" di ogni singolo record, più "ampio" diventa l'indice, perché dobbiamo coprire più pagine di dati.

Секция prima dei cambiamenti:

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

Секция dopo le modifiche:

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

In effetti, noi ho iniziato a scrivere su TOAST 2 volte meno spesso, che ha scaricato non solo il disco, ma anche la CPU:

Noterò che siamo diventati più piccoli anche nella “lettura” del disco, non solo nella “scrittura” - poiché quando inseriamo un record in una tabella, dobbiamo anche “leggere” parte dell'albero di ciascun indice per determinarne posizione futura in essi.

Chi può vivere bene con PostgreSQL 11

Dopo l'aggiornamento a PG11, abbiamo deciso di continuare a “sintonizzare” TOAST e abbiamo notato che a partire da questa versione il parametro toast_tuple_target:

Il codice di elaborazione TOAST viene attivato solo quando il valore della riga da archiviare nella tabella è maggiore di byte TOAST_TUPLE_THRESHOLD (solitamente 2 KB). Il codice TOAST comprimerà e/o sposterà i valori del campo fuori dalla tabella finché il valore della riga non diventa inferiore a TOAST_TUPLE_TARGET byte (valore variabile, anch'esso solitamente 2 KB) o la dimensione non può essere ridotta.

Abbiamo deciso che i dati di cui disponiamo solitamente sono “molto brevi” o “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 influenzato il caricamento del disco dopo la riconfigurazione:

Non male! Media la coda sul disco è diminuita circa 1.5 volte e il disco "occupato" è al 20%! Ma forse questo ha in qualche modo influenzato la CPU?

Almeno non è andata peggio. Tuttavia, è difficile giudicare se anche tali volumi non riescano ad aumentare il carico medio della CPU 5%.

Cambiando la posizione dei termini la somma... cambia!

Come sai, un centesimo fa risparmiare un rublo e con i nostri volumi di stoccaggio è tutto 10TB/mese anche una piccola ottimizzazione può dare un buon profitto. Pertanto, abbiamo prestato attenzione alla struttura fisica dei nostri dati: come esattamente Campi “impilati” all’interno del record ciascuno dei tavoli.

Perché a causa di allineamento dei dati questo è semplice influisce sul volume risultante:

Molte architetture forniscono l'allineamento dei dati sui confini delle parole macchina. Ad esempio, su un sistema x32 a 86 bit, gli interi (tipo intero, 4 byte) verranno allineati su un limite di parola di 4 byte, così come i numeri in virgola mobile a doppia precisione (virgola mobile a doppia precisione, 8 byte). E su un sistema a 64 bit, i valori doppi verranno allineati ai limiti delle parole di 8 byte. Questo è un altro motivo di incompatibilità.

A causa dell'allineamento, la dimensione di una riga della tabella dipende dall'ordine dei campi. Solitamente questo effetto non è molto evidente, ma in alcuni casi può portare ad un notevole aumento delle dimensioni. Ad esempio, se si combinano campi char(1) e interi, in genere verranno sprecati 3 byte tra di essi.

Cominciamo con i modelli 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 dove provengono un paio di byte in più nel primo caso? È semplice - Smallint di 2 byte allineato sul limite di 4 byte prima del campo successivo, e quando è l'ultimo, non c'è niente e non c'è bisogno di allinearsi.

In teoria va tutto bene e puoi riorganizzare i campi come preferisci. Controlliamolo su dati reali usando l'esempio di una delle tabelle, la cui sezione giornaliera occupa 10-15 GB.

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 dopo aver modificato l'ordine delle colonne - esattamente stessi campi, solo ordine diverso:

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)

Il volume totale della sezione è determinato dal numero di “fatti” e dipende solo da processi esterni, quindi dividiamo la dimensione dell’heap (pg_relation_size) in base al numero di record in esso contenuti, ovvero otteniamo dimensione media del record effettivamente memorizzato:

Meno 6% di volume, Grande!

Ma ovviamente non tutto è così roseo - dopo tutto, negli indici non possiamo cambiare l'ordine dei campi, e quindi “in generale” (pg_total_relation_size) ...

...anche qui ancora risparmiato 1.5%senza modificare una sola riga di codice. Si si!

Noto che l'opzione di cui sopra per organizzare i campi non è il fatto che sia la più ottimale. Perché non vuoi "strappare" alcuni blocchi di campi per motivi estetici, ad esempio un paio (pack, recno), che è il PK per questa tabella.

In generale, determinare la disposizione “minima” dei campi è un compito di “forza bruta” abbastanza semplice. Pertanto, puoi ottenere risultati ancora migliori dai tuoi dati rispetto ai nostri: provalo!

Fonte: habr.com