Olá.
Meu nome é Vanya e sou desenvolvedor Java. Acabei trabalhando muito com PostgreSQL – faço configuração de Banco de Dados, otimização de estrutura, desempenho e um pouco de DBA nos fins de semana.
Recentemente, organizei várias bases de dados em nossos microsserviços e escrevi uma biblioteca Java. , que facilita esse trabalho, economiza meu tempo e ajuda a evitar alguns erros comuns cometidos pelos desenvolvedores. É sobre essa biblioteca que falarei hoje.
Aviso Legal
A versão principal do PostgreSQL com a qual trabalho é a 10. Todas as consultas SQL que uso também foram testadas na versão 11. A versão mínima suportada é 9.6.
Antecedentes
Tudo começou quase um ano atrás com uma situação estranha para mim: a criação concorrente de um índice em um lugar tranquilo terminou com um erro. O próprio índice, como costuma acontecer, permaneceu em um estado inválido no banco. A análise dos logs mostrou a falta de . E daí em diante… Ao investigar mais a fundo, descobri um monte de problemas na configuração do Banco de Dados e, arregaçando as mangas, com brilho nos olhos, comecei a consertá-los.
O primeiro problema – configuração padrão
Talvez a metáfora sobre o Postgres que pode ser executado em uma cafeteira já esteja bastante batida, mas… a configuração padrão realmente levanta uma série de questões. Pelo menos, vale a pena prestar atenção em maintenance_work_mem, temp_file_limit, statement_timeout e lock_timeout.
No nosso caso maintenance_work_mem era por padrão 64 MB, enquanto temp_file_limit algo em torno de 2 GB – basicamente nos faltava memória para criar um índice em uma tabela grande.
Portanto, em pg-index-health compilei uma série de , na minha opinião, que vale a pena ajustar para cada Banco de Dados.
O segundo problema – índices duplicados
Nossos bancos de dados vivem em discos SSD, e usamos HA-configuração com vários data centers, um host mestre e n-um número de réplicas. O espaço no disco é um recurso muito valioso para nós; é tão importante quanto o desempenho e o consumo de CPU. Portanto, por um lado, precisamos de índices para leitura rápida, enquanto, por outro lado, não queremos ver índices desnecessários no Banco de Dados, pois eles consomem espaço e desaceleram as atualizações de dados.
Portanto, ao restaurar todos os e observando , eu decidi fazer uma "grande" limpeza. Acontece que os desenvolvedores não gostam de ler a documentação do banco de dados. Eles realmente não gostam. Por causa disso, surgem dois erros típicos - um índice criado manualmente na chave primária e um índice "manual" semelhante na coluna única. A questão é que eles não são necessários - o Postgres faz isso sozinho. Esses índices podem ser removidos sem problemas, e para isso existe um diagnóstico. .
O terceiro problema - índices sobrepostos
A maioria dos desenvolvedores iniciantes cria índices em uma única coluna. Gradualmente, após experimentarem isso, as pessoas começam a otimizar suas consultas e adicionar índices mais complexos que incluem várias colunas. Assim, surgem índices para as colunas A, A+B, A+B+C etc. Os dois primeiros desses índices podem ser descartados, pois são prefixos do terceiro. Isso também economiza bastante espaço em disco e para isso existe um diagnóstico. .
O quarto problema - chaves estrangeiras sem índices
O Postgres permite a criação de restrições de chaves estrangeiras sem especificar um índice de suporte. Em muitas situações, isso não representa um problema e pode não se manifestar… Até que uma hora…
Foi o que aconteceu conosco: em algum momento, um job que executava periodicamente e limpava a base de pedidos de teste começou a "sobrecarregar" nosso master host. O CPU e I/O dispararam, as consultas ficaram lentas e foram interrompidas por timeout, e o serviço começou a retornar erro 500. Uma rápida análise mostrou que as consultas do tipo estavam travadas:
deletar de <table> onde id em (…)Enquanto isso, o índice por id na tabela alvo, naturalmente, existia, e os registros estavam sendo removidos com poucas condições. Parecia que tudo deveria funcionar, mas, infelizmente, não funcionava.
Veio em auxílio o maravilhoso explain analyze e revelou que, além da remoção de registros na tabela alvo, ainda estava ocorrendo uma verificação de integridade referencial, e em uma das tabelas relacionadas, essa verificação falhava em um sequential scan devido à ausência de um índice adequado. Assim, surgiu o diagnóstico. .
O quinto problema - valor nulo em índices
Por padrão, o Postgres inclui valores nulos em índices btree, mas eles geralmente não são necessários. Portanto, eu me esforço para remover esses nulos (diagnóstico ), criando índices parciais em colunas que aceitam nulos do tipo where is not null. Dessa forma, consegui reduzir o tamanho de um dos nossos índices de 1877 MB para 16 KB. E em um dos serviços, o tamanho total do banco de dados foi reduzido em 16% (4,3 GB em números absolutos) ao excluir valores nulos dos índices. Uma economia colossal de espaço em disco com melhorias bastante simples. 🙂
Problema seis – ausência de chaves primárias
Devido às particularidades do mecanismo , pode ocorrer uma situação em que , quando o tamanho da sua tabela cresce rapidamente devido a um grande número de registros mortos. Eu ingenuamente pensei que isso não nos afetaria e que nosso banco não enfrentaria tal problema, afinal, nós, uau!!!, somos desenvolvedores normais... Que tolo e ingênuo eu fui...
Certa vez, uma maravilhosa migração atualizou todos os registros em uma tabela grande e frequentemente utilizada. Recebemos +100 GB no tamanho da tabela do nada. Foi extremamente frustrante, mas nossas desventuras não terminaram aí. Depois que o autovácuo foi concluído nessa tabela, após 15 horas, ficou claro que o espaço físico não retornaria. Não pudemos parar o serviço para fazer um VACUUM FULL, então decidimos usar . E então ficou claro que pg_repack não consegue lidar com tabelas sem chave primária ou outra restrição de exclusividade, e nossa tabela não tinha chave primária. Assim nasceu o diagnóstico .
Na versão da biblioteca 0.1.5 foi adicionada a capacidade de coletar dados sobre o bloat de tabelas e índices e reagir a isso em tempo hábil.
Problemas sete e oito – falta de índices e índices não utilizados
Os dois diagnósticos seguintes – e – em sua forma final, surgiram relativamente recentemente. A questão é que não era possível simplesmente adicioná-los.
Como já mencionei, usamos uma configuração com várias réplicas, e a carga de leitura em diferentes hosts é fundamentalmente diferente. O resultado é uma situação em que algumas tabelas e índices em alguns hosts praticamente não são utilizados, e para análise é necessário coletar estatísticas de todos os hosts do cluster. também precisa ser feito em cada host do cluster, não é possível fazer isso apenas no mestre.
Essa abordagem nos permitiu economizar várias dezenas de gigabytes ao remover índices que nunca foram utilizados, além de adicionar os índices ausentes em tabelas raramente usadas.
Em conclusão
Claro, praticamente todas as diagnósticas podem ser configuradas . Dessa forma, é possível implementar rapidamente verificações em seu aplicativo, prevenindo o surgimento de novos erros e, em seguida, corrigindo gradualmente os antigos.
Parte das diagnósticas pode ser executada já nos testes funcionais logo após a aplicação das migrações do banco de dados. E esta é, sem dúvida, uma das funcionalidades mais poderosas da minha biblioteca. Um exemplo de uso pode ser visto em .
Verificações de índices não utilizados ou ausentes, bem como de bloat, devem ser realizadas somente em um banco de dados real. Os valores coletados podem ser registrados em ou enviados para o sistema de monitoramento.
Espero muito que pg-index-health seja útil e procurada. Você também pode contribuir para o desenvolvimento da biblioteca, reportando problemas encontrados e sugerindo novas diagnósticas.
Fonte: habr.com
