Continuando o tópico de registro de grandes fluxos de dados levantado por , neste veremos as maneiras pelas quais você pode reduzir o tamanho “físico” do armazenado no PostgreSQL e seu impacto no desempenho do servidor.
Nós falaremos sobre Configurações do TOAST e alinhamento de dados. “Em média”, esses métodos não economizarão muitos recursos, mas sem modificar em nada o código do aplicativo.
No entanto, a nossa experiência revelou-se muito produtiva neste aspecto, uma vez que o armazenamento de quase qualquer monitorização pela sua natureza é principalmente apenas para acréscimos em termos de dados registrados. E se você está se perguntando como pode ensinar o banco de dados a gravar no disco 200MB / s metade - por favor, em gato.
Pequenos segredos do big data
Por perfil de trabalho , eles voam regularmente para ele dos covis pacotes de texto.
E desde cujo banco de dados monitoramos é um produto multicomponente com estruturas de dados complexas, então consultas para desempenho máximo ficar bem assim . Portanto, o volume de cada instância individual de uma solicitação ou do plano de execução resultante no log que chega até nós acaba sendo “em média” bastante grande.
Vejamos a estrutura de uma das tabelas nas quais escrevemos dados “brutos” - ou seja, aqui está o texto original da entrada de log:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ключ секции
date
, data -- самое главное
text
, PRIMARY KEY(pack, recno)
);Uma placa típica (já seccionada, claro, então este é um modelo de seção), onde o mais importante é o texto. Às vezes bastante volumoso.
Lembre-se de que o tamanho “físico” de um registro em um PG não pode ocupar mais de uma página de dados, mas o tamanho “lógico” é uma questão completamente diferente. Para escrever um valor volumétrico (varchar/text/bytea) em um campo, use :
O PostgreSQL usa um tamanho de página fixo (normalmente 8 KB) e não permite que tuplas ocupem várias páginas. Portanto, é impossível armazenar diretamente valores de campos muito grandes. Para superar essa limitação, valores de campos grandes são compactados e/ou divididos em múltiplas linhas físicas. Isso acontece despercebido pelo usuário e tem pouco impacto na maior parte do código do servidor. Este método é conhecido como TOAST...
Na verdade, para cada tabela com campos "potencialmente grandes", automaticamente cada registro “grande” em segmentos de 2 KB:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
Isto é, se tivermos que escrever uma string com um valor “grande” data, então a gravação real ocorrerá não só para a mesa principal e seu PK, mas também para TOAST e seu PK.
Reduzindo a influência do TOAST
Mas a maioria dos nossos discos ainda não são tão grandes, deve caber em 8 KB - Como posso economizar dinheiro com isso?
É aqui que o atributo vem em nosso auxílio na coluna da tabela:
- ESTENDIDO permite compactação e armazenamento separado. Esse opção padrão para a maioria dos tipos de dados compatíveis com TOAST. Ele primeiro tenta executar a compactação e depois a armazena fora da tabela se a linha ainda for muito grande.
- A PRINCIPAL permite compactação, mas não armazenamento separado. (Na verdade, o armazenamento separado ainda será realizado para essas colunas, mas apenas como último recurso, quando não há outra maneira de reduzir a string para que ela caiba na página.)
Na verdade, é exatamente disso que precisamos para o texto - comprima-o o máximo possível e, se não couber, coloque-o no TOAST. Isso pode ser feito imediatamente, com um comando:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;Como avaliar o efeito
Como o fluxo de dados muda todos os dias, não podemos comparar números absolutos, mas em termos relativos participação menor Escrevemos no TOAST - tanto melhor. Mas há aqui um perigo – quanto maior o volume “físico” de cada registo individual, mais “amplo” se torna o índice, porque temos de cobrir mais páginas de dados.
Секция antes das mudanças:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
Секция depois das mudanças:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)Na verdade, nós comecei a escrever para o TOAST 2 vezes menos, que descarregou não só o disco, mas também a CPU:
Observo que também nos tornamos menores na “leitura” do disco, não apenas na “escrita” - já que ao inserir um registro em uma tabela, também temos que “ler” parte da árvore de cada índice para determinar seu posição futura neles.
Quem pode viver bem no PostgreSQL 11
Após atualizar para PG11, decidimos continuar “ajustando” o TOAST e percebemos que a partir desta versão o parâmetro ficou disponível para ajuste :
O código de processamento TOAST só é acionado quando o valor da linha a ser armazenado na tabela é maior que TOAST_TUPLE_THRESHOLD bytes (geralmente 2 KB). O código TOAST compactará e/ou moverá os valores dos campos para fora da tabela até que o valor da linha se torne menor que TOAST_TUPLE_TARGET bytes (valor da variável, também geralmente 2 KB) ou o tamanho não possa ser reduzido.
Decidimos que os dados que normalmente temos são “muito curtos” ou “muito longos”, por isso decidimos limitar-nos ao valor mínimo possível:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);Vamos ver como as novas configurações afetaram o carregamento do disco após a reconfiguração:
Nada mal! Média a fila para o disco diminuiu aproximadamente 1.5 vezes e o disco “ocupado” é 20%! Mas talvez isso tenha afetado de alguma forma a CPU?
Pelo menos não piorou. Embora seja difícil avaliar se mesmo esses volumes ainda não conseguem aumentar a carga média da CPU 5%.
Ao mudar os lugares dos termos, a soma... muda!
Como você sabe, um centavo economiza um rublo e, com nossos volumes de armazenamento, trata-se de 10 TB/mês mesmo uma pequena otimização pode gerar um bom lucro. Portanto, prestamos atenção à estrutura física dos nossos dados - como exatamente Campos “empilhados” dentro do registro cada uma das tabelas.
Porque por causa isso é direto :
Muitas arquiteturas fornecem alinhamento de dados nos limites das palavras da máquina. Por exemplo, em um sistema x32 de 86 bits, os números inteiros (tipo inteiro, 4 bytes) serão alinhados em um limite de palavra de 4 bytes, assim como os números de ponto flutuante de precisão dupla (ponto flutuante de precisão dupla, 8 bytes). E em um sistema de 64 bits, os valores duplos serão alinhados aos limites das palavras de 8 bytes. Este é outro motivo de incompatibilidade.
Devido ao alinhamento, o tamanho de uma linha da tabela depende da ordem dos campos. Normalmente este efeito não é muito perceptível, mas em alguns casos pode levar a um aumento significativo no tamanho. Por exemplo, se você misturar campos char(1) e inteiros, normalmente haverá 3 bytes desperdiçados entre eles.
Vamos começar com 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 vieram alguns bytes extras no primeiro caso? É simples - Smallint de 2 bytes alinhado no limite de 4 bytes antes do próximo campo, e quando for o último, não há nada nem necessidade de alinhamento.
Em teoria está tudo bem e você pode reorganizar os campos como quiser. Vamos verificar com dados reais usando o exemplo de uma das tabelas, cuja seção diária ocupa de 10 a 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)Seção após alterar a ordem das colunas - exatamente mesmos campos, apenas ordem 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 seção é determinado pela quantidade de “fatos” e depende apenas de processos externos, então vamos dividir o tamanho do heap (pg_relation_size) pelo número de registros nele - ou seja, obtemos tamanho médio do registro armazenado real:
Menos 6% do volume, Ótimo!
Mas tudo, é claro, não é tão róseo - afinal, nos índices não podemos alterar a ordem dos campose, portanto, “em geral” (pg_total_relation_size) ...
...ainda aqui também economizou 1.5%sem alterar uma única linha de código. Sim Sim!
Observo que a opção acima para organizar os campos não é a mais ideal. Porque você não quer “rasgar” alguns blocos de campos por razões estéticas - por exemplo, alguns (pack, recno), que é o PK desta tabela.
Em geral, determinar o arranjo “mínimo” dos campos é uma tarefa bastante simples de “força bruta”. Portanto, você pode obter resultados ainda melhores com seus dados do que os nossos - experimente!
Fonte: habr.com