Por padrão, o PostgreSQL não está configurado para a carga de trabalho. Os valores padrão são definidos para garantir que o PostgreSQL funcione em qualquer lugar com a menor quantidade de recursos. Existem configurações padrão para todas as configurações do banco de dados. A principal responsabilidade de um administrador ou desenvolvedor de banco de dados é configurar o PostgreSQL para se adequar à carga do sistema. Neste blog, descreveremos as recomendações básicas para ajustar as configurações do banco de dados PostgreSQL para melhorar o desempenho do banco de dados de acordo com a carga de trabalho.
Lembre-se de que, embora a otimização da configuração do servidor PostgreSQL melhore o desempenho, o designer do banco de dados também deve ter cuidado ao escrever consultas. Se as consultas realizarem uma varredura completa da tabela em que um índice possa ser usado ou executar junções pesadas ou operações de agregação caras, o sistema ainda poderá funcionar mal, mesmo se as configurações do banco de dados estiverem definidas corretamente. Ao escrever consultas no banco de dados, é importante prestar atenção ao desempenho.
No entanto, os parâmetros do banco de dados também são muito importantes, então vamos ver os oito que têm maior potencial para melhorar o desempenho.
Opções personalizadas do PostgreSQL
O PostgreSQL usa seu próprio buffer e também um IO do kernel com buffer. Isso significa que os dados são armazenados na memória duas vezes, primeiro no buffer do PostgreSQL e depois no buffer do kernel. Diferentemente de outros bancos de dados, o PostgreSQL não fornece E / S direta. Isso é chamado de buffer duplo. O buffer do PostgreSQL é chamado
shared_buffer , que é o parâmetro personalizado mais eficiente para a maioria dos sistemas operacionais. Este parâmetro define quanto de memória alocada o PostgreSQL utilizará para armazenar em cache.
O valor padrão para shared_buffer é definido como muito baixo e você não se beneficiará muito dele. Isso ocorre porque algumas máquinas e sistemas operacionais não suportam valores mais altos. Mas na maioria das máquinas modernas, você precisa aumentar esse valor para obter o desempenho ideal.
O valor recomendado é 25% da RAM total do computador. Você deve tentar valores mais baixos e mais altos, porque em alguns casos você pode obter um bom desempenho com uma configuração de mais de 25%. Mas a configuração real depende da sua máquina e do conjunto de dados de trabalho. Se o seu conjunto de dados de trabalho puder caber facilmente na sua RAM, você poderá aumentar o valor do shared_buffer para que ele contenha todo o banco de dados e todo o conjunto de dados de trabalho possa estar no cache. No entanto, você obviamente não deseja reservar toda a RAM para o PostgreSQL.
Percebe-se que, em ambientes de produção, o bom desempenho realmente dá grande importância ao shared_buffer, embora os testes sempre devam ser realizados para alcançar o equilíbrio certo.
Verificando o valor do shared_buffertestdb=
Nota : Tenha cuidado, pois alguns kernels não suportam um valor maior , especialmente no Windows.wal_buffers
O PostgreSQL primeiro grava as entradas no WAL (log de pré-registro) nos buffers e, em seguida, esses buffers são liberados para o disco. O tamanho padrão do buffer definido por
wal_buffers é 16 MB. Mas se você tiver muitas conexões simultâneas, um valor mais alto poderá melhorar o desempenho.
effective_cache_size
effective_cache_size fornece uma estimativa da memória disponível para armazenamento em cache do disco. Esta é apenas uma diretriz, não a quantidade exata de memória ou cache alocado. Ele não aloca memória real, mas informa ao otimizador a quantidade de cache disponível no kernel. Se esse parâmetro for definido como muito baixo, o planejador de consultas pode decidir não usar alguns índices, mesmo que sejam úteis. Portanto, definir um grande valor sempre faz sentido.
work_mem
Essa configuração é usada para classificação complexa. Se você precisar fazer uma classificação complexa, aumente o valor de
work_mem para obter bons resultados. Classificar na memória é muito mais rápido que classificar dados no disco. Configurá-lo com um valor muito alto pode causar um gargalo de memória para o seu ambiente, pois esta opção está relacionada à operação de classificação do usuário. Portanto, se você tiver muitos usuários tentando executar operações de classificação, o sistema destacará:
work_mem * total sort operations
para todos os usuários. Definir esse parâmetro globalmente pode resultar em um uso de memória muito alto. Portanto, é altamente recomendável que você o altere no nível da sessão.
work_mem = 2MB testdb=
O nó de classificação da solicitação inicial é avaliado em 514431.86. Custo é uma unidade calculada arbitrariamente. Para a solicitação acima, temos work_mem apenas 2 MB. Para fins de teste, vamos aumentar esse valor para 256 MB e ver se isso afeta o custo.
work_mem = 256MB testdb=
O custo do pedido foi reduzido de 514431,86 para 360617,36, ou seja, diminuiu 30%.
maintenance_work_mem
maintenance_work_mem é um parâmetro de memória usado para tarefas de manutenção. O valor padrão é 64 MB. Definir um valor alto ajuda em tarefas como VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY e ALTER TABLE.
maintenance_work_mem = 10MB postgres=
maintenance_work_mem = 256MB postgres=
O tempo de criação do índice é 170091.371 ms se o parâmetro maintenance_work_mem estiver definido como apenas 10 MB, mas diminui para 111274.903 ms quando aumentamos o parâmetro maintenance_work_mem para 256 MB.
synchronous_commit
Usado para garantir que uma confirmação de transação aguarde um WAL gravar no disco antes de retornar um status de conclusão bem-sucedida ao cliente. Essa é uma troca entre desempenho e confiabilidade. Se o seu aplicativo for projetado de forma que o desempenho seja mais importante que a confiabilidade, desative o
synchronous_commit . Nesse caso, a transação é confirmada muito rapidamente, porque não esperará que o arquivo WAL seja redefinido, mas a confiabilidade será comprometida. No caso de uma falha no servidor, os dados podem ser perdidos, mesmo que o cliente tenha recebido uma mensagem indicando que a confirmação da transação foi concluída com êxito.
checkpoint_timeout, checkpoint_completion_target
O PostgreSQL grava alterações no WAL. O processo do ponto de verificação libera dados para arquivos. Esta ação é executada quando um ponto de interrupção (CHECKPOINT) ocorre. Essa é uma operação cara e pode causar um grande número de operações de E / S. Todo esse processo envolve operações caras de leitura / gravação no disco. Os usuários sempre podem iniciar a tarefa do ponto de verificação (CHECKPOINT) quando necessário, ou automatizar a inicialização usando os parâmetros
checkpoint_timeout e
checkpoint_completion_target .
O parâmetro checkpoint_timeout é usado para definir o tempo entre os pontos de interrupção do WAL. Definir um valor muito baixo reduz o tempo de recuperação após uma falha, porque mais dados estão sendo gravados no disco, mas também reduz o desempenho, pois cada ponto de verificação consome recursos valiosos do sistema.
checkpoint_completion_target é a fração do tempo entre os pontos de verificação para concluir um ponto de verificação. Os pontos de verificação de alta frequência podem afetar o desempenho. Para concluir sem problemas o trabalho do ponto de verificação,
checkpoint_timeout deve estar baixo. Caso contrário, o sistema operacional acumulará todas as páginas sujas até que a proporção seja observada e produzirá uma redefinição grande.
Conclusão
Há mais opções que você pode ajustar para obter melhor desempenho, mas elas têm menos impacto do que as destacadas aqui. No final, devemos sempre lembrar que nem todos os parâmetros são relevantes para todos os tipos de aplicações. Alguns aplicativos funcionam melhor ao definir opções e outros não. As configurações do banco de dados PostgreSQL devem ser adaptadas às necessidades específicas do aplicativo e do sistema operacional em que é executado.