Parâmetros de servidor no Banco de Dados do Azure para PostgreSQL – Servidor Flexível

APLICA-SE A: Banco de Dados do Azure para PostgreSQL – Servidor Flexível

O Banco de Dados do Azure para PostgreSQL fornece um subconjunto de parâmetros configuráveis para cada servidor. Para obter mais informações sobre parâmetros do Postgres, confira a Documentação do PostgreSQL.

Tipos de parâmetro

O Banco de Dados do Azure para PostgreSQL – Servidor Flexível vem pré-configurado com configurações padrão ideais para cada parâmetro. Os parâmetros são categorizados em um dos seguintes tipos:

• Parâmetros estáticos: esses parâmetros exigem uma reinicialização do servidor para implementar quaisquer alterações.
• Parâmetros dinâmicos: esses parâmetros podem ser alterados sem a necessidade de reiniciar a instância do servidor. No entanto, as alterações serão aplicadas somente a novas conexões estabelecidas após a modificação.
• Parâmetros somente leitura: esses parâmetros não são configuráveis pelo usuário devido à sua função crítica na manutenção da confiabilidade, da segurança ou de outros aspectos operacionais do serviço.

Para determinar o tipo de parâmetro, acesse o portal do Azure e abra o painel Parâmetros do servidor. Os parâmetros são agrupados em guias para facilitar a identificação.

Personalização de parâmetros

Vários métodos e níveis estão disponíveis para personalizar seus parâmetros de acordo com suas necessidades específicas.

Nível global

Para alterar as configurações globalmente no nível da instância ou do servidor, acesse o painel Parâmetros do servidor no portal do Azure. Você também pode usar outras ferramentas disponíveis, como a CLI do Azure, a API REST, modelos do Azure Resource Manager ou ferramentas de parceiro.

Observação

Como o Banco de Dados do Azure para PostgreSQL é um serviço de banco de dados gerenciado, os usuários não têm acesso ao host ou ao sistema operacional para exibir ou modificar arquivos de configuração, como postgresql.conf. O conteúdo dos arquivos é atualizado automaticamente com base nas alterações que você faz no parâmetro.

Níveis granulares

Você pode ajustar parâmetros em níveis mais granulares. Esses ajustes substituem valores definidos globalmente. O escopo e a duração dependem do nível em que você os faz:

• Nível de banco de dados: utilize o comando ALTER DATABASE para configurações específicas do banco de dados.

• Nível de usuário ou função: use o comando ALTER USER para configurações centradas no usuário.

• Nível de procedimento, função: ao definir uma função ou procedimento, você pode especificar ou alterar os parâmetros de configuração que serão definidos quando a função for chamada.

• Nível de tabela: por exemplo, você pode modificar parâmetros relacionados ao vácuo automático nesse nível.

• Nível de sessão: durante uma sessão de banco de dados individual, você pode ajustar parâmetros específicos. O PostgreSQL facilita esse ajuste com os seguintes comandos SQL:

  • Use o comando SET permite para fazer ajustes específicos à sessão. Essas alterações servem como configurações padrão durante a sessão atual. O acesso a essas alterações pode exigir privilégios SET específicos, e as limitações de parâmetros modificáveis e somente leitura descritos antes não se aplicam. A função SQL correspondente é set_config(setting_name, new_value, is_local).
  • Use o comando SHOW para examinar as configurações de parâmetro existentes. A função SQL equivalente é current_setting(setting_name text).

Parâmetros importantes

As seções a seguir descrevem alguns dos parâmetros.

shared_buffers

Atributo	Valor
Valor padrão	25% de RAM total
Valor permitido	10-75% de RAM total
Tipo	Estático
Nível	Global
Observações específicas do Azure	A configuração shared_buffers escala de forma linear (aproximadamente) conforme os vCores aumentam em uma camada.

Descrição

O parâmetro de configuração shared_buffers determina a quantidade de memória do sistema alocada para o banco de dados PostgreSQL para armazenar dados em buffer. Ele serve como um pool de memória centralizado acessível a todos os processos de banco de dados.

Quando os dados são necessários, o processo de banco de dados primeiro verifica o buffer compartilhado. Se os dados necessários estiverem presentes, eles serão recuperados rapidamente e ignora uma leitura de disco mais demorada. Ao servir como intermediário entre os processos de banco de dados e o disco, shared_buffers reduz efetivamente o número de operações de E/S necessárias.

huge_pages

Atributo	Valor
Valor padrão	TRY
Valor permitido	TRY, ON, OFF
Tipo	Estático
Nível	Global
Observações específicas do Azure	Para servidores com quatro ou mais vCores, páginas enormes são alocadas automaticamente do sistema operacional subjacente. O recurso não está disponível para servidores com menos de quatro vCores. O número de páginas enormes será ajustado automaticamente se alguma configuração de memória compartilhada for alterada, incluindo alterações em shared_buffers.

Descrição

Páginas enormes são um recurso que permite que a memória seja gerenciada em blocos maiores. Normalmente, você pode gerenciar blocos de até 2 MB, em vez das páginas padrão de 4 KB.

O uso de páginas enormes pode oferecer vantagens de desempenho que efetivamente descarregam a CPU:

• Elas reduzem a sobrecarga associada a tarefas de gerenciamento de memória, como menos erros de TLB (buffer lookaside de tradução).
• Elas reduzem o tempo necessário para o gerenciamento de memória.

Especificamente, no PostgreSQL, você pode usar páginas enormes apenas para a área de memória compartilhada. Uma parte significativa da área de memória compartilhada é alocada para buffers compartilhados.

Outra vantagem é que páginas enormes impedem a troca da área de memória compartilhada para o disco, que estabiliza ainda mais o desempenho.

Recomendações

• Para servidores que têm recursos de memória significativos, evite desabilitar páginas enormes. Desabilitar páginas enormes pode comprometer o desempenho.
• Se você começar com um servidor menor que não dá suporte a páginas enormes, mas antecipa escalar verticalmente para um servidor que o faça, mantenha a configuração de huge_pages em TRY para uma transição perfeita e desempenho ideal.

work_mem

Atributo	Valor
Valor padrão	4MB
Valor permitido	4MB-2GB
Tipo	Dinâmico
Nível	Global e granular

Descrição

O parâmetro work_mem no PostgreSQL controla a quantidade de memória alocada para determinadas operações internas dentro da área de memória privada de cada sessão de banco de dados. Exemplos dessas operações são classificação e hash.

Ao contrário dos buffers compartilhados, que estão na área de memória compartilhada, work_mem é alocado em um espaço de memória privada por sessão ou por consulta. Ao definir um tamanho de work_mem adequado, você pode melhorar significativamente a eficiência dessas operações e reduzir a necessidade de gravar dados temporários em disco.

Pontos-chave

• Memória de conexão privada: work_mem faz parte da memória privada que cada sessão de banco de dados usa. Essa memória é distinta da área de memória compartilhada que shared_buffers usa.
• Uso específico de consulta: nem todas as sessões ou consultas usam work_mem. É improvável que consultas simples como SELECT 1 exijam work_mem. No entanto, consultas complexas que envolvem operações como classificação ou hash podem consumir uma ou várias partes de work_mem.
• Operações paralelas: para consultas que abrangem vários back-ends paralelos, cada back-end poderia potencialmente utilizar uma ou várias partes de work_mem.

Monitorando e ajustando work_mem

É essencial monitorar continuamente o desempenho do sistema e ajustar work_mem conforme necessário, principalmente se ocorrerem tempos lentos de execução de consulta relacionados a operações de classificação ou hash. Aqui estão maneiras de monitorar o desempenho usando as ferramentas disponíveis no portal do Azure:

• Insights de desempenho de consulta: verifique a guia Consultas principais por arquivos temporários para identificar consultas que estão gerando arquivos temporários. Essa situação sugere uma possível necessidade de aumentar work_mem.
• Guias de solução de problemas: utilize a guia Arquivos temporários elevados nas guias de solução de problemas para identificar consultas problemáticas.

Ajuste granular

Ao gerenciar o parâmetro work_mem, geralmente é mais eficiente adotar uma abordagem de ajuste granular em vez de definir um valor global. Essa abordagem garante que você aloque a memória de forma criteriosa com base nas necessidades específicas de processos e usuários. Ela também minimiza o risco de encontrar problemas de memória insuficiente. Veja como fazer isso:

• Nível de usuário: se um usuário específico estiver envolvido principalmente em tarefas de agregação ou relatório, que têm uso intenso de memória, considere personalizar o valor work_mem para esse usuário. Use o comando ALTER ROLE para melhorar o desempenho das operações do usuário.

• Nível de função/procedimento: nos casos em que funções ou procedimentos específicos estão gerando arquivos temporários substanciais, aumentar o valor work_mem no nível específico da função ou do procedimento pode ser benéfico. Use o comando ALTER FUNCTION ou ALTER PROCEDURE para alocar especificamente mais memória a essas operações.

• Nível de Banco de Dados: altere work_mem no nível do banco de dados se apenas bancos de dados específicos estiverem gerando grandes quantidades de arquivos temporários.

• Nível global: se uma análise do sistema revelar que a maioria das consultas está gerando pequenos arquivos temporários, enquanto apenas algumas estão criando arquivos grandes, pode ser prudente aumentar globalmente o valor work_mem. Essa ação facilita a maioria das consultas a serem processadas na memória, para que você possa evitar operações baseadas em disco e melhorar a eficiência. No entanto, sempre seja cauteloso e monitore a utilização de memória em seu servidor para garantir que ele possa lidar com o aumento do valor work_mem.

Determinação do valor mínimo work_mem para operações de classificação

Para localizar o valor mínimo work_mem para uma consulta específica, especialmente uma que gera arquivos de disco temporários durante o processo de classificação, comece considerando o tamanho do arquivo temporário gerado durante a execução da consulta. Por exemplo, se uma consulta estiver gerando um arquivo temporário de 20 MB:

1. Conecte-se ao banco de dados usando psql ou seu cliente PostgreSQL preferido.
2. Defina um valor work_mem inicial ligeiramente superior a 20 MB para considerar cabeçalhos adicionais ao processar na memória. Use um comando como: SET work_mem TO '25MB'.
3. Execute EXPLAIN ANALYZE na consulta problemática na mesma sessão.
4. Revise a saída de "Sort Method: quicksort Memory: xkB". Se indicar "external merge Disk: xkB", aumente o valor do work_mem de forma incremental e teste novamente até que "quicksort Memory" apareça. A aparência de "quicksort Memory" sinaliza que a consulta agora está operando na memória.
5. Depois de determinar o valor por meio desse método, você pode aplicá-lo globalmente ou em níveis mais granulares (conforme descrito anteriormente) para atender às suas necessidades operacionais.

maintenance_work_mem

Atributo	Valor
Valor padrão	Dependente da memória do servidor
Valor permitido	1MB-2GB
Tipo	Dinâmico
Nível	Global e granular

Descrição

maintenance_work_mem é um parâmetro de configuração no PostgreSQL. Ele rege a quantidade de memória alocada para operações de manutenção, como VACUUM, CREATE INDEX e ALTER TABLE. Ao contrário de work_mem, que afeta a alocação de memória para operações de consulta, maintenance_work_mem é reservado para tarefas que mantêm e otimizam a estrutura do banco de dados.

Pontos-chave

• Limite de memória a vácuo: se você quer acelerar a limpeza de tuplas inativas com o aumento de maintenance_work_mem, lembre-se de que o VACUUM tem uma limitação interna para coletar identificadores de tupla inativas. Ele pode usar apenas até 1 GB de memória para esse processo.
• Separação de memória para vácuo automático: você pode usar a configuração autovacuum_work_mem para controlar a memória que as operações de vácuo automático usam independentemente. Essa configuração atua como um subconjunto de maintenance_work_mem. Você pode decidir a quantidade de memória usada pelo vácuo automático sem afetar a alocação de memória para outras tarefas de manutenção e operações de definição de dados.

Próximas etapas

Para obter informações sobre extensões PostgreSQL com suporte, consulte Extensões PostgreSQL no Banco de Dados do Azure para PostgreSQL – Servidor Flexível.