Getting Tuning Recommendations in MC
If queries perform sub-optimally, you can get tuning recommendations for them, as well as for hints about optimizing database objects, by using Workload Analyzer.
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.
View Workload Analyzer Reccomendations
Workload Analyzer recommendations are available through the database's Overview page.
- On the right side of the Overview page in the Quick Stats bar, view the Workload Analyzer module. This module alerts you to the number of recommendations Workload Analyzer has generated.
-
Click the number to view Workload Analyzer recommendations on the Database Designer page. The Workload Analyzer Results dialog displays tuning recommendations and the resource cost of running each command (Low, Medium, or High).
For more information about tuning recommendations and costs, see Analyzing Workloads in the Administrator's Guide andANALYZE_WORKLOADin the SQL Reference Manual.
The following image shows a list of Workload Analyzer tuning recommendations for a database. In the output, Workload Analyzer advises that you run the Database Designer on several tables and provides the cost estimate of running these operations.
Note that the cost of running Database Designer is high. When you see a high cost, you might want to run the recommended tuning action after hours.
MC displays fifteen recommendations per page and includes a message at bottom left, which lets you know where you are within the total number of recommendations; for example, "Showing n-nn of nn entries." Depending on the total number of recommendations, you can move forward and backward through them by clicking the arrows at bottom right.
You can force the Workload Analyzer task to run immediately by clicking Update Recommendations over the Cost column.
Set and Disable Workload Analyzer Events
On MC, Workload Analyzer automatically begins monitoring data one minute after the MC process starts. Workload Analyzer then runs once per day, or immediately after you import a database to MC. It continually gathers data in the background as long as the database is running. If you haven't created a database yet, or if the database is down, Workload Analyzer does nothing until the database is back up.
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 MC monitors. You can also set MC to never run Workload Analyzer automatically.
- 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.