OOM no Postgresql (out of memory)

algumas causas, como identificar e prevenir

Recentemento resolvi um caso de Out of Memory no ambiente de analytics de um cliente e achei interessante fazer um post para falar sobre.

OOM é um erro que ocorre quando a memória RAM do seu servidor é esgotada, quando isso ocorre o processo do Postgresql é imediatamente finalizado para que ocorra liberação de memoria para o SO. Não é algo raro de acontecer e pode, facilmente, derrubar seu servidor de produção se você não tomar os cuidados devidos.

INTRODUÇÃO

Bom, para entender o motivo primeiro precisamos entender minimamente como funciona a utilização da memoria no postgresql. Podemos dividir a memoria utilizada no postgresql em 2 grandes grupos:

• Memória local
• Memória compartilhada

Caso queira entender a arquitetura do Postgresql em mais detalhes eu fiz outro artigo que explica melhor sobre o funcionamento do WAL.

[embed]Como funciona o WAL no PostgreSQL? Um guia simples e direto para entender (W)rite (A)head (L)og no postgresqlmedium.com

Memoria Local

Na faculdade estudamos diversos tipos de algoritmos ( quicksort, mergesort, hashjoin, etc… ) esses algoritmos precisam de memória para realizar suas operações e é aí que entra a memória local.

A memória local que pode ser configuradas por nós se resume em: work_mem e maintanance_work_mem.

O postgresql limita para cada operação um espaço de memória que pode ser utilizada, nós chamamos esse espaço reservado de work_mem.

E se precisarmos de mais memória do que o work_mem nos fornece? Então vamos pegar memória emprestada do disco, esse processo se denomina spill do disco.

O spill do disco torna a operação consideravelmente mais lenta, então fazer com que as suas operações caibam no work_mem faz com que suas operações no banco fiquem mais rápidas.

A mesma lógica do work_mem também é aplicada para o maintanance_work_mem, mas em vez de realizar joins, orders by e group by o maintanance utiliza a memória para criação de tabelas, índices e realização de vacuums.

Obs: Uma mesma conexão pode utilizar vários work_mems ao mesmo tempo

Memória compartilhada

Diferente da memória local, ela é compartilhada entre todas as conexões, ela é dividida em diversas partes ( shared buffer, commit log, procarry ,wal buffer, etc…) , mas hoje vamos nos concentrar somente na shared buffer.

Quando acessamos informações no postgresql elas precisam estar na sharedbuffer para serem acessadas( tabelas, index, tabelas temporárias, etc… )

Aumentar os shared_buffers de forma que todas as tabelas e índices (ou os dados mais frequentemente utilizados) caibam nela incrementa substancialmente o desempenho do seu banco. Um valor inicial recomendado para começar é 25% da memória total da máquina.

CAUSAS

Agora que vimos como o Postgresql utiliza memória e os benefícios de aumenta esses parâmetros, vamos entender o risco envolvido.

Quando aumentamos esses valores de forma indiscriminada corremos o risco da memória disponível no servidor acabar. Caso isso ocorra o SO manda um comando para matar o processo que causou o out of memory e em seguida o postgresql realiza um checkpoint ( salva em disco as modificações contidas na shared_buffer ) e inicia o processo de shutdown.

A imensa maioria das vezes que ocorre um OOM é devido a um tuning de work_mem que o responsável pelo banco não se atentou ao número máximo de conexões simultâneas.

Para demonstrar eu criei um lab local no vagrant com as seguintes configurações:

• 2vCPU
• 1536MB de RAM
• 20GB de Disco
• postgres v17

No teste criei uma tabela de 1 Mi de linhas onde foram feitas 25 transações com um SELECT + ORDER BY (uma por segundo até alcançar 25).

Memoria antes da execução (1116MB livres):

Coloquei o script para rodar enquanto monitoro a memória. Podemos observar que, na sessão 14, a memória começou a cair até que ocorreu o OOM, devolvendo a memória para o SO:”:

Enquanto isso deixei uma sessão aberta no psql para contar o número de conexões abertas

Quando abrimos o log do Postgresql é possível identificar qual a consulta que gerou o OOM

# verifica aonde o log do postgres está
pg_lscluster

# abre as últimas 100 linhas do log
tail -n 100 /var/log/postgresql/postgresql-17-main.log

Quando cruzamos o log do Postgresql com o log do kernel é possível ver que é o mesmo PID.

# procura por oom no log do kernel
dmesg -T | grep -i "oom"
# ou 
dmesg | grep -i "killed process"

Achamos o mesmo PID ‘2330’ confirmando que realmente foi aquela query que causou o OOM.

E como eu faço para subir meu cluster novamente? primeiro precisamos ver se o cluster já esta down, pois quando ocorre o OOM o postgres primeiramente inicia o processo de checkpoint ( joga as alterações não salvas da shared_buffer no datafile ) e só depois realiza o shutdown do cluster.

Caso não tenha acabado ainda, você vai ver o cluster online, mas não vai poder conectar nele.

# lista informações dos clusters postgresql na isntância
pg_lscluster

# comando para administrar instância postgres
pg_ctlcluster [$versao] [$nome] [start/stop/restart]

Caso você não queira esperar o processo acabar, você pode matar a sessão, mas somente se tiver CERTEZA de que a transação foi salva no arquivo WAL; caso contrário, o banco pode apresentar inconsistência.

Caso você esteja usando transações com synchronous_commit não é recomendado fazer isso, pois pode haver perda de dados.

Como esse não é o nosso caso então iremos matar a sessão para subir logo o banco:

# Verifica pid dos processos do postgres(estamos procurando o checkpointer)
ps aux | grep postgres

# mata de forma abrupta o processo
kill -9 [pid]

por fim vamos usar o comando para subir o banco novamente e tan dam😁

EVITANDO O PROBLEMA

Saber configurar ‘work_mem’ e ‘shared buffers’ não é uma ciência exata, é uma arte.

Para configurar bem os parâmetros é preciso entender o contexto , não existe fórmula ou número mágico!, MAAAAASSSS é possível ter um bom ponto de partida utilizando algumas fórmulas e números.

Um jeito conservador de fazer seria subtrair o valor da shared buffer(25%) e o valor de uma margem que o SO utilizaria (eu costumo estimar em 25%) pela memória total, então dividir a memória o número de conexões máximas vezes 2 (estimativa de work_mem por consulta) .

Um exemplo com 8GB de RAM total e 200 de max_connections:

que arredondando para baixo ficamos com 10MB, claro que é apenas um ponto de partida, podemos monitorar o uso de memória do servidor para aumentar mais esse parâmetro.

Outra forma de evitar OOM é realizar esse ajuste por usuário. podemos reservar um usuários que sabemos que irá realizar queries mais pesadas que vai se beneficiar por esse tipo de tuning de work_mem e aumentar o parâmetro só para ele.

Dessa forma ajudamos a prevenir significativamente que ocorra um OOM. Essa abordagem não é tão eficiente quanto a próxima, mas pode ajudar em ambientes onde não temos acesso ao kernel, como em bancos gerenciados pela nuvem (AWS, GCP, Azure, etc.).

Infelizmente isso não mitiga 100% do risco, pois o usuário ainda pode alterar o parâmetro da query da própria sessão. ( honestamente acho que deveria ter uma forma de bloquear isso…)

A Forma mais eficaz e sofisticada de Evitar de vez o OOM é modificando os parâmetro overcommit_memory do kernel do linux.

O Linux pode limitar o usuário de alocar mais memória do que realmente existe, o parâmetro que define isso é o ‘overcommit_memory’

Se o mais seguro é a opção 2 por qual motivo o Linux vem com o zero por padrão? A verdade é que existe um ganho de desempenho quando você desativa esse parâmetro, mas em um ambiente de banco de dados de produção, a segurança é mais importante !!

# verifica o estado atual do overcommit_memory
cat /proc/sys/vm/overcommit_memory

# altera o valor para a sessão atual
sudo sysctl -w vm.overcommit_memory=2

# altera o valor permanentemente para as proximas sessões 
sudo vi /etc/sysctl.conf # abra o arquivo e edite [ vm.overcommit_memory = 2 ]
sudo sysctl -p           # aplique a alteração

Agora quando executamos o nosso teste em lab com os mesmos parâmetros recebemos a mensagem que não foi possível finalizar a transação por OOM, enquanto isso o nosso servidor postgresql continua 100% UP ✅

Tuning de work_mem e shared buffers são extremamente eficazes, mas não se esqueçam de fazer com moderação e mudar o overcommit_memory para 2 !!

Vejo vocês no próximo post, até 👋

REFERÊNCIAS

[embed]19.4. Resource Consumption 19.4. Resource Consumption # 19.4.1. Memory 19.4.2. Disk 19.4.3. Kernel Resource Usage 19.4.4. Background Writer…www.postgresql.org

[embed]Let's get back to basics - PostgreSQL memory components This is a simplified explanation for DBAs as to the significant memory areas of a PostgreSQL instance and how are they…www.postgresql.fastware.com

[embed]🔧 PostgreSQL 17 Kernel Tuning Guide: Managing System Parameters for Optimal Performance PostgreSQL is a highly performant database system, but to fully leverage its power — especially at scale — proper…medium.com

https://postgresqlblog.hashnode.dev/solving-and-avoiding-memory-killer-issues-in-postgresql-a-complete-guide

[embed]