Desempenho lento do banco de dados

Os sintomas típicos desse problema são os seguintes:

• Alta utilização de E/S (entrada/saída) do disco ou da memória
• Consultas lentas

A causa mais comum é a análise de consultas lentas. Siga as etapas a seguir para determinar se esse é o caso:

1. Listar as consultas de longa duração:

   select age(now(),query_start), pid, client_addr, state, substring(query,0,80) from pg_stat_activity where state != 'idle' order by 1 desc;
   

2. Validar bloqueio de consulta:

   select pg_blocking_pids(pid), pid AS blocked_pid, state, query, age(now(),query_start) AS duration from pg_stat_activity where array_length(pg_blocking_pids(pid),1) > 0 order by 1,2;
   

3. Verifique o plano de execução da consulta:

   Explain <query>;
   

4. Verifique se faltam estatísticas do banco de dados. Use a seguinte consulta para validar: Ação > Executar Analisar Como alternativa, execute o seguinte comando:

   Analyze <tablename>  select relname, n_live_tup, n_dead_tup, last_vacuum,last_autovacuum,last_analyze,last_autoanalyze, analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='public';
   

5. Verifique se o índice está faltando na tabela. PostgreSQL fornece métodos de índice, como B-Tree, hash, GiST,SP-GiST, GIN e BRIN. Use um desses métodos para criar o índice.

6. Verifique se a tabela está inchada. Execute a seguinte consulta para validar. Ação > Executar aspirador

   Como alternativa, use a seguinte instrução:

   SELECT
   relname AS table_name,
   pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
   pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24))) * 100 / pg_relation_size(c.oid)
   END
   ) AS bloat_percentage, pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24)))
   END
   )AS bloat_size
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r' AND n.nspname = 'public' -- Adjust schema if needed
   ORDER BY bloat_size DESC NULLS LAST;
   

7. Considere se o banco de dados ou os objetos foram criados com pg_dump ou pg_restore. Use Ação > atualizar estatísticas para atualizar.

8. Certifique-se de que há work_mem alocação suficiente, se a consulta estiver concluindo uma operação de classificação.

9. Use pg_stat instruções para descobrir as seguintes informações:

   • consultar o tempo médio de execução e o número de chamadas

   • Porcentagem de uso da CPU pelas consultas

   • porcentagem de uso da memória pelas consultas

pg_stat_activity, pg_stat_bgwriter, e pg_buffercache são ferramentas importantes no PostgreSQL para monitorar e compreender o desempenho do banco de dados.

Monitoramento de implantação e monitoramento de carga do banco de dados

Databases for PostgreSQL oferecem uma integração com o serviço IBM Cloud Monitoring para monitorar o uso de recursos em sua implementação. Utilize este serviço para monitorar sua implantação (disco e memória) e a carga do seu banco de dados.

Use Cloud Databases painéis para definir alertas sobre limites de CPU, memória e IOPS do disco. Muitas das métricas disponíveis, como uso de disco e IOPS, são úteis para ajudá-lo a configurar o dimensionamento automático em sua implementação. O Autoscaling não está habilitado por padrão, portanto, você deve configurá-lo manualmente.

Observar as tendências de uso e configurar o dimensionamento automático para responder a essas tendências pode ajudar a aliviar os problemas de desempenho antes que os bancos de dados se tornem instáveis devido ao esgotamento de recursos. Por exemplo, alterações na E/S do disco ou falhas na CPU ou na memória que resultam em problemas de desempenho.

IOPS de disco

O número de operações de entrada/saída por segundo (IOPS) é limitado pelo tipo e pelo tamanho do volume de armazenamento. Os volumes de armazenamento para Databases for PostgreSQL implantações são provisionados em volumes Block Storage de resistência na camada de 10 IOPS por GB.

Se sua carga operacional saturar ou exceder o limite de IOPS, as solicitações e operações do banco de dados serão atrasadas até que o subsistema de armazenamento possa recuperar o atraso. Períodos prolongados de carga pesada podem fazer com que sua implementação não consiga processar as consultas e se torne efetivamente indisponível. Você pode aumentar o número de IOPS disponível para sua implementação aumentando o tamanho do disco. Por exemplo, para 10 IOPS por camada de GB, você pode aumentar o IOPS aumentando o tamanho do volume.

Períodos prolongados de até 40-50% de utilização do disco podem ter um impacto negativo significativo no desempenho do banco de dados. Aloque pelo menos 100 GB de disco (1.000 IOPS) para ambientes de produção. IOPS = 10 × GB alocado (por exemplo, 100 GB = 1.000 IOPS)

Uso de memória

A memória é a maneira mais rápida e eficiente de acessar e processar dados. Por esse motivo, um banco de dados quase sempre tem um desempenho mais rápido usando a memória em vez de ler os dados do disco. Existem outras métricas a serem analisadas e consideradas, como acertos de cache, blocos lidos e blocos acionados. Apenas ver 100% de uso de memória não é, por si só, motivo para preocupação. Se a memória estiver esgotada e as páginas de troca forem utilizadas, o desempenho pode diminuir significativamente.

Você pode definir a quantidade de memória dedicada ao pool de buffer compartilhado do banco de dados ajustando shared_buffers na configuração do site Databases for PostgreSQL. O valor máximo recomendado é 25% da memória total da implementação. A alocação de muita memória para o pool de buffer compartilhado pode privar o sistema de memória para outros fins, prejudicar o desempenho ou até mesmo desativar o banco de dados.

Monitoramento de carga do banco de dados

Você tem duas opções para verificar a carga do banco de dados:

{

source logs|filter message.attr.durationMillis>=1000

}

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT username, database, queryid, query_preview, calls, total_exec_time, pct_exec_time, ROUND(SUM(pct_exec_time) OVER (ORDER BY total_exec_time DESC)) AS cum_pct_exec_time, avg_exec_time FROM ( SELECT pu.usename AS username, pd.datname AS database, pss.queryid, LEFT(pss.query, 50) AS query_preview, pss.calls, ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time, ROUND((100.0 * pss.total_exec_time / SUM(pss.total_exec_time) OVER ())::numeric, 2) AS pct_exec_time, ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time FROM pg_stat_statements pss JOIN pg_user pu ON pss.userid = pu.usesysid JOIN pg_database pd ON pss.dbid = pd.oid ) AS subquery ORDER BY total_exec_time DESC LIMIT 25;

SELECT
   username,
   database,
   queryid,
   query_preview,
   calls,
   pct_calls,
   ROUND(SUM(pct_calls) OVER (ORDER BY calls DESC)) AS cum_pct_calls,
   total_exec_time,
   avg_exec_time
FROM (
   SELECT
      pu.usename AS username,
      pd.datname AS database,
      pss.queryid,
      LEFT(pss.query, 50) AS query_preview,
      pss.calls,
      ROUND(100.0 * pss.calls / SUM(pss.calls) OVER (), 2) AS pct_calls,
      ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time,
      ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time
   FROM
      pg_stat_statements pss
      JOIN pg_user pu ON pss.userid = pu.usesysid
      JOIN pg_database pd ON pss.dbid = pd.oid
) AS subquery
ORDER BY
   calls DESC
LIMIT 25;

SELECT query, calls, total_exec_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Outras ações que você pode realizar para melhorar o desempenho

Você também pode considerar as seguintes ações para solucionar problemas de desempenho:

• Otimize consultas lentas

  • Execute EXPLAIN (ANALYZE, BUFFERS) em consultas lentas. Isso mostra como o banco de dados executa a consulta e onde há lentidão. Por exemplo:

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM student WHERE student_id = 12345;
  

  • Procure por:

    • Consultas que utilizam muita memória ou espaço em disco

    • Adicione IBM Cloud recursos conforme necessário

    • Dimensionar o disco/memória para obter IOPS e memória mais elevados

• Índices ausentes ou ineficientes são uma causa comum de consultas lentas. Use EXPLAIN para identificar varreduras sequenciais e considere adicionar índices.

• Execute o VACUUM para auxiliar na análise da integridade do banco de dados.

• Considere o uso de pooling de conexões para lidar com mais conexões. Databases for PostgreSQL define o número máximo de conexões ao banco de dados Databases for PostgreSQL como 115. 15 conexões são reservadas para o superusuário para manter o estado e a integridade do banco de dados e 100 conexões estão disponíveis para você e seus aplicativos. Depois que o limite de conexão for atingido, qualquer tentativa de iniciar uma nova conexão resultará em um erro. Para evitar sobrecarregar sua implementação com conexões, use a definição do conjunto de conexões ou escale sua implementação e aumente o limite de conexão dela. Para obter mais informações, consulte Gerenciando o pool PostgreSQL de conexões.

• Verifique se há receitas executando backups e upload em lote de dados, por exemplo: Os backups automáticos são concluídos diariamente e mantidos com um cronograma de retenção simples de 30 dias. Se um backup estiver travado, você pode verificar a seção Backups disponíveis e identificar o backup travado na página da interface do usuário da nuvem da instância do banco de dados.

• Verifique suas IBM Cloud notificações para saber se há alguma manutenção. Por exemplo, aplicação de patches em bancos de dados.

• Se você acredita que se trata de um problema da plataforma, como manutenção, entre em contato com IBM o Suporte com o CRN do banco de dados.