Aforra un centavo en grandes volumes en PostgreSQL

Continuando co tema da gravación de grandes fluxos de datos xerados por artigo anterior sobre a partición, neste miraremos as formas en que podes reducir o tamaño "físico" do almacenado en PostgreSQL e o seu impacto no rendemento do servidor.

Xa falaremos Configuración de TOAST e aliñamento de datos. "De media", estes métodos non aforrarán demasiados recursos, pero sen modificar o código da aplicación.

Non obstante, a nosa experiencia resultou moi produtiva neste sentido, xa que o almacenamento de case calquera monitorización pola súa natureza é na súa maioría só anexo en canto aos datos rexistrados. E se estás a preguntar como podes ensinarlle á base de datos a escribir no disco 200MB / s a metade - por favor, baixo gato.

Pequenos segredos do big data

Por perfil laboral o noso servizo, voan regularmente para el desde as guaridas paquetes de texto.

E desde entón Complexo VLSIcuxa base de datos monitorizamos é un produto de varios compoñentes con estruturas de datos complexas, despois consultas para o máximo rendemento resulta bastante así "multi-volume" con lóxica algorítmica complexa. Así, o volume de cada instancia individual dunha solicitude ou o plan de execución resultante no rexistro que nos chega resulta ser "de media" bastante grande.

Vexamos a estrutura dunha das táboas nas que escribimos datos "en bruto", é dicir, aquí está o texto orixinal da entrada do rexistro:

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

Un sinal típico (xa seccionado, por suposto, polo que este é un modelo de sección), onde o máis importante é o texto. Ás veces bastante voluminoso.

Lembre que o tamaño "físico" dun rexistro nun PG non pode ocupar máis dunha páxina de datos, pero o tamaño "lóxico" é unha cuestión completamente diferente. Para escribir un valor volumétrico (varchar/text/bytea) nun campo, use Tecnoloxía TOAST:

PostgreSQL usa un tamaño de páxina fixo (normalmente 8 KB) e non permite que as tuplas abranguen varias páxinas. Polo tanto, é imposible almacenar directamente valores de campo moi grandes. Para superar esta limitación, os valores de campo grandes comprímense e/ou divídense en varias liñas físicas. Isto pasa desapercibido para o usuario e ten pouco impacto na maioría do código do servidor. Este método coñécese como TOAST...

De feito, para cada táboa con campos "potencialmente grandes", automaticamente créase unha táboa pareada con "slicing". cada rexistro "grande" en segmentos de 2 KB:

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

É dicir, se temos que escribir unha cadea cun valor "grande". data, entón producirase a gravación real non só á mesa principal e ao seu PK, senón tamén ao TOAST e ao seu PK.

Redución da influencia de TOAST

Pero a maioría dos nosos discos aínda non son tan grandes, debería caber en 8KB - Como podo aforrar diñeiro nisto?...

Aquí é onde o atributo vén na nosa axuda STORAGE na columna da táboa:

• AMPLIADO permite tanto a compresión como o almacenamento separado. Isto opción estándar para a maioría dos tipos de datos compatibles con TOAST. Primeiro tenta realizar a compresión e despois gárdao fóra da táboa se a fila aínda é demasiado grande.
• PRINCIPAL permite a compresión pero non o almacenamento separado. (De feito, aínda se realizará o almacenamento separado para tales columnas, pero só como último recurso, cando non hai outra forma de encoller a cadea para que quepa na páxina.)

De feito, isto é exactamente o que necesitamos para o texto: comprimilo o máximo posible, e se non cabe nada, méteo en TOAST. Isto pódese facer directamente sobre a marcha, cun comando:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Como avaliar o efecto

Dado que o fluxo de datos cambia cada día, non podemos comparar números absolutos, senón en termos relativos participación menor Apuntámolo en TOAST, moito mellor. Pero hai un perigo aquí: canto maior sexa o volume "físico" de cada rexistro individual, máis "amplo" se fai o índice, porque temos que cubrir máis páxinas de datos.

Sección antes de cambios:

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

Sección despois de cambios:

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

De feito, nós comezou a escribir para TOAST 2 veces menos, que descargou non só o disco, senón tamén a CPU:

Observarei que tamén nos facemos máis pequenos ao "ler" o disco, non só ao "escribir", xa que ao inserir un rexistro nunha táboa, tamén temos que "ler" parte da árbore de cada índice para determinar o seu posición futura neles.

Quen pode vivir ben en PostgreSQL 11

Despois de actualizar a PG11, decidimos seguir “afinando” TOAST e observamos que a partir desta versión o parámetro toast_tuple_target:

O código de procesamento TOAST só se activa cando o valor da fila que se vai almacenar na táboa é maior que TOAST_TUPLE_THRESHOLD bytes (normalmente 2 KB). O código TOAST comprimirá e/ou moverá os valores dos campos fóra da táboa ata que o valor da fila sexa inferior a TOAST_TUPLE_TARGET bytes (valor variable, tamén normalmente 2 KB) ou o tamaño non se pode reducir.

Decidimos que os datos que temos habitualmente son “moi curtos” ou “moi longos”, polo que decidimos limitarnos ao mínimo valor posible:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Vexamos como afectaron a nova configuración á carga do disco despois da reconfiguración:

Non está mal! Media a cola no disco diminuíu aproximadamente 1.5 veces, e o disco "ocupado" é un 20 por cento! Pero quizais isto afectou dalgún xeito á CPU?

Polo menos non foi peor. Aínda que, é difícil xulgar se mesmo tales volumes aínda non poden aumentar a carga media da CPU 5%.

Ao cambiar os lugares dos termos, a suma... cambia!

Como sabes, un céntimo aforra un rublo, e cos nosos volumes de almacenamento trátase 10 TB/mes incluso unha pequena optimización pode dar un bo beneficio. Polo tanto, prestamos atención á estrutura física dos nosos datos - como exactamente campos "apilados" dentro do rexistro cada unha das táboas.

Porque por mor de aliñación de datos isto é directo afecta o volume resultante:

Moitas arquitecturas proporcionan o aliñamento de datos nos límites das palabras da máquina. Por exemplo, nun sistema x32 de 86 bits, os números enteiros (tipo enteiro, 4 bytes) aliñaranse nun límite de palabras de 4 bytes, así como os números de coma flotante de precisión dobre (coma flotante de dobre precisión, 8 bytes). E nun sistema de 64 bits, os valores dobres aliñaranse cos límites de palabras de 8 bytes. Este é outro motivo de incompatibilidade.

Debido ao aliñamento, o tamaño dunha fila da táboa depende da orde dos campos. Normalmente este efecto non é moi perceptible, pero nalgúns casos pode levar a un aumento significativo de tamaño. Por exemplo, se mesturas char(1) e campos enteiros, normalmente haberá 3 bytes desperdiciados entre eles.

Comecemos cos modelos sintéticos:

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

De onde saíron un par de bytes adicionais no primeiro caso? É sinxelo - Smallint de 2 bytes aliñados no límite de 4 bytes antes do seguinte campo, e cando é o último, non hai nada nin necesidade de aliñar.

En teoría, todo está ben e podes reorganizar os campos como queiras. Comprobámolo en datos reais co exemplo dunha das táboas, cuxa sección diaria ocupa 10-15 GB.

Estrutura 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ón despois de cambiar a orde das columnas - exactamente mesmos campos, só orde diferente:

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)

O volume total da sección está determinado polo número de "feitos" e depende só de procesos externos, así que imos dividir o tamaño do montón (pg_relation_size) polo número de rexistros nel, é dicir, obtemos tamaño medio do rexistro real almacenado:

Menos 6% de volume, xenial!

Pero todo, por suposto, non é tan rosado - despois de todo, nos índices non podemos cambiar a orde dos campos, e polo tanto "en xeral" (pg_total_relation_size) ...

... aínda aquí tamén aforrou un 1.5%sen cambiar unha soa liña de código. Si, si!

Observo que a opción anterior para organizar campos non é o feito de que sexa a máis óptima. Porque non queres "arrancar" algúns bloques de campos por razóns estéticas, por exemplo, un par (pack, recno), que é o PK desta táboa.

En xeral, determinar a disposición "mínima" dos campos é unha tarefa de "forza bruta" bastante sinxela. Polo tanto, podes obter resultados aínda mellores cos teus datos que os nosos: probalo!

Fonte: www.habr.com