Tuning de Banco de Dados: Melhorando a Performance do SGBD PostgreSQL

Quando ouvimos falar em Tuning de Banco de Dados, logo vem a mente da maioria das pessoas a criação de índices para melhoria da velocidade de busca das informações. Isto faz até um pouco de sentido, visto que a
proposta do SGBD (Sistema Gerenciador de Banco de Dados) é agrupar os dados de um banco de dados e disponibilizá-los quando forem solicitados. Além disto, uma das estratégias de Tuning também é esta, a de criação de alguns Índices
afinal, em se tratando de base de dados, os índices são os objetos responsáveis pela “busca rápida” das informações.

Porém gostaríamos de desmistificar a ideia de que Tuning é apenas isto. Para tanto iremos discorrer acerca de alguns conceitos que envolvem o SGBD PostgreSQL bem como o Sistema Operacional Linux, Sistema que no mercado detém o maior % de servidores com a instalação deste SGBD, em ambientes produtivos. Que tal seguirmos nesta jornada??

Entendendo Tuning

A palavra Tuning pode ser traduzida como afinação, sintonia. Trata-se de ajustes que são realizados sobre um sistema de forma a melhorar a sua performance de retorno de processamento. Em se tratando do SGBD PostgreSQL, o mesmo deve ser ajustando em todas as camadas que compôem a sua instalação, a saber: Hardware, Sistema Operacional, Configurações do SGBD PostgreSQL, Aplicação e Otimização das Consultas. Vamos passar por cada uma destas camadas, começando do Hardware, a camada de menor abstração, até a mais alta, as consultas sobre o banco de dados.

Camada de Hardware

Nas primeiras aulas de computação nos é dito que o sistema é composto por 03 principais partes: O Hardware, a parte tangível, O Software a parte lógica ou intangível e o PeopleWare, as pessoas que operam sobre o sistema.
O Hardware é o responsável elétrico pela salvaguarda dos dados. Aqui podemos citar vários componentes que podem influenciar, para bem ou para mal, no desempenho do sistema.

O Disco

É o lugar onde são armazenados os dados. Falamos disco porque isto nos remete aos primeiros HDs, muito embora hoje tenhamos outros formatos de armazenamento. Aqui devemos ter em mente que as controladoras possuem duas formas de escrita dos dados. Write Back e Write Through. A Write Back possui maior desempenho devido a realizar a escrita depois (depois inclusive de confirmar que “guardou” os dados), porém pode ser suscetível a erros, em caso de queda de energia, caso não possua módulos de bateria de backup. Já a escrita Write Through, devido a realizar a escrita de forma síncrona, torna-se mais lenta, porém garante a efetividade da guarda dos dados.

Memória RAM

A memória é a área efetivamente onde são realizadas as operações sobre os dados. A ideia aqui é oferecer o máximo de memória RAM disponível para o SGBD e sempre optar pelas tecnologias mais rápidas. Em ordem decrescente de velocidade temos: NVME > SSD > SAS > SCSI > SATA.

CPU

Para o PostgreSQL é melhor oferecer processadores com maior velocidade de clock (operações por segundo). Além disso, quanto mais núcleos melhor para o sistema, pois algumas operações do PostgreSQL estão adicionando o conceito de paralelismo e isto só pode ser obtido com processadores compatíveis com este tipo de processamento.

Sistema Operacional Linux

O sistema operacional empresta vários conceitos ao SGBD. Processos, Comunicação Interprocessos, Memória Compartilhada e outros, são alguns destes conceitos. Uma das características mais importantes que foi criada para os Sistemas Operacionais é o conceito de SWAP, que trata-se de alocar parte do Então Disco Magnético, como extensão da Memória Principal do sistema (Memória RAM). Isto traz impactos negativos ao SGBD, pois a velocidade de leitura em Disco é absurdamente mais lenta do que a velocidade de leitura da Memória. Por isso é interessante modificar um parâmetro no linux chamado vm.swappiness.

Este parâmetro pode ser verificado com o seguinte comando:

sysctl vm.swappiness

A partir dele, podemos realizar o cálculo de em que momento o sistema começará a fazer SWAP. A conta é bem simples.

Início de SWAP = 100 – VM.SWAPPINESS

Como o valor default deste parâmetro é algo em torno de 30 a 40, a sugestão é setá-lo com valor 1. Desta feita, o sistema só irá realizar SWAP a partir de 99% da Memória RAM ocupada.

echo ’vm.swappiness = 1’ >> /etc/sysctl.d/pgsql.conf

sysctl -p /etc/sysctl.d/pgsql.conf

Além do Swappiness, outro conceito de SO é o Overcommit de Memória. De forma resumida, o SO sempre imagina que os processos (Programas em Execução) irão utilizar menos memória do que eles solicitam para ser alocado. Então ele vai fornecendo memória, mesmo que o sistema não possua. Isto pode acabar por liberar um processo de SO denominado OOM Killer (Out of Memory Killer). Trata-se de um assassino de processos, que irá matar alguém aleatoriamente para não travar o sistema, visto que a memória se extinguiu.

O problema é quando o Killer mata um dos processos de background do SGBD PostgreSQL. Isto já aconteceu com um de nossos clientes e trouxe transtornos a seu negócio.

A solução para isto é configurar o seguinte parâmetro de SO, com o valor 2. Assim o sistema só irá oferecer a memória que realmente possui, e não teremos o risco de uma morte de um processo de postgres inocente.

echo ’vm.overcommit_memory = 2’ >> /etc/sysctl.d/pgsql.conf

sysctl -p /etc/sysctl.d/pgsql.conf

Configurações do SGBD PostgreSQL

Em relação as configurações do PostgreSQL as principais são associadas aos recursos do Sistema. A documentação oficial nos sugere, por exemplo, utilizar uma Shared_Buffers com 25 a 40 % da memória do Sistema.
Aleḿ desta a WORK_MEM é a memória de sessão e deve ser calculada multiplicando seu valor pelo MAX_CONNECTIONS. Deve haver também a lembrança das memórias de manutenção como a MANTENANCE_WORK_MEM, que serve para operações de CREATE INDEX, VACUUM, ANALYZE e etc.

Não existe uma fórmula ideia para cada sistema. Deve-se observar as características dos sistema e realizar seu ajuste fino. Por isso é importante contar sempre com um DBA no time e caso não tenham, podem nos procurar na 4Linux que iremos lhe auxiliar neste processo (Tem que vender o peixe neh rsrsrsrs).

A Aplicação

Em relação a aplicação temos que ter em mente que para cada tipo de sistema (OLTP, OLAP, Sistemas Web) as características irão mudar. Por exemplo, sistemas OLTP precisarão de mais memórias de conexão (MAX_CONNECTIONS) de forma a permitir a concorrência de usuários sobre a aplicação.

Já sistemas OLAP, farão uso de mais memória compartilhada (SHARED_BUFFERS), visto que seu propósito é obter um maior conjunto de dados do SGBD.

Mas novamente enfatizo, não há “receita de bolo” pronta, tudo depende da análise que o DBA irá realizar em conjunto com o time de Dev e também com pessoas do negócio.

Otimização de Consultas – Agora sim falamos dos Índices

Agora sim, chegamos a parte que aborda a criação de índices. Finalmente hein, eu querendo resolver todos os meus problemas criando alguns índices… Opa, não necessariamente você irá criar índices rsrsrsr.
Talvez o índice já exista mais os responsáveis por escrever a query não estejam utilizando. Talvez a ordem dos campos do índice estejam invertidas. Existema várias situações a serem consideradas.
O campo escolhido, possui índice mas ele não é muito seletivo ( quando é utilizado como filtro em uma consulta ).

Percebe que ao se falar de Tuning não há uma resposta pronta.

Mas os índices podem sim ajudar. Digamos que na maioria das consultas lentas a criação assertiva de um índice pode resolver o problema (trazendo uma consulta de minutos para milisegundos, por exemplo).

Aliás, sobre índices vou dar uma dica importantíssima e que gera muito dos problemas de lentidão em sistemas. as FKs (Chaves Estrangeiras), não tem índice por default. SGBD só cria índice por default para PK (Chave Primária) e também para restrição de integridade UNIQUE (Alguns SGBDs). A FK não cria por default e quando você faz um JOIN (Coisa boa em se tratando de performance), o problema pode estar na coluna / tabela que é a Chave Estrangeira. Se liga aí 😉

Enfim, são vários pontos que devem ser considerados em se tratando de Tuning.

Espero ter ajudado você a abrir sua mente para o tema, e perceber algo que pode estar “fora da caixa”.

Obrigado pela leitura, até a próxima.