Starting in version 5.8, MemSQL (and now SingleStore DB) automatically exposes comprehensive metrics about cluster performance through a set of management views found in the information_schema database. At a high level, these views provide insight into workload bottlenecks, resource usage, and overall cluster performance. A database administrator can use this data to assess the health of the cluster, predict resource constraints, and diagnose issues.

Background

One of the core responsibilities of a database administrator is to monitor the performance of a database, and as necessary, take preventative or reactive steps to maintain its health. Complex workloads on a clustered database can make it especially difficult to prevent or fix problems when they arise. Third-party and native operating system tooling can help to diagnose issues, but some metrics are only available within the database itself.

To help alleviate these difficulties, SingleStore DB gathers statistics about the entire cluster, including all queries across all nodes, and exposes them in logical tables (views). These views contain data that can be used to help answer common questions, such as:

• Which queries are using the highest proportion of system resources?
• Is a query running, or is it waiting for available resources?
• Which system resources are causing bottlenecks?

Using these views in conjunction with built-in commands such as PROFILE, an administrator can better diagnose the root cause of pathological query performance, and determine which workload or resource improvements are necessary.

Enable Advanced Counters

By default, the workload profiling counters are not enabled. You must enable the advanced counters by setting the read_advanced_counters global variable, which is also discussed in the Advanced Statistics section of the Management View Statistics Reference topic. For more information on how to set engine variables, see the engine variables overview.

memsql> SET GLOBAL read_advanced_counters = ON;
Query OK, 0 rows affected (0.00 sec)

Concepts

Each management view – and the type of data stored in it – is intended for performance analysis methodologies which seek to accomplish the following goals:

• Finding high-latency queries
• Finding the bottlenecks causing high-latency queries
• Gathering statistics about all aspects of cluster performance to find issues beyond query execution

Management views make it easier to achieve these goals by exposing the appropriate performance counters to measure each relevant attribute of the database, namely:

• Node-level statistics, including what queries are running on both aggregators and leaves, or which background tasks are consuming resources
• Database-level and partition-level statistics, including the specific database partition(s) associated with a query
• Query-level statistics, including latency, throughput, saturation, and error metrics

Together, these statistics provide insight into both high-level and low-level activities on the cluster. To learn more about the concepts and data exposed in each management view, see the Management View Reference topic.

Next Steps

• To learn about common scenarios and use cases for workload profiling, see the Workload Profiling Scenarios topic.
• To understand each management view and workload profiling concepts in greater detail, see the Management View Reference and Management View Statistics Reference topics.

Was this Article Helpful?

Thanks For Your Feedback!