Classifying Workloads for Analysis with Workload Views

You can use Workload XM to analyze your data warehouse workloads at the cluster level, but using the Workload View feature, you can analyze workloads with much finer granularity. For example, you can analyze how queries that access a particular database or that use a specific resource pool are performing against SLAs. Or you can examine how all the queries are performing that a specific user sends to your cluster.

Use workload views that Workload XM automatically generates, or manually define your own workload views to drill down on specific criteria:

Using Auto-Generated Workload Views

To immediately start analyzing your workloads with the Workload View feature, use the following steps to use auto-generated views:

  1. Log in to the Workload XM console at: wxm.cloudera.com, and in Search, type the name of the cluster that is running the workloads you want to analyze.
  2. Under Data Warehouse in the left menu, select Workloads:



  3. Click Define New and choose Select recommended views from the drop-down menu:



  4. Review the Criteria that are used to create the workload views, select the auto-generated workload views you want to use, and then click Add Selected:



    After clicking Add Selected, the workloads you selected are saved and can be viewed on the Data Warehouse Workloads page.

  5. Click Workloads in the menu on the left, and on the Data Warehouse Workload page scroll to locate the auto-generated workload view you just added. Then click that workload to view its details:



    See Step 8 in the following procedure for tips on how you can use the workload view details for analysis.

Using Manually-Defined Workload Views

Use the following steps to manually define your own Workload Views, or if you want to see this feature in action, view the following video:

Video: Classifying Workloads to Gain Insights

For better video quality, click YouTube in the lower right corner of the video player to watch this video on YouTube.com.

  1. Log in to the Workload XM console at: wxm.cloudera.com, and in Search, type the name of the cluster that is running the workloads you want to analyze.
  2. In the left menu under Data Warehouse, select Summary, and click the arrow next to the date range in the upper right corner of the page to select the date range for the workloads you want to analyze:



  3. Under Data Warehouse in the left menu, select Workloads:



  4. With Workloads selected in the left menu, click Define Workload to access the Define Data Warehouse Workload View page where you can define a set of criteria that enables you to analyze a specific set of queries. For example, you can review all failed queries using a specific database that are subject to a fifteen second SLA:



    The above workload view definition, which is named applog_db_under_15s, monitors queries that use the applog database. When 100 percent of these queries miss a 15s SLA, that is total query execution time exceeds 15 seconds, then the workload is flagged with a failing status.

  5. After specifying your criteria, click Preview and a summary of the queries matching this criteria displays:



  6. If you are satisfied with the results of the criteria you specified, click Save in the lower right hand corner of the page.

  7. After saving the workload view, you are returned to the Data Warehouse Workloads page and your workload view appears in the list of other workload views on the page. Click the up or the down arrow next to the Workload column heading and the workload view names are sorted alphabetically so it is easier to find the view you just created. Click the workload view name to view its details:



  8. The detail page for your workload view contains several graphs and tabs you can view to analyze how this group of queries is meeting its SLA. For example:

    • In the Trend region, you can view the counts of executing queries By Status or By Statement Type:



      Click the number under Total Queries, Failed Queries, and Query Active Time to view further details.

    • Also in the Trend region, you can view the number of queries executing concurrently when you click the Concurrency tab:



      In the above example, it shows that the maximum concurrency for this workload view is 2. This means that for the queries in this workload view, a maximum of only two queries access the same data at the same time during the specified time period. At the bottom of the graph, it shows how concurrency fluctuates over the date range specified for the workload view.

    • In the Outliers region of the page, you can view the Slowest queries and the Most Complex queries:



    • Also in the Outliers region, you can view the users who are executing the queries in your workload view and the pools and databases being used. You can click a user, pool, or database name to view further details about the specific queries being executed:



    • You can also view the different statement types contained in the workload view, the active time of the queries, and you can drill down to view more granular details on each query.