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 de consulta e o uso excessivo do disco.
• Verificar 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

Analise as métricas e os gráficos de desempenho do cluster para ajudar você a encontrar a causa raiz da degradação do desempenho. Visualize 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 obter mais informações, consulte Monitoramento do Amazon Redshift usando métricas do Amazon CloudWatch.

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

Conferir as recomendações do Amazon Redshift Advisor

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

Quando uma consulta é executada, o Amazon Redshift observa o desempenho da consulta e indica se a consulta está sendo executada de forma eficiente. 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 na tabela interna do sistema STL_ALERT_EVENT_LOG.

Se você tiver uma consulta lenta ou ineficiente, verifique as entradas de 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.

Veja a seguir um exemplo de saída da consulta e das informações que descrevem por que o 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 junção de loop aninhada é o tipo de junção menos eficaz. Como as junções de loops aninhados aumentam significativamente o runtime da consulta, tente evitar loops aninhados.

Para ajudar você a diagnosticar o problema,Identifique as consultas que realizam os loops aninhados. Para obter mais informações, consulte Como posso 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 executar uma consulta no cluster, o Amazon Redshift pode gerar bloqueios em nível de tabela nas tabelas envolvidas nas execuções de consultas. Às vezes, as consultas parecem não estar respondendo ou há um pico no runtime da consulta. Se você tiver um pico no runtime da consulta, um problema de bloqueio pode estar causando o pico. Para obter mais informações, consulte Por que meu tempo de planejamento de consultas é 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 transação de longa duração pode fazer com que uma consulta pare de responder. Execute as seguintes ações para que sessões ou transações de longa duração não afetem o desempenho da sua consulta:

• Use as tabelas STL\ _SESSIONS e SVV\ _TRANSACTIONS para verificar se há sessões e transações de longa duração e, em seguida, encerre-as.
• Elabore suas consultas para que o Amazon Redshift possa processá-las com rapidez e eficiência.

**Observação:**Sessões ou transações de longa duração também afetam a operação VACUUM para recuperar espaço em disco e levam a um maior número de linhas fantasmas ou linhas não confirmadas. As linhas fantasmas que as consultas examinam podem afetar o desempenho da consulta.

Para mais informações, consulte Como detectar e liberar bloqueios no Amazon Redshift?

Verifique sua configuração do WLM

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

Para verificar as filas de WLM de um cluster no período de 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_pcntFROM
(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 runtime, 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;

Observação: 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

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. Durante o processo, a performance do cluster pode diminuir.

Para identificar os eventos que afetaram o desempenho do seu cluster, verifique seus eventos de cluster do Amazon Redshift.

Para monitorar o processo de restauração de dados, use a tabela STV_UNDERREPPED_BLOCKS. Execute a consulta a seguir para recuperar os blocos que exigem restauração de dados:

SELECT COUNT(1) FROM STV_UNDERREPPED_BLOCKS;

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

Para verificar a integridade de um determinado nó, use a consulta a seguir para comparar sua 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
);

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

Observação: A saída anterior mostra que o nó 7 processou 19429840046 KB de dados por 6242661 segundos. Isso é muito mais lento do que os outros nós.

A razão entre o número de linhas na coluna sum\ _rows e o número de bytes processados na coluna kb é praticamente a mesma. Dependendo do desempenho do hardware, o número de linhas na coluna kb\ _s também é quase o mesmo que o número de linhas na coluna sum\ _rows. Se um nó estiver processando menos dados durante um período de tempo, pode haver um problema de hardware subjacente. Para confirmar se há um problema de hardware subjacente, revise o gráfico de performance do nó.