Como solucionar problemas de performance de clusters ou consultas no Amazon Redshift?

Breve descrição

Se você estiver enfrentando problemas de performance no cluster do Amazon Redshift, considere as seguintes abordagens:

• Monitore as métricas de performance do seu cluster.
• Confira as recomendações do Amazon Redshift Advisor.
• Analise os alertas de execução da consulta e o uso excessivo do disco.
• Verifique se há problemas de bloqueio e sessões ou transações de longa duração.
• Verifique sua configuração de gerenciamento de workloads (WLM).
• Verifique a manutenção e a performance do hardware do nó do cluster.

Resolução

Monitorar as métricas de performance do seu cluster

Se você observar problemas de performance com seu cluster do Amazon Redshift, revise as métricas e os gráficos de performance do cluster. As métricas e os gráficos de performance do cluster podem ajudar você a identificar a possível causa raiz da degradação da performance. Você pode visualizar dados de performance no console do Amazon Redshift para comparar o desempenho do cluster ao longo do tempo.

Um aumento nessas métricas pode indicar maior workload e contenção de recursos no cluster do Amazon Redshift. Para mais informações sobre o monitoramento de métricas de performance, consulte Monitorar o Amazon Redshift usando métricas do Amazon CloudWatch.

Verifique o detalhamento da execução da workload no console do Amazon Redshift para analisar consultas e tempos de execução específicos. Por exemplo, se você observar um aumento no tempo de planejamento da consulta, isso pode ser uma indicação de que uma consulta está aguardando um bloqueio.

Conferir as recomendações do Amazon Redshift Advisor

O Amazon Redshift Advisor oferece recomendações sobre como você pode melhorar e otimizar a performance do seu cluster do Amazon Redshift. O Amazon Redshift Advisor está disponível gratuitamente no console do Amazon Redshift. Use Recomendações do Amazon Redshift Advisor para aprender sobre possíveis áreas de melhoria para seu cluster. As recomendações são baseadas em padrões de uso comuns e nas práticas recomendadas do Amazon Redshift.

Analisar os alertas de execução da consulta e o uso excessivo do disco

Durante a execução da consulta, o Amazon Redshift observa a performance da consulta e indica se ela está sendo executada com eficiência. Se a consulta for identificada como ineficiente, o Amazon Redshift anotará o ID da consulta e fornecerá recomendações para melhorar sua performance. Essas recomendações são registradas em STL_ALERT_EVENT_LOG uma tabela interna do sistema.

Se você observar uma consulta lenta ou ineficiente, verifique as entradas STL_ALERT_EVENT_LOG. Para recuperar informações da tabela STL_ALERT_EVENT_LOG, use a seguinte consulta:

SELECT TRIM(s.perm_table_name) AS TABLE
    , (SUM(ABS(DATEDIFF(SECONDS, Coalesce(b.starttime, d.starttime, s.starttime), CASE
            WHEN COALESCE(b.endtime, d.endtime, s.endtime) > COALESCE(b.starttime, d.starttime, s.starttime)
            THEN COALESCE(b.endtime, d.endtime, s.endtime)
        ELSE COALESCE(b.starttime, d.starttime, s.starttime)
    END))) / 60)::NUMERIC(24, 0) AS minutes
    , SUM(COALESCE(b.ROWS, d.ROWS, s.ROWS)) AS ROWS
    , TRIM(SPLIT_PART(l.event, ':', 1)) AS event
    , SUBSTRING(TRIM(l.solution), 1, 60) AS solution
    , MAX(l.QUERY) AS sample_query
    , COUNT(DISTINCT l.QUERY)
FROM STL_ALERT_EVENT_LOG AS l
LEFT JOIN stl_scan AS s
    ON s.QUERY = l.QUERY AND s.slice = l.slice AND s.segment = l.segment
LEFT JOIN stl_dist AS d
    ON d.QUERY = l.QUERY AND d.slice = l.slice AND d.segment = l.segment
LEFT JOIN stl_bcast AS b
    ON b.QUERY = l.QUERY AND b.slice = l.slice AND b.segment = l.segment
WHERE l.userid > 1 AND l.event_time >= DATEADD(DAY, -7, CURRENT_DATE)
GROUP BY 1, 4, 5
ORDER BY 2 DESC, 6 DESC;

Essa consulta lista os IDs de consulta e os problemas e ocorrências mais comuns da consulta em execução no cluster.

Aqui está um exemplo de saída da consulta e as informações que descrevem por que seu alerta foi acionado:

table | minutes | rows |               event                |                        solution                        | sample_query | count
-------+---------+------+------------------------------------+--------------------------------------------------------+--------------+-------
NULL  |    NULL | NULL | Nested Loop Join in the query plan | Review the join predicates to avoid Cartesian products |      1080906 |     2

Analise a performance da consulta verificando as consultas de diagnóstico para ajuste da consulta. Certifique-se de que suas operações de consulta sejam projetadas para execução com eficiência. Por exemplo, nem todas as operações de união são efetivas. Uma união de loop aninhado é o tipo de união menos eficaz e deve ser evitada, se possível, pois esse tipo aumenta significativamente o tempo de execução da consulta.

Identifique as consultas que realizam os loops aninhados para ajudar você a diagnosticar o problema. Para mais informações sobre como diagnosticar problemas comuns de uso do disco, consulte Como solucionar problemas de uso alto ou total do disco com o Amazon Redshift?

Verificar se há problemas de bloqueio e sessões ou transações de longa duração

Antes de uma consulta ser executada no cluster, talvez seja necessário obter bloqueios em nível de tabela nas tabelas envolvidas na execução da consulta. Pode haver casos em que as consultas aparecem como “suspensas” ou há um aumento no tempo de execução da consulta. Se você observar um aumento no tempo de execução da consulta, um problema de bloqueio pode ser a causa. Para mais informações sobre um tempo de execução de consultas atrasado, consulte Por que meu tempo de planejamento de consulta é tão alto no Amazon Redshift?

Se a sua tabela estiver bloqueada atualmente por outro processo ou consulta, sua consulta não poderá continuar. Como resultado, você não verá sua consulta aparecer na tabela STV_INFLIGHT. Em vez disso, sua consulta em execução aparecerá na tabela STV_RECENTS.

Às vezes, uma consulta suspensa é causada por uma transação de longa duração. Para evitar que transações de longa duração afetem a performance da consulta, considere as seguintes dicas:

• Identifique as sessões de longa duração e encerre-as imediatamente. Você pode usar as tabelas STL_SESSIONS e SVV_TRANSACTIONS para verificar transações de longa duração.
• Crie suas consultas para que o Amazon Redshift possa processá-las com rapidez e eficiência.

Observação: transações de longa duração também afetam a capacidade de VACUUM de recuperar espaço em disco, resultando em um maior número de linhas fantasmas ou de linhas não confirmadas. As linhas fantasmas que são examinadas por consultas podem afetar a performance da consulta.

Para mais informações sobre como identificar sessões de longa duração que podem causar bloqueios de tabela, consulte Como detectar e liberar bloqueios no Amazon Redshift?

Verificar sua configuração de gerenciamento de workloads (WLM)

Dependendo da configuração de WLM, uma consulta pode começar a ser executada imediatamente ou passar algum tempo na fila. O objetivo sempre deve ser minimizar o tempo em que uma consulta fica na fila para execução. Se você deseja definir suas filas, verifique sua alocação de memória de WLM.

Para verificar as filas de WLM de um cluster em alguns dias, use a seguinte consulta:

SELECT *, pct_compile_time + pct_wlm_queue_time + pct_exec_only_time + pct_commit_queue_time + pct_commit_time AS total_pcnt
FROM
(SELECT IQ.*,
   ((IQ.total_compile_time*1.0 / IQ.wlm_start_commit_time)*100)::DECIMAL(5,2) AS pct_compile_time,
   ((IQ.wlm_queue_time*1.0 / IQ.wlm_start_commit_time)*100)::DECIMAL(5,2) AS pct_wlm_queue_time,
   ((IQ.exec_only_time*1.0 / IQ.wlm_start_commit_time)*100)::DECIMAL(5,2) AS pct_exec_only_time,
   ((IQ.commit_queue_time*1.0 / IQ.wlm_start_commit_time)*100)::DECIMAL(5,2) pct_commit_queue_time,
   ((IQ.commit_time*1.0 / IQ.wlm_start_commit_time)*100)::DECIMAL(5,2) pct_commit_time
  FROM
    (SELECT trunc(d.service_class_start_time) AS DAY,
       d.service_class,
       d.node,
       COUNT(DISTINCT d.xid) AS count_all_xid,
       COUNT(DISTINCT d.xid) -COUNT(DISTINCT c.xid) AS count_readonly_xid,
       COUNT(DISTINCT c.xid) AS count_commit_xid,
       SUM(compile_us) AS total_compile_time,
       SUM(datediff (us,CASE WHEN d.service_class_start_time > compile_start THEN compile_start ELSE d.service_class_start_time END,d.queue_end_time)) AS wlm_queue_time,
       SUM(datediff (us,d.queue_end_time,d.service_class_end_time) - compile_us) AS exec_only_time,
       nvl(SUM(datediff (us,CASE WHEN node > -1 THEN c.startwork ELSE c.startqueue END,c.startwork)),0) commit_queue_time,
       nvl(SUM(datediff (us,c.startwork,c.endtime)),0) commit_time,
       SUM(datediff (us,CASE WHEN d.service_class_start_time > compile_start THEN compile_start ELSE d.service_class_start_time END,d.service_class_end_time) + CASE WHEN c.endtime IS NULL THEN 0 ELSE (datediff (us,CASE WHEN node > -1 THEN c.startwork ELSE c.startqueue END,c.endtime)) END) AS wlm_start_commit_time
     FROM
       (SELECT node, b.*
          FROM (SELECT -1 AS node UNION SELECT node FROM stv_slices) a,
               stl_wlm_query b
         WHERE queue_end_time > '2005-01-01'
           AND exec_start_time > '2005-01-01') d
     LEFT JOIN stl_commit_stats c USING (xid,node)
     JOIN (SELECT query, MIN(starttime) AS compile_start, SUM(datediff (us,starttime,endtime)) AS compile_us
           FROM svl_compile
           GROUP BY 1) e USING (query)
    WHERE d.xid > 0
      AND d.service_class > 4
      AND d.final_state <> 'Evicted'
 GROUP BY trunc(d.service_class_start_time),
          d.service_class,
          d.node
 ORDER BY trunc(d.service_class_start_time),
          d.service_class,
          d.node) IQ)
WHERE node < 0 ORDER BY 1,2,3;

Essa consulta fornece o número total de transações (xid), o tempo de execução, o tempo na fila e os detalhes da fila de confirmação. Você pode verificar os detalhes da fila de confirmações para ver se as confirmações frequentes estão afetando a performance da workload.

Para verificar os detalhes das consultas em execução em um determinado momento, use a seguinte consulta:

select b.userid,b.query,b.service_class,b.slot_count,b.xid,d.pid,d.aborted,a.compile_start,b.service_class_start_time,b.queue_end_time,b.service_class_end_time,c.startqueue as commit_startqueue,c.startwork as commit_startwork,c.endtime as commit_endtime,a.total_compile_time_s,datediff(s,b.service_class_start_time,b.queue_end_time)
    as wlm_queue_time_s,datediff(s,b.queue_end_time,b.service_class_end_time) as wlm_exec_time_s,datediff(s, c.startqueue, c.startwork) commit_queue_s,datediff(s, c.startwork, c.endtime) commit_time_s,undo_time_s,numtables_undone,datediff(s,a.compile_start,nvl(c.endtime,b.service_class_end_time))
    total_query_s ,substring(d.querytxt,1,50) as querytext from (select query,min(starttime) as compile_start,max(endtime) as compile_end,sum(datediff(s,starttime,endtime)) as total_compile_time_s from svl_compile group by query) a left join stl_wlm_query
    b using (query) left join (select * from stl_commit_stats where node=-1) c using (xid) left join stl_query d using(query) left join (select xact_id_undone as xid,datediff(s,min(undo_start_ts),max(undo_end_ts)) as undo_time_s,count(distinct table_id)
    numtables_undone from stl_undone group by 1) e on b.xid=e.xid
WHERE '2011-12-20 13:45:00' between compile_start and service_class_end_time;

Substitua “2011-12-20 13:45:00" pela hora e data específicas que você deseja verificar se há consultas em fila e concluídas.

Analise a performance do hardware do seu nó de cluster

Durante a janela de manutenção do cluster, podem ocorrer tarefas de manutenção, como aplicação de patches, alterações na configuração interna e substituição de nós. Se um nó foi substituído durante a janela de manutenção, o cluster poderá estar disponível em breve. No entanto, pode levar algum tempo para que os dados sejam restaurados no nó substituído. Esse processo é conhecido como hidratação. Durante o processo de hidratação, a performance do cluster pode diminuir.

Para identificar quais eventos (como hidratação) afetaram a performance do cluster, verifique seus eventos de cluster do Amazon Redshift. Seus eventos de cluster informam você sobre qualquer ação de substituição de nó e/ou qualquer outra ação de cluster que seja executada.

Para monitorar o processo de hidratação, use a tabela STV_UNDERREPPED_BLOCKS. Os blocos que requerem hidratação podem ser recuperados usando a seguinte consulta:

SELECT COUNT(1) FROM STV_UNDERREPPED_BLOCKS;

Observação: a duração do processo de hidratação depende da workload do cluster. Para medir o progresso do processo de hidratação do seu cluster, verifique os blocos em determinados intervalos.

Para verificar a integridade de um determinado nó, use a consulta a seguir para comparar seu performance com a de outros nós:

SELECT day
  , node
  , elapsed_time_s
  , sum_rows
  , kb
  , kb_s
  , rank() over (partition by day order by kb_s) AS rank
FROM (
  SELECT DATE_TRUNC('day',start_time) AS day
    , node
    , sum(elapsed_time)/1000000 AS elapsed_time_s
    , sum(rows) AS sum_rows
    , sum(bytes)/1024 AS kb
    , (sum(bytes)/1024)/(sum(elapsed_time)/1000000) AS "kb_s"
  FROM svl_query_report r
    , stv_slices AS s
  WHERE r.slice = s.slice
    AND elapsed_time > 1000000
  GROUP BY day
    , node
  ORDER BY day
    , node
);

Aqui está um exemplo de saída de consulta:

day    node    elapsed_time_s    sum_rows         kb         kb_s  rank
...
4/8/20     0      3390446     686216350489    21570133592    6362    4
4/8/20     2      3842928     729467918084    23701127411    6167    3
4/8/20     3      3178239     706508591176    22022404234    6929    7
4/8/20     5      3805884     834457007483    27278553088    7167    9
4/8/20     7      6242661     433353786914    19429840046    3112    1
4/8/20     8      3376325     761021567190    23802582380    7049    8
...This output indicates that the node 7 processed 19429840046 Kb of data for 6242661 amount of seconds, which is a lot slower than the other nodes.

A proporção entre o número de linhas (coluna “sum_rows”) e o número de bytes processados (coluna “kb”) é praticamente a mesma. O número de linhas na coluna “kb_s” também é aproximadamente o mesmo que o número de linhas, dependendo da performance do hardware. Se você observar que um determinado nó está processando menos dados durante um período de tempo, essa baixa performance pode indicar um problema de hardware base. Para confirmar se há um problema de hardware subjacente, revise o gráfico de performance do nó.

---