使用新的统一 SYS 视图简化亚马逊 Redshift 监控

Amazon Redshift 是一项完全托管的 PB 级云端数据仓库服务，其 性价 比是任何其他云数据仓库的 五倍 ，开箱即用的性能创新无需额外付费。成千上万的客户每天使用 Amazon Redshift 处理艾字节的数据，以支持他们的分析工作负载。

在这篇文章中，我们将讨论亚马逊 Redshift 系统监控视图，以及它们如何简化对 Amazon Redshift 工作负载和资源使用情况的监控。

SYS 监控视图概述

系统监控视图是 Amazon Redshift 中的系统视图，可用于监控预置集群和无服务器工作组的查询和工作负载资源使用情况。它们提供以下好处：

• 它们根据功能对齐进行分类，同时考虑查询状态、性能指标和查询类型
• 我们引入了新的性能指标，例如 pl anning_time、lock_ wait_tim e 、rem ote_read_io 和 local_read_io ，以帮助进行性能故障排除
• 它通过记录用户提交的查询而不是由 Redshift 优化器重写的查询来提高监控视图的可用性
• 它使用更少的视图提供更多的故障排除指标
• 它允许您在预配置的集群或无服务器工作组中使用相同的查询，从而实现统一的 Amazon Redshift 监控

让我们来看看 SYS 监控视图的一些功能以及如何使用它们进行监控。

统一各种查询级监控指标

下表显示如何将来自多个系统表和视图的查询的各种指标和信息统一到一个系统监控视图中。

有关 SYS 到 STL/SVL/STV 映射的更多信息，请参阅 迁移到 SYS 监控视图。

用户查询级日志

为了提高查询性能，Redshift 查询引擎可以重写用户提交的查询。用户提交的查询标识符与重写的查询标识符不同。在这篇文章中，我们将用户提交的 查询称为 父查询 ，将重写的 查询称为 子查询 。

下图说明了父查询级别和子查询级别的日志记录。父查询标识符为 1000，子查询标识符为 1001、1002 和 1003。

查询生命周期时机

SYS_QUERY_ HISTORY 具有增强的列列表，可提供与不同查询生命周期阶段相关的精细时间指标。请注意，所有时间均以微秒为单位记录。下表汇总了这些指标。

解决方案概述

我们讨论以下场景以帮助熟悉 SYS 监控视图：

• 工作负载和查询生命周期监控
• 数据摄取监控
• 外部查询监控
• 查询性能缓慢疑难解答

先决条件

您应该具备以下先决条件以及本文中的示例：

• 一个 亚马逊云科技 账户
• Redshift 预置的集群（当前曲目）或 Amazon Red shift 无服务器端点

此外， 请将本文中引用的 所有 SQL 查询下载 为 Redshift 查询编辑器 v2 SQL 笔记本。

工作负载和查询生命周期监控

在本节中，我们将讨论如何监控工作负载和查询生命周期。

识别机上查询

SYS_QUERY_HISTORY 提供了一个单一视图来查看所有运行中的查询和历史 运行情况。参见以下示例查询：

SELECT  
  *
FROM    
  sys_query_history
WHERE    status IN ('planning', 'queued', 'running', 'returning')
ORDER BY
  start_time;

我们得到以下输出。

识别最长时间运行的查询

以下查询有助于检索运行时间最长的前 100 个查询。分析（如果可行，还可以优化）这些查询可以帮助提高整体性能。这些指标是所有查询运行的累积统计信息。请注意，所有时间值均以微秒为单位。

--top long running query by elapsed_time
SELECT  
  user_id
  , transaction_id
  , query_id
  , database_name
  , query_type
  , query_text::VARCHAR(100)
  , lock_wait_time
  , planning_time
  , compile_time
  , execution_time
  , elapsed_time
FROM    
  sys_query_history
ORDER BY
  elapsed_time DESC
LIMIT 100;

我们得到以下输出。

按查询类型、时间段和状态收集每日查询数

以下查询可深入了解不同类型的查询在不同日期内的分布情况，并有助于评估和跟踪工作负载的任何变化：

--daily breakdown of workload by query types and status
SELECT  
  DATE_TRUNC('day', start_time) period_daily
  , query_type
  , status
  , COUNT(*)
FROM    
  sys_query_history
GROUP BY
  period_daily
  , query_type
  , status
ORDER BY
  period_daily
  , query_type
  , status;

我们得到以下输出。

收集飞行中查询的运行详情

要确定正在进行的查询的运行级别详细信息，可以在查询 SYS_QUERY_DETAIL 表时使用 is_active = 't' 过滤器。 参见以下示例：

SELECT  
  query_id
  , child_query_sequence
  , stream_id
  , segment_id
  , step_id
  , step_name
  , table_id
  , coalesce(table_name,'')|| coalesce(source,'') as table_name
  , start_time
  , end_time
  , duration
  , blocks_read
  , local_read_io
  , remote_read_io
FROM    
  sys_query_detail
WHERE is_active = 't'
ORDER BY
  query_id
  , child_query_sequence
  , stream_id
  , segment_id
  , step_id;

要查看最新运行的 100 个 COPY 查询，请使用以下代码：

SELECT  
  session_id
  , transaction_id
  , query_id
  , database_name
  , table_name
  , data_source
  , loaded_rows
  , loaded_bytes
  , duration / 1000.00 duration_ms
FROM    
  sys_load_history
ORDER BY
  start_time DESC LIMIT 100;

我们得到以下输出。

收集交易级别的详细信息以供提交和撤消

SYS_TRANSACTION_ HISTORY 通过提供对已提交交易的见解，包括提交的区块、状态和隔离级别（使用可序列化或快照）等详细信息，来提供交易级别的日志记录。它还记录有关回滚或撤消事务的详细信息。

以下屏幕截图说明如何获取有关成功提交的交易的详细信息。

以下屏幕截图说明如何获取有关已回滚交易的详细信息。

统计数据和真空

SYS_ANALYZE_ HISTORY 监控视图提供诸如分析查询的最后时间戳、特定分析查询的运行时长、表中的行数以及修改的行数等详细信息。以下示例查询提供了针对所有永久表运行的最新分析查询的列表：

SELECT  
  TRIM(schema_name) schema_name
  , TRIM(table_name) table_name
  , table_id
  , status
  , COUNT(*) times_analyze_was_triggered
  , MAX(last_analyze_time) last_analyze_time
  , MAX(end_time) end_time
  , AVG(ROWS) "rows"
  , AVG(modified_rows) modified_rows
FROM    
  sys_analyze_history
WHERE
   status != 'Skipped'
GROUP BY
  schema_name
  , table_name
  , table_id
  , status
ORDER BY
  schema_name
  , table_name
  , table_id
  , status
  , end_time;

我们得到以下输出。

SYS_VACUUM_HISTORY 监控视图在单个视图中提供有关 VACUUM 的完整详细信息。例如，参见以下代码：

SELECT  
  user_id
  , transaction_id
  , query_id
  , TRIM(database_name) as database_name
  , TRIM(schema_name) as schema_name
  , TRIM(table_name) table_name
  , table_id
  , vacuum_type
  , is_automatic as is_auto
  , duration
  , rows_before_vacuum
  , size_before_vacuum
  , reclaimable_rows
  , reclaimed_rows
  , reclaimed_blocks
  , sortedrows_before_vacuum
  , sortedrows_after_vacuum
FROM    
  sys_vacuum_history
WHERE    status LIKE '%Finished%'
ORDER BY
  start_time;

我们得到以下输出。

数据摄取监控

在本节中，我们将讨论如何监控数据摄取。

摄取摘要

SYS_LOAD_HISTOR Y 提供了 COPY 命令统计信息的详细信息。使用此视图可以汇总了解您的采集工作量。以下示例查询提供了每小时的摄取摘要，该摘要按摄取数据的表进行了细分：

SELECT  
  date_trunc('hour', start_time) period_hourly
  , database_name
  , table_name
  , status
  , file_format
  , SUM(loaded_rows) total_rows_ingested
  , SUM(loaded_bytes) total_bytes_ingested
  , SUM(source_file_count) num_of_files_to_process
  , SUM(file_count_scanned) num_of_files_processed
  , SUM(error_count) total_errors
FROM    
  sys_load_history
GROUP BY
  period_hourly
  , database_name
  , table_name
  , status
  , file_format
ORDER BY
  table_name
  , period_hourly
  , status;

我们得到以下输出。

文件级入口日志

SYS_LOAD_DETA IL 可以更详细 地了解如何在文件级别执行摄取。例如，使用 sys_load_ history 查看以下查询：

SELECT  
  *
FROM    
  sys_load_history
WHERE table_name = 'catalog_sales'
ORDER BY
  start_time;

我们得到以下输出。

以下示例显示了详细的文件级监控是什么样子：

 SELECT  
  user_id
  , query_id
  , TRIM(file_name) file_name
  , bytes_scanned
  , lines_scanned
  , splits_scanned
  , record_time
  , start_time
  , end_time
FROM    
  sys_load_detail
WHERE query_id = 1824870
ORDER BY
  start_time;

在入口过程中检查是否有错误

SYS_LOAD_ERROR_DETA IL 使您能够跟踪和解决在摄取过程中可能发生的错误。此视图记录了在提取过程中遇到错误的文件的详细信息，以及发生错误的行号和该行中的列详细信息。参见以下代码：

select * from sys_load_error_detail order by start_time limit 100;

我们得到以下输出。

外部查询监控

SYS_EXTERNAL_QUERY_DETAIL 提供外部查询 的运行详情，其中包括亚马逊 Red shift Spectrum 和联合查询。 此视图记录区段级别的详细信息，并提供有用的见解，以便在单个监控视图中对外部查询进行故障排除和监控性能。以下是此监控视图提供的一些有用的指标和数据点：

• 扫描的外部文件数量（ scanned_files ）和外部文件的格式（ 文件格式 ），例如 Parquet、文本文件等
• 按行（返回的 行 ）和字节（返回的 字节）扫描的数据
• 外部查询和表对分区（ 总分 区和 限定分区）的 使用
• 详细了解给定外部对象的列表 ( s3list_time ) 和限定分区 ( get_partition_ time) 所花费的时间
• 外部文件位置（文件位置）和外部表名（ 表名）
• 外部来源的类型（ source_type ），例如适用于 Redshift Spectrum 的 亚马逊简单存储服务 （Amazon S3）或联合来源
• 递归扫描子目录 ( is_recursiv e ) 或访问嵌套列数据类型 (is_nested)

例如，以下查询显示了运行的外部查询和扫描的数据数量的每日摘要：

SELECT  
  DATE_TRUNC('hour', start_time) period_hourly
  , user_id
  , TRIM(source_type) source_type
  , COUNT (DISTINCT query_id) query_counts
  , SUM(returned_rows) returned_rows
  , ROUND(SUM(returned_bytes) / 1024^3,2) returned_gb
FROM    
  sys_external_query_detail
GROUP BY
  period_hourly
  , user_id
  , source_type
ORDER BY
  period_hourly
  , user_id
  , source_type;

我们得到以下输出。

分区的使用

您可以验证扫描大量数据和文件的外部查询是否已分区。使用分区时，可以通过根据分区键进行修剪来限制外部查询必须扫描的数据量。参见以下代码：

SELECT  
  file_location
  , CASE
      WHEN NVL(total_partitions,0) = 0
      THEN 'No'
      ELSE 'Yes'
    END is_partitioned
  , SUM(scanned_files) total_scanned_files
  , COUNT(DISTINCT query_id) query_count
FROM    
  sys_external_query_detail
GROUP BY
  file_location
  , is_partitioned
ORDER BY
  total_scanned_files DESC;

我们得到以下输出。

对于外部查询中遇到的任何错误，请查看 SYS_EXTERNAL_QUERY_ERROR ，它按 文件 位置、列和行号的粒度记录详细信息。

查询性能缓慢疑难解答

有关如何使用 SYS 监控视图执行查询级故障排除的分步指南，请参阅作为先决条件一部分下载的 sysview_slow _query_performance_formance_dishoublace SQL 笔记本，并查找以下问题的答案：

• 被比较的查询是否有相似的查询文本？
• 查询是否使用了结果缓存？
• 查询生命周期的哪些部分（排队、编译、规划、锁定等待）对查询运行时的贡献最大？
• 查询计划有变化吗？
• 查询是否正在读取更多数据块？
• 查询是否已溢出到磁盘？如果是，它会溢出到本地还是远程存储？
• 查询在数据（分布）和时间（运行时间）方面是否存在严重偏差？
• 您是否看到在联接步骤或嵌套循环中处理了更多行？
• 是否有任何警报表明统计数据已过时？
• 上次对查询中涉及的表进行抽真和分析是什么时候？

清理

如果您在本文中创建了任何 Redshift 预置集群或 Redshift Serverless 工作组，并且不再需要它们来处理您的工作负载，则可以将其删除以避免产生额外费用。

结论

在这篇文章中，我们解释了如何使用 Redshift SYS 监控视图来监控预置集群和无服务器工作组的工作负载。SYS 监控视图简化了对工作负载的监控，可以从统一视图访问各种查询级别的监控指标，并能够使用相同的 SYS 监控视图查询在预置集群和无服务器工作组上运行。我们还使用系统监控视图介绍了一些关键的监控和故障排除方案。

我们鼓励您开始使用新的 SYS 监控视图来处理 Redshift 工作负载。如果您有任何反馈或问题，请在评论中留言。

作者简介

Urvish Shah 是亚马逊 Redshift 的高级数据库工程师。他在数据库、数据仓库和分析领域拥有十多年的工作经验。工作之余，他喜欢做饭、旅行和与女儿共度时光。

Ranjan Burman 是 亚马逊云科技 的分析专家解决方案架构师。他专门研究亚马逊 Redshift，帮助客户构建可扩展的分析解决方案。他在不同的数据库和数据仓库技术方面拥有超过 15 年的经验。他热衷于使用云解决方案自动化和解决客户问题。