No trabalho, estamos enfrentando uma espécie de problema misterioso. A cada poucas horas, uma de nossas tabelas ( tasks) vê a latência da consulta aumentar (causada pelo uso da CPU) seguida por uma explosão de LWLock:BufferContent, depois volta ao normal. Aqui está uma captura de tela dos insights de desempenho do RDS:
Fizemos muito trabalho para tentar mitigar isso, e o trabalho resultou em incidentes (anedoticamente) menos frequentes e menos graves. No entanto, o problema não desapareceu.
Primeiro notamos que havia algumas consultas que não atingiam os índices. Passei um tempo certificando-me de que todas as nossas consultas para esta tabela executassem varreduras de índice e somente de índice. As consultas tendem a ser assim:
SELECT COUNT(1) FROM tasks WHERE status = 'PENDING' AND NOT deleted
e temos índices correspondentes statuscom uma condição status = 'PENDING' AND deleted = FALSE. EXPLAIN ANALYZEmostra que o índice está sendo usado corretamente.
Descobrimos que algumas consultas que especificam uma condição em nossa userIdcoluna tinham um plano de consulta incorreto quando o usuário tinha um grande número de registros. Isso foi solucionado com correções na consulta e melhorias no índice. Pelo que podemos dizer, não há consultas que tenham um plano de consulta incorreto para quaisquer valores (ainda assim, o problema persiste).
Durante os picos, não há aumento na carga recebida. Antes, durante e depois do incidente, as consultas afetadas mostram o mesmo número de "chamadas/s" nos insights de desempenho do RDS, e nosso aplicativo cliente mostra um QPS estável, portanto, isso não é causado por um aumento nas consultas .
Observei que os EXPLAIN ANALYZEresultados dessas consultas mostram um aumento nas buscas de heap durante esses picos, mesmo para verificações somente de índice. Li que o heap ainda é atingido durante as varreduras de índice porque o mapa de visibilidade pode indicar que as páginas que contêm tuplas referenciadas pelo índice podem não estar visíveis. Este parecia ser um sinal forte: PENDINGas tarefas (conforme mostrado na consulta acima) são frequentemente linhas "quentes" que recebem muitas atualizações, por isso faz sentido que o mapa de visibilidade para páginas que contêm essas linhas exija buscas.
Para compensar, as configurações de vácuo automático foram ajustadas para causar vácuos muito mais frequentes. Agora vemos vácuos automáticos a cada poucas horas, mas o problema não desapareceu. Eu vi esses picos apenas 20 minutos após a conclusão do vácuo automático da mesa. Além disso, o número de buscas de heap relatado EXPLAIN ANALYZEdurante o pico pode ser de 1 a 2 ordens de magnitude maior que o número de PENDINGregistros de tarefas: o número raramente excede 200 no total, e podemos ver muitos milhares de buscas de heap em varreduras somente de índice para índices que contêm apenas PENDINGregistros.
Durante a depuração, percebi que o planejador de consultas tende a ajustar quais índices consulta ao longo do tempo, mesmo quando a carga permanece ~constante. Às vezes, ele escolhe índices que permitem varreduras de índice, mas não varreduras apenas de índice. Por exemplo, ele pode escolher um índice on statusalone e ignorar um índice on statuscondicional on status = 'PENDING' and not deleted, mesmo quando este último satisfaz perfeitamente a consulta. Ajustei as configurações seq_page_coste random_page_costpara ambos em 1.0vez dos padrões do Postgres (1 e 4, respectivamente), o que deve direcionar o Postgres a preferir varreduras de índice. Infelizmente, o problema persistiu (embora talvez com menos frequência). A captura de tela acima é após o ajuste das configurações.
Algumas informações adicionais:
- O problema afeta apenas esta tabela (ou devo dizer, as consultas para esta tabela).
- Todas as consultas na tabela durante o pico são afetadas, e não uma única consulta.
Neste ponto, estou quase sem ideias. Para referência, estamos no Postgres 14.10 rodando db.m5.16xlargecom um volume SSD GP2. Minha suspeita é que esta versão do Postgres só precisa de uma atualização (trabalho planejado), mas não é uma solução satisfatória.
Algo que me deixa um pouco perplexo é por que o uso de recursos que causa a latência é a CPU. Você pode ver na imagem acima o aumento em verde (CPU). Basicamente, em todas as consultas, deve haver muito poucos registros verificados. Todas as consultas são agregações de contagem e as condições das consultas são todas de igualdade simples. Aqui está uma das saídas de consulta afetadas quando não há pico:
Aggregate (cost=46.02..46.03 rows=1 width=8) (actual time=0.362..0.362 rows=1 loops=1)
" -> Index Only Scan using ""tasks_globalPending"" on tasks (cost=0.25..37.54 rows=3393 width=0) (actual time=0.239..0.353 rows=128 loops=1)"
Heap Fetches: 178
Planning Time: 0.161 ms
Execution Time: 0.377 ms
Muito rápido! Indiscutivelmente extremamente eficiente (embora eu prefira zero buscas de heap). E para um COUNT()valor que nunca retorna um valor superior a 300, é surpreendente como isso pode ir de 0,4 ms para 10-20 ms por consulta e queimar esse tempo no uso da CPU. O que isso está fazendo?
O mistério foi resolvido!
O problema eram consultas de longa duração em uma réplica de leitura
hot_standby_feedbackhabilitada. Consultas de longa duração na réplica para um trabalho de ETL estavam perfeitamente alinhadas com os picos. Descobri isso quando percebi que o topo do pico parecia sempre acontecer cerca de 30 minutos depois da hora. Procurando por coisas que funcionam nessa cadência, encontrei nosso trabalho de ETL.hot_standby_feedbackdiz ao primário "ei, estou fazendo uma coisa, avisarei você quando terminar". Isso evita o atraso na replicação.O atraso na replicação pode ocorrer durante consultas de longa duração na réplica devido à forma como a replicação do Postgres funciona. O Postgres replica as alterações nas tuplas em vez de enviar consultas e reproduzi-las na réplica. O "feed" de atualizações das tuplas chega em série e é aplicado na réplica. Considere este cenário:
Neste ponto, a réplica tem de pausar a aplicação dessas atualizações porque não sabe se essas tuplas específicas afetam o resultado da consulta. Isso faz com que a réplica fique atrás da primária.
hot_standby_feedbackpermite que o primário saiba que não deve fazer isso enquanto a réplica estiver em uma dessas consultas, permitindo que consultas simultâneas na réplica sejam concluídas com êxito com dados recentes do primário.A desvantagem é que as tabelas muito ocupadas parecem causar vários problemas (pelo menos no Postgres 14.10). A solução foi adicionar índices à tabela ocupada de modo que as consultas de longa duração na réplica se tornassem consultas relativamente rápidas. Feito isso, o problema desapareceu completamente no primário.