Gerenciamento de Memória em Bancos PostgreSQL

Um dos pontos mais importante a ser analisado quando administramos um banco de dados é a MEMÓRIA COMPARTILHADA.
Saber, ao montar um servidor o quanto deixar de memória para o sistema operacional e o quanto alocar para o banco.
Será que a memória compartilhada é o suficiente para a quantidade de usuários (conexões) que tenho ???? e se meu banco tiver uma quantidade muito grande de transições ??? e se for um DW onde a maior fatia são das consultas???
Pois bem, esses eram alguns fantasmas que rondava a minha singela vida de DBA. Mas eu aprendi e espero que possa ajudar a quem estiver com essas mesmas dúvidas.

Primeiramente qual o papel da memória compartilhada?

Memória compartilhada é o valor disponibilizado no buffer para as transações do banco. Esse valor é variável, em bancos Postgres é indicado que este valor deve estar entre 1000 (8MB) a 500000 (400MB), e manipular esses valores dependendo do número de conexões e consultas (complexas ou simples).
Alguns bancos indicam ainda que o valor a ser destinado ao banco não pode ultrapassar de 1/3 (33%)da RAM disponível.

Como ela é calculada?

Um usuário consome em média 14,2 KB por conexão, com essa informação calculamos através da seguinte fórmula:((bloco do S.O X pag. a ser utilizada)+(14,2 KB * conexões))+250 KB= memória compartilhada

EX: ((4096 X 500000)+(14,2 *50))+250 KB= 2048000960 (2.048GB)

Esse valor deve ser setado nos parâmetros do kernel da seguinte forma:

echo “kernel.shmmax=2048000960 ” >> /etc/sysctl.conf ; sysctl -p

Tamanho dos Blocos

Esse parâmetro é usado para configurar o tamanho do bloco, o tamanho é definido tendo em vista as transições no banco, pois em uma transição pequena é melhor a busca em blocos pequenos e no caso de grandes buscas como em um DW por exemplo, é melhor um bloco maior.
Para grandes buscas como um DW, use 8192, porém esse valor só será considerável em servidores que suporte esse tipo de blocagem, e não se esqueça que será necessário diminuir a shared buffer, caso contrario, use a blocagem 4096.
Cada usuário utiliza em media 14,2 KB por conexão. Quanto mais conexões existirem no banco, menor deve ser a quantidade de páginas disponíveis. Tudo esta relacionado ao tipo e tamanho das transações e a área de ordenação.

Área de Ordenação

A área de ordenação (work_mem), é uma quantidade de memória física para realização de consultas por usuários. No caso de um DW é interessante ter essa área grande, pois, quanto maior a quantidade de memória destinada melhor será o desempenho da consulta principalmente se a mesma for complexa. Se a quantidade de memória estipulado nesse parâmetro foi ultrapassado o banco será obrigado a fazer SWAP causando lentidão nas consultas (e muito estress recheadas de ligações de usuários histéricos), resultando na concorrência entre o banco de dados e o sistema operacional.
No caso de banco com muitas consultas como um DW é indicado um servidor com uma quantidade considerável de memória e colocar o sistema operacional e o banco em discos separados para manter um melhor desempenho.

Então como diminuir a quantidade de páginas?

Bem, a paginação deve ser diminuída quando o número de conexões aumentar, isso tem que acontecer para que posteriormente não seja necessário mexer na memória compartilhada. Quando necessário, a quantidade de páginas a ser diminuída seguindo a seguinte fórmula:
(usuário acrescidos * 14,2KB) / blocos

Ex: 20 usuários acrescidos com a blocagem de 4 KB

(20 * 14,2) / 4
284 /4
71 KB(arredondando 100KB)

Sendo assim é necessário diminuir 100KB do shared buffer

Consultando as informações para os cálculos e alterando os arquivos de configuração

banco1=#
SELECT name, setting
FROM pg_settings
WHERE name IN(‘shared_buffers’,’max_connections’,’block_size’,’work_mem’);

name | setting

——————+———
block_size | 4096
max_connections | 50
shared_buffers | 500000
work_mem | 500000

com exceção do block_size que é definido na compilação os outros parâmetros podem ser alterados no postgresql.conf de cada banco.

Kenia Milene