使用查询存储来监视性能
适用于：
Azure Database for PostgreSQL 灵活服务器
Azure Database for PostgreSQL 灵活服务器中的查询存储功能提供了一种跟踪一段时间内的查询性能的方法。 通过帮助快速查找运行时间最长且资源最密集的查询，查询存储可简化性能故障排除。 查询存储自动捕获查询和运行时统计信息的历史记录，并保留它们以供查看。 它按时间切分数据，以便可以查看时间使用模式。 所有用户、数据库和查询的数据都存储在 Azure Database for PostgreSQL 灵活服务器实例中名为 azure_sys 的数据库中。
重要
请勿修改 azure_sys 数据库或其架构。 执行此操作将阻止查询存储和相关的性能功能正常运行。
启用查询存储
查询存储在所有区域中可用，无需额外付费。 它是一项选择加入功能，因此默认情况下它未在服务器上启用。 对于给定服务器上的所有数据库，可以全局启用或禁用查询存储，但无法针对每个数据库启用或禁用。
重要
不要在可突发定价层上启用查询存储，因为这会影响性能。
在 Azure 门户中启用查询存储
- 登录到 Azure 门户并选择你的 Azure Database for PostgreSQL 灵活服务器实例。
- 在菜单的“设置”部分中选择“服务器参数”。
- 搜索
pg_qs.query_capture_mode参数。 - 将该值设置为
TOP或ALL，具体取决于是要跟踪顶级查询还是还要跟踪嵌套查询（在函数或过程中执行的查询），然后单击“保存”。 允许第一批数据在 azure_sys 数据库中最多保留 20 分钟。
启用查询存储等待采样
- 搜索
pgms_wait_sampling.query_capture_mode参数。 - 将值设置为
ALL并保存。
查询存储中的信息
查询存储由两个存储组成：
- 用于保存查询执行统计信息的运行时统计信息存储。
- 用于保存等待统计信息的等待统计信息存储。
使用查询存储的常见方案包括：
- 确定在给定时间窗口内执行查询的次数。
- 比较某个查询在多个时间窗口上的平均执行时间，以查看较大的增量。
- 标识过去几个小时内运行时间最长的查询。
- 标识正在等待资源的前 N 个查询。
- 了解特定查询的等待性质。
为尽量减少空间使用量，运行时统计信息存储中的运行时执行统计信息在一个固定的、可配置的时间范围内聚合。 可以使用视图查询这些存储中的信息。
访问查询存储信息
查询存储数据存储在 Azure Database for PostgreSQL 灵活服务器实例上的 azure_sys 数据库中。 以下查询返回有关查询存储中的查询的信息：
SELECT * FROM query_store.qs_view;
或此等待统计信息的查询：
SELECT * FROM query_store.pgms_wait_sampling_view;
查找等待查询
等待事件类型按相似性将不同的等待事件组合到存储桶中。 查询存储提供等待事件类型、特定等待事件名称和有争议的查询。 能将此等待信息与查询运行时统计信息相关联，意味着可更深入地了解有助于查询性能特征的因素。
以下是一些示例，说明如何使用查询存储中的等待统计信息获得有关工作负载的更多见解：
| 观测 | 操作 |
|---|---|
| 高锁定等待 | 检查受影响查询的查询文本，并确定目标实体。 在查询存储中查找修改同一实体的其他查询，这些查询经常执行和/或持续很长时间。 确定这些查询后，请考虑更改应用程序逻辑以提高并发性，或使用限制较少的隔离级别。 |
| 高缓冲 IO 等待 | 在查询存储中查找具有大量物理读取的查询。 如果它们匹配具有高 IO 等待的查询，考虑在基础实体上引入索引，以便进行搜索而不是扫描。 这将最小化查询的 IO 开销。 检查门户中服务器的“性能建议”，以查看是否存在可优化查询的此服务器的索引建议。 |
| 高内存等待 | 在查询存储中查找消耗内存最多的查询。 这些查询可能会延迟受影响查询的进度。 检查门户中服务器的“性能建议”，以查看是否存在可优化这些查询的索引建议。 |
配置选项
启用查询存储后，它会按 pg_qs.interval_length_minutes 服务器参数（默认值为 15 分钟）确定的聚合窗口保存数据。 对于每个窗口，可最多存储 500 个非重复（具有不同的 userid、dbid 和 queryid）查询。 如果在一段时间间隔内，非重复查询数达到 500，则会解除分配使用率最低的 5% 的查询，以便为更多的查询腾出空间。
以下选项可用于配置查询存储参数：
| 参数 | 说明 | 默认 | 范围 |
|---|---|---|---|
| pg_qs.query_capture_mode | 设置跟踪哪些语句。 | 无 | none, top, all |
| pg_qs.interval_length_minutes (*) | 设置 pg_qs 的 query_store 捕获间隔（以分钟为单位）- 这是持久保存数据的频率。 | 15 | 1 - 30 |
| pg_qs.store_query_plans | 为 pg_qs 启用或禁用查询计划。 | 关闭 | on, off |
| pg_qs.max_plan_size | 为 pg_qs 设置将要保存的查询计划文本最大字节数；过长的计划会被截断。 | 7500 | 100 - 10k |
| pg_qs.max_query_text_length | 设置可以保存的最大查询长度；较长的查询将会截断。 | 6000 | 100 - 10K |
| pg_qs.retention_period_in_days | 设置 pg_qs 的保留期窗口（以天为单位）- 数据将在此时间之后删除。 | 7 | 1 - 30 |
| pg_qs.track_utility | 设置实用工具命令是否由 pg_qs 跟踪。 | on | on, off |
(*) 静态服务器参数，其值的更改需要服务器重启才能生效。
以下选项专用于等待统计信息：
| 参数 | 说明 | 默认 | 范围 |
|---|---|---|---|
| pgms_wait_sampling.query_capture_mode | 选择 pgms_wait_sampling 扩展跟踪哪些语句。 | 无 | none, all |
| Pgms_wait_sampling.history_period | 设置对等待事件采样的频率（以毫秒为单位）。 | 100 | 1-600000 |
注意
将 pgms_wait_sampling.query_capture_mode 替代为 pg_qs.query_capture_mode 。 如果 pg_qs.query_capture_mode 为 NONE，则 pgms_wait_sampling.query_capture_mode 设置无效。
使用 Azure 门户获取或设置参数的不同值。
视图和函数
使用以下视图和函数查看并管理查询存储。 PostgreSQL 公共角色中的任何人都可使用这些视图来查看查询存储中的数据。 这些视图仅在 azure_sys 数据库中可用。
通过查看查询的结构并忽略语义上不重要的任何内容（如文本、常量、别名或大小写差异）来规范化查询。
如果两个查询在语义上是相同的，那么即使它们对同一引用的列和表使用不同的别名，它们也会使用相同的 query_id 进行标识。 如果两个查询之间只有在它们中使用的文本值不同，则它们也会使用相同的 query_id 进行标识。 对于使用同一 query_id 标识的所有查询，其 sql_query_text 将属于自查询存储启动录制活动以来首先执行的查询，或自上次放弃持久化数据以来首先执行的查询，因为执行函数 query_store.qs_reset。
查询规范化的工作原理
下面是一些示例，用于尝试说明此规范化的工作原理：
假设你使用以下语句创建表：
create table tableOne (columnOne int, columnTwo int);
你启用查询存储数据收集，并且单个或多个用户按照以下确切顺序执行以下查询：
select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";
上述所有查询共享同一 query_id。 查询存储保留的文本是启用数据收集后执行的第一个查询的文本。 因此，它会是 select * from tableOne;。
以下一组查询在规范化后将与上一组查询不匹配，因为 WHERE 子句使它们在语义上不同：
select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;
但是，这最后一组中的所有查询都共享同一 query_id，并且用于标识它们的文本都是该批中第一个查询的文本 select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;。
最后，下面提供了一些与上一批中查询的 query_id 不匹配的查询，以及它们不匹配的原因：
查询：
select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
不匹配的原因：列的列表引用同两列（columnOne 和 ColumnTwo），但它们的引用顺序从上一批中的 columnOne, ColumnTwo 反转为此查询中的 ColumnTwo, columnOne。
查询：
select * from tableOne where columnTwo = 25 and columnOne = 25;
不匹配的原因：WHERE 子句中计算的表达式的引用顺序从上一批中的 columnOne = ? and ColumnTwo = ? 反转为此查询中的 ColumnTwo = ? and columnOne = ?。
查询：
select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;
不匹配的原因：列的列表中的第一个表达式不再是 columnOne，而是针对 columnOne 计算的函数 abs (abs(columnOne))，这在语义上并不等效。
查询：
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;
不匹配的原因：WHERE 子句中的第一个表达式不再计算 columnOne 与某个文本是否相等，而是计算它与针对某个文本计算的函数 ceiling 的结果是否相等，这在语义上并不等效。
视图
query_store.qs_view
此视图返回已持久保存在查询存储的支持表中的所有数据。 当前活动时间窗口内记录在内存中的数据在该时间窗口结束之前不可见，并且其内存中的易失数据会收集并持久保存到磁盘上存储的表中。 此视图为每个不同的数据库 (db_id)、用户 (user_id) 和查询 (query_id) 返回不同的行。
| Name | 类型 | 参考 | 说明 |
|---|---|---|---|
| runtime_stats_entry_id | bigint | runtime_stats_entries 表中的 ID。 | |
| user_id | oid | pg_authid.oid | 执行此语句的用户的 OID。 |
| db_id | oid | pg_database.oid | 在其中执行此语句的数据库的 OID。 |
| query_id | bigint | 根据此语句的分析树计算的内部哈希代码。 | |
| query_sql_text | varchar(10000) | 代表语句的文本。 具有相同结构的不同查询聚集在一起；此文本是群集中第一个查询的文本。 最大查询文本长度的默认值为 6000，可以使用查询存储参数 pg_qs.max_query_text_length 进行修改。 如果查询的文本超过此最大值，则会截断为前 pg_qs.max_query_text_length 个字符。 |
|
| plan_id | bigint | 与此查询对应的计划的 ID。 | |
| start_time | timestamp | 查询按时间窗口聚合，而时间窗口的时间跨度由服务器参数 pg_qs.interval_length_minutes（默认值为 15 分钟）定义。 这是与此条目的时间窗口相对应的开始时间。 |
|
| end_time | timestamp | 与此条目的时间窗口相对应的结束时间。 | |
| calls | bigint | 此时间窗口内执行该查询的次数。 请注意，对于并行查询，每个执行的调用数对应于 1（用于驱动查询执行的后端进程），加上用于每个后端工作进程（为协作执行该执行树的并行分支而启动）的其他单元数。 | |
| total_time | 双精度 | 总查询执行时间（以毫秒为单位）。 | |
| min_time | 双精度 | 最短查询执行时间（以毫秒为单位）。 | |
| max_time | 双精度 | 最长查询执行时间（以毫秒为单位）。 | |
| mean_time | 双精度 | 平均查询执行时间（以毫秒为单位）。 | |
| stddev_time | 双精度 | 查询执行时间的标准偏差（以毫秒为单位）。 | |
| 行 | bigint | 该语句检索或影响的行的总数。 请注意，对于并行查询，每个执行的行数对应于由驱动查询执行的后端进程返回到客户端的行数，加上每个后端工作进程（为协作执行该执行树的并行分支而启动）返回到此驱动后端进程的所有行的总数。 | |
| shared_blks_hit | bigint | 由该语句命中的共享块缓存总数。 | |
| shared_blks_read | bigint | 由该语句读取的共享块总数。 | |
| shared_blks_dirtied | bigint | 由该语句更新的共享块总数。 | |
| shared_blks_written | bigint | 由该语句写入的共享块总数。 | |
| local_blks_hit | bigint | 由该语句命中的本地块缓存总数。 | |
| local_blks_read | bigint | 由该语句读取的本地块总数。 | |
| local_blks_dirtied | bigint | 由该语句更新的本地块总数。 | |
| local_blks_written | bigint | 由该语句写入的本地块总数。 | |
| temp_blks_read | bigint | 由该语句读取的临时块总数。 | |
| temp_blks_written | bigint | 由该语句写入的临时块总数。 | |
| blk_read_time | 双精度 | 该语句读取块所花费的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为零）。 | |
| blk_write_time | 双精度 | 该语句写入块所花费的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为零）。 | |
| is_system_query | boolean | 确定查询是否由 user_id = 10 (azuresu) 的角色执行，该角色具有超级用户特权，并用于执行控制窗格操作。 由于此服务是托管的 PaaS 服务，因此只有 Microsoft 是该超级用户角色的一部分。 | |
| query_type | text | 查询表示的操作的类型。 可能的值有 unknown、select、update、insert、delete、merge、utility、nothing、undefined。 |
query_store.query_texts_view
此视图返回查询存储中的查询文本数据。 每个不同的 query_sql_text 都有一行。
| Name | 类型 | 说明 |
|---|---|---|
| query_text_id | bigint | query_texts 表的 ID |
| query_sql_text | varchar(10000) | 代表语句的文本。 具有相同结构的不同查询聚集在一起；此文本是群集中第一个查询的文本。 |
| query_type | smallint | 查询表示的操作的类型。 在 <= 14 的 PostgreSQL 版本中，可能的值为 0 (unknown)、1 (select)、2 (update)、3 (insert)、4 (delete)、5 (utility)、6 (nothing)。 在 >= 15 的 PostgreSQL 版本中，可能的值为 0 (unknown)、1 (select)、2 (update)、3 (insert)、4 (delete)、5 (merge)、6 (utility)、7 (nothing)。 |
query_store.pgms_wait_sampling_view
此视图返回查询存储中的等待事件数据。 此视图为每个不同的数据库 (db_id)、用户 (user_id)、查询 (query_id) 和事件 (event) 返回不同的行。
| Name | 类型 | 参考 | 描述 |
|---|---|---|---|
| start_time | timestamp | 查询按时间窗口聚合，而时间窗口的时间跨度由服务器参数 pg_qs.interval_length_minutes（默认值为 15 分钟）定义。 这是与此条目的时间窗口相对应的开始时间。 |
|
| end_time | timestamp | 与此条目的时间窗口相对应的结束时间。 | |
| user_id | oid | pg_authid.oid | 执行此语句的用户的 OID。 |
| db_id | oid | pg_database.oid | 在其中执行此语句的数据库的 OID。 |
| query_id | bigint | 根据此语句的分析树计算的内部哈希代码。 | |
| event_type | text | 后端正在等待的事件类型。 | |
| event | text | 后端当前正在等待的等待事件名称。 | |
| calls | integer | 已捕获同一事件的次数。 |
注意
有关 query_store.pgms_wait_sampling_view 视图的 event_type 和 event 列中可能值的列表，请参阅 pg_stat_activity 的官方文档，并查找引用同名列的信息。
query_store.query_plans_view
此视图返回已用于执行查询的查询计划。 每个不同的数据库 ID 和查询 ID 都有一行。 这将仅存储非实用工具查询的查询计划。
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | bigint | 由 EXPLAIN 生成的规范化查询计划的哈希值。 它被视为规范化，因为它排除了计划节点的估计成本和缓冲区的使用。 | |
| db_id | oid | pg_database.oid | 在其中执行此语句的数据库的 OID。 |
| query_id | bigint | 根据此语句的分析树计算的内部哈希代码。 | |
| plan_text | varchar(10000) | 在给定了 costs=false、buffers=false 且 format=text 的情况下该语句的执行计划。 这是 EXPLAIN 给出的相同输出。 |
函数
query_store.qs_reset
此函数会丢弃查询存储迄今为止收集的所有统计信息。 它会丢弃已关闭的时间窗口的统计信息（这些统计信息已持久保存到磁盘表上），以及当前时间窗口的统计信息（这些统计信息仍保留在内存中）。 此函数只能由服务器管理员角色 (azure_pg_admin) 执行。
query_store.staging_data_reset
此函数将丢弃查询存储收集到内存中的所有统计信息（即尚未刷新到支持持久保存查询存储收集的数据的磁盘表上的内存中数据）。 此函数只能由服务器管理员角色 (azure_pg_admin) 执行。
限制和已知问题
Azure 存储和查询存储兼容性
由于兼容性问题，无法同时启用 Azure 存储和查询存储扩展。 建议用户一次仅启用其中一个扩展，以确保正常运行并避免潜在冲突。
要使用 Azure 存储，请执行以下操作：
- 通过将参数
pg_qs.query_capture_mode设置为NONE来禁用查询存储。 此参数是动态的，因此无需重启。
要使用查询存储，请执行以下操作：
- 通过颁发
DROP EXTENSION azure_storage;来禁用 Azure 存储扩展。 - 从
shared_preload_libraries中移除 Azure 存储。 - 重启数据库服务器。
这些步骤对于防止冲突和确保系统正确运行是必要的。 我们正在努力解决这些兼容性问题，并将随时向你通知全部更新。
只读模式
当 Azure Database for PostgreSQL 灵活服务器实例处于只读模式（例如 default_transaction_read_only 参数设置为 on 时）或由于达到存储容量而自动启用只读模式时，查询存储不会捕获任何数据。