Personalizando o PostgreSQL - work_mem | Vinicius Schettino
Blog Personalizando o PostgreSQL - work_mem
Personalização do PostgreSQL
Os parâmetros de configuração do PostgreSQL existem para que o especialista responsável ajuste o comportamento do sistema de gerenciamento de banco de dados em detrimento do contexto no qual ele será aplicada. Notoriamente, duas forças devem ser observadas na etapa de projeto:
Aspectos arquiteturais de hardware, como memória, armazenamento, distribuição, etc.
Aspectos não funcionais como disponibilidade, eficiência de leitura/escrita, segurança, confiabilidade, etc.
Estas forças não podem ser analisadas separadamente, e ainda por cima interagem diretamente com as funcionalidades que compõem o produto, criando um ecossistema único de características que devem ser observadas. O PostgreSQL suporta essas necessidades distintas principalmente através de parâmetros configuráveis, que aproveitam melhor os recursos e dão, sempre em troca de outro requisito, retorno em aspectos que qualidade importantes para a aplicação.
Neste artigo falaremos do parâmetro work_mem, um dos mais fáceis de entender, detectar mau uso e personalizar.
Encontrando espaço para trabalhar
Talvez o aspecto mais importante da configuração de work_mem seja a escalabilidade deste parâmetro em relação ao crescimento natural de uma aplicação, e por consequência seu banco de dados. Os anos passam, o número de usuários funcionalidades aumenta. Isso significa que apenas o volume de operações com dados vai crescer? Não. Junto com o aumento da demanda pelos dados, a complexidade das operações também cresce. Ou seja, são necessários mais filtros, joins, agregações e restrições de integridade. Buscar otimização destas consultas através da indexação, evolução do projeto conceitual e até mesmo estruturas auxiliares de dados (por exemplo, um BD não relacional) são as primeiras soluções que pensamos, mas elas não são eficientes se o PostgreSQL não encontrar na memória principal um espaço de trabalho adequado.
Pense no work_mem como o limitador do espaço (ou buffer) que cada conexão tem para realizar as operações de busca, especialmente operações de Hash. Assim, as operações que não cabem nesta memória dedicada são realizadas com auxílio de file buffers que se apoiam na memória secundária (Disco), muito mais lenta.
Como Descobrir se a work_mem está transbordando?
Como geralmente acontece, podemos investigar as estratégias de operação através da declaração EXPLAIN ANALYZE. Ao executar uma consulta com esta instrução, você verá uma linha parecida com essa:
Aqui, sem entrar em detalhes técnicos do funcionamento da Hash Table, o PostgreSQL tinha disponível apenas 4096 buckets para trabalhar (que é o padrão, 4MB), e por isso foi necessário criar dois Batches diferentes, apenas um deles na memória (que alocou 2948 dos buckets). O resto foi parar no (lento) file buffer.
Se você alterar o parâmetro work_mem no arquivo postgresql.conf, digamos para 8MB, o resultado do EXPLAIN ANALYZE poderia ser diferente
Aqui vemos que foi possível alocar mais memória para apoiar a operação, o que fez desnecessária a utilização do file buffer.
Como nem tudo são flores, este parâmetro não é mágico; Este valor é dedicado não só para cada conexão, mas possivelmente cada join da consulta, quando estes demandam merge sorts em sua operação. Por isso, precisamos ser razoáveis com seu acréscimo. gastar muita memória com com estas operações e deixar outras funções do PostgreSQL (ou até de outro serviço, dependendo da sua arquitetura) sem espaço para trabalhar pode degradar ainda mais a performance da aplicação.
Como sempre, teste diferentes valores e faça medições concisas. Discuta com sua equipe sobre os resultados encontrados e monitore continuamente a performance do banco de dados.
https://dzone.com/articles/is-your-postgres-query-starved-for-memory (referência principal e bom ponto de partida para compreensão do papel das Hash Tables no PostgreSQL)