教程:监视和优化 Azure Database for PostgreSQL(单一服务器)Tutorial: Monitor and tune Azure Database for PostgreSQL - Single Server

Azure Database for PostgreSQL 提供有助于了解和改进服务器性能的功能。Azure Database for PostgreSQL has features that help you understand and improve your server performance. 在本教程中,将了解如何:In this tutorial you will learn how to:

  • 启用查询和等待统计信息收集Enable query and wait statistics collection
  • 访问和利用收集的数据Access and utilize the data collected
  • 查看查询性能和等待一段时间内的统计信息View query performance and wait statistics over time
  • 分析数据库以获取性能建议Analyze a database to get performance recommendations
  • 应用性能建议Apply performance recommendations

开始之前Before you begin

需要一个使用 PostgreSQL 版本 9.6 或 10 的 Azure Database for PostgreSQL 服务器。You need an Azure Database for PostgreSQL server with PostgreSQL version 9.6 or 10. 可以按照创建教程中的步骤创建服务器。You can follow the steps in the Create tutorial to create a server.

Important

查询存储 、Query Performance Insight 和性能建议 均在公共预览版中提供。Query Store, Query Performance Insight, and Performance Recommendation are in Public Preview.

允许收集数据Enabling data collection

查询存储捕获服务器上的查询和等待统计信息的历史记录,并将其存储在服务器上的 azure_sys 数据库中。The Query Store captures a history of queries and wait statistics on your server and stores it in the azure_sys database on your server. 这是一个可选的功能。It is an opt-in feature. 若要启用此功能,请执行以下操作:To enable it:

  1. 打开 Azure 门户。Open the Azure portal.

  2. 选择你的 Azure Database for PostgreSQL 服务器。Select your Azure Database for PostgreSQL server.

  3. 在左侧菜单的“设置”部分中选择“服务器参数” 。Select Server parameters which is in the Settings section of the menu on the left.

  4. 将 pg_qs.query_capture_mode 设置为“TOP” 以开始收集查询性能数据。Set pg_qs.query_capture_mode to TOP to start collecting query performance data. 将 pgms_wait_sampling.query_capture_mode 设置为“ALL” 以开始收集等待统计信息。Set pgms_wait_sampling.query_capture_mode to ALL to start collecting wait statistics. 保存。Save.

    查询存储服务器参数

  5. 允许第一批数据在 azure_sys 数据库中最多保留 20 分钟。Allow up to 20 minutes for the first batch of data to persist in the azure_sys database.

性能见解Performance insights

Azure 门户中的 Query Performance Insight 视图将显示来自查询存储的关键信息的可视化效果。The Query Performance Insight view in the Azure portal will surface visualizations on key information from Query Store.

  1. 在 Azure Database for PostgreSQL 服务器的门户页面中,选择左侧菜单的“支持 + 疑难解答” 部分下的“Query Performance Insight” 。In the portal page of your Azure Database for PostgreSQL server, select Query performance Insight under the Support + troubleshooting section of the menu on the left.

  2. “长时间运行查询” 选项卡按平均每次执行持续时间显示前 5 个查询,每隔 15 分钟聚合一次。The Long running queries tab shows the top 5 queries by average duration per execution, aggregated in 15 minute intervals.

    Query Performance Insight 登陆页面

    可以通过从“查询数量” 下拉列表中进行选择来查看更多查询。You can view more queries by selecting from the Number of Queries drop down. 执行此操作时,特定查询 ID 的图表颜色可能会更改。The chart colors may change for a specific Query ID when you do this.

  3. 可以在图表中单击并拖动以缩小到特定的时间窗口。You can click and drag in the chart to narrow down to a specific time window.

  4. 使用放大和缩小图标分别查看更短或更长的时间段。Use the zoom in and out icons to view a smaller or larger period of time respectively.

  5. 查看图表下方的表,以了解有关该时间窗口中长时间运行的查询的更多详细信息。View the table below the chart to learn more details about the long-running queries in that time window.

  6. 选择“等待统计信息” 选项卡以查看服务器中等待次数的相应可视化效果。Select the Wait Statistics tab to view the corresponding visualizations on waits in the server.

    Query Performance Insight 等待统计信息

权限Permissions

查看 Query Performance Insight 中查询文本所需的“所有者” 或“参与者” 权限。Owner or Contributor permissions required to view the text of the queries in Query Performance Insight. 读者 可以查看图表和表格,但不能查看查询文本。Reader can view charts and tables but not query text.

性能建议Performance recommendations

性能建议功能跨服务器分析工作负载以标识可能会提高性能的索引。The Performance Recommendations feature analyzes workloads across your server to identify indexes with the potential to improve performance.

  1. 从 PostgreSQL 服务器的 Azure 门户页上的菜单栏的“支持 + 疑难解答” 部分中打开“性能建议” 。Open Performance Recommendations from the Support + troubleshooting section of the menu bar on the Azure portal page for your PostgreSQL server.

    性能建议登陆页面

  2. 选择“分析” 并选择数据库。Select Analyze and choose a database. 随即会开始分析。This will begin the analysis.

  3. 这可能需要几分钟的时间才能完成,具体取决于你的工作负载。Depending on your workload, this may take several minutes to complete. 分析完成后,门户中将出现通知。Once the analysis is done, there will be a notification in the portal.

  4. 如果无发现,则 “性能建议”窗口将显示建议列表。The Performance Recommendations window will show a list of recommendations if any were found.

  5. 建议将显示有关相关“数据库” 、“表” 、“列” 和“索引大小” 的信息。A recommendation will show information about the relevant Database, Table, Column, and Index Size.

    “性能建议”结果

  6. 若要实施建议,请复制查询文本并从所选的客户端中运行。To implement the recommendation, copy the query text and run it from your client of choice.

权限Permissions

使用性能建议功能运行分析所需的 “所有者”或“参与者” 权限。Owner or Contributor permissions required to run analysis using the Performance Recommendations feature.

后续步骤Next steps