Azure Database for PostgreSQL 灵活服务器中的自主优化

自动优化是 Azure Database for PostgreSQL 灵活服务器的一项功能，它会分析工作负载中记录的查询，并提供有助于提高这些查询性能的建议。

它是Azure Database for PostgreSQL灵活服务器中的内置产品/服务，基于查询存储功能构建。 自主优化会分析查询存储所跟踪的工作负载，并生成有关索引或表的建议，以提高已分析工作负载的性能。 它可以生成创建新索引、消除重复索引或未使用的索引、分析没有统计信息或过时统计信息的表或清空膨胀表的建议。

• 确定创建哪些索引是有益的，因为它们可以显著改善在自动优化会话期间分析的查询。
• 标识确切重复项并可以消除的索引。
• 确定可配置期间内未使用的索引，这些索引可能是要消除的候选索引。
• 标识标记为无效的索引 ，这些索引应重新编制索引，以将其转换为有效索引。
• 标识缺少当前统计信息且需要分析的表。
• 标识应清空的膨胀表。

自动调整算法的概述

当您将 index_tuning.mode 参数配置为 report 时，系统会按照您在 index_tuning.analysis_interval 参数中配置的频率自动启动调优会话，频率以分钟为单位。

在第一阶段，优化过程会查找这样一份数据库列表：针对这些数据库提出的建议可能会显著影响系统的整体性能。 为此，它会收集查询存储记录的所有查询，这些查询存储的执行是在此优化会话关注的查找间隔内捕获的。 查找间隔目前为过去 index_tuning.analysis_interval 分钟，从优化会话的开始时间开始。

对于所有用户启动的查询，其执行记录在查询存储中，并且其运行时统计信息未重置，系统会根据它们的聚合总执行时间对它们进行排名。 它根据查询的持续时间将注意力集中在最突出的查询上。

以下查询已从该列表中排除：

• 系统启动的查询。 （即，由 azuresu 角色执行的查询）
• 在任何系统数据库（azure_sys、template0、template1 和 azure_maintenance）的上下文中执行的查询。

算法在目标数据库上循环访问，搜索可能提高所分析工作负载性能的索引。 它还会查找那些因重复或在可配置的一段时间内未被使用而可以删除的索引。 它还会识别出缺少最新统计信息的表或臃肿的表。

创建索引建议

对于标识为候选分析的每个数据库，该过程将考虑在查找间隔和该特定数据库的上下文中执行的所有 SELECT、UPDATE、INSERT 和 DELETE 查询。

该过程根据聚合后的总执行时间对生成的一组查询进行排名，并分析排名前 index_tuning.max_queries_per_database 的查询，以查找可能的索引建议。

潜在建议旨在提高这些类型查询的性能：

• 具有筛选器的查询（即 WHERE 子句中具有谓词的查询）。
• 联接多个关系的查询（使用 JOIN 子句表示联接或在 WHERE 子句中表示联接谓词）。
• 合并筛选器和联接谓词的查询。
• 带分组的查询（带 GROUP BY 子句的查询）。
• 合并筛选器和分组的查询。
• 带排序的查询（带 ORDER BY 子句的查询）。
• 合并筛选器和排序的查询。

如果查询引用表的一列且该表没有统计信息，则该过程不会生成任何索引建议来改进其执行。 但是，它会生成用于分析表的建议。

index_tuning.max_indexes_per_table 指定可以建议的索引数，不包括在优化会话期间由任意数量的查询引用的任何单个表的表上可能已经存在的任何索引。

index_tuning.max_index_count 指定为优化会话期间分析的任何数据库的所有表生成的索引建议数。

对于要发出的索引建议，优化引擎必须估计它以 index_tuning.min_improvement_factor 指定的因子改进所分析工作负载中至少一个查询。

同样，该过程会检查所有索引建议，以确保它们不会对指定 index_tuning.max_regression_factor因子的该工作负荷中的任何单个查询引入回归。

前面段落中提到的所有参数、默认值和有效范围都在 配置选项中介绍。

与创建索引的建议一起生成的脚本遵循以下模式：

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

它包括 CONCURRENTLY 子句。 有关此子句效果的详细信息，请参阅 CREATE INDEX 的 PostgreSQL 官方文档。

自动调优自动生成建议索引的名称，这些名称通常由不同的键列名称用“_”（下划线）分隔，并以固定的“_idx”后缀组成。 如果名称的总长度超过 PostgreSQL 限制，或者与任何现有关系冲突，则名称略有不同。 它可以截断，并且可以在名称的末尾追加数字。

计算 CREATE INDEX 建议的影响

创建索引建议的影响是根据 IndexSize（兆字节）和 QueryCostImprovement（百分比）来度量的。

IndexSize 是单一值，表示索引的估计大小，考虑到表的当前基数和建议索引引用的列大小。

QueryCostImprovement 由值数组组成，其中每个元素表示每个查询的计划成本改进，如果存在此索引，其计划的成本估计将得到改进。 每个元素都显示查询的标识符（已查询），以及实施建议时计划成本会改善的百分比（幅度）。

DROP INDEX 和 REINDEX 建议

对于标识为候选数据库的每个数据库，该过程将启动一个新会话。 CREATE INDEX 建议生成阶段完成后，会根据以下标准建议删除或重建现有索引：

• 如果被视为重复项，则删除。
• 如果在可配置的时间内未使用，请删除。
• 重新编制标记为无效的索引。

删除重复索引

有关删除重复索引的建议首先确定哪些索引具有重复项。

重复项根据可归入索引的不同函数进行排名，并根据其估计大小进行排名。

该流程最终建议删除所有排名低于其参考主项的重复项，并说明每个重复项为何会得到当前的排名。

若要将两个索引视为重复索引，必须：

• 通过同一个表创建。
• 是完全相同类型的索引。
• 使其键列相匹配；对于多列索引键，还要匹配其被引用的顺序。
• 匹配其谓词的表达式树。 此条件仅适用于部分索引。
• 匹配所有非简单列引用的表达式树。 此条件仅适用于在表达式上创建的索引。
• 匹配键中引用的每列的排序规则。

删除未使用的索引

删除未使用索引的建议可识别出符合以下条件的索引：

• 至少有 index_tuning.unused_min_period 天没有使用。
• 在创建索引的表上显示(每日平均)最少 index_tuning.unused_dml_per_table DML 数。
• 在创建索引的表上显示(每日平均)最少 index_tuning.unused_reads_per_table 读取数。

重新编制无效索引

有关对现有索引重新编制索引的建议可识别出那些被标记为无效的索引。 若要详细了解索引被标记为无效的原因和时间，请参阅 PostgreSQL 官方文档中的 REINDEX 。

计算 DROP INDEX 建议的影响

删除索引建议的影响是从两个维度来衡量的：效益（百分比）和 IndexSize（兆字节）。

benefit 是一个暂时可以忽略的单一值。

IndexSize 是单一值，表示索引的估计大小，考虑到表的当前基数和建议索引引用的列大小。

数据表建议

对于标识为候选分析的每个数据库，该过程将启动一个旨在生成表级建议的会话。 这些建议会建议你对已检查的查询所访问的表运行 ANALYZE 或 VACUUM。 优化引擎认为运行这些命令可以提高工作负荷的性能。

分析表格推荐

建议用于分析的表格以识别那些表格：

• 在查询中被引用，并且该表的某一列在其某个谓词（WHERE、JOIN、ORDER BY、GROUP BY）中被使用，并且还满足以下两个条件之一：
  • 绝不会被分析。
  • 在某些时候进行了分析，但现在缺少统计信息（通常是因为服务器在统计信息保存到磁盘之前崩溃）。

VACUUM 表格推荐

有关清空表的建议标识那些膨胀的表。 仅当在分析工作负荷时，autovacuum_enabled 未在服务器级别设置为 off，该过程才会生成这些建议。

配置自治调优

您可以通过一组用于控制其行为的参数，启用、禁用和配置自主优化。

启用自治优化时，它会以参数（默认为 720 分钟或 12 小时）中 index_tuning.analysis_interval 配置的频率唤醒，并开始分析该时间段内查询存储记录的工作负荷。

如果更改其值 index_tuning.analysis_interval，则新值仅在下一个计划执行完成后生效。 例如，如果某天上午 10:00 启用自动优化，由于 index_tuning.analysis_interval 的默认值为 720 分钟，因此首次执行计划在当天晚上 10:00 开始。 对上午 10：00 到下午 10：00 之间的值 index_tuning.analysis_interval 所做的任何更改都不会影响初始计划。 仅当计划的运行完成时，它才会读取为 index_tuning.analysis_interval 该值设置的当前值，并根据该值计划下一次执行。

使用以下选项配置自动优化参数：

如果使用 CLI 命令 az postgres flexible-server autonomous-tuning show-settings 和 az postgres flexible-server autonomous-tuning set-settings 显示或修改任何自治优化设置，则接受为参数参数 --name 的值是上表的 “参数 ”列中显示的值，但不包括前缀 index_tuning.。

由自动调优生成的信息

使用自治优化建议 详细介绍了如何获取和使用自治优化生成的建议。

限制和可支持性

以下列表说明了自主优化的限制和支持范围。

自动删除建议

系统在上次生成建议 35 天后自动删除建议。 要使此自动删除机制生效，必须启用自动优化。

hypopg 扩展的依赖项

自动调优使用 CREATE INDEX 扩展来生成 建议。

如果扩展在调优会话开始时已存在，则该进程会在创建该扩展的架构中使用它。 调优会话结束后，该进程不会卸载该扩展。 此规则的例外情况是，如果扩展是在pg_catalog架构中创建的。 如果是这种情况，自动调优会去掉扩展。

如果该扩展原本就不存在，或者该进程因其是在 pg_catalog 架构中创建的而将其删除，则自主优化会在名为 ms_temp_recommendations709253 的架构下创建它。 优化会话成功完成后，进程会删除扩展并删除架构。

属于 azure_pg_admin 角色的用户可以随时删除 hypopg 扩展，即使该扩展是由自动调优功能创建的。 但是，在运行自治优化会话时删除它可能会导致该会话失败，并且不生成任何建议。

支持的计算层和 SKU

Azure Database for PostgreSQL 灵活服务器版支持在所有当前可用的服务层级上进行自主优化：突发性能型、常规用途型和内存优化型。 它还支持在任何具有至少 4 个 vCore 的当前支持的计算 SKU上进行自动优化。

PostgreSQL 的受支持版本

适用于 PostgreSQL 的 Azure 数据库灵活服务器支持在主版本12 或更高版本上使用自主优化。

使用 search_path

自主优化使用 search_path 中 列的值。 在分析每个查询时，它会使用该查询最初执行时设置的同一个 search_path 值来分析可能的建议。

参数化查询

使用 PREPARE 或通过使用 扩展查询协议 创建的参数化查询会被解析和分析，以生成索引建议。

对于参数化查询的分析，当查询存储捕获查询执行时，自治优化要求 pg_qs.parameters_capture_mode 必须设置为 capture_first_sample。 它还要求查询存储在执行查询时正确捕获参数。 换句话说，对于要分析的查询，parameters_capture_statusquery_store.qs_view 中的列必须设置为 succeeded。

只读模式和只读副本

由于自动优化依赖于 查询存储 在 azure_sys 数据库本地持久保存的数据，并且不支持 只读副本或服务器处于只读模式，因此此功能不支持只读副本，也不支持处于只读模式的服务器。

您在只读副本上看到的任何建议，都是在仅分析主副本上执行的工作负载后于主副本上生成的。

缩减计算

如果在服务器上启用自治优化，然后将该服务器的计算缩减为小于所需 vCore 数的最小数量，该功能将保持启用状态。 由于该功能在少于 4 个 vCore 的服务器上不受支持，因此它不会运行以分析工作负荷并生成建议，即使 index_tuning.mode 已设置为 ON 在缩减计算时也是如此。 虽然服务器不满足最低要求，但所有 index_tuning.* 参数都无法访问。 每当您将服务器缩放回来，使其满足最低要求时，index_tuning.mode 会配置为先前缩小到不符合要求的计算之前设置的值。

高可用性和只读副本

如果在服务器上配置 高可用性 或 只读副本 ，请注意在实现建议索引时与在主服务器上生成写入密集型工作负荷相关的影响。 创建大小估计很大的索引时要特别小心。

自动调优可能不为某些查询生成创建索引建议的原因

自动优化不会为以下类型的查询生成 CREATE INDEX 建议：

• 当自治优化引擎尝试在分析阶段获取 EXPLAIN 输出时遇到错误的查询。
• 在系统目录中引用表的查询，这些表没有有关其内容的 pg_statistic 统计信息。 在这些表上运行 ANALYZE ，以便优化引擎将来可以考虑这些查询。
• 查询存储中带有截断查询文本的查询。 当查询文本长度超过 在 pg_qs.max_query_text_length 中配置的值时，会发生此截断。
• 引用在分析发生之前删除或重命名的对象的查询。 这些查询仍然可以在语法上有效，但它们在语义上无效。
• 访问临时表或其上的索引的查询。
• 访问视图或物化视图的查询。
• 访问已分区表的查询。
• 标识为实用工具语句的查询。 实用程序语句或实用程序命令，基本上是指任何不被视为 SELECT、INSERT、UPDATE、DELETE 或 MERGE 的语句，以及包含这些语句之一的某些命令。
• 在分析的数据库和时间段中，不属于最 index_tuning.max_queries_per_database 最慢的查询。
• 在某个特定数据库的上下文中运行的查询，但其中没有任何查询被识别为服务器级别最慢的查询。

相关内容

• 查询存储。
• 配置自动优化。
• 使用自动调优建议。