Configurar o banco de dados corretamente é apenas a primeira etapa no ajuste de desempenho do PostgreSQL. Com cada tabela que você adiciona e cada consulta que você executa, seus bancos de dados exigirão manutenção e atualizações para garantir a otimização ideal ao PostgreSQL.
A otimização do PostgreSQL é bastante direta, no entanto, existem algumas coisas que ele precisa saber.
Quando se trata de ajuste de desempenho do PostgreSQL de um aplicativo, uma regra se aplica: não otimize antecipadamente. Comece primeiro implementando seu banco de dados e esquema. Então, quando tiver sua configuração ideal, você pode começar a monitorar suas consultas SQL com ferramentas como Retrace .
Isso é importante porque o ajuste de desempenho do PostgreSQL tem tudo a ver com compensações. Você pode aceitar uma consulta de execução lenta em uma parte do seu aplicativo que não é usada com frequência para obter um tempo de resposta extremamente rápido em uma consulta realizada com frequência.
Hoje, vou guiá-lo por algumas otimizações diferentes do PostgreSQL. Primeiro, veremos algumas das opções básicas de configuração disponíveis:
- Atualizações de hardware – alterações que você pode fazer no servidor físico
- Configuração – Modificações para a configuração padrão do PostgreSQL
- Vacuum – Maneiras em que as configurações de vácuo podem melhorar o desempenho
- Assim que seu sistema estiver configurado, veremos como você pode analisar e melhorar seu esquema:
- Analise o desempenho de sua consulta
- Analise seus registros
- Índice para desempenho de consulta
Reforçando seu hardware PostgreSQL
O lugar óbvio para começar quando se trata de otimizar o desempenho do PostgreSQL é examinar o hardware do próprio sistema. Alguns dos fatores mais importantes para seu banco de dados são a memória disponível, CPU e espaço em disco e desempenho.
Quando se trata de atualizações de hardware, você deve considerar o seguinte:
Atualizando sua memória. A memória é o que seu sistema usa para armazenar em cache efetivamente os dados de que precisa com freqüência. É importante manter a memória em mente ao otimizar suas consultas posteriormente. Quanto maior o cache, menos o banco de dados precisará ir para o disco – o que pode ser doloroso para o desempenho. O PostgreSQL tenta manter os dados acessados com mais frequência na memória para fazer melhorias de desempenho com base em como suas consultas são realizadas e a configuração fornecida. Mas voltaremos à otimização de desempenho baseada em memória mais tarde.
Separando o aplicativo do banco de dados. Com o objetivo de aumentar a memória, se você estiver executando o aplicativo de banco de dados no mesmo servidor que o aplicativo, talvez queira isolá-lo. Será difícil realmente analisar e melhorar o desempenho do banco de dados se um componente separado estiver afetando suas métricas e ambiente.
Configuração do banco de dados: o quê, por quê e como
O PostgreSQL vem com um conjunto de configurações padrão. Esta configuração padrão é definida para compatibilidade e é essencialmente a melhor estimativa que tenta se adequar a todos os casos de uso possíveis do PostgreSQL. Felizmente para você, isso significa que há alguns ganhos rápidos em potencial se você começar a ajustar a configuração padrão.
Antes de mergulharmos e passarmos por algumas das otimizações de configuração mais comuns que você pode fazer, vale a pena apontar que existem várias ferramentas de ajuste de desempenho do PostgreSQL disponíveis – como PGTune – que tentam fazer parte desse trabalho de configuração para você. Sua eficácia varia, mas pode valer a pena experimentar o delta entre a saída dessas ferramentas e a configuração atual do banco de dados.
As configurações do banco de dados no PostgreSQL são feitas diretamente no arquivo de configuração ( postgresql.conf) ou através da execução de um comando ALTER SYSTEM . Se você deseja ver todas as configurações atuais do seu banco de dados agora, basta executar o comando SHOW da seguinte forma:
SHOW ALL
Este comando listará todas as configurações existentes e suas configurações. Também é importante observar que configurações diferentes serão aplicadas apenas em certas condições, como a reinicialização do banco de dados. Algumas mudanças na configuração exigirão a reinicialização do servidor; outros exigirão uma recarga da configuração.
Depois de fazer alterações na configuração do seu banco de dados, você pode ver detalhes como se uma reinicialização é necessária para a atualização da configuração executando o seguinte comando:
SELECT * FROM pg_settings WHERE pending_restart = true ;
Alterações na configuração de ajuste de desempenho do PostgreSQL
Provavelmente, você desejará modificar várias configurações diferentes para obter o máximo do banco de dados PostgreSQL. Vamos examinar algumas das principais configurações que você pode alterar para obter mais desempenho do seu sistema.
max_connections
As conexões são como seus aplicativos se comunicam com seu banco de dados. Cada conexão exigirá alguma vibração de comunicação e configuração para ser estabelecida. No entanto, uma vez estabelecidas, as consultas podem ser enviadas para o seu banco de dados. Seu banco de dados PostgreSQL virá com um número padrão de conexões, que você pode alterar. Você quer ter certeza de que seus aplicativos não estão se conectando desnecessariamente, pois isso pode ser caro e pode afetar o desempenho. As alocações de memória são realizadas em uma base por conexão, então você vai querer equilibrar entre a alocação de memória e o número de conexões.
checkpoint_segments
Um ponto de verificação é uma ação periódica que armazena informações sobre o seu sistema. Por padrão, um ponto de verificação será executado após vários segmentos, mas dependendo do seu sistema, você pode querer aumentar este valor. Falaremos mais adiante neste artigo sobre como você pode fazer logout dos dados do ponto de verificação, mas a configuração do ponto de verificação é importante porque pode ser uma operação bastante cara. Muitas vezes, pensa-se que a configuração padrão é muito agressiva e executa pontos de verificação com muita frequência, portanto, convém aumentar esse valor para tornar os pontos de verificação menos frequentes.
work_mem
Como mencionei anteriormente, a alocação e o gerenciamento de memória são uma grande parte do ajuste de desempenho do PostgreSQL. Se seu sistema estiver fazendo muitas classificações complexas, aumentar a memória de classificação pode ajudar o banco de dados a otimizar sua configuração para sua configuração. Isso permite que o PostgreSQL armazene em cache mais dados na memória enquanto realiza sua classificação, em vez de fazer chamadas caras para o disco.
random_page_cost
Essa configuração é essencialmente a quantidade de tempo que o otimizador deve gastar lendo a memória antes de chegar ao disco. Você deve alterar essa configuração apenas quando tiver feito outras otimizações baseadas em plano que abordaremos em breve, como vacuum, indexar ou alterar suas consultas e esquema.
Essas são apenas algumas das otimizações que você pode fazer para as configurações do banco de dados, mas há muito mais . Agora que você sabe como ajustar a configuração do seu banco de dados, vamos examinar outra área de investigação:
Vacuum
Use vacuum para evitar inchaço dos objetos. O vacuum é uma varredura que marca as tuplas como não sendo mais necessárias para que possam ser substituídas. Deixar de fazer isso pode significar que você tem tuplas mortas remanescentes em seu sistema. Essas tuplas mortas são freqüentemente chamadas de inchaço. O inchaço se origina principalmente de registros que estão sendo excluídos, atualizados ou inseridos.
O PostgreSQL habilita o Vacuum por padrão, mas assim como você pode definir outras configurações, você também pode configurar o Vacuum. Você pode até definir as configurações de uma base por tabela para uma otimização mais refinada.
Para realizar um Vacuum, você simplesmente executa o comando:
VACUUM
Se quiser ver o histórico de Vacuum executados anteriormente, você pode fazer isso executando o seguinte:
SELECT * FROM pg_stat_user_tables
De modo geral, o Vacuum regular não pode ser suficiente. O Vacuum mais frequente reduz o inchaço ao mínimo e garante que o desempenho do banco de dados permaneça alto. Embora o autovacuuming já esteja configurado, você pode querer alterar as configurações para ser mais agressivo.
Parte 2: Analisando o desempenho do PostgreSQL
A configuração do sistema inicial terá um limite nos aumentos de desempenho do PostgreSQL. Embora você possa fazer alguma configuração antecipadamente, muitas das suas decisões de configuração serão orientadas pelos dados e análises que você tem do seu sistema. Você deseja otimizar a configuração, saber como está sendo consultado e otimizar com base nesses casos de uso. O PostgreSQL e a comunidade nos fornecem algumas ferramentas úteis para fazer essas otimizações.
Atualize os dados do algoritmo Postgres com ANALYZE
Antes de continuarmos, é importante entender o ciclo de vida de uma consulta. O ciclo de vida é o que acontece desde uma solicitação inicial ao banco de dados até a resposta que ele envia. O PostgreSQL não mergulha apenas em um grande saco de memória. Quando você tem muitos dados, a busca bruta de seus dados pode levar a quedas de desempenho. Se você estiver varrendo seu banco de dados sequencialmente (geralmente chamado de varredura de tabela) em busca de seus dados, seu desempenho aumentará linearmente – mais linhas, desempenho mais lento. Mas podemos fazer melhor do que isso.
Então, qual é o ciclo de vida de uma consulta? Inicialmente, há uma transmissão da string de consulta ao PostgreSQL. A string de consulta é então analisada e um plano é criado. Os planos são muito importantes. Os planos são as etapas que o PostgreSQL executará para localizar os dados solicitados. A maneira como você define a configuração do banco de dados, o esquema e os índices (mais sobre índices em breve) afetarão o desempenho desses planos. Portanto, será importante entendermos os planos e como otimizá-los. Finalmente, o banco de dados executará o plano e recuperará os dados.
Pode ocorrer uma discrepância entre o plano de banco de dados que o PostgreSQL pretende usar para obter seus dados e a maneira como ele realmente os busca. Isso ocorre porque o PostgreSQL baseia seu plano em métricas e estatísticas raramente atualizadas. Alguns dados devem ser atualizados periodicamente para que as estatísticas usadas para os planos sejam atualizadas.
É aqui que entra ANALYZE. A execução do comando ANALYZE atualiza essas estatísticas para que o Postgres tenha um novo conjunto de dados sobre como criar seus planos. Portanto, se você estiver atualizando as tabelas ou o esquema ou adicionando índices, lembre-se de executar um comando ANALYZE depois para que as alterações tenham efeito.
Compreenda profundamente o desempenho da sua consulta
Uma das próximas áreas óbvias é a otimização de consulta. As consultas que você está executando podem ser ineficientes por vários motivos, portanto, precisamos encontrar uma maneira de entender o que está acontecendo com essas consultas – que é onde entra o comando EXPLAIN.
EXPLAIN fornece uma análise precisa de como o PostgreSQL executará sua consulta. O plano é baseado em estatísticas sobre a tabela e leva em consideração itens como índices de banco de dados para encontrar o caminho mais eficiente para seus dados. Mas EXPLAIN só vai adivinhar e dar a você um plano que ele acha que vai executar. Você pode executar EXPLAIN ANALYZE para obter não apenas as informações sobre o plano previsto, mas também uma atualização sobre o desempenho da consulta.
Quando você tiver o plano que o banco de dados está tentando executar, poderá começar a analisá-lo. Existem várias ferramentas para tornar a vida mais fácil, como PEV, que permite visualizar e entender a saída do seu comando EXPLAIN.
Registros como uma fonte de dados de ajuste de desempenho do PostgreSQL de alta qualidade
Vamos supor que você tenha um banco de dados em execução e esteja procurando depurar um desempenho lento no aplicativo. Uma maneira de fazer isso é por meio de logs. Logs são declarações de informações curtas que o aplicativo deixa para trás quando executa uma ação. Você pode então reunir essas ações e analisá-las em uma ferramenta de gerenciamento de log como o Retrace para obter uma imagem de como o sistema está se comportando.
Por padrão, seu aplicativo não registra todos os dados. Isso ocorre porque os aumentos no registro também afetam o desempenho do banco de dados. Portanto, enquanto examinamos como você pode alterar as configurações de log para analisar o desempenho , lembre-se de que as próprias configurações de log podem afetar o desempenho.
Os logs são emitidos para um arquivo em seu sistema, dependendo de sua configuração. Quando você descobrir onde seus logs estão, você pode usar uma ferramenta como Retrace para analisá-los. Retrace mostrará estatísticas como as consultas executadas com mais frequência, quanto tempo as consultas demoram em média e assim por diante. Essas métricas agregadas podem fornecer uma melhor compreensão de onde você pode ter gargalos de desempenho em seu sistema.
Portanto, agora que vemos como os logs são ótimos para entender o desempenho do sistema, como vamos configurá-los ou alterá-los?
log_line_prefix
Este é o formato de seus logs de banco de dados. Para ferramentas como o PGBadger, eles precisarão de dados ricos e também precisarão saber o formato dos dados. O prefixo da linha de log informa ao PostgreSQL em qual formato seus dados de log devem ser emitidos. Se quiser fazer qualquer análise de log significativa, você precisará definir esse valor para ser compatível com as ferramentas que está usando para analisar seus logs.
log_statement
Este é o nível de registro em que você está operando. Os níveis de log geralmente se referem ao nível de detalhe que você deseja em seus logs. Por exemplo, você quer absolutamente todos os logs ou apenas logs de um determinado tipo crítico? log_statement tem algumas configurações diferentes, incluindo:
ddl , que registra apenas mudanças estruturais no banco de dados
mod , que registra modificações nos dados existentes (basicamente tudo, exceto SELECT)
all , que registra … tudo
log_checkpoints
Conforme discutimos nas definições de configuração, os pontos de verificação no PostgreSQL são ações periódicas que armazenam dados sobre o seu sistema. Esses pontos de verificação de log podem, se excessivos, levar à degradação do desempenho. Se você suspeitar que pode ser o caso, ao habilitar os pontos de verificação de log, você poderá ver dados valiosos sobre esses pontos de verificação, como a frequência com que estão sendo executados e o que pode estar desencadeando-os.
logging_connection
Você também pode querer saber informações sobre conexões. Se você tem apenas um aplicativo conectado ao seu banco de dados, mas está vendo muitas conexões simultâneas, algo pode estar errado. Muitas conexões inundando seu banco de dados também podem significar que as solicitações estão falhando em alcançar o banco de dados e podem estar afetando os usuários finais de seu aplicativo.
Agora que temos muitos dados sobre nosso sistema, quais ferramentas temos para melhorar a estrutura de nosso sistema para ter mais desempenho?
Consultas mais rápidas com índices de banco de dados
Em um mundo sem índices, cada solicitação ao banco de dados levaria a uma varredura completa de toda a tabela para encontrar os resultados relevantes. Com um grande conjunto de dados, isso pode ser extremamente lento – e é aí que entra a indexação do banco de dados. Assim como em um livro, os índices fornecem informações ao mecanismo de banco de dados sobre aproximadamente em que parte do sistema estão os dados que você está procurando. Para indexar nosso sistema corretamente, precisamos entender nossos dados e como estamos tentando acessá-los. É por isso que é importante termos ferramentas de observação e monitoramento, como a configuração do Retrace para nos ajudar. Os índices não vêm de graça; se você atualizou o conteúdo de um livro, terá que atualizar o índice cada vez que fizer uma alteração no conteúdo.
O PostgreSQL possui alguns tipos diferentes de índice. A diferença entre eles é que todos usam um algoritmo diferente. Os índices disponíveis são B-tree (o índice padrão), Hash, GiST, SP-GiST e GIN. O PostgreSQL criará índices implícitos quando você criar uma chave primária ou uma restrição de chave única. No entanto, você terá que adicionar o restante de seus índices manualmente.
A sintaxe básica para um INDEX é:
CREATE INDEX index_name ON table_name ;
No entanto, você não deve usar um índice em alguns casos de uso – por exemplo, quando a sobrecarga de usar o índice excede o benefício do algoritmo, como em uma pequena tabela. Mas as tabelas que realizam grandes atualizações em lote também podem apresentar problemas de desempenho. Pode fazer sentido remover os índices dessas tabelas temporariamente durante a atualização, antes de restaurar os índices.
Aí está: ajuste de desempenho do PostgreSQL ultrarrápido
Esperançosamente, isso lhe dará uma compreensão de como iniciar o ajuste de desempenho do PostgreSQL em seu banco de dados. Agora você deve ser capaz de analisar suas consultas com EXPLAIN, extrair seus logs para uma visão mais aprofundada e modificar seus índices para um desempenho mais rápido. Além disso, você deve ser capaz de ajustar a configuração do seu banco de dados para corresponder ao uso, para que possa obter o máximo dele.
Lembre-se de que ajustar seu banco de dados exigirá tempo e prática. Portanto, seja paciente e fique curioso para saber mais sobre o seu sistema para obter os melhores resultados de
