Project Description

Cliente: empresa de tecnologia especializada em soluções de relacionamento com clientes.

Por conta do acordo de confidencialidade entre as empresas, detalhes específicos do projeto não serão divulgados. Somente serão descritas informações de conhecimento geral e/ou domínio público sobre hardware, software e atividades que sejam inerentes à administração de banco de dados.

Descrição do projeto:

Em um de seus projetos, o cliente desenvolveu um sistema de integração de dados que utiliza o banco de dados PostgreSQL e necessitou de nosso apoio para resolver questões de desempenho que começaram a surgir no banco de dados e na aplicação.

Diagnóstico

As atividades de análise de desempenho de banco de dados da DBA possuem as seguintes características:

  • Análises e ajustes padrão para qualquer tecnologia de banco de dados, tais como:
    • Levantamento do hardware atual e da sua utilização para o tamanho do banco de dados;
    • Levantamento da distribuição dos discos, RAIDs, filesystems, e, em alguns casos, a distribuição de tabelas e índices nesses discos;
    • Ajustes das configurações do servidor para uso com bancos de dados;
    • Monitoramento do desempenho do servidor e do banco de dados;
    • Monitoramento e ajuste das queries executadas no banco de dados;
  • Análises e ajustes específicos para a tecnologia, nesse caso o PostgreSQL, tais como:
    • Levantamento e ajuste do pool de conexões e do cache;
    • Replicação/Particionamento (sharding) do banco de dados;
    • Particionamento dos objetos;
    • Instalação de extensões (contrib modules) ao produto;
    • Instalação de extensões (add-ons) ao produto.

Sugestões

Com base na análise de uso do banco dados e com foco na melhoria da performance, os seguintes sugestões e ajustes foram solicitadas:

  • Aumentar mais 2 cores virtuais no servidor. Total de 4 cores virtuais;
  • Aumentar memória total de cada servidor virtual em mais 4GB. Total 28GB;
  • Verificar a possibilidade de ajustar a configuração atual dos discos para permitir maior velocidade no I/O;
  • Desabilitar o contador de tempo de acesso aos arquivos;
  • Priorizar utilização da RAM ao SWAP;
  • Ajustar parâmetros de kernel do sistema operacional;
  • Mover os arquivos de WAL para fora do diretório raiz e em áreas de maior capacidade de I/O;
  • Mover os arquivos temporários da área padrão do banco de dados;
  • Ajustar o buffer cache e cache efetivo do banco de dados;
  • Ajustar parâmetros do banco de dados para comportar as necessidades da aplicação;
  • Criação de índices em diversas colunas de diversas tabelas do banco de dados;
  • Criação de rotina de expurgo de dados em 5 tabelas do banco de dados;
  • Particionamento de 10 tabelas do banco de dados;
  • Re-agendar as rotina de backup para períodos de menor atividade do banco de dados;
  • Criar uma política de atualização constante do ambiente de desenvolvimento e homologação;
  • Evitar cláusula DISTINCT nos comandos SQL;
  • Evitar valores fixos nos comandos. Preferir variáveis tipo BIND.

Tecnologias de Banco de Dados Utilizadas

O PostgreSQL suporta o particionamento básico de tabelas. Esta seção descreve por que e como implementar o particionamento como parte do design do seu banco de dados.

Resumidamente, particionamento refere-se a dividir uma grande tabela em partes físicas menores. Tal funcionalidade pode fornecer vários benefícios:

  • O desempenho da consulta pode ser melhorado drasticamente em certas situações, especialmente quando a maioria das linhas da tabela (acessadas com mais frequência) estejam em uma única partição ou em um pequeno número de partições;
  • O particionamento substitui as colunas principais de índices, reduzindo o tamanho do índice e tornando mais provável sua permanência em memória;
  • Quando consultas ou atualizações acessam uma grande porcentagem de uma única partição, o desempenho pode ser melhorado aproveitando a varredura sequencial da partição em vez de usar uma varredura aleatória espalhada por toda a tabela;
  • As cargas e exclusões em massa podem ser realizadas adicionando ou removendo partições, se esse requisito for planejado no desenho do particionamento;
  • Dados ​​raramente usados podem ser migrados para mídias de armazenamento mais lentas e mais baratas.

O PostgreSQL oferece suporte integrado para as seguintes formas de particionamento:

  • Particionamento por intervalo de dados (RANGE): A tabela é particionada em “intervalos” definidos por uma coluna-chave ou conjunto de colunas, sem sobreposição entre os intervalos de valores atribuídos a diferentes partições;
  • Particionamento por lista de dados (LIST): A tabela é particionada listando explicitamente quais valores de chave aparecem em cada partição.

O indexamento é uma maneira comum de melhorar o desempenho do acesso aos dados no banco de dados. Um índice permite que o servidor de banco de dados localize e recupere linhas específicas de uma tabela muito mais rápido do que seria possível sem um índice. Mas os índices também adicionam sobrecarga, portanto devem ser usados de maneira sensata. Os tipos mais comuns são:

Tipo Descrição Operadores apropriados
BTree Podem manipular consultas de igualdade e alcance em dados que podem ser classificados em algumas ordenações. < <= = >= >
Hash Só podem manipular comparações simples de igualdade. =
Gin São “índices invertidos” que são apropriados para valores de dados que contêm vários componentes, como matrizes. <@ @> = &&
BRIN Block Range Indexes armazenam resumos sobre os valores armazenados em intervalos de blocos físicos consecutivos de uma tabela. < <= = >= >

Existem outros tipos de índices menos comuns que não foram utilizados na estrutura do cliente.

Resultados Alcançados

Com as atividades realizadas no banco de dados do cliente, alcançamos os seguintes objetivos:

  • Maior estabilidade do banco de dados, através da identificação e ajuste dos principais gargalos do sistema como um todo, tais como:
    • Configurações do sistema operacional;
    • Comandos SQL enviados pela aplicação;
    • Estruturas físicas do banco de dados;
    • Estruturas lógicas do banco de dados.
  • Redução do tempo de resposta dos comandos da aplicação para usuários finais do sistema;
  • Redução da utilização dos recursos do servidor utilizados pelo banco de dados para processar a mesma carga de trabalho;
  • Capacidade aumentada do servidor de banco de dados para processar até o triplo de solicitações enviadas pela aplicação;
  • Maior qualidade e agilidade dos serviços do cliente ao usuário final, com a garantia de que a aplicação estará sempre em funcionamento adequado.