Como soluciono o alto uso da CPU no Amazon Redshift?

Breve descrição

O Amazon Redshift foi projetado para utilizar todos os recursos disponíveis durante a execução de consultas. Isso significa que você pode esperar picos no uso da CPU no cluster do Redshift. Um aumento na utilização da CPU pode depender de fatores como workloads do cluster, dados distorcidos e não classificados ou tarefas do nó líder.

No entanto, se o uso da CPU afetar o tempo de consulta, considere as seguintes abordagens:

• Analise a workload do cluster do Redshift.
• Mantenha a higiene dos seus dados.
• Atualize o design das tabelas.
• Verifique se há atualizações de manutenção.
• Verifique se há picos no uso da CPU do node líder.
• Use o Amazon CloudWatch para monitorar picos na utilização da CPU.

Resolução

Analisar a workload do cluster do Redshift

Os seguintes fatores podem afetar a utilização da CPU no cluster do Redshift:

• Aumento da workload (devido ao aumento de consultas em execução). O aumento da workload aumenta o número de conexões de banco de dados, causando maior simultaneidade de consultas.
• O maior número de consultas simultâneas afeta a contenção de recursos, o tempo de espera do bloqueio e o tempo de espera da fila de gerenciamento de workloads (WLM).
• Mais conexões de banco de dados. Isso pode ser resultado de sessões inativas presentes no cluster. Sessões inativas podem causar problemas adicionais de contenção de bloqueios.

Enquanto as consultas estiverem em execução, recupere as informações de bloqueio. Para identificar sessões de longa duração, use a seguinte consulta SQL:

select *,datediff(s,txn_start,getdate())/86400||' days '||datediff(s,txn_start,getdate())%86400/3600||' hrs '||datediff(s,txn_start,getdate())%3600/60||' mins '||datediff(s,txn_start,getdate())%60||' secs' as "duration"
from svv_transactions where lockable_object_type='transactionid' and pid<>pg_backend_pid() order by 3;<br>

Em seguida, execute PG_TERMINATE_BACKEND para interromper qualquer transação de longa duração. Para evitar que essas sessões permaneçam abertas, certifique-se de que todas as transações estejam fechadas. Por exemplo, certifique-se de que todas as transações que começam com uma instrução BEGIN sejam acompanhadas por uma declaração END ou COMMIT.

Em seguida, execute a seguinte consulta SQL para identificar consultas que consomem muita CPU:

select stq.userid, stq.query, trim(stq.label) as label, stq.xid, stq.pid, svq.service_class,
query_cpu_usage_percent as "cpu_%",starttime, endtime, datediff(s,starttime, endtime) as duration_s,
substring(stq.querytxt,1,100) as querytext from stl_query stq
join svl_query_metrics svq on stq.query=svq.query
where query_cpu_usage_percent is not null and starttime > sysdate - 1
order by query_cpu_usage_percent desc;

Para analisar as etapas de execução em nível de segmento e de fatia para cada consulta, execute a seguinte consulta:

select query, segment, step, label ,is_rrscan as rrS, is_diskbased as disk, is_delayed_scan as DelayS, min(start_time) as starttime, max(end_time) as endtime, datediff(ms, min(start_time), max(end_time)) as "elapsed_msecs", sum(rows) as row_s , sum(rows_pre_filter) as rows_pf, CASE WHEN sum(rows_pre_filter) = 0 THEN 100 ELSE sum(rows)::float/sum(rows_pre_filter)::float*100 END as pct_filter, SUM(workmem)/1024/1024 as "Memory(MB)", SUM(bytes)/1024/1024 as "MB_produced" from svl_query_report where query in (<query_ids>) group by query, segment, step, label , is_rrscan, is_diskbased , is_delayed_scan order by query, segment, step, label;

Para mais informações sobre como ajustar essas consultas, consulte As dez principais técnicas de ajuste de performance para o Amazon Redshift.

Você também pode usar a visualização wlm_query_trend_hourly para revisar o padrão de workload do cluster do Redshift. Em seguida, determine quais das seguintes abordagens podem ajudar você a reduzir o tempo de espera na fila:

• Reduza a simultaneidade de consultas por fila para fornecer mais memória para cada slot de consulta. Essa redução ajuda as consultas que exigem mais memória a serem executadas com mais eficiência.
• Habilite a aceleração de consultas breves (SQA) para priorizar consultas de curta execução em vez de consultas de longa duração.
• Dimensione o cluster do Redshift para acomodar o aumento da workload. A escalabilidade de um cluster fornece mais memória e capacidade de computação, o que pode ajudar as consultas a serem executadas mais rapidamente. Para mais informações, consulte Como redimensionar um cluster do Redshift?

Manter a higiene dos seus dados

A integridade dos dados é medida pela porcentagem de estatísticas obsoletas e linhas não classificadas presentes em uma tabela. Uma alta porcentagem de ambos pode fazer com que o otimizador de consultas gere um plano de execução em que as consultas são executadas de forma ineficiente ao fazer referência a tabelas. Dados não classificados podem fazer com que as consultas examinem blocos de dados desnecessários, que exigem operações adicionais de E/S. Uma consulta com baixa performance afeta negativamente o uso da CPU do seu cluster do Redshift.

Use a exibição do sistema SVV_TABLE_INFO para recuperar stats_off e a porcentagem de dados não classificados de uma tabela. Essas porcentagens devem permanecer próximas de 0. Se as porcentagens forem altas, execute o utilitário de esquema Analyze & Vacuum no repositório do AWS Labs GitHub para atualizar suas tabelas.

Atualizar o design das tabelas

O design da tabela é governado pelas chaves de classificação designadas, pelo estilo de distribuição e pela chave de distribuição. A chave de distribuição e o estilo de distribuição determinam como os dados são distribuídos entre os nós.

Uma chave de distribuição ou um estilo de distribuição inadequados podem induzir uma distorção de distribuição entre os nós. Para reduzir a distorção da distribuição de dados, escolha o estilo de distribuição e a chave de classificação com base nos padrões de consulta e nos predicados. A chave de distribuição deve ser compatível com as condições de união em suas consultas e colunas com alta cardinalidade. Uma seleção adequada da chave de distribuição pode ajudar as consultas a realizar uniões de mesclagem em vez de uniões de hash ou de loop aninhado, o que acaba afetando por quanto tempo as consultas são executadas.

Para identificar tabelas com distribuição distorcida, use o script table_inspector.sql. Em seguida, use o Manual de design de tabelas do Amazon Redshift para escolher as chaves de classificação, chaves de distribuição e estilos de distribuição mais adequados para sua tabela.

Verificar se há atualizações de manutenção

O Amazon Redshift armazena em cache o código compilado, permitindo que as consultas reutilizem o código para segmentos executados anteriormente. Em seguida, o cache é apagado durante qualquer atualização de manutenção. Como resultado, as consultas executadas pela primeira vez após uma atualização de patch passarão algum tempo na compilação. Essa sobrecarga de compilação pode aumentar o uso da CPU de um cluster do Redshift.

Use a consulta SQL a seguir para verificar quantos segmentos estão sendo compilados a cada hora:

select "hour", count(query) total_queries, count(case when is_compiled='Y' then 1 else null end ) as queries_compiled_count, sum(segements_count) total_segments_count, sum(segments_compiled_count) total_segments_compiled_count from
(
  select q.query, date_trunc('h',q.starttime) as "hour", q.starttime, q.endtime, q.aborted, (case when compiled_queries.segments_compiled_count = 0 then 'N' ELSE 'Y' end) is_compiled, compiled_queries.segements_count, compiled_queries.segments_compiled_count
  from stl_query q
  left join (select query, count(segment) segements_count, sum(compile) segments_compiled_count from svl_compile group by query) compiled_queries on q.query = compiled_queries.query
  where q.userid > 1
  and q.starttime > trunc(sysdate) -7
)
group by "hour"
order by "hour";

Verificar se há picos no uso da CPU do nó líder

Tarefas do node líder, como análise e otimização de consultas, geração de código compilado e agregação de resultados de nós de computação, consomem recursos de CPU. Esse consumo leva ao aumento do uso da CPU do nó líder. O uso da CPU do nó líder também poderá aumentar se as consultas estiverem referenciando fortemente as tabelas do catálogo do sistema ou executando funções somente do nó líder.

Se o aumento no uso da CPU for causado por um nó líder, verifique em Eventos no console do Amazon Redshift. Verifique se alguma manutenção ocorreu no seu cluster do Redshift. Use a consulta SQL fornecida em Verificar atualizações de manutenção para verificar se mais segmentos estão sendo compilados do que o normal.

Use o CloudWatch para monitorar picos na utilização da CPU

Use as métricas do CloudWatch para comparar os picos entre a utilização da CPU e conexões de banco de dados. Analise a performance da workload verificando o gráfico de detalhamento da execução da workload. O gráfico de detalhamento da execução da workload mostra em quais estágios as consultas estão gastando mais tempo.

Para identificar as 100 principais consultas que consomem mais CPU durante um período especificado, use a consulta a seguir:

select qms.*, substring(q.querytxt,1,100) qtxt
from svl_query_metrics_summary qms
join stl_query q on q.query=qms.query
where q.starttime > sysdate - 1
and q.userid>1
order by qms.query_cpu_time desc nulls last limit 100;

Para recuperar uma lista de consultas que consomem mais recursos quando a CPU atinge 100%, use a seguinte consulta:

select a.userid, service_class, a.query, b.pid, b.xid, a.starttime, slices, max_rows, max_blocks_read, max_blocks_to_disk, max_query_scan_size, segment, round(max_cpu_time/(max_run_time*1.0)*100,2) as max_cpu_pcnt, round(cpu_time/(run_time*1.0)*100,2) as cpu_pcnt, max_cpu_time, max_run_time, case when segment > -1 then 'segment' else 'query' end as metric_lvl, text from pg_catalog.stv_query_metrics a left join stv_inflight b using (query) where step_type=-1 order by query, segment;

Para verificar a quantidade de dados que são processados por cada nó, execute a seguinte consulta:

select iq.day_d, sl.node, sum(iq.elapsed_ms) as elapsed, sum(iq.bytes) as bytes from (select start_time::date as day_d, slice,query,segment,datediff('ms',min(start_time),max(end_time)) as elapsed_ms, sum(bytes) as bytes from svl_query_report where end_time > start_time group by 1,2,3,4) iq join stv_slices as sl on (sl.slice = iq.slice) group by 1,2 order by 1 desc, 3 desc;

Você pode usar regras de monitoramento de consultas (QMR) para identificar e registrar qualquer consulta mal projetada. Por exemplo, regras de QMR podem ser definidas para registrar consultas que consomem alto uso da CPU ou um tempo de execução estendido.

---