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 the Workload Analyzer (WLA).

WLA 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 WLA Reccomendations

WLA recommendations are available through the database's Overview page.

  1. 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 WLA has generated.
  2. Click the number to view the WLA 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 additional information about tuning recommendations and cost see Analyzing Workloads in the Administrator's Guide and ANALYZE_WORKLOAD in the SQL Reference Manual.

The following image shows a list of WLA tuning recommendations for a database. In the output, WLA 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.

Tip: You can force the WLA task to run immediately by clicking Update Recommendations over the Cost column.

Set and Disable WLA Events

On MC, WLA automatically begins monitoring data one minute after the MC process starts. WLA 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, WLA does nothing until the database is back up.

By default, WLA runs each day at 2 AM. To optimize when WLA uses resources, you can set WLA to run at a different time for any or all databases that MC monitors. You can also set MC to never run WLA automatically.

  1. On the Home page, click MC Settings.
  2. Click the Monitoring tab.
  3. Under the Workload Analyzer Assistant section of the Monitoring page, select your time zone.
  4. Select the radio button for one of the options: 
    • All Databases: Select a time from the list. WLA 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, WLA will run at that time on the database you selected.
    • Do Not Run Workload Analyzer On Any Database: MC will never run WLA automatically on any database it monitors.
  5. Click Apply at the top right of the page.

For additional information about tuning recommendations and their triggering event, see Understanding WLA Triggering Conditions.

See Also

Analyzing Workloads

Getting Tuning Recommendations