Recomendações do Amazon Redshift Advisor

O Amazon Redshift Advisor oferece recomendações sobre como otimizar seu cluster Amazon Redshift para aumentar a performance e economizar nos custos operacionais. É possível encontrar explicações para cada recomendação no console, conforme descrito anteriormente. É possível encontrar mais detalhes sobre essas recomendações nas seções a seguir.

Compactar objetos de arquivo do Amazon S3 carregados por COPY

O comando COPY tira proveito da arquitetura de processamento paralelo maciço (MPP) no Amazon Redshift para ler e carregar dados em paralelo. Ele pode ler arquivos do Amazon S3, tabelas do DynamoDB e saída de texto de um ou mais hosts remotos.

Ao carregar grandes quantidades de dados, é altamente recomendável usar o comando COPY para carregar arquivos de dados compactados do S3. A compactação de grandes conjuntos de dados economiza tempo no upload dos arquivos para o Amazon S3. COPY também pode agilizar o processo de carregamento ao descompactar os arquivos à medida que são lidos.

Análise

Comandos COPY de execução prolongada, que carregam grandes conjuntos de dados descompactados, geralmente têm a oportunidade de melhorar consideravelmente a performance. A análise do Advisor identifica comandos COPY que carregam grandes conjuntos de dados descompactados. Nesse caso, o Advisor gera uma recomendação para implementar a compactação nos arquivos de origem no Amazon S3.

Recomendação

Verifique se cada COPY que carrega uma quantidade significativa de dados, ou que é executado por um tempo significativo, ingere objetos de dados compactados do Amazon S3. É possível identificar os comandos COPY que carregam grandes conjuntos de dados descompactados do Amazon S3 executando o comando SQL a seguir como um superusuário.

SELECT
    wq.userid, query, exec_start_time AS starttime, COUNT(*) num_files,
    ROUND(MAX(wq.total_exec_time/1000000.0),2) execution_secs,
    ROUND(SUM(transfer_size)/(1024.0*1024.0),2) total_mb,
    SUBSTRING(querytxt,1,60) copy_sql
FROM stl_s3client s
JOIN stl_query q USING (query)
JOIN stl_wlm_query wq USING (query)
WHERE s.userid>1 AND http_method = 'GET'
    AND POSITION('COPY ANALYZE' IN querytxt) = 0
    AND aborted = 0 AND final_state='Completed'
GROUP BY 1, 2, 3, 7
HAVING SUM(transfer_size) = SUM(data_size) 
AND SUM(transfer_size)/(1024*1024) >= 5
ORDER BY 6 DESC, 5 DESC;         
          

Se os dados preparados permanecerem no Amazon S3 depois de carregá-los, o que é comum em arquiteturas de data lake, armazenar esses dados em um formato compactado poderá reduzir os custos de armazenamento.

Dicas de implementação

Isolar vários bancos de dados ativos

Como prática recomendada, recomendamos isolar os bancos de dados no Amazon Redshift uns dos outros. As consultas são executadas em um banco de dado específico e não podem acessar dados de qualquer outro banco de dados no cluster. No entanto, as consultas executadas em todos os bancos de dados de um cluster compartilham o mesmo espaço de armazenamento os recursos de computação do cluster subjacente. Quando um único cluster contém vários bancos de dados ativos, seus workloads geralmente não são relacionados.

Análise

A análise do Advisor analisa todos os bancos de dados do cluster para workloads ativos executados ao mesmo tempo. Se houver workloads ativos em execução ao mesmo tempo, o Advisor gera uma recomendação para considerar a migração de bancos de dados para clusters separados do Amazon Redshift.

Recomendação

Considere mover cada banco de dados consultado ativamente para um cluster dedicado separado. Usar um cluster separado pode reduzir contenção de recursos e melhorar a performance de consulta. Isso é possível, pois permite que você defina o tamanho de cada cluster para as necessidades de performance, custo e armazenamento de cada workload. Além disso, as workloads não relacionadas geralmente se beneficiam de diferentes configurações de gerenciamento da workload.

Para identificar quais bancos de dados são usados ativamente, é possível executar este comando SQL como um superusuário.

SELECT database,
  COUNT(*) as num_queries,
  AVG(DATEDIFF(sec,starttime,endtime)) avg_duration,
  MIN(starttime) as oldest_ts,
  MAX(endtime) as latest_ts
FROM stl_query
WHERE userid > 1
GROUP BY database;            
         

Dicas de implementação

Realocar a memória do Workload Management (WLM)

O Amazon Redshift roteia consultas de usuário para Implementar o WLM manual para processamento. O gerenciamento de workload (WLM) define como essas consultas são roteadas para as filas. O Amazon Redshift aloca a cada fila uma parte da memória disponível do cluster. A memória de uma fila é dividida entre os slots de consulta da fila.

Quando uma fila é configurada com mais slot do que o necessário para o workload, a memória alocada para esses slots não utilizados é subutilizada. Reduzir os slots configurados para corresponder aos requisitos do workload de pico redistribui a memória subutilizada para slots ativos, e pode resultar na melhoria da performance de consulta.

Análise

A análise do Advisor analisa os requisitos de simultaneidade do workload para identificar filas de consulta com slots não utilizados. O Advisor gera uma recomendação para reduzir o número de slots em uma fila quando encontrar o seguinte:

Recomendação

Reduzir os slots configurados para corresponder aos requisitos do workload de pico redistribui memória subutilizada para slots ativos. Considere reduzir a contagem de slots configurados para filas em que os slots nunca foram totalmente utilizados. Para identificar essas filas, é possível comparar os requisitos de slot por hora de pico para cada fila executando o seguinte comando SQL como um superusuário.

WITH
 generate_dt_series AS (select sysdate - (n * interval '5 second') as dt from (select row_number() over () as n from stl_scan limit 17280)),
 apex AS (
     SELECT iq.dt, iq.service_class, iq.num_query_tasks, count(iq.slot_count) as service_class_queries, sum(iq.slot_count) as service_class_slots
     FROM
         (select gds.dt, wq.service_class, wscc.num_query_tasks, wq.slot_count
         FROM stl_wlm_query wq
         JOIN stv_wlm_service_class_config wscc ON (wscc.service_class = wq.service_class AND wscc.service_class > 5)
         JOIN generate_dt_series gds ON (wq.service_class_start_time <= gds.dt AND wq.service_class_end_time > gds.dt)
         WHERE wq.userid > 1 AND wq.service_class > 5) iq
     GROUP BY iq.dt, iq.service_class, iq.num_query_tasks),
     maxes as (SELECT apex.service_class, trunc(apex.dt) as d, date_part(h,apex.dt) as dt_h, max(service_class_slots) max_service_class_slots
                     from apex group by apex.service_class, apex.dt, date_part(h,apex.dt))
SELECT apex.service_class - 5 AS queue, apex.service_class, apex.num_query_tasks AS max_wlm_concurrency, maxes.d AS day, maxes.dt_h || ':00 - ' || maxes.dt_h || ':59' as hour, MAX(apex.service_class_slots) as max_service_class_slots
FROM apex
JOIN maxes ON (apex.service_class = maxes.service_class AND apex.service_class_slots = maxes.max_service_class_slots)
GROUP BY  apex.service_class, apex.num_query_tasks, maxes.d, maxes.dt_h
ORDER BY apex.service_class, maxes.d, maxes.dt_h;            
         

A coluna max_service_class_slots representa o número máximo de slots da consulta de WLM na fila de consulta para aquela hora. Se existirem filas subutilizadas, implemente a otimização de redução de slots modificando um grupo de parâmetros, conforme descrito no Guia de gerenciamento de clusters do Amazon Redshift.

Dicas de implementação

Ignorar a análise de compactação durante o comando COPY

Quando você carrega dados em uma tabela vazia com codificação de compactação declarada com o comando COPY, o Amazon Redshift aplica compactação de armazenamento. Essa otimização garante que os dados no cluster sejam armazenados com eficiência, mesmo quando carregados por usuários finais. A análise necessária para a compactação pode levar um tempo significativo.

Análise

A análise do Advisor verifica operações COPY que foram atrasadas por análise de compactação automática. A análise determina as codificações de compactação com uma amostragem de dados durante o carregamento. Essa amostragem é semelhante àquela realizada pelo comando ANALYZE COMPRESSION.

Quando você carrega dados como parte de um processo estruturado, como em um lote noturno de extração, transformação e carregamento (ETL), é possível definir a compactação antecipadamente. Também é possível otimizar as definições de tabela para ignorar permanentemente essa fase sem nenhum impacto negativo.

Recomendação

Para melhorar a agilidade do comando COPY ignorando a fase de análise de compactação, implemente uma destas duas opções:

A melhor solução geralmente é usar a codificação de coluna durante a criação da tabela, pois essa abordagem também mantém o benefício de armazenar dados compactados em disco. É possível usar o comando ANALYZE COMPRESSION para sugerir codificações de compactação, mas é necessário recriar a tabela para aplicar essas codificações. Para automatizar esse processo, você pode usar o AWSColumnEncodingUtility, encontrado no GitHub.

Para identificar as operações COPY que acionam análise de compactação automática, execute o seguinte comando SQL.

  WITH xids AS (
    SELECT xid FROM stl_query WHERE userid>1 AND aborted=0 
    AND querytxt = 'analyze compression phase 1' GROUP BY xid
    INTERSECT SELECT xid FROM stl_commit_stats WHERE node=-1)
SELECT a.userid, a.query, a.xid, a.starttime, b.complyze_sec, 
    a.copy_sec, a.copy_sql
FROM (SELECT q.userid, q.query, q.xid, date_trunc('s',q.starttime) 
    starttime, substring(querytxt,1,100) as copy_sql, 
    ROUND(datediff(ms,starttime,endtime)::numeric / 1000.0, 2) copy_sec
    FROM stl_query q JOIN xids USING (xid)
    WHERE (querytxt ilike 'copy %from%' OR querytxt ilike '% copy %from%') 
    AND querytxt not like 'COPY ANALYZE %') a
LEFT JOIN (SELECT xid, 
    ROUND(sum(datediff(ms,starttime,endtime))::numeric / 1000.0,2) complyze_sec 
    FROM stl_query q JOIN xids USING (xid)
    WHERE (querytxt like 'COPY ANALYZE %' 
    OR querytxt like 'analyze compression phase %') 
    GROUP BY xid ) b ON a.xid = b.xid
WHERE b.complyze_sec IS NOT NULL ORDER BY a.copy_sql, a.starttime;
         

Dicas de implementação

Dividir objetos do Amazon S3 carregados por COPY

O comando COPY tira proveito da arquitetura de processamento paralelo massivo (MPP) no Amazon Redshift para ler e carregar dados de arquivos no Amazon S3. O comando COPY carrega os dados em paralelo a partir de vários arquivos, dividindo o workload entre os nós em seu cluster. Para atingir a taxa de transferência ideal, é altamente recomendável que você divida seus dados em vários arquivos para aproveitar o processamento paralelo.

Análise

A análise do Advisor identifica os comandos COPY que carregam grandes conjuntos de dados contidos em um pequeno número de arquivos preparados no Amazon S3. Comandos COPY de execução prolongada, que carregam grandes conjuntos de dados de poucos arquivos, geralmente têm a oportunidade de melhorar consideravelmente a performance. Quando o Advisor identifica que esses comandos COPY estão levando uma quantidade significativa de tempo, ele cria uma recomendação para aumentar o paralelismo dividindo os dados em arquivos adicionais no Amazon S3.

Recomendação

Nesse caso, recomendamos as seguintes ações, listada em ordem de prioridade:

Certos comandos COPY carregam uma quantidade significativa de dados ou são executados por um período significativo. Para esses comandos, recomendamos que você carregue uma série de objetos de dados do Amazon S3 que seja equivalente a um múltiplo do número de fatias do cluster. Para identificar quantos objetos do S3 cada comando COPY carregou, execute o seguinte código SQL como um superusuário.

SELECT
    query, COUNT(*) num_files,
    ROUND(MAX(wq.total_exec_time/1000000.0),2) execution_secs,
    ROUND(SUM(transfer_size)/(1024.0*1024.0),2) total_mb,
    SUBSTRING(querytxt,1,60) copy_sql
FROM stl_s3client s
JOIN stl_query q USING (query)
JOIN stl_wlm_query wq USING (query)
WHERE s.userid>1 AND http_method = 'GET'
    AND POSITION('COPY ANALYZE' IN querytxt) = 0
    AND aborted = 0 AND final_state='Completed'
GROUP BY query, querytxt
HAVING (SUM(transfer_size)/(1024*1024))/COUNT(*) >= 2
ORDER BY CASE
WHEN COUNT(*) < (SELECT max(node)+1 FROM stv_slices) THEN 1
WHEN COUNT(*) < (SELECT COUNT(*) FROM stv_slices WHERE node=0) THEN 2
ELSE 2+((COUNT(*) % (SELECT COUNT(*) FROM stv_slices))/(SELECT COUNT(*)::DECIMAL FROM stv_slices))
END, (SUM(transfer_size)/(1024.0*1024.0))/COUNT(*) DESC;   
            
         

Dicas de implementação

Atualizar estatísticas da tabela

O Amazon Redshift usa um otimizador de consulta baseado em custo para escolher o plano de execução ideal para consultas. As estimativas de custos são baseadas em estatísticas da tabela obtidas usando o comando ANALYZE. Quando as estatísticas estão desatualizadas ou ausentes, o banco de dados escolhe um plano menos eficiente para a execução de consulta, principalmente para consultas complexas. Manter estatísticas atuais ajuda que a execução de consultas complexas ocorra no mínimo tempo possível.

Análise

A análise do Advisor monitora tabelas cujas estatísticas estão desatualizadas ou ausentes. Ela revisa os metadados de acesso da tabela associados a consultas complexas. Se as tabelas que são geralmente acessadas com padrões complexos não tiverem estatísticas, o Advisor criará uma recomendação crucial para executar ANALYZE. Se as tabelas que são geralmente acessadas com padrões complexos tiverem estatísticas desatualizadas, o Advisor criará uma recomendação sugerida para executar ANALYZE.

Recomendação

Sempre que o conteúdo da tabela for alterado significativamente, atualize as estatísticas com ANALYZE. Recomendamos executar ANALYZE sempre que um número significativo de novas linhas de dados for carregado para uma tabela existente com os comandos COPY ou INSERT. Também recomendamos executar ANALYZE sempre que um número significativo de linhas for modificado com comandos UPDATE ou DELETE. Para identificar as tabelas com estatísticas ausentes ou atualizadas, execute o seguinte comando SQL como um superusuário. Os resultados são ordenados da tabela maior para a menor.

Para identificar as tabelas com estatísticas ausentes ou atualizadas, execute o seguinte comando SQL como um superusuário. Os resultados são ordenados da tabela maior para a menor.

SELECT
   ti.schema||'.'||ti."table" tablename,
   ti.size table_size_mb,
   ti.stats_off statistics_accuracy
 FROM svv_table_info ti
 WHERE ti.stats_off > 5.00
 ORDER BY ti.size DESC;          
         

Dicas de implementação

O limite ANALYZE padrão é 10 por cento. Esse padrão significa que o comando ANALYZE ignorará uma determinada tabela se menos de 10 por cento das linhas que ela contém tiverem sofrido alterações desde o último ANALYZE. Como resultado, você pode escolher executar comandos ANALYZE ao final de cada processo de ETL. Optar por essa abordagem significa que o comando ANALYZE será ignorado frequentemente, mas também garante que ele seja executado quando for necessário.

As estatísticas do comando ANALYZE têm maior impacto para colunas usadas em junções (por exemplo, JOIN tbl_a ON col_b) ou como predicados (por exemplo, WHERE col_b = 'xyz'). Por padrão, o comando ANALYZE coleta estatísticas para todas as colunas na tabela especificada. Se necessário, é possível reduzir o tempo necessário para executar o comando ANALYZE executando-o somente para as colunas onde tem o maior impacto. É possível executar o seguinte comando SQL para identificar colunas usadas como predicados. Você também pode permitir que o Amazon Redshift escolha quais colunas analisar especificando ANALYZE PREDICATE COLUMNS.

WITH predicate_column_info as (
SELECT ns.nspname AS schema_name, c.relname AS table_name, a.attnum as col_num,  a.attname as col_name,
        CASE
            WHEN 10002 = s.stakind1 THEN array_to_string(stavalues1, '||') 
            WHEN 10002 = s.stakind2 THEN array_to_string(stavalues2, '||')
            WHEN 10002 = s.stakind3 THEN array_to_string(stavalues3, '||')
            WHEN 10002 = s.stakind4 THEN array_to_string(stavalues4, '||')
            ELSE NULL::varchar
        END AS pred_ts
   FROM pg_statistic s
   JOIN pg_class c ON c.oid = s.starelid
   JOIN pg_namespace ns ON c.relnamespace = ns.oid
   JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum)
SELECT schema_name, table_name, col_num, col_name,
       pred_ts NOT LIKE '2000-01-01%' AS is_predicate,
       CASE WHEN pred_ts NOT LIKE '2000-01-01%' THEN (split_part(pred_ts, '||',1))::timestamp ELSE NULL::timestamp END as first_predicate_use,
       CASE WHEN pred_ts NOT LIKE '%||2000-01-01%' THEN (split_part(pred_ts, '||',2))::timestamp ELSE NULL::timestamp END as last_analyze
FROM predicate_column_info;

Para ter mais informações, consulte Análise de tabelas.

Habilitar a aceleração de consultas breves

A aceleração de consultas breves (SQA) prioriza as consultas de curta execução sobre as consultas de execução demorada. A SQA executa consultas breves em um espaço dedicado, de maneira que as consultas SQA não sejam forçadas a esperar em filas atrás de consultas mais demoradas. O SQA apenas prioriza consultas que são de execução curta e estão em uma fila definida pelo usuário. Com a SQA, consultas breves são iniciadas com mais rapidez e os usuários veem os resultados mais cedo.

Se você ativar a SQA, poderá reduzir ou eliminar as filas de gerenciamento de workload (WLM) que são dedicadas à execução de consultas breves. Além disso, as consultas demoradas não precisam disputar slots em uma fila com as consultas breves, e assim você pode configurar suas filas do WLM usando menos slots de consulta. Quando você usa um nível de simultaneidade menor, a taxa de transferência de consultas aumenta e a performance geral do sistema melhora na maioria dos workloads. Para ter mais informações, consulte Trabalhar com a aceleração de consulta breve.

Análise

O Advisor verifica os padrões de workload e relata o número de consultas recentes em que a SQA reduziria a latência e o tempo de fila diário de consultas qualificadas para SQA.

Recomendação

Modificar a configuração de WLM para ativar a SQA. O Amazon Redshift usa um algoritmo de Machine Learning para analisar cada consulta qualificada. As previsões serão melhores, pois a SQA aprenderá com os padrões das suas consulta. Para obter mais informações, consulte Configurar filas do Workload Management.

Ao ativar a SQA, o WLM define o tempo máximo do ambiente de tempo de execução de consultas breves como dinâmico, por padrão. Recomendamos manter a configuração dinâmica para o tempo de execução máximo de SQA.

Dicas de implementação

Para conferir se a SQA está ativada, execute a consulta a seguir. Se a consulta retornar uma linha, a SQA está ativada.

select * from stv_wlm_service_class_config 
where service_class = 14;

Para ter mais informações, consulte Monitoramento da SQA.

Alterar chaves de distribuição em tabelas

O Amazon Redshift distribui as linhas da tabela em todo o cluster de acordo com o estilo de distribuição da tabela. As tabelas com distribuição de CHAVE precisam de uma coluna como a chave de distribuição (DISTKEY). Uma linha da tabela é atribuída a uma fatia do nó de um cluster com base no valor da coluna DISTKEY.

Uma DISTKEY apropriada coloca uma quantidade semelhante de linhas e cada fatia do nó e costuma ser mencionada em condições de união. Uma união otimizada ocorre quando as tabelas são unidas nas colunas DISTKEY, acelerando a performance da consulta.

Análise

O Advisor analisa o workload do cluster para identificar a chave de distribuição mais apropriada para as tabelas que podem se beneficiar significativamente de um estilo de distribuição de CHAVE.

Recomendação

O Advisor fornece instruções ALTER TABLE que alteram o DISTSTYLE e a DISTKEY de uma tabela com base em sua análise. Para obter um benefício de performance significativo, garanta a implementação de todas as instruções SQL em um grupo de recomendação.

A redistribuição de uma tabela grande com ALTER TABLE consome recursos do cluster e requer bloqueios de tabela temporários em vários momentos. Implemente cada grupo de recomendação quando o outro workload do cluster for leve. Para ver mais detalhes sobre como otimizar as propriedades de distribuição da tabela, consulte o Manual de design de tabela avançada do Amazon Redshift Engineering: estilos de distribuição e chaves de distribuição.

Para obter mais informações sobre ALTER DISTSYLE e DISTKEY, consulte ALTER TABLE.

Alterar as chaves de classificação em tabelas

O Amazon Redshift classifica as linhas da tabela de acordo com a chave de classificação da tabela. A classificação de linhas da tabela tem como base os valores da coluna da chave de classificação.

Classificar uma tabela em uma chave de classificação apropriada pode acelerar a performance de consultas, especialmente aquelas com predicados restritos por intervalos, exigindo que menos blocos de tabela sejam lidos do disco.

Análise

O Advisor analisa a workload do cluster ao longo de vários dias para identificar uma chave de classificação que beneficie as suas tabelas.

Recomendação

O Advisor fornece dois grupos de instruções ALTER TABLE que alteram a chave de classificação de uma tabela com base na análise dele:

Quando uma tabela grande é classificada com a instrução ALTER TABLE, são consumidos recursos de cluster e são necessários bloqueios de tabela em vários momentos. Implemente cada recomendação quando o workload de um cluster for moderado. Mais detalhes sobre como otimizar as configurações de chave de classificação de tabela podem ser encontrados no Manual de design de tabela avançada do Amazon Redshift Engineering: chaves de classificação compostas e intercaladas.

Para obter mais informações sobre a ALTER SORTKEY, consulte ALTER TABLE.

Alterar codificações de compactação em colunas

A compactação é uma operação em nível de coluna que reduz o tamanho dos dados quando são armazenados. A compactação é usada no Amazon Redshift para economizar espaço de armazenamento e melhorar a performance da consulta, reduzindo a quantidade de E/S de disco. Recomendamos uma codificação de compactação ideal para cada coluna com base em seu tipo de dados e em padrões de consulta. Com a compactação ideal, as consultas podem ser executadas de forma mais eficiente e o banco de dados pode ocupar espaço mínimo de armazenamento.

Análise

O Advisor realiza a análise do workload e do esquema de banco de dados do cluster continuamente para identificar a codificação de compactação ideal para cada coluna da tabela.

Recomendação

O Advisor fornece instruções ALTER TABLE que alteram a codificação de compactação de colunas específicas, com base na análise dele.

Alterando codificações de compactação de coluna com ALTER TABLE consome recursos do cluster e requer bloqueios de tabela em vários momentos. É melhor implementar recomendações quando o workload do cluster for leve.

Para referência, o Exemplos de ALTER TABLE mostra várias instruções que alteram a codificação de uma coluna.

Recomendações de tipo de dados

O Amazon Redshift tem uma biblioteca de tipos de dados SQL para vários casos de uso. Entre eles, incluem-se tipos inteiros, como INT, e tipos para armazenar personagens, como VARCHAR. O Redshift armazena tipos de modo otimizado para garantir acesso rápido e boa performance nas consultas. Além disso, o Redshift fornece funções para tipos específicos, que você pode usar para formatar ou executar cálculos usando resultados de consultas.

Análise

O Advisor realiza a análise da workload e do esquema de banco de dados do cluster continuamente para identificar as colunas que podem se beneficiar significativamente de uma alteração de tipo de dado.

Recomendação

O Advisor fornece uma instrução ALTER TABLE que adiciona uma nova coluna com o tipo de dado sugerido. Uma instrução complementar UPDATE copia os dados da coluna existente para a nova coluna. Depois de criar a nova coluna e carregar os dados, altere suas consultas e scripts de ingestão para acessar a nova coluna. Depois, aproveite os recursos e as funções especializadas para o novo tipo de dado, encontrados em Referência de funções SQL.

Copiar os dados existentes para a nova coluna pode levar algum tempo. Recomendamos que você implemente cada recomendação do Advisor quando a workload do cluster for leve. Consulte a lista de tipos de dados disponíveis em Tipos de dados.

O Advisor não fornece recomendações quando não há dados suficientes ou quando o benefício esperado da alteração de tipo de dado é pequeno.