Outdated Version

You are viewing an older version of this section. View current production version.

Management View Reference

This topic contains reference information about each workload profiling management view.

Concepts and Terminology

Before querying these management views, it’s important to understand the concepts behind the terminology used in each view’s schema.

Task

MemSQL is instrumented to divide its execution time into tasks. Workload profiling management views in the information_schema database report the resource usage of sets of tasks. The views differ mainly in that they report on different sets over different intervals of time.

Task Examples

A single query’s execution is instrumented with a task per partition, as well as a single task on the relevant aggregator.
A single garbage collection pass is instrumented with a single task.
A single replication pass against a database partition is instrumented with a single task.
Tasks may or may not be successful. For example, a query task which commits a transaction is successful, but a query task which encounters rollback is not.

Activity

An activity is a set of identical tasks.

All tasks associated with the same node ID, database, partition, and name comprise the same activity. The tasks in an activity can be considered to share or be instances of an activity.

In practice, tasks share an activity when they instrument multiple repetitions of the same recurring work.

Every activity has a type of either Query, Database, or System. Tasks in a database activity are those which can be associated with an individual database, but not with any individual query. Tasks in a system activity can be associated with neither.

Every activity has a name and an aggregator activity name. We say that an activity is high-level if its aggregator activity name is either NULL or equal to its name.

If an activity is not high-level, then its aggregator activity name is the name of the high-level activity to which it contributes.

In practice, we report aggregator activity name in order to link per-partition query activities with the unique aggregator activity associated with their query. The actual name of these activities may not be the same on all nodes.

Activity Examples

All tasks instrumenting the same query against the same partition share an activity, which has Query type. Tasks instrumenting the query on different partitions have different activities.
All garbage collection pass tasks share an activity, which has System type.
All replication tasks against the same database partition share an activity, which has Database type.
All activities associated with a query have the same aggregator activity name, which is the name of the unique high-level activity associated with the query on the aggregator node.

mv_activities

The mv_activities view in the information_schema database is a high-level summary profile of all tasks which ran recently on any node in the cluster.

It holds a row per high-level activity H. Each row describes the set of tasks which have run over a recent interval of time and which are either instances of the high level activity H, or else instances of an activity L whose aggregator activity name is the name of H. It reports the sum of the profiling statistics of each task in the set, as collected over the recent interval.

The effect of grouping the instances of multiple activities together is mainly to group all tasks associated with a query, across all partitions, into a single row.

mv_activities determines recent resource use by computing the change in mv_activities_cumulative over an interval of time. This interval is controlled by the value of the activities_delta_sleep_s session variable.

mv_activities may only be queried while connected to an aggregator node.

We recommend using this view to begin a performance investigation, as it is the most concise.

However, we recommend against the use of mv_activities to compute the average latency or resource usage of a query. This is because its statistics include the latency and resource usage of currently running tasks, which will skew attempted average calculations. We recommend mv_finished_tasks for this purpose instead.

Column name	Description
`activity_type`	The type of the high-level activity.
`activity_name`	The name of the high-level activity. This column is often human-readable, but does not include the full query text for query tasks. Join with `mv_queries` for the query text.
`database_name`	The name of the database associated with the activity, or `NULL` if none could be assigned.
`run_count`	The number of instances which were running at the end of the interval.
`success_count`	The number of instances which completed successfully during the interval.
`failure_count`	The number of instances which completed unsuccessfully during the interval.
SIMPLIFIED STATISTICS	This view contains all simplified statistics columns.

mv_activities_extended

The mv_activities_extended view in the information_schema database is a detailed profile of all tasks which ran recently on any node in the cluster.

It holds a row per activity. Each row describes the instances of its activity which ran during a recent interval of time. It reports the sum of the profiling statistics of each task in the set, as collected over the recent interval.

mv_activities determines recent resource use by computing the change in mv_activities_extended_cumulative over an interval of time. This interval is controlled by the value of the activities_delta_sleep_s session variable.

mv_activities_extended may only be queried while connected to an aggregator node.

We recommend using this view to get fine-grained understanding of the system behavior described in mv_activities. The main distinctions between mv_activities and mv_activities_extended are:

For most queries, mv_activities_extended will report the resource usage of the query with at least a row per partition. mv_activities aggregates these into a single row per query.
mv_activities reports simplified statistics, while mv_activities_extended reports extended statistics.

However, we recommend against the use of mv_activities_extended to compute the average latency or resource usage of a query. This is because its statistics include the latency and resource usage of currently running tasks, which will skew attempted average calculations. We recommend mv_finished_tasks for this purpose instead.

Column name	Description
`node_id`	An ID equal to `id` in the row of `mv_nodes` describing the node on which the instances ran.
`activity_type`	The type of the activity.
`activity_name`	The name of the activity. This column is often human-readable, but does not include the full query text for query tasks. Join with `mv_queries` for the query text.
`aggregator_activity_name`	The aggregator activity name of the activity, which may be `NULL`.
`database_name`	The name of the database associated with the activity, or `NULL` if none could be assigned.
`partition_id`	The unique ID of the database partition associated with the activity, or `NULL` if none could be assigned.
`run_count`	The number of instances which were running at the end of the interval.
`success_count`	The number of instances which completed successfully during the interval.
`failure_count`	The number of instances which completed unsuccessfully during the interval.
EXTENDED STATISTICS	This view contains all extended statistics columns.

mv_activities_cumulative

The mv_activities_cumulative view in the information_schema database is a high-level summary profile of completed and currently running tasks on all nodes of the cluster.

It holds a row per high-level activity H. Each row describes tasks which have completed or are currently running, and which are either instances of the high level activity H, or else instances of an activity L whose aggregator activity name is the name of H. It reports the sum of the profiling statistics of each task in the set, as collected over its lifetime.

The effect of grouping the instances of multiple activities together is mainly to group all tasks associated with a query, across all partitions, into a single row.

mv_activities_cumulative may only be queried while connected to an aggregator node.

We recommend against extensive use of this table. It doesn’t accurately describe past system behavior because it doesn’t include all completed instances. This is because historical task statistics may be discarded by MemSQL at unspecified times.

Column name	Description
`activity_type`	The `type` of the activities.
`activity_name`	An ID shared by all instances of the high level activity. This ID is often human-readable, but does not include the query text for query tasks. See `mv_queries` for the raw query text.
`database_name`	The name of the database associated with the activity, or `NULL` if none could be assigned.
`last_finished_timestamp`	The timestamp of the most recent completion of an instance of this activity.
`run_count`	The number of running instances of the activity.
`success_count`	The number of successfully completed instances of the activity.
`failure_count`	The number of unsuccessfully completed instances of the activity.
SIMPLIFIED STATISTICS	This view contains all simplified statistics columns.

mv_activities_extended_cumulative

The mv_activities_extended_cumulative view in the information_schema database is a detailed profile of completed and currently running tasks on all nodes of the cluster.

It holds a row per activity. Each row describes completed and currently running instances of its activity. It reports the sum of the profiling statistics of each instance, as collected over its lifetime.

mv_activities_extended_cumulative may only be queried while connected to an aggregator node.

Column name	Description
`node_id`	An ID equal to `id` in the row of `mv_nodes` describing the node on which the activity’s instances have run.
`activity_type`	The `type` of the activity.
`activity_name`	An ID shared by all instances of the activity. This ID is often human-readable, but does not include the query text for query tasks. See `mv_queries`.
`aggregator_activity_name`	The `aggregator_activity_name` of the activity.
`database_name`	The name of the database associated with the activity, or `NULL` if none could be assigned.
`partition_id`	The unique ID of the database partition associated with the activity, or `NULL` if none could be assigned.
`last_finished_timestamp`	The timestamp of the most recent completion of an instance of this activity.
`run_count`	The number of running instances of this activity.
`success_count`	The number of successfully completed instances of this activity.
`failure_count`	The number of unsuccessfully completed instances of this activity.
EXTENDED_STATISTICS	This view contains all extended statistics columns.

mv_finished_tasks

The mv_finished_tasks view in the information_schema database is a detailed profile of completed tasks on all nodes of the cluster.

It holds a row per activity. Each row describes completed instances of its activity. It reports the sum of the profiling statistics of each instance, as collected over its lifetime.

mv_finished_tasks may only be queried while connected to an aggregator node.

We recommend against extensive use of this table. It doesn’t accurately describe past system behavior because it doesn’t include all completed instances. It will also hide the performance impact of long-running tasks which have yet to complete.

However, we do recommend using this table to estimate the average latency or resource usage of a query. This is because all other statistics tables include currently running tasks in their profiles, skewing attempted average calculations.

Column name	Description
`node_id`	An ID equal to `id` in the row of `mv_nodes` describing the node on which the activity’s instances ran.
`activity_type`	The `type` of the activity.
`activity_name`	An ID shared by all instances of the activity. This ID is often human-readable, but does not include the query text for query tasks. Join with `mv_queries` for the query text.
`aggregator_activity_name`	The `aggregator_activity_name` of the activity.
`database_name`	The name of the database associated with the activity, or `NULL` if none could be assigned.
`partition_id`	The unique ID of the database partition associated with the activity, or `NULL` if none could be assigned.
`last_finished_timestamp`	The timestamp of the most recent completion of an instance of the activity.
`success_count`	The number of successfully completed instances of this activity.
`failure_count`	The number of unsuccessfully completed instances of this activity.
EXTENDED STATISTICS	This view contains all extended statistics columns.

mv_tasks

The mv_tasks view in the information_schema database is a detailed profile of all currently running tasks on all nodes of the cluster.

It holds a row per running task. Each row reports the profiling statistics of the task, as collected between the time it began and the current time.

mv_tasks may only be queried while connected to an aggregator node.

We recommend against extensive use of this table. It will hide the performance impact of short-lived but frequent tasks, and exaggerate the impact of long-lived tasks.

Column name	Description
`node_id`	An ID equal to `id` in the row of `mv_nodes` describing the node on which the task is running.
`activity_type`	The `type` of the task’s activity.
`activity_name`	An ID shared by all instances of the task’s activity. This ID is often human-readable, but does not include the query text for query tasks.
`aggregator_activity_name`	The `aggregator_activity_name` of the task’s activity.
`database_name`	The name of the database associated with the task’s activity, or `NULL` if none could be assigned.
`partition_id`	The unique ID of the database partition associated with the task’s activity, or `NULL` if none could be assigned.
`raw_task_name`	The full name associated with the task, or `NULL` if none could be assigned. In particular, this will hold the raw query text for query tasks. It differs from `query_text` in `mv_nodes` because it is fully parameterized. Note that this column is only available in mv_tasks.
EXTENDED STATISTICS	This view contains all extended statistics columns.

mv_nodes

The mv_nodes view in the information_schema database describes all MemSQL nodes in the cluster. It holds one row per node.

mv_nodes may only be queried while connected to an aggregator node.

Column name	Description
`id`	The ID of the node. This ID matches the `node_id` reported for tasks running on the node.
`ip_addr`	The IP address of the node.
`port`	The port on which the node is listening.
`type`	`MA`, `Leaf`, or `CA`.
`state`	`Online` or `Offline.`
`availability_group`	The availability group of the node.
`num_cpus`	The number of logical CPU cores on which the node may execute.
`memory_used_mb`	The value of `total_server_memory` on the node.
`max_memory_mb`	Maximum allowed memory for node

mv_queries

The mv_queries view in the information_schema database describes queries seen by MemSQL. It holds a row for each query sent to the cluster by a user, as well as rows for artificial queries sent between nodes according to internal MemSQL logic.

Its purpose is to map the activity_name of query activities to their associated de-parameterized query text.

mv_queries may only be queried while connected to an aggregator node.

Column name	Description
`activity_name`	The ID associated with the query. This will be the `activity_name` of all query tasks associated with the query.
`query_text`	The de-parameterized text of the query.

Was this Article Helpful?

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.