Using NSDA to understand query workloads

[vc_row][vc_column][vc_empty_space][vc_column_text]

Introduction to NSDA

We are all aware of the inherent value of customer data within the databases that support our applications. This data identifies customers, accounts, transactional interactions and other patterns that are important for understanding customer behaviors and habits – all of which are useful for improving business processes and customer satisfaction. But what about the value of data about the data processing workloads? Is there value in understanding the processing workloads of our customers? What could we do with workload information if we had the ability to easily organize workload data into meaningful categories and groups, and view the dynamics of the processing patterns over time?

NSDA – NonStop Database Analyzer – is a new product from HPE NonStop. Its purpose is to capture information about query workloads, system performance, heath and other metrics, and store that data in a repository for both real-time and historical analysis. NSDA provides browser-based graphical views of workload activity and supplies many built-in reports, with the ability to develop and store custom reports. With NSDA, one can view historical workloads over time, easily zoom into the details of specific periods and analyze query workload performance. Or, simply view query workloads in real-time.

NSDA is designed specifically for SQL/MX query workloads that access either SQL/MX or SQL/MP tables through the MXCS (MX Connectivity Services) subsystem. While MEASURE provides information about query workloads through specific entities, NSDA treats queries as a distinct, tangible workload, and provides intuitive, easy-to-use tools to view query workloads on-demand. This means one can view the detailed workload metrics for an individual query, or aggregate the activity to higher levels of reporting, grouping the results along any dimension. This flexibility allows one to “slice and dice” query workload data and customize the results to meet reporting requirements of any type.

SQL query plans are often used to analyze database access. However, one must take care to ensure all environment controls are properly set, otherwise the plan produced may not represent the one used by the query under investigation. NSDA eliminates these complexities by automatically storing the query plan for each query executed, allowing one to access and view a query plan simply with the click of a button. This eliminates a source of confusion, and radically accelerates and simplifies query plan analysis.

For customers with significant query workloads initiated from ODBC and/or JDBC remote clients, NSDA provides the visibility into those queries workloads (and other system metrics) in a manner never before available on a NonStop system. It is a “must-have” solution to properly manage an active, dynamic query workload environment.

What kind of data is captured by NSDA?

Although the primary focus is on query workload data, NSDA captures several categories of data important for proper analysis and system management. Furthermore, NSDA insures data from the various categories remains correctly correlated as a user navigates across time and through different levels of granularity.

The following list shows the basic categories of data available in NSDA:

• Query workload details
  • These include query counts and elapsed time, and detailed query performance metrics similar to those provided by MEASURE

• Query data access plan details
  • These are the actual explain plans for the queries executed, represented both in text and graphical forms

• System resource utilization
  • The quantification of basic system resources used, such as CPU, disk, and memory, depicting both average consumption and skew

• System and subsystem health metrics
  • These provide information about the health of the subcomponents of the system

Dimensions and metrics

Query execution is always associated with multiple dimensions and NSDA captures this information along with consumption metrics. Dimensions tend to be non-computational data attributes and include items such as:

• • User name, client application, remote client machine, MXCS service and data source names, network IP address, and more.

Dimensions are the attributes over which query workloads are analyzed. They are what allows the metric data to be “sliced and diced” during analysis.

In addition to those dimensions listed above, NSDA 2.0 includes two new dimensions – tenant and query session – over which analysis can be done.

A database tenant is a concept of DBS (SQL/MX Database Services), which is an independent database environment allocated from system resources, securely isolated from other database tenants on the same system. A tenant may represent an organizational department or a collection of users accessing unique applications and databases, similar to what is done in cloud processing. With tenants exposed as a dimension, customers now have the ability to gain insight into the workload and resource consumption characteristics for those users, just as they can for any other dimension.

The session dimension allows one to analyze all queries executed within a session. This capability is useful when diagnosing performance issues and for understanding query usage patterns of users.

Metrics are the numerically-based values of interest – the objects of analysis. Metrics tend to be computational and include items such as:

• Query CPU and elapsed time, number of disk I/Os, memory usage, the count of inter-process messages, the number of database rows accessed and used, lock-related information, etc.

Metrics quantify the activity being done by a query and are often aggregated to show workload usage across combinations of dimensions.

NSDA views of data

The data captured within NSDA can be represented in various ways for maximum flexibility. For example, after logging into the system, the user is taken to a landing page (Domain Dashboard) showing a high-level view of all NonStop systems under NSDA management, with basic charts depicting the workloads and system resource usage of each system over time. This provides the user with an overview of system usage, correlated to one another and presented along a timeline. Like nearly all NSDA pages, the landing page makes use of a “heat map” – which is a concise, eye-catching, color-coded representation of various health metrics on the timeline – visually informing the user to the possibility of any issues with resources or subsystems.

From this page the user may navigate to additional pages to obtain greater detail for a selected system. The Instance Dashboard page provides charts depicting the types of workloads correlated with the consumption of system resources. A series of “Top-5” charts shows the top five consuming workloads ordered by a specific metric, such as elapsed time, rows accessed, or disk I/Os.

These are active charts, depicting both the historical trend as well as the current activity. Each page contains mechanisms to control the historical time range and refresh rate of data. For example, the time range of activity can be easily changed by dragging the mouse across the heat map to isolate a period of time. All of the charts on the page are then automatically adjusted to synchronize with the new time range.

Hovering over a chart with the mouse pointer causes “callouts” to appear on the page. These callouts contain detailed performance metrics for the specific workload and point in time. As the mouse pointer is moved across a chart red vertical lines appear on all charts to synchronization the corresponding time for the other metrics. These are some of the simple ways to quickly gain an understanding of the most significant workloads on the system.

The Workloads page is where workload details are shown for the queries, in the form of a list (similar to a spreadsheet format). Selection criteria may be specified in multiple ways. Filters control the status of the workloads displayed (executing, completed, etc.) and the selection of certain dimensions, such as the user-name, data source, time range, etc. The resulting data is a list of query workloads meeting that initial criteria. Both dimension and metric data are displayed, and the results may be further refined by including search criteria in search fields below each column. The data in each column may be sorted, either individually or in groups. Columns may be dragged and moved around or horizontally sized for convenience. These types of controls allow the user to quickly focus on the most interesting and pertinent query workloads under investigation. The results may also be downloaded into a local file in CSV format for supplemental processing – for example in Microsoft Excel.

Each row in the workload results contains a query name field which is also a hyperlink to the Workload Details page. This page contains all of the details available about the specific query selected. There are over 80 dimension and metric fields for each query organized into four main categories:

• Query details, which are the complete set of dimension attributes for the query
• Compile-time metrics, which are the metrics produced during query compile time
• Run-time metrics, which are the metrics produced during query execution time
• Query text, which is the full query text as submitted by the user

Additionally, the run-time metrics are refreshed periodically in real time (based on the refresh rate setting) so a user can observe the change in metric values as the query continues executing. Values that change are color-coded for easier identification.

It is from this page the user may elect to view the query explain plan. It is represented in a hierarchical graphical tree, and is sizable to adjust for query complexity and viewing screen size. Hovering the mouse pointer over any of the plan steps produces a callout showing the details for that step. Below the query plan graphic are the full text-based query plan details.

For the DBA or Performance Analyst investigating query workloads, data access paths, or performance issues, the above pages will provide most of the information required to satisfy the objectives – with speed and ease of use unprecedented prior to the introduction of NSDA.

Additional NSDA pages provide extended information and include: a Health page showing the status and health of various subsystems; a Query Whiteboard page allowing the user to customize or develop their own reports against the data; a Gantt Chart page displaying the graphical representation of query elapsed time history; and several new pages in NSDA 2.0 (including a Sessions page and various Alerts pages).

How can NSDA-derived data be used?

The historical workload, health, and other data managed by NSDA provides a wealth of information about customer query workloads. The dimensional data provides the insight into who or what is using the system resources, while the metric data quantifies that usage. By viewing data along certain dimensions of interest and at certain levels of metric aggregation one can gain great insight into workload patterns that are useful to the business, and in some cases, which can be monetized. Let’s consider a few examples.

Performance analysis, tuning, etc.

It should be clear from the above descriptions that NSDA accelerates the investigative process to address performance-related issues. Searching for a rouge query is now simplified given the insight into workloads which NSDA provides. One can easily sift through the noise to find queries that are excessive consumers of resources. The “Top-n” query list for any resource or metric can be quickly and easily constructed. The full query text is available as part of the workload details, and the corresponding query plan can be viewed at will. An analysis of the query, query plan, and database design can begin immediately. The analyst will know the identity of the user submitting the query, the remote client system from which it was submitted, the MXCS service and data source through which it arrived on the system, and more. Furthermore, a rogue query can be observed in real time, and then cancelled, if desired. All this is viable – rapidly and effortlessly – using NSDA.

Such performance investigations can be performed in real-time to address active issues, or against historical data as part of a more comprehensive evaluation of query efficiency and performance.

Capacity planning and resource sizing

Historical workload patterns can be used in conjunction with future workload projections for input into capacity planning, forecasting, and system sizing projects. Reports can be tailored to represent resource consumption along any dimension. For example, the average queries per user per-hour, or CPU-time consumed per tenant, or daily query response time distributions per tenant, can easily be calculated with NSDA data.

Usage patterns can be processed to reveal the occurrence and duration of various workloads cycles, including peaks, lows, average, percentiles, etc. This can be used for resource planning in preparation for new peak periods.

Workload quantification and SLA compliance

Customers having SLA (service level agreements) with their users need a way to quantify workloads and query performance, at various time periods, and along certain dimensions (such as user groups and database tenants). These agreements can be complex, and often express response time requirements as a percentile of queries attaining the response time objective, over different time periods, and detailed by users or groups. The SLAs may be different for each time range and group of users. NSDA captures the data, and provides the reporting capabilities to satisfy compliance for these types of requirements.

Billing and chargeback

NonStop customers providing services to their own users or outside clients can use NSDA for the basis of developing billing and chargeback systems, and optionally monetize the services provided. Costing algorithms can be developed based on system resource usage, and the scope of billing charges based on a variety of dimensions. NSDA reports can aggregate resource usage metrics by dimensions such as user groups or tenants across any time period, as needed. Reports can be parameterized to allow dynamic changes to the algorithm over time.

Summary

The data captured by NSDA provides a wealth of information about query workloads executing on customer systems. There is value in mining that data. In addition to capturing the data, NSDA provides the ability to analyze its content. NSDA furnishes a variety of reports that are adaptable to unique customer requirements, and provides a Query Whiteboard in which customers develop and store their own reports, complete with graphing capabilities. Customers can tailor their reports to any purpose or requirement.

Reports are commonly used for query performance analysis and tuning; workload tracking, forecasting, capacity planning, and sizing; and in certain cases, as the basis for service billing and chargeback systems.

Never before on a NonStop system has it been easier to obtain this type of information and useful insights about dynamic query workloads.

Click here for more information about NSDA.[/vc_column_text][vc_empty_space][/vc_column][/vc_row]