Postgres de repente mais lento e maior uso da CPU

Question

Estamos executando um Postgres DB com cerca de 38 GB, hospedado em uma instância de 68 GB do EC2. Ele está funcionando bem, com carga em ~ 0,7 (em uma máquina de 8 núcleos) e pouco uso da CPU até cerca de 16 horas atrás, quando o uso da CPU aumentou ao longo de algumas horas e agora é muito maior do que antes (em torno de 20 % do total) e a média de carga aumentou de acordo (agora entre 5 e 8).

A máquina vê entre 100 e 300 conexões simultâneas, de acordo com pg_stat_activity. Nossa maior tabela é de cerca de 9.0GB, incluindo índices.

Veja o que descartamos:

• pg_top mostra que há sempre alguns threads sendo executados com alta porcentagem de CPU, enquanto o restante dos threads abaixo deles parece bem. Não há nada de extraordinário nos SELECTs e INSERTs lá.
• O IOWait não aumentou de jeito nenhum, e não parece que estamos atingindo o disco com mais frequência
• Até onde podemos dizer, os padrões de uso não mudaram; na verdade, segunda-feira é mais lenta que nos finais de semana
• Aumentamos o tamanho da nossa instância do memcached, sem sucesso
• Desativamos synchronous_commit, mas isso também não ajudou muito
• Tentamos aumentar e diminuir os buffers compartilhados (atualmente configurados para 2 GB); nem 4GB nem 1GB melhoraram as coisas
• Reiniciar o postgres e todos os nossos servidores apache não ajudou
• Reiniciar o pgbouncer, que nossas caixas Apache / Django usam para falar com o Postgres, também provou ser infrutífero
• Todas as nossas principais consultas têm os índices corretos e verificamos que o postgres as está usando. Fizemos um VACUUM ANALYZE apenas por segurança, e o planejador de consulta parece estar fazendo a coisa certa
• As consultas ficarão bem por alguns segundos e depois serão bloqueadas. uma varredura de índice que normalmente leva 200 ms ou menos, levará vários segundos inteiros.

Alguma idéia de como proceder ou rastrear esse problema?

    

Answer 1

Ative o registro completo por um curto período (~ 10 minutos), se puder. Salve o log e faça com que o pgfouine ( link ) o analise. É possível que você esteja fazendo mais consultas do que antes ou esteja fazendo uma consulta que não seja muito eficiente.

Em seguida, é possível que você tenha algumas consultas lentas afetando negativamente as coisas. Veja quais consultas estão sendo executadas agora:

SELECT pg_stat_activity.procpid AS pid, pg_stat_activity.usename AS username, pg_stat_activity.waiting, now() - pg_stat_activity.query_start AS age, pg_stat_activity.current_query AS query FROM pg_stat_activity WHERE pg_stat_activity.current_query <> '<IDLE>'::text ORDER BY now() - pg_stat_activity.query_start DESC;

Procure as consultas no topo da lista. Alguém está correndo há muito tempo? Se você tem uma transação que está aberta por mais de 16 horas, isso definitivamente vai atrasar muito as coisas.