Com base no artigo de Peter Zaitsev sobre MySQL Performance Bottlenecks , quero falar um pouco sobre o PostgreSQL.
Os frameworks ORM são freqüentemente usados para trabalhar com PostgreSQL atualmente. Geralmente funcionam bem, mas com o tempo a carga aumenta e é necessário ajustar o servidor de banco de dados. Por mais confiável que o PostgreSQL seja, ele pode ficar lento conforme o tráfego aumenta.
Existem muitas maneiras de eliminar gargalos de desempenho, mas neste artigo iremos nos concentrar no seguinte:
- Parâmetros do servidor
- Gerenciamento de conexão
- Configuração de Autovacuum
- Configuração adicional de autovacuum
- Mesas de inchaço (inchaço)
- Pontos quentes nos dados
- Servidores de aplicativos
- Replicação
- Ambiente de servidor
Sobre "categorias" e "impacto potencial"
“Complexidade” refere-se à facilidade de implementação da solução proposta. E o "impacto potencial" dá uma indicação do grau de melhoria no desempenho do sistema. Porém, devido à idade do sistema, seu tipo, dívida técnica, etc. descrever com precisão a complexidade e o impacto pode ser problemático. Afinal, em situações difíceis, a escolha final é sempre sua.
Categorias:
- Complexidade
- Baixo
- Média
- Alto
- Baixo Médio Alto
- Impacto potencial
- Baixo
- A média
- Alto
- Baixo Médio Alto
Parâmetros do servidor
Dificuldade: baixa.
Impacto potencial: alto.
Não muito tempo atrás, havia momentos em que as versões atuais do postgres podiam ser executadas no i386. As configurações padrão foram alteradas desde então, mas ainda estão configuradas para usar a menor quantidade de recursos.
Essas configurações são muito fáceis de alterar e geralmente são definidas durante a instalação inicial. Valores incorretos desses parâmetros podem levar a alta utilização de CPU e E / S:
- Parâmetro efetivo_cache_size ~ 50 a 75%
- Parâmetro shared_buffers ~ 1/4 - 1/3 da quantidade de RAM
- Parâmetro work_mem ~ 10 MB
O valor recomendado para effective_cache_size, embora típico, pode ser calculado com mais precisão se nos referirmos a “top” - free + cached .
Calcular o valor de shared_buffers é um quebra-cabeça interessante. Você pode olhar para isso de dois lados: se você tiver um banco de dados pequeno, poderá definir o valor de shared_buffers grande o suficiente para caber todo o banco de dados na RAM. Por outro lado, você pode configurar o carregamento apenas de tabelas e índices usados com frequência na memória (lembre-se do 80/20) Anteriormente, era recomendado definir o valor para 1/3 da quantidade de RAM, mas com o tempo, conforme a quantidade de memória crescia, ele foi reduzido para 1/4. Se houver pouca memória alocada, a E / S e a carga do processador aumentarão. Muita alocação de memória será indicada ao atingir o platô do processador e a carga de E / S.
Outro fator a considerar é o cache do sistema operacional . Com RAM suficiente, o Linux armazenará em cache tabelas e índices na memória e, dependendo da configuração, pode fazer o PostgreSQL acreditar que está lendo dados do disco em vez da RAM. A mesma página está no buffer do postgres e no cache do sistema operacional, e esta é uma das razões para não tornar shared_buffers muito grandes. Usando a extensão pg_buffercachevocê pode ver o uso do cache em tempo real.
O parâmetro work_mem especifica a quantidade de memória usada para operações de classificação. Definir este valor muito baixo garante um desempenho ruim, uma vez que a classificação será executada usando arquivos temporários no disco. Por outro lado, embora a definição de um valor alto não afete o desempenho, com um grande número de conexões existe o risco de ficar sem RAM. Ao analisar a memória usada por todas as solicitações e sessões, você pode calcular o valor necessário.
Usando EXPLAIN ANALYZE, você pode ver como as operações de classificação são realizadas e, alterando o valor da sessão, determinar quando o flush para o disco começa.
Você também pode usar benchmarks sistemas.
Gerenciamento de conexão
Dificuldade: baixa.
Impacto potencial: Baixa-Média-Alta
A carga alta geralmente está associada a um aumento nas sessões do cliente por unidade de tempo. Muitos deles podem bloquear processos, causar atrasos ou até mesmo levar a erros.
A solução simples é aumentar o número máximo de conexões simultâneas:
# postgresql.conf: default is set to 100<br />max_connections
Mas uma abordagem mais eficiente é o pool de conexões . Existem muitas soluções, mas a mais popular é o pgbouncer . O PgBouncer pode gerenciar as conexões usando um dos três modos:
- (session pooling). . , . , . .
- (transaction pooling). . PgBouncer , , .
- (statement pooling). . . , .
Você também deve prestar atenção ao Secure Socket Layer (SSL). Quando ativadas, as conexões usarão SSL por padrão, o que aumentará a carga no processador em comparação com as conexões não criptografadas. Para clientes regulares, você pode configurar a autenticação baseada em host sem SSL (
pg_hba.conf) e usar SSL para tarefas administrativas ou para replicação de streaming.
Configuração de Autovacuum
Dificuldade: média.
Impacto potencial: baixo-médio.
O Controle de simultaneidade de várias versões é um dos princípios fundamentais que tornam o PostgreSQL uma solução de banco de dados popular. No entanto, um dos problemas mais irritantes é que, para cada registro alterado ou excluído, são criadas cópias não utilizadas, que devem ser descartadas. Um processo de autovacuum configurado incorretamente pode degradar o desempenho. Além disso, quanto mais carregado o servidor, mais o problema se manifesta.
Os seguintes parâmetros são usados para controlar o daemon autovacuum:
- autovacuum_max_workers. ( ). , . . . .
- maintenance_work_mem. , . , . , .
- autovacuum_freeze_max_age TXID WRAPAROUND. , , . , , , . , txid, . / txid pg_stat_activity WRAPAROUND.
Cuidado para não sobrecarregar a RAM e a CPU. Quanto maior o valor definido inicialmente, maior o risco de esgotamento de recursos quando a carga no sistema aumentar. Se definido muito alto, o desempenho pode cair drasticamente quando um determinado nível de carga é excedido.
Semelhante ao cálculo de work_mem , este valor pode ser calculado aritmeticamente ou comparados para obter valores ideais .
Configuração adicional de autovacuum
Dificuldade: alta.
Impacto potencial: alto.
Este método, devido à sua complexidade, só deve ser usado quando o desempenho do sistema já está à beira dos limites físicos do host e isso realmente se tornou um problema.
As opções de autovacuum de tempo de execução são configuradas em
postgresql.conf. Infelizmente, não existe uma solução única para todos que funcione em qualquer sistema de alta carga.
Opções de armazenamento para tabelas . Freqüentemente, em um banco de dados, uma parte significativa da carga cai em apenas algumas tabelas. Personalizar as configurações de autovacuum para uma mesa é uma ótima maneira de evitar ter que iniciar manualmente o VACUUM, o que pode afetar significativamente o sistema.
Você pode personalizar tabelas usando o comando :
ALTER TABLE .. SET STORAGE_PARAMETERMesas de inchaço (inchaço)
Dificuldade: baixa.
Impacto potencial: Médio-alto.
Com o tempo, o desempenho do sistema pode diminuir devido a políticas de limpeza inadequadas devido ao inchaço excessivo das tabelas. Portanto, mesmo configurar o daemon autovacuum e iniciar manualmente o VACUUM não resolve o problema. Nestes casos, a extensão pg_repack vem ao resgate .
Usando a extensão pg_repack , você pode reconstruir e reorganizar tabelas e índices em produção
Pontos quentes nos dados
Dificuldade: alta.
Impacto potencial: Baixo-Médio-Alto.
Assim como no MySQL , o PostgreSQL depende de seus fluxos de dados para se livrar dos pontos de acesso e pode até mesmo alterar a arquitetura do seu sistema.
Em primeiro lugar, você deve prestar atenção ao seguinte:
- Índices . Certifique-se de que haja índices nas colunas que estão sendo pesquisadas. Você pode usar catálogos e exibições do sistema para monitorar e verificar se as consultas estão usando índices. Use as extensões pg_stat_statement e pgbadger para analisar o desempenho da consulta.
- Heap Only Tuples (HOT) . Pode haver muitos índices. Você pode reduzir o inchaço potencial e reduzir o tamanho da tabela descartando índices não utilizados.
- . , , . , , , . , . , , .
- . postgres. , .
- . , . . , !
Dificuldade: baixa.
Impacto potencial: alto.
Evite executar aplicativos (PHP, Java e Python) e postgres no mesmo host. Tenha cuidado com os aplicativos nessas linguagens, pois eles podem consumir grandes quantidades de RAM, especialmente o coletor de lixo, o que acarreta competição com os sistemas de banco de dados por recursos e desempenho geral reduzido.
Replicação
Dificuldade: baixa.
Impacto potencial: alto.
Replicação síncrona e assíncrona. As versões recentes do postgres oferecem suporte à replicação lógica e de streaming nos modos síncrono e assíncrono. Embora o modo de replicação padrão seja assíncrono, você precisa considerar as implicações do uso da replicação síncrona, especialmente em redes com latência significativa.
Ambiente de servidor
Por último, mas não menos importante, é um simples aumento na capacidade do host. Vamos dar uma olhada no que cada um dos recursos afeta em termos de desempenho do PostgreSQL:
- . , . . , , -.
- . , , . .
- . .
- -, ,
- .
- . , .
- .
- . .
- WAL-, , , . , (log shipping) , , .
: