Cluster Recommendations

Recommendations

The Recommendations tab shows a scorecard and a list of recommendations for a cluster which can help to improve the Postgres cluster performance.

The Scorecard and Recommendation are directly related. A cluster will only score lower than Excellent if one or more recommendations are available. Once these recommendations are applied or dismissed the score will return to Excellent.

Scorecard

In the Scorecard, a score is assigned to a given cluster for each grading category:

• Indexes,
• Statistics,
• Configuration,
• Security,
• Workload.

Note: only the first two (Indexes and Statistics) are available at the moment.

Generally, grades are assigned on an A-F scale.

For Indexes:

• Excellent (A), if no recommendations were generated to tune indexes,
• Good (B), Fair (C) and Poor (D), depending on how many recommendations were generated.

For Statistics:

• Excellent (A), if no recommendations were generated to add statistics,
• Good (B), if at least one recommendation was generated.

Please note that:

• “Fail” (F) is not used for the time being since it is reserved for unhealthy cluster states that require immediate action.
• “Unknown” (U) is used when information to evaluate a cluster is unavailable.

For Indexes, the scores are computed by considering only primary nodes in the case of PSR and write leaders in the case of PGD/AHA. Furthermore, the score is related to the database in the PG cluster that is performing the worst (not an average over all databases created in such a cluster).

When selecting a specific category, text is displayed to help the user understand the relevance of these recommendations, beyond providing more context on how the score was computed.

A radar chart shows the global score of the cluster across all categories.

Recommendations List

Below the scorecard, a list of recommendations is displayed depending on the category selected in the scorecard:

• “Status”, the status of a recommendation (New, Ignored, Applied),
• “Database Name”,
• “Node ID”,
• “Size Increase” (for indexes), estimated index size,
• “Absolute Benefit” (for indexes), the absolute cost reduction that applying this index could produce,
• “Total Cost” (for indexes), the execution time plus plan time for running this query,
• “Cost Reduction” (for indexes), the estimated percentage cost reduction computed as (abs_benefit/total_cost) * 100, which is relative to all the workloads executed in a database,
• “Weight” (for statistics), a rough estimation of the potential benefit of applying such statistic,
• “Recommended Time”, creation time for this recommendation,
• “Query”, text of the query to create an index or a statistic.

With the cog icon, it’s possible to show/hide columns.

CTA buttons in each row let you:

• Mark the recommendation as completed,
• Ignore the recommendation,
• Copy the text of the recommendation.

The filters allow users to restrict data by:

• Database,
• Node,
• Status.

The drop-down menu on the right side allows you to sort rows in the table by column in ascending or descending order.

The search bar on the left side lets you search for a substring in the query text.

Understanding Recommendations

What do the "mark the recommendation as applied/ignored" buttons do?

You should use the 'applied' option to tell Hybrid Manager that you have applied the recommended change. If you mark a recommendation as applied, it will disappear from the list and when the page is next reloaded your score will increase commensurately. If the same recommendation is received again (suggesting the change was not actually applied or has been undone), the recommendation will reappear and the score will drop.

You should use the 'ignored' option to tell Hybrid Manager that you have reviewed the recommended change and decided it is not appropriate to apply it. If you mark a recommendation as ignored, it will disappear from the list and when the page is next reloaded your score will increase commensurately. If the same recommendation is received again it will be ignored and will not reappear.

Where does this data come from?

The Index and Statistics recommendations are sourced from the Query Advisor extension, as such this extension must be enabled in the monitored database.

In future releases, recommendations will be generated from other data sources including historical metrics and Query Diagnostics.