Hey.
Meu nome é Vanya e sou desenvolvedor Java. Acontece que trabalho muito com PostgreSQL - montando banco de dados, otimizando estrutura, performance e jogando um pouco de DBA nos finais de semana.
Recentemente organizei vários bancos de dados em nossos microsserviços e escrevi uma biblioteca java , o que facilita esse trabalho, economiza tempo e me ajuda a evitar alguns erros comuns cometidos por desenvolvedores. É desta biblioteca que falaremos hoje.
Aviso Legal
A versão principal do PostgreSQL com a qual trabalho é a 10. Todas as consultas SQL que utilizo também são testadas na versão 11. A versão mínima suportada é 9.6.
Pré-história
Tudo começou há quase um ano com uma situação que me pareceu estranha: a criação competitiva de um índice do nada terminou em erro. O próprio índice, como sempre, permaneceu no banco de dados em estado inválido. A análise de log mostrou uma escassez . E lá vamos nós... Indo mais fundo, descobri um monte de problemas na configuração do banco de dados e, arregaçando as mangas, comecei a corrigi-los com brilho nos olhos.
Problema um – configuração padrão
Provavelmente todo mundo está cansado da metáfora sobre o Postgres, que pode ser executado em uma cafeteira, mas... a configuração padrão realmente levanta uma série de questões. No mínimo, vale a pena prestar atenção Maintenance_work_mem, limite_arquivo_temperário, declaração_tempo limite и lock_timeout.
No nosso caso Maintenance_work_mem o padrão era 64 MB e limite_arquivo_temperário algo em torno de 2 GB - simplesmente não tínhamos memória suficiente para criar um índice em uma tabela grande.
Portanto, em pg-index-health Eu colecionei uma série , na minha opinião, os parâmetros que devem ser configurados para cada banco de dados.
Problema dois – índices duplicados
Nossos bancos de dados residem em unidades SSD e usamos HA-configuração com múltiplos data centers, host mestre e n-número de réplicas. O espaço em disco é um recurso muito valioso para nós; não é menos importante que o desempenho e o consumo de CPU. Portanto, por um lado, precisamos de índices para leitura rápida e, por outro lado, não queremos ver índices desnecessários no banco de dados, pois eles ocupam espaço e retardam a atualização dos dados.
E agora, tendo restaurado tudo e tendo visto o suficiente , decidi organizar um “grande” expurgo. Acontece que os desenvolvedores não gostam de ler a documentação do banco de dados. Eles não gostam muito disso. Por causa disso, surgem dois erros típicos - um índice criado manualmente em uma chave primária e um índice “manual” semelhante em uma coluna exclusiva. O fato é que eles não são necessários - o Postgres fará tudo sozinho. Esses índices podem ser excluídos com segurança e surgiram diagnósticos para esse fim .
Problema três – índices de interseção
A maioria dos desenvolvedores novatos cria índices em uma única coluna. Aos poucos, tendo experimentado profundamente esse negócio, as pessoas começam a otimizar suas consultas e a adicionar índices mais complexos que incluem várias colunas. É assim que os índices nas colunas aparecem A, A + B, A + B + C e assim por diante. Os dois primeiros desses índices podem ser descartados com segurança, pois são prefixos do terceiro. Isso também economiza muito espaço em disco e há diagnósticos para isso .
Problema quatro – chaves estrangeiras sem índices
Postgres permite criar restrições de chave estrangeira sem especificar um índice de apoio. Em muitas situações isso não é problema, podendo nem se manifestar... Por enquanto...
Foi o mesmo conosco: só que em algum momento um trabalho, rodando de acordo com um cronograma e limpando o banco de dados de pedidos de teste, começou a ser “adicionado” a nós pelo host mestre. CPU e IO foram desperdiçados, as solicitações ficaram lentas e expiraram, o serviço foi de quinhentos. Análise rápida mostrou que consultas como:
delete from <table> where id in (…)Nesse caso, é claro, havia um índice por id na tabela de destino e pouquíssimos registros foram excluídos de acordo com a condição. Parecia que tudo deveria funcionar, mas, infelizmente, não funcionou.
O maravilhoso veio ao resgate explicar analisar e disse que além de deletar registros na tabela alvo, há também uma verificação de integridade referencial, e em uma das tabelas relacionadas essa verificação falha varredura sequencial devido à falta de um índice adequado. Assim nasceu o diagnóstico .
Problema cinco – valor nulo em índices
Por padrão, o Postgres inclui valores nulos nos índices btree, mas eles geralmente não são necessários lá. Portanto, tento diligentemente descartar esses nulos (diagnóstico ), criando índices parciais em colunas anuláveis por tipo where <A> is not null. Desta forma consegui reduzir o tamanho de um dos nossos índices de 1877 MB para 16 KB. E em um dos serviços, o tamanho do banco de dados diminuiu 16% no total (4.3 GB em números absolutos) devido à exclusão de valores nulos dos índices. Enorme economia de espaço em disco com modificações muito simples. 🙂
Problema seis – falta de chaves primárias
Devido à natureza do mecanismo uma situação como esta é possível quando o tamanho da sua tabela está crescendo rapidamente devido a um grande número de registros mortos. Eu ingenuamente acreditei que isso não iria nos ameaçar, e que isso não aconteceria com a nossa base, porque nós, nossa!!!, somos desenvolvedores normais... Como eu fui estúpido e ingênuo...
Um dia, uma migração maravilhosa pegou e atualizou todos os registros em uma tabela grande e usada ativamente. Conseguimos +100 GB para o tamanho da mesa do nada. Foi uma pena, mas nossas desventuras não terminaram aí. Após o término do autovácuo nesta mesa, 15 horas depois, ficou claro que o local físico não retornaria. Não conseguimos interromper o serviço e deixar o VACUUM FULL, então decidimos usar . E então descobriu-se que pg_repack não sabe como processar tabelas sem uma chave primária ou outra restrição de exclusividade, e nossa tabela não tinha uma chave primária. Assim nasceu o diagnóstico .
Na versão da biblioteca 0.1.5 Foi adicionada a capacidade de coletar dados do excesso de tabelas e índices e responder a eles em tempo hábil.
Problemas sete e oito – índices insuficientes e índices não utilizados
Os dois diagnósticos a seguir são: и – apareceu em sua forma final há relativamente pouco tempo. A questão é que eles não poderiam simplesmente ser tomados e adicionados.
Como já escrevi, usamos uma configuração com várias réplicas, e a carga de leitura em hosts diferentes é fundamentalmente diferente. Como resultado, acontece que algumas tabelas e índices em alguns hosts praticamente não são usados e, para análise, é necessário coletar estatísticas de todos os hosts do cluster. Isso também é necessário em todos os hosts do cluster; você não pode fazer isso apenas no mestre.
Essa abordagem nos permitiu economizar várias dezenas de gigabytes removendo índices que nunca foram usados, bem como adicionando índices ausentes a tabelas raramente usadas.
Como conclusão
Claro, para quase todos os diagnósticos você pode configurar . Dessa forma, você pode implementar rapidamente verificações em sua aplicação, evitando que novos erros apareçam, e então corrigir gradativamente os antigos.
Alguns diagnósticos podem ser realizados em testes funcionais imediatamente após a implementação das migrações de banco de dados. E este é talvez um dos recursos mais poderosos da minha biblioteca. Um exemplo de uso pode ser encontrado em .
Faz sentido realizar verificações de índices não utilizados ou ausentes, bem como de inchaço, apenas em um banco de dados real. Os valores coletados podem ser registrados em ou enviado para o sistema de monitoramento.
Eu realmente espero que pg-index-health será útil e procurado. Você também pode contribuir para o desenvolvimento da biblioteca relatando problemas encontrados e sugerindo novos diagnósticos.
Fonte: habr.com