Recentemente eu contei como, usando receitas padrão do banco de dados PostgreSQL. Hoje vamos falar sobre como a gravação pode ser feita com mais eficiência no banco de dados sem usar nenhuma “reviravolta” na configuração - simplesmente organizando corretamente os fluxos de dados.
#1. Seccionando
Um artigo sobre como e por que vale a pena organizar já foi, aqui falaremos sobre a prática de aplicar algumas abordagens dentro do nosso .
"Coisas de dias passados..."
Inicialmente, como qualquer MVP, nosso projeto começou com uma carga bastante leve - o monitoramento era realizado apenas para os dez servidores mais críticos, todas as tabelas eram relativamente compactas... Mas com o passar do tempo, o número de hosts monitorados tornou-se cada vez maior , e mais uma vez tentamos fazer algo com um dos tabelas com 1.5 TB de tamanho, percebemos que embora fosse possível continuar vivendo assim, era muito inconveniente.
Os tempos eram quase épicos, diferentes versões do PostgreSQL 9.x eram relevantes, então todo o particionamento tinha que ser feito “manualmente” - através de herança de tabela e gatilhos roteamento com dinâmica EXECUTE.
A solução resultante acabou sendo universal o suficiente para poder ser traduzida para todas as tabelas:
- Foi declarada uma tabela pai de “cabeçalho” vazia, que descrevia todos índices e gatilhos necessários.
- O registro do ponto de vista do cliente foi feito na tabela “raiz”, e internamente utilizando gatilho de roteamento
BEFORE INSERTo registro foi inserido “fisicamente” na seção exigida. Se ainda não existia, pegamos uma exceção e... - … usando foi criado com base no modelo da tabela pai seção com restrição na data desejadapara que quando os dados forem recuperados, a leitura seja realizada apenas neles.
PG10: primeira tentativa
Mas o particionamento por herança não tem sido historicamente adequado para lidar com um fluxo de gravação ativo ou com um grande número de partições filhas. Por exemplo, você pode lembrar que o algoritmo para selecionar a seção necessária tinha complexidade quadrática, que funciona com mais de 100 seções, você mesmo entende como...
No PG10 esta situação foi bastante optimizada através da implementação de medidas de apoio . Portanto, tentamos aplicá-lo imediatamente após a migração do armazenamento, mas...
Como descobrimos depois de pesquisar o manual, a tabela particionada nativamente nesta versão é:
- não suporta descrições de índice
- não suporta gatilhos nele
- não pode ser “descendente” de ninguém
- não suporta
INSERT ... ON CONFLICT - não é possível gerar uma seção automaticamente
Tendo recebido uma dolorosa pancada na testa com um ancinho, percebemos que seria impossível fazer sem modificar a aplicação e adiamos novas pesquisas por seis meses.
PG10: segunda chance
Então, começamos a resolver os problemas que surgiram um por um:
- Porque os gatilhos e
ON CONFLICTDescobrimos que ainda precisávamos deles aqui e ali, então fizemos um estágio intermediário para resolvê-los tabela proxy. - Livre-se do "roteamento" em gatilhos - isto é, de
EXECUTE. - Eles tiraram separadamente tabela de modelo com todos os índicespara que eles nem estejam presentes na tabela de proxy.
Por fim, depois de tudo isso, particionamos a tabela principal de forma nativa. A criação de uma nova seção ainda fica a cargo da consciência do aplicativo.
Dicionários de “serrar”
Como em qualquer sistema analítico, também tivemos "fatos" e "cortes" (dicionários). No nosso caso, nesta qualidade atuaram, por exemplo, consultas lentas semelhantes ou o texto da própria consulta.
Os “fatos” já eram seccionados por dia há muito tempo, então excluímos com calma as seções desatualizadas e elas não nos incomodaram (registros!). Mas houve um problema com os dicionários...
Não quer dizer que eram muitos, mas aproximadamente 100 TB de “fatos” resultaram em um dicionário de 2.5 TB. Você não pode excluir convenientemente nada dessa tabela, não pode compactá-la no tempo adequado e escrever nela gradualmente se torna mais lento.
Como um dicionário... nele cada entrada deve ser apresentada exatamente uma vez... e isso está correto, mas!.. Ninguém nos impede de ter um dicionário separado para cada dia! Sim, isso traz uma certa redundância, mas permite:
- escrever/ler mais rápido devido ao tamanho menor da seção
- consumir menos memória trabalhando com índices mais compactos
- armazenar menos dados devido à capacidade de remover rapidamente desatualizados
Como resultado de todo o complexo de medidas A carga da CPU diminuiu cerca de 30%, a carga do disco cerca de 50%:
Ao mesmo tempo, continuamos a escrever exatamente a mesma coisa no banco de dados, só que com menos carga.
#2. Evolução e refatoração do banco de dados
Então decidimos o que temos cada dia tem sua própria seção com dados. Na verdade, CHECK (dt = '2018-10-12'::date) — e há uma chave de particionamento e a condição para que um registro caia em uma seção específica.
Como todos os relatórios em nosso serviço são construídos no contexto de uma data específica, os índices para eles desde “tempos não particionados” têm sido de todos os tipos (Servidor, Data, Modelo de plano), (Servidor, Data, nó Plano), (Data, Classe de erro, Servidor), ...
Mas agora eles vivem em todas as seções suas cópias cada um desses índices... E dentro de cada seção a data é uma constante... Acontece que agora estamos em cada um desses índices basta inserir uma constante como um dos campos, o que aumenta tanto o volume quanto o tempo de busca, mas não traz nenhum resultado. Eles deixaram o ancinho para eles mesmos, opa...
A direção da otimização é óbvia - simples remova o campo de data de todos os índices em tabelas particionadas. Dados os nossos volumes, o ganho é de cerca de 1TB/semana!
Agora notamos que esse terabyte ainda precisava ser gravado de alguma forma. Ou seja, nós também o disco agora deve carregar menos! Esta imagem mostra claramente o efeito obtido com a limpeza, à qual dedicamos uma semana:
#3. “Espalhando” o pico de carga
Um dos grandes problemas dos sistemas carregados é sincronização redundante algumas operações que não o exigem. Às vezes “porque não perceberam”, às vezes “era mais fácil assim”, mas mais cedo ou mais tarde você tem que se livrar disso.
Vamos ampliar a imagem anterior e ver que temos um disco “bombas” sob a carga com amplitude dupla entre amostras adjacentes, o que claramente “estatisticamente” não deveria acontecer com tal número de operações:
Isso é muito fácil de conseguir. Já começamos a monitorar quase 1000 servidores, cada um é processado por um thread lógico separado, e cada thread redefine as informações acumuladas para serem enviadas ao banco de dados em uma determinada frequência, algo assim:
setInterval(sendToDB, interval)O problema aqui reside precisamente no fato de que todos os threads começam aproximadamente ao mesmo tempo, portanto, os horários de envio quase sempre coincidem “direto ao ponto”. Ops #2...
Felizmente, isso é muito fácil de corrigir, adicionando uma corrida “aleatória” por tempo:
setInterval(sendToDB, interval * (1 + 0.1 * (Math.random() - 0.5)))#4. Armazenamos em cache o que precisamos
O terceiro problema tradicional de alta carga é sem cache onde ele está poderia ser.
Por exemplo, tornamos possível analisar em termos de nós do plano (todos estes Seq Scan on users), mas imediatamente penso que eles são, em sua maior parte, iguais - eles esqueceram.
Não, claro, nada é gravado no banco de dados novamente, isso interrompe o gatilho com INSERT ... ON CONFLICT DO NOTHING. Mas esses dados ainda chegam ao banco de dados e são desnecessários lendo para verificar se há conflito tem de fazer. Ops #3...
A diferença no número de registros enviados ao banco de dados antes/depois da habilitação do cache é óbvia:
E esta é a queda concomitante na carga de armazenamento:
No total
“Terabyte por dia” parece assustador. Se você fizer tudo certo, então é só 2 ^ 40 bytes/86400 segundos = ~ 12.5 MB/sque até os parafusos IDE de desktop aguentaram. 🙂
Mas falando sério, mesmo com uma “inclinação” de carga dez vezes maior durante o dia, você pode facilmente atender aos recursos dos SSDs modernos.
Fonte: habr.com