HPE NonStop Database Analyzer – Instant insight on your HPE NonStop SQL database

[vc_row][vc_column][vc_empty_space][penci_text_block block_title_align=”style-title-left” penci_show_desk=”1″ penci_show_tablet=”1″ penci_show_mobile=”1″ css=”.vc_custom_1565899448108{padding-top: 25px !important;padding-right: 25px !important;padding-bottom: 25px !important;padding-left: 25px !important;background-color: #5f7a76 !important;}”]

In January HPE introduced HPE NonStop Database Analyzer (NSDA), a new way to analyze, correlate, and visualize data workload, in real-time. HPE NSDA is a leap forward to optimize HPE NonStop SQL workload while simultaneously serving both transactional and analytical requirements

[/penci_text_block][vc_empty_space][vc_column_text]

Data analytics applied to your query workload

Why understanding your query workload matters
You are a DBA or IT director and you have a well-crafted vision on how to make valuable data available to different business units. Chances are you do a good job at it, but you end up being a victim of your success with many applications accessing the data with conflicting requirements. Typically, it is a combination of transactional and analytical requirements. It not only means simple queries—inserts, updates, and deletes—but also more complex queries with joins involving multiple tables and sometimes, very convoluted predicate clauses where you are not sure if the query even makes sense by just looking at it.

Can you control user queries?
If queries on your database are all programmed from an application, then you have the chance to review those queries and optimize them. This is the case with embedded SQL where queries are hard-coded into the program. However, if users run ad hoc queries on the database, with business intelligence tools like Business Objects or Informatica, chances are these users will come up with all types of queries. No matter what, users will sometimes execute bad queries from a database efficiency or DBA point of view. Not a lot you can do about it unless you have a way to locate and control those queries, manually or automatically to deprioritize them, queue them, or even cancel them.

This typical problem triggered many different approaches in the industry to solve it such as exporting online transaction processing (OLTP) data in dedicated analytical databases or even more recently exporting to NoSQL databases to further solve scalability problems for Big Data. However, this is far from being an ideal solution. Managing multiple copies of the same data has many ramifications such as additional DBs and tools required, security concerns, data synchronization, and compliance. What if the users want real-time data? What if there is a gap between the replicated databases? What if users want to use SQL tools?

HTAP
A more recent resulting trend, however, is to come back to a simpler and more traditional approach. What if we could keep only one copy of the data for both OLTP and analytical purposes? More commonly known as hybrid transactional and analytical processing (HTAP), this approach has all the advantages of not having to maintain multiple copies of the data along with leveraging the large tool ecosystem available today for relational databases. It is an attractive approach considering it can significantly reduce costs as you don’t have to pay additional licenses and maintain multiple DBMS, duplicated storage infrastructure and data integration software.

Workload management
At the same time, with the evolution to new consumption models such as database as a service (DBaaS) and the opportunity to monetize databases services, a more precise mean to quantify database activity is required. As the DBaaS provider, how does one make sure that they deliver the expected throughput to customers or tenants in a multitenancy scenario? As the DBaaS consumer, how does one know if the SLAs are met? There is a new requirement here to not only quantify compute activity and disk space usage but also query workload activity. Query workload becomes the unit by which data monetization is measured.

Agile and fast deployment
A third requirement comes from the evolution in the sphere of application deployment. DevOps and continuous integration concepts have highlighted that deploying an application always hit an obstacle. When the application requires a database task such as creating a new database or evaluating its throughput, the developer depends on the DBA’s availability. It hinders the promises of agile and fast deployment that are expected with the new DevOps and continuous integration models. Developers are not the only new users of DBaaS; data scientists or business groups all want to have better visibility into how their data is being used. A key reality here is that neither developers nor other users have time to learn the specifics of each database implementation. Therefore, a solution in this space should not require using typical DBA tools but rather a more focused implementation with immediately meaningful metrics delivered in a database-agnostic interface that is instantly comprehensible without any training on the underlying database product.

HPE NSDA design

What makes HPE NSDA different from existing tools?
Many tools already take the task of rendering graphically the system activity, including database activity. So, what makes HPE NSDA different? The response lies with multiple factors that we will review here and that will serve as an introduction to some of HPE NSDA unique features.

The query workload, a new measurement unit
While numerous metrics are available, generated by HPE NonStop Measure, a lot of work is left to the end user to interpret the provided counters. Even if rendered graphically, counters are by design describing system level attributes such as disk I/O or how busy a CPU is, but those are not immediately correlated to a given transaction. In the context of database queries, it is possible to measure the overall system impact of all the queries on a system. However, when it comes to look at a particular user or a single query, it becomes a complex work of correlation to extract more granular information. While possible, it is very time consuming to provide how much compute resources a query is using. And it requires a great deal of knowledge and experience of the system tools and database architecture.

The truth is, providing even just a simple summary of how long a query is running, how many I/O it produced, using how much memory and processing cycles becomes an art only accessible to just a few very experienced analysts. Often customers even request the assistance of Hewlett Packard Enterprise to look into sets of data captured because there is so much data to decipher. Getting the information becomes restricted to only exceptional scenarios such as troubleshooting critical performance problems where there is no other choice than spending long hours for investigating just one issue at hand.

Therefore, the first building block of HPE NSDA is to use a new set of metrics that is more oriented toward business level information. One key metric attribute introduced is the query ID. With this new concept, it is now possible to associate resources usage to a given, well defined query. All the attributes you would expect for a query such as how many accessed rows and how long the query took are all accounted for and aggregated for the query. HPE implemented a new infrastructure, namely Workload Management Services (WMS) within HPE NonStop SQL/MX to support the production of the new metadata. The design of WMS is a key factor in producing the metrics, as it would be a showstopper if the data gathering itself has an impact on performance. While the impact cannot be completely removed, WMS was designed so that as little as possible instrumentation is in the execution path of the query. This is particularly important for short running queries.

Repository
From a long experience of working with query workloads and analyzing them, a key information we learned is to be able to provide context. Knowing that a query took 10 seconds to execute in itself is not a very useful information. Maybe it is expected to execute in 50ms, or maybe it is a remarkably well tuned query that is scanning a lot of data in parallel and that could otherwise take 30 minutes to complete. Maybe it takes 10 seconds when you looked at it and longer otherwise. Maybe the system is overloaded with something else that is impacting the query. Once again, here, it becomes an art to analyze this information. A very experienced DBA may have an idea by looking at the query text but it still could be incorrect if not factoring other metrics such as system load at the time or anything that could be unexpectedly affecting the query. Data is only meaningful if provided within a given context.

Therefore, HPE NSDA also implements a repository to store the data captured. Doing so allows to very easily identify what is the expected versus abnormal behavior. One can look at how the query response time has evolved over the last month or compare the query execution for a given user compared to another. The repository includes all the queries (except if you turn off capture of short running queries) allowing you to analyze a problem after the fact and not have to ask the user to re-execute the query. The repository is itself implemented with an analytical database, located outside HPE NonStop SQL/MX environment to once again avoid any impact to the production database performance.

Real-time analytics
Providing real-time analytics in this context means that one does not need to wait for a query to complete to get the statistics. This is extremely important for decision support systems (DSS) queries, which are designed to execute for longer periods of time such as one hour. Examining the query in real time also allows better identification and correlation of information with other system events.

Correlation
As mentioned in the previous paragraph, a key element of HPE NSDA is its ability to provide information that is immediately actionable. To be able to do this, many of the monitored counters are grouped and correlated so that a meaningful information is provided to the end user. They are many places in HPE NSDA where this happens, but to name just a few, you will find, for example, “Top N longer running queries”. With this graph, you can immediately see if they are abnormal queries running on your system. Another is “Top N cpu usage queries”. With this one, you can see if a user is taking too many resources. Correlation is not a manual, tedious, multiple steps process anymore. It is already done for you based on real time data feed and immediately rendered on a graphic for immediate action.

User experience
The overall interface is providing graphical features designed to optimize the user experience further. For example, many graphics include rubber-banding techniques that allow you to define and redefine as needed a slice of time to look at with just a few mouse clicks. Drill-down capability allows you to start from the main page with a view on all your systems and then drills down to a level as granular as one query, again with just a few clicks. You no longer need to search for queries in the EMS log. In a few seconds, you can stop a query that is using too many resources. This feature is likely to highly reduce any downtime that would be workload induced. Dashboards, Gantt, and 3D style graphics complete the solution to make sure that you can analyze information from all possible angles.

Reports
Last but not least is the reporting capability that allows you to either use existing prepackaged reports or create your own. Reporting allows you to tackle requests for a specific metric from a specific business unit or to produce your own reports to discover how much resource a particular business group is using. Reports can include auto-generated graphs.

HPE NSDA 1.0
In summary, with the capabilities described previously, HPE NSDA provides instant and meaningful information about the query workload allowing a more efficient use of system resources. In the context of hybrid processing requirements, along with HPE NonStop SQL/MX mixed workload capabilities, HPE NSDA helps to ensure and demonstrate that the expected quality of service for both OLTP and analytical requirements is met. The business-oriented metrics allows for a wider range of users to gain visibility and control of information that matters to them without requiring specific database knowledge. Dramatic reduction of the time spent for data gathering, correlation, and analysis helps improve agility and speed of execution. Application deployment time is reduced; application outage situations are either quickly identified or prevented. The overall gain in visibility and control challenges the idea that analytics can only be done on a separate copy of the database. The benefits here are significant such as simplifying your IT infrastructure and reducing your costs while maintaining or improving the user experience with real-time data information.

HPE NSDA immediate benefits

• • Detect and identify patterns for correction or optimization
  • Get the real facts right with historical and real-time data
  • Optimize usage of your systems by better balancing resource usage
  • Better identify data ownership and usage per business groups
  • Discover potential revenue opportunities based on data usage
  • Predict and prevent problems

Find more details on NSDA at
URL – Public: https://www.hpe.com/h20195/v2/Getdocument.aspx?docname=a00063463ENW
URL – Mobile https://www.hpe.com/h20195/v2/getmobile.aspx?docname=a00063463ENW[/vc_column_text][/vc_column][/vc_row]