Working with workload analyzer recommendations in MC

If queries perform sub-optimally, use Workload Analyzer to get tuning recommendations and hints about optimizing database objects.

Workload Analyzer is a Vertica utility that analyzes system information in Vertica system tables. It then returns a set of tuning recommendations based on statistics, system and data collector events, and database/table/projection design. You can use these recommendations to tune query performance.

Configuring the workload analyzer execution time

By default, Workload Analyzer runs each day at 2 AM. To optimize when Workload Analyzer uses resources, you can set Workload Analyzer to run at a different time for any or all databases that Management Console monitors. Alternately, you can set Management Console to never run Workload Analyzer automatically.

Note

Workload Analyzer automatically begins monitoring data one minute after the Management Console process starts. Workload Analyzer then runs once per day, or immediately after you import a database to Management Console. It continually gathers data in the background as long as the database is running. If you have not yet created a database, or if the database is down, Workload Analyzer does nothing until the database is back up.

On the Home page, click MC Settings.
Click the Monitoring tab.
Under the Workload Analyzer Assistant section of the Monitoring page, select your time zone.
Select the radio button for one of the options:
- All Databases: Select a time from the list. Workload Analyzer will run at that time on all databases that MC monitors.
- Specific Database at Specific Time: Select a database and a time from the list. At the time you specify, Workload Analyzer will run at that time on the database you selected.
- Do Not Run Workload Analyzer On Any Database: MC will never run Workload Analyzer automatically on any database it monitors.
Click Apply at the top right of the page.

For additional information about tuning recommendations and their triggering event, see Workload analyzer recommendations.

View workload analyzer recommendations

Workload Analyzer recommendations are available from the Quick Stats sidebar on the right of the database's Overview page. The Workload Analyzer module displays the number of tuning recommendations that the Workload Analyzer has generated.

To view the Workload Analyzer Results on the Database Designer page, click the number in the Workload Analyzer module.

The Workload Analyzer Results window allows you to view details about and perform actions using current and processed recommendations.

Click the Current Recommendations radio button to display available Workload Analyzer recommendations. When ANALYZE_STATISTICS is returned as a tuning recommendation, select the check mark to the left of the row and click Run Selected Recommendations to execute the recommendation automatically.

Click the Processed Recommendations radio button to display the Workload Analyzer recommendations that you previously executed. To remove a recommendation from the list, click the check mark to the left of the row and click Clear, located above the Close button in the bottom-right of the window. To expand or hide the processed recommendation's execution history, click the plus or minus sign to the left of the row.

You can force the Workload Analyzer task to run immediately by clicking Update Recommendations, located above the Status column.

The total recommendations and the number of recommendations currently selected to run are displayed under the recommendations grid. Use the settings under the grid to view more recommendations per page or to cycle through the recommendations that do not fit on the page.

The following columns are used to describe recommendations:

Tuning Description — Describes the Workload Analyzer recommendation.
Tuning Cost — Resource cost of running each command (LOW, MEDIUM, or HIGH).

Tip
When the tuning cost is HIGH, consider running the recommended tuning during off-peak load times.
Tuning Command — SQL command used to execute the recommendation.
Last Executed On — Date that the recommendation was last run. In MM/DD/YYYY format.
Status — Describes the execution stage of a tuning recommendation ran from Workload Analyzer Results.

For more information about tuning recommendations, see Getting tuning recommendations and ANALYZE_WORKLOAD.

Running workload analyzer recommendations to optimize a query

When the Workload Analyzer recommends that you run ANALYZE_STATISTICS to optimize a query, you can run the recommendation automatically from Workload Analyzer Results.

Record the data source and execution time for a query that is running sub-optimally.
1. Click the Query Execution tab at the bottom.
2. Use the Query Runner to execute a query that you want to optimize.
3. Record the database table or tables in the query's FROM clause, and record the Execution time, located under the Query Results table.
Click the Overview tab at the bottom of the window.
On the Overview page, click the number in the Workload Analyzer box on the right.

Workload Analyzer Results opens.
To filter the recommendations, enter the sub-optimal query's database table or tables in the field at the top of the Tuning Description column.
Select one or more ANALYZE_STATISTICS recommendations by clicking the check mark to the left of the row. To select all of the filtered ANALYZE_STATISTICS recommendations, click the check mark to the left of the Tuning Description column header.
Click Run Selected Recommendations, located in the bottom-right of the window.
This process might take several minutes.
After the tuning recommendations are completed, click the Processed Recommendations radio button at the top of the window.
The previously executed recommendations are displayed.
Locate any recommendations that you just executed, and verify that the Status column says COMPLETED.
Verify that the query was optimized.
1. Click the Query Execution tab at the bottom of the Management Console.
2. Execute the query that was performing sub-optimally. Note the Execution time under the query results to verify the performance increase.