配置自治优化

可以通过一组用于控制其行为的参数来启用、禁用和配置自动调优，例如调整会话应多长时间运行一次。

自治优化取决于azure_sys将数据保存在本地数据库。建议不要在可突发定价层上启用查询存储，因为这可能会对性能造成影响。出于同样的原因，不建议对使用突发型计算层的服务器进行自主调优。

在默认情况下，自动调优是一项需用户选择加入的功能，未在服务器上启用。对于给定服务器上的所有数据库，可以将其进行全局启用或禁用，但无法针对每个数据库启用或禁用。

启用自动调优的步骤

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择“ 自主优化”。
如果pg_qs.query_capture_mode被设置为none或index_tuning.mode被设置为off，自治优化页提供启用自治优化的选项。选择两个 “启用优化 ”按钮之一，以便在禁用查询存储时启用自治优化功能及其所需的查询存储依赖项。
在考虑启用该功能之前，请等待部署成功完成。
启用自主调优后，允许自主调优引擎需要最长 12 小时来分析在此期间由查询存储收集的工作负载，并最终生成建议。

重要

通过 启用调优 按钮开启自动调优时，如果 pg_qs.query_capture_mode 设置为 none，则会更改为 all。如果它已设置为 top 或 all，则保持其当前状态。

您可以通过 az postgres flexible-server autonomous-tuning update 命令在现有服务器上启用自动调整。

若要启用自动优化，请使用以下命令：

az postgres flexible-server autonomous-tuning update \
  --resource-group <resource_group> \
  --server-name <server> \
  --enabled true

如果上一个命令成功执行，应会看到以下输出：

WARNING: Enabling autonomous tuning for the server.
WARNING: Autonomous tuning is enabled for the server.

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择“ 自主优化”。
选择“ 禁用优化 ”以禁用该功能。
在考虑禁用该功能之前，请等待部署成功完成。
评估是否要继续使用查询存储来监视工作负载性能并保持启用，或者如果想要禁用它，请设置pg_qs.query_capture_mode为none。

重要

通过禁用优化按钮禁用自动调优时，服务器参数pg_qs.query_capture_mode不会被设置为none，而是保持当前配置。

可以通过 az postgres flexible-server autonomous-tuning update 命令禁用现有服务器中的自动调优。

要禁用自动调整，请使用以下命令：

az postgres flexible-server autonomous-tuning update \
  --resource-group <resource_group> \
  --server-name <server> \
  --enabled false

如果上一个命令成功执行，应会看到以下输出：

WARNING: Disabling autonomous tuning for the server.
WARNING: Autonomous tuning is disabled for the server.

评估是否希望继续使用查询存储来监视工作负荷的性能并保持其启用状态，或者通过将pg_qs.query_capture_mode设置为none来禁用它。

重要

通过 CLI 命令禁用自动调优时，服务器参数 pg_qs.query_capture_mode 未设置为 none，而是保持当前配置。

自动调优状态的显示步骤

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择“ 自主优化”。
如果启用了自动优化，页面将显示 禁用优化 按钮。
如果禁用了自动调优，页面将显示“启用优化”按钮。

可以通过 az postgres flexible-server autonomous-tuning show 命令来查看已有服务器上的自动调优状态。

若要显示自治优化的状态，请使用以下命令：

az postgres flexible-server autonomous-tuning show \
  --resource-group <resource_group> \
  --server-name <server>

如果启用了自动调优，您应该会看到以下输出：

WARNING: Autonomous tuning is enabled for the server.

如果禁用了自动调优，您将会看到以下输出：

WARNING: Autonomous tuning is disabled for the server.

显示自动调优设置的步骤

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择“ 自主优化”。
选择 “调优设置”。

可以通过命令 az postgres flexible-server autonomous-tuning show-settings 在现有服务器中显示某个自主优化设置的值。

例如，若要显示名为analyze_interval的自动调优设置的值，请使用以下命令：

az postgres flexible-server autonomous-tuning show-settings \
  --resource-group <resource_group> \
  --server-name <server> \
  --name analyze_interval

该命令返回与自治优化设置对应的服务器参数的所有信息，输出如下所示：

{
  "allowedValues": "60-10080",
  "dataType": "Integer",
  "defaultValue": "720",
  "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
  "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
  "isConfigPendingRestart": false,
  "isDynamicConfig": true,
  "isReadOnly": false,
  "name": "index_tuning.analysis_interval",
  "resourceGroup": "<resource_group>",
  "source": "user-override",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "unit": "minutes",
  "value": "720"
}

此外，还可以通过 az postgres flexible-server autonomous-tuning list-settings 命令显示现有服务器中所有自治优化设置的列表。

若要列出所有自调优设置，请使用此命令：

az postgres flexible-server autonomous-tuning list-settings \
  --resource-group <resource_group> \
  --server-name <server>

该命令返回所有控制不同自治调优设置的服务器参数，输出类似如下所示：

[
  {
    "allowedValues": "60-10080",
    "dataType": "Integer",
    "defaultValue": "720",
    "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.analysis_interval",
    "resourceGroup": "<resource_group>",
    "source": "user-override",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "minutes",
    "value": "720"
  },
  {
    "allowedValues": "1-10",
    "dataType": "Integer",
    "defaultValue": "2",
    "description": "Maximum number of columns that can be part of the index key for any recommended index.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_columns_per_index",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_columns_per_index",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "2"
  },
  {
    "allowedValues": "1-25",
    "dataType": "Integer",
    "defaultValue": "10",
    "description": "Maximum number of indexes that can be recommended for each database during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_index_count",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_index_count",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "10"
  },
  {
    "allowedValues": "1-25",
    "dataType": "Integer",
    "defaultValue": "10",
    "description": "Maximum number of indexes that can be recommended for each table.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_indexes_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_indexes_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "10"
  },
  {
    "allowedValues": "5-100",
    "dataType": "Integer",
    "defaultValue": "25",
    "description": "Number of slowest queries per database for which indexes can be recommended.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_queries_per_database",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_queries_per_database",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "25"
  },
  {
    "allowedValues": "0.05-0.2",
    "dataType": "Numeric",
    "defaultValue": "0.1",
    "description": "Acceptable regression introduced by a recommended index on any of the queries analyzed during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_regression_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_regression_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.1"
  },
  {
    "allowedValues": "0-1.0",
    "dataType": "Numeric",
    "defaultValue": "0.1",
    "description": "Maximum total size, in percentage of total disk space, that all recommended indexes for any given database can use.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.max_total_size_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.max_total_size_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.1"
  },
  {
    "allowedValues": "0-20.0",
    "dataType": "Numeric",
    "defaultValue": "0.2",
    "description": "Cost improvement that a recommended index must provide to at least one of the queries analyzed during one optimization session.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.min_improvement_factor",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.min_improvement_factor",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "percentage",
    "value": "0.2"
  },
  {
    "allowedValues": "off,report",
    "dataType": "Enumeration",
    "defaultValue": "off",
    "description": "Configures index optimization as disabled ('OFF') or enabled to only emit recommendation. Requires Query Store to be enabled by setting pg_qs.query_capture_mode to 'TOP' or 'ALL'.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.mode",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.mode",
    "resourceGroup": "<resource_group>",
    "source": "user-override",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "off"
  },
  {
    "allowedValues": "0-9999999",
    "dataType": "Integer",
    "defaultValue": "1000",
    "description": "Minimum number of daily average DML operations affecting the table, so that their unused indexes are considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_dml_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_dml_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "1000"
  },
  {
    "allowedValues": "30-720",
    "dataType": "Integer",
    "defaultValue": "35",
    "description": "Minimum number of days the index has not been used, based on system statistics, so that it is considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_min_period",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_min_period",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": "days",
    "value": "35"
  },
  {
    "allowedValues": "0-9999999",
    "dataType": "Integer",
    "defaultValue": "1000",
    "description": "Minimum number of daily average read operations affecting the table, so that their unused indexes are considered for dropping.",
    "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
    "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.unused_reads_per_table",
    "isConfigPendingRestart": false,
    "isDynamicConfig": true,
    "isReadOnly": false,
    "name": "index_tuning.unused_reads_per_table",
    "resourceGroup": "<resource_group>",
    "source": "system-default",
    "systemData": null,
    "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
    "unit": null,
    "value": "1000"
  }
]

修改自调设置的步骤

Portal
CLI

使用 Azure 门户：

选择你的 Azure Database for PostgreSQL 灵活服务器实例。
在资源菜单中的 “智能性能”下，选择“ 自主优化”。
选择 “调优设置”。
根据需要修改任意数量的设置的值，然后选择“ 保存”。
等待部署成功完成，然后再考虑更改设置的值。

可以通过 az postgres flexible-server autonomous-tuning set-settings 命令修改现有服务器中单个自治优化设置的值。

例如，若要将名为analyze_interval的自治优化设置的值设为1440，请使用以下命令：

az postgres flexible-server autonomous-tuning set-settings \
  --resource-group <resource_group> \
  --server-name <server> \
  --name analyze_interval \
  --value 1440

该命令返回与自治优化设置对应的服务器参数的所有信息，输出如下所示：

{
  "allowedValues": "60-10080",
  "dataType": "Integer",
  "defaultValue": "720",
  "description": "Sets the frequency at which each index optimization session is triggered when index_tuning.mode is set to 'REPORT'.",
  "documentationLink": "https://go.microsoft.com/fwlink/?linkid=2274149",
  "id": "/subscriptions/<subscription_id>/resourceGroups/<resource_group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>/configurations/index_tuning.analysis_interval",
  "isConfigPendingRestart": false,
  "isDynamicConfig": true,
  "isReadOnly": false,
  "name": "index_tuning.analysis_interval",
  "resourceGroup": "<resource_group>",
  "source": "user-override",
  "systemData": null,
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "unit": "minutes",
  "value": "1440"
}

Last updated on 2026-02-12

Compartir a través de

配置自治优化

启用自动调优的步骤

禁用自动调优的步骤

自动调优状态的显示步骤

显示自动调优设置的步骤

修改自调设置的步骤

相关内容

Recursos adicionales