Optimize performance using Azure Database for PostgreSQL Recommendations

4월 25, 2019에 게시됨

Program Manager II, Microsoft Azure

As a developer today, you have a lot of things to worry about. There are new technologies, frameworks, and deployment models that you need to keep up with so that you can build game-changing apps for customers. At the end of the day though, one thing that remains unchanged is the need to focus on developing features that add value while ensuring that your database remains functional and performant. In the past, a DBA would take care of your database performance, but today that responsibility can be shared across many roles.

You no longer have to be a database expert to optimize your database. Make your job easier and start taking advantage of Azure Database for PostgreSQL Recommendation for Azure Database for PostgreSQL today.

Analyzing workloads + making recommendations = heart

By analyzing the workloads on your server, the recommendations feature gives you daily insights about the Azure Database for PostgreSQL resources that you can optimize for performance. These recommendations are tightly integrated with Azure Advisor to provide you with best practices directly within the Azure portal. Azure Advisor is a personalized cloud consultant that helps you follow guidelines to optimize your Azure deployments.

If you’re running your Postgres application on Azure today and you want to see the recommendations we’ve already made to help you optimize your Azure Database for PostgreSQL resources, it’s easy! Just check out the performance recommendations tab in the Azure Advisor.

Types of settings we make recommendations about

The recommendation feature continuously monitors and analyzes your database servers' telemetry to determine if your workload performance can be improved by configuring one or more of the resource settings. Currently, we monitor, analyze, and make recommendations for four different types of settings for your Azure Database for PostgreSQL deployment.

  • CPU bottlenecks – Very high utilization of the CPU over an extended period can cause slow query performance for your workload. Increasing the CPU size is likely to help in optimizing the runtime of the database queries and improve overall performance. We will identify servers with a high CPU utilization that are likely running CPU constrained workloads and recommend scaling your compute.
  • Connection constraints – Each new connection to Postgres occupies some memory. The database server's performance degrades if connections to your server are failing because of an upper limit in memory. We will identify servers running with many connection failures and recommend upgrading your server's connections limits to provide more memory to your server by scaling up compute or using Memory Optimized SKUs, which have more memory per core.
  • Memory constraints – A low cache hit ratio can result in slower query performance and increased IOPS. This could be due to a bad query plan or running a memory intensive workload. Fixing the query plan or increasing the memory of your server will help optimize the execution of the database workload. Azure Database for PostgreSQL Recommendation identifies servers affected due to this high buffer pool churn and recommends either fixing the query plan, moving to a higher SKU with more memory, or increasing storage size to get more IOPS.
  • Read-intensive workloads – The new Azure Database for PostgreSQL recommendation feature leverages workload-based heuristics such as the ratio of reads to writes on the server over the past seven days. Your PostgreSQL resource with a very high read/writes ratio can result in CPU and/or memory contentions leading to slow query performance. Adding a replica will help in scaling out reads to the replica server, preventing CPU and/or memory constraints on the primary server. Azure Database for PostgreSQL Recommendation will identify servers with such high read-intensive workloads and recommend adding a read replica to offload some of the read workloads.

Getting started with the Recommendation feature

The only prerequisite for getting started with Azure Database for PostgreSQL Recommendation is that you have an instance of Azure Database for PostgreSQL running with some load.

Access Azure Database for PostgreSQL Recommendation in the Azure portal

You can access the recommendations feature by default with no additional cost. The recommendations provided for your server are accessible either via the Azure Advisors page or the server’s overview page, as shown in Figures 1 and 2 below.

fig1

Fig 1: Azure Database for PostgreSQL Recommendation on the Azure Advisor’s blade on the portal

Screenshot of Azure Recommendations from the overview blade of your PostgreSQL resource

Fig 2: Azure Database for PostgreSQL Recommendation from the overview blade of your PostgreSQL resource

Go deeper with the recommendation on Azure portal

After accessing the type of recommendation, you need to select a specific recommendation. Based on the recommendation type, you can take specific actions to address the performance constraints for the server. You can see the recommendation details as you select the recommendation type (Label 1 in Fig 3). This provides the details you need to optimize your Azure resource. Additionally, you can act on a recommendation by selecting recommended actions (Label 2 in Figure 3).

fig3

Fig 3: Gather further recommendation details and take actions

Azure Advisor provides inline actions, a convenient way to select and implement recommendations without leaving the Azure Advisor portal.

If you don’t intend to act immediately, you can postpone a recommendation for a period. You can also dismiss a recommendation to ensure that it no longer appears. If you do not want to receive recommendations for a specific subscription or resource group, you can configure Azure Advisor via the portal or by using the CLI to generate recommendations only for those that you specify. For more information about using Azure Advisor, see the article Get started with Azure Advisor.

After implementing any recommendation, be sure to evaluate performance to measure the impact of the changes you made.

Feedback on the new recommendation feature

Our early users of the recommendations feature in Azure Database for PostgreSQL have benefitted by the recommendations and the resulting performance improvements. Our goal is to make it easier for you to focus on your application without having to worry about the performance of your database server.

So, go ahead and check out Azure Database for PostgreSQL Recommendation in the Azure portal. If you identify any problems or want to make a feature suggestion, please reach out to the Ask Azure DB for PostgreSQL alias. You can also engage with us on the Azure feedback forums for Azure Database for PostgreSQL.

We look forward to hearing from you!