Information Schema View List

On this page

Here is an alphabetical list of all the information_schema views that SingleStore supports. Data retention value listed as "N/A" means the view returns applicable data in the current state of the cluster.

There are some additional, unused tables that are included purely for backward compatibility with MySQL, listed here.

Table Name

Table Description

Data Retention

ADVANCED_HISTOGRAMS

This view contains information about histograms that have been collected by automatic statistics or using ANALYZE.

N/A

AGGREGATE_FUNCTIONS

This view contains information about user defined aggregate functions (UDAF).

N/A

AGGREGATORS

This view contains information about aggregators. It is useful for understanding the number and attributes of the aggregators for a given cluster.

N/A

AVAILABILITY_GROUPS

This view contains information about availability groups.

N/A

CHARACTER_SETS

This view stores information about available character sets.

N/A

COLLATION_CHARACTER_SET_APPLICABILITY

This view stores information about the available collations for each character set.

N/A

COLLATIONS

This view stores information about collations for each character set.

N/A

COLUMN_STATISTICS

This view contains information about each column in a given cluster. Each row displays information about a given column on a given database partition.

N/A

COLUMNAR_SEGMENTS

This view stores information about each columnar_segment (column segment).

N/A

COLUMNS

This view stores information about each column.

N/A

CURRENT_USER_GROUPS

This view contains information about the group(s) the current user belongs to.

N/A

CURRENT_USER_ROLES

This view contains information about the role(s) of the current user.

N/A

DISTRIBUTED_DATABASES

This view contains information about databases.

N/A

DISTRIBUTED_DATABASES_ON_SITES

This view contains information about how databases are distributed across clusters.

N/A

DISTRIBUTED_PARTITIONS

This view contains information about partitions.

N/A

GROUP_PRIVILEGES

This view contains information about privileges as they relate to the roles and groups they are associated with.

N/A

GROUPS_ROLES

This view contains information about groups and the roles that are part of them.

N/A

IND_CS_PARTITION_ROW_SEGMENT_GROUPS

This view store information about groups of row segments that are sorted together.

N/A

INDEX_STATISTICS

This view contains statistics about indexes. Each row displays information about a given index on a given database partition.

N/A

JSON_COLUMN_SCHEMA

This view contains information about the schema inferred for JSON columns in columnstore tables.

N/A

KEY_COLUMN_USAGE

This view contains information about columns that are part of unique keys (constraints) and displays one row per column in a unique constraint.

N/A

LEAVES

This view contains information about leaves.

N/A

LMV_ASYNC_COMPILES

This view shows the queries being compiled.

N/A

LMV_CONNECTION_ATTRIBUTES

This view shows connection attributes (key value pairs) for the node.

Set when the connection is established and remain until the connection is closed.

LMV_JOIN_OPTIMIZATION_RESULTS

This view contains information about the results of join optimization operations. This table displays rows for each query plan and each table joined in the query plan.

Tied to in-memory plancache expiration (default setting is 720 minutes)

LMV_LOCAL_DATABASES

This view shows all databases in a cluster and their current states.

N/A

LMV_PROFILE_STATS

This view contains statistics collected with PROFILE. The table shows only the PROFILE from the last query instance of a given activity.

N/A

LMV_REPLICATION_STATUS

This view provides the replication status of every replication link on a single node. This view can be accessed from any node in the cluster.

N/A

MV_ACTIVITIES

This view provides a high-level summary profile of all tasks which ran recently on any node in the cluster.

N/A

MV_ACTIVITIES_CUMULATIVE

This view provides a high-level summary profile of completed and currently running tasks on all nodes of the cluster.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_ACTIVITIES_EXTENDED

This view provides a detailed profile of all tasks which ran recently on any node in the cluster.

N/A

MV_ACTIVITIES_EXTENDED_CUMULATIVE

This view provides a detailed profile of completed and currently running tasks on all nodes of the cluster.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_AGGREGATED_COLUMN_USAGE

This view provides a summary of mv_query_column_usage, aggregating over the columns over all queries run.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_AGGREGATED_REPLICATION_STATUS

This view provides the aggregated replication status of each database in the cluster.

N/A

MV_BACKUP_HISTORY

This view stores information about backups that have been made.

N/A

MV_BACKUP_STATUS

This view stores information about the status of ongoing backups.

Stores entries until CLEAR BACKUP_HISTORY command is executed

MV_BLOB_CACHE_EVENTS

This view stores information about events pertaining to cached blobs.

Stores the 115,122 most recent events on each node since the last restart

MV_BLOCKED_QUERIES

This view shows which queries are waiting, and what they are waiting for. It can be used to help debug unexpected waiting issues.

N/A

MV_BOTTOMLESS_API_EVENTS

This view gives details on every remote API call that is made from the engine.

Stores the 115,122 most recent events on each node since the last restart

MV_BOTTOMLESS_DATABASE

This view provides basic information about all the databases on an unlimited storage cluster. Includes databases currently attached on the cluster, as well as any detached databases.

1440 minutes.

Configured via bottomless_gc_retention_period_minutes.

MV_BOTTOMLESS_REMOTE_STATS

This view gives a breakdown of the remote storage information for each unlimited storage database, both reference and partition.

N/A

MV_BOTTOMLESS_STATUS

This view gives an overview of all the unlimited storage databases in a cluster, includes the reference databases and the partition databases.

N/A

MV_BOTTOMLESS_STATUS_EXTENDED

This view provides more diagnostic information compared to MV_BOTTOMLESS_STATUS, such as more detailed LSN, upload statistics, API statistics, and logchunk/snapshot information.

N/A

MV_BOTTOMLESS_SUMMARY

This view is an at-a-glance rollup table the shows what is happening at the moment with the unlimited storage cluster.

N/A

MV_BT

This view shows backtrace information for all all nodes in the cluster. This is primarily used by SingleStore Customer Support.

N/A

MV_CACHED_BLOBS

This view stores information about currently cached blobs.

N/A

MV_CLUSTER_STATUS

This view stores information about partitions within a cluster.

N/A

MV_COLLECTED_ROW_CHANGE_COUNTS

This view displays the best-effort counter for the number of changes that have been observed for the table partitions over time, and are persisted to disk.

N/A

MV_COLUMNAR_SEGMENT_INDEX

This view stores information about indexes used to sort columnstore segments.

N/A

MV_COLUMNSTORE_FILES

This view stores information about columnstore files.

N/A

MV_COLUMNSTORE_MERGE_STATUS

This view stores information about the status of background_merger processes for columnstore tables.

N/A

MV_CONNECTION_ATTRIBUTES

This view shows connection attributes (key value pairs) for all nodes in the cluster.

Set when the connection is established and remain until the connection is closed.

MV_DATA_DISK_USAGE

Displays the breakdown of disk usage by database.

N/A

MV_DISK_USAGE

Displays the breakdown of disk usage by SingleStore.

N/A

MV_DISTRIBUTED_DATABASES_STATUS

This view contains information about the status of databases.

N/A

MV_EVENTS

This view contains information about events and is useful for monitoring events across clusters over time.

Stores the 1,028 most recent events

MV_FINISHED_TASKS

This view provides a detailed profile of completed tasks on all nodes of the cluster.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_GLOBAL_STATUS

This view contains information on the global server status variables that are set on the cluster’s nodes.

N/A

MV_GLOBAL_VARIABLES

This view contains information about the global variables that are set on the cluster’s nodes.

N/A

MV_HOSTS_INFORMATION

This view contains information about hosts.

N/A

MV_MILESTONES

This view displays the list of milestones that have been created in the engine. This is the list of points that can be used to perform PITR.

N/A

MV_NODES

This view describes all SingleStore nodes in the cluster.

N/A

MV_PLANCACHE

This view shows plancache information for all nodes in the cluster.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_PROCESSLIST

This view contains information about currently running threads on a cluster for all nodes.

N/A

MV_PROSPECTIVE_HISTOGRAMS

This view contains information about columns which were used in queries where a histogram could have been utilized, but did not have histograms.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_QUERIES

This view describes queries seen by SingleStore.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_QUERY_ACTIVITIES

This view provides a high-level summary profile of all the queries that ran recently in the cluster.

N/A

MV_QUERY_ACTIVITIES_EXTENDED_CUMULATIVE

This view provides a detailed profile of completed and currently running queries on all nodes of the cluster. It holds a row per query.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_QUERY_COLUMN_USAGE

This view provides information about the usage of columns by queries in the workload.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_QUERY_PROSPECTIVE_HISTOGRAMS

This view contains information about columns which were used in queries where a histogram could have been utilized, but did not have histograms.

Tied to in-memory plancache expiration (default setting is 720 minutes)

MV_REBALANCE_STATUS

This view contains information about the status of REBALANCE commands such as REBALANCE ALL DATABASES and REBALANCE PARTITIONS.

N/A

MV_RECOVERY_STATUS

This view includes information about the status of the current recovery process.

N/A

MV_­REPLICATION_­MANAGEMENT_­ORPHANS_­STATE

This view shows the state of orphan partitions (orphans are partition databases that exist on a node but are either detached or not referenced in metadata).

N/A

MV_­REPLICATION_­MANAGEMENT_­PARTITIONS_­STATE

This view shows the state of partition instances relevant to the node, which are listed in cluster metadata.

N/A

MV_­REPLICATION_­MANAGEMENT_­REF_DB_­STATE

This view shows the state of the reference database replica on the node.

N/A

MV_­REPLICATION_­MANAGEMENT_­STATE

This view shows the aggregated state of replication management.

N/A

MV_REPLICATION_STATUS

This view provides the replication status of each replication link in all nodes of the cluster.

N/A

MV_RESOURCE_POOL_STATUS

This view contains information about the status of resource pools.

N/A

MV_ROW_CHANGE_COUNTS

As this view is primarily for internal use, SingleStore recommends using MV_COLLECTED_ROW_CHANGE_COUNTS instead.

N/A

MV_SYSINFO_CPU

This view describes the total CPU usage breakdown of SingleStore and other processes per node.

N/A

MV_SYSINFO_CPULIST

This view describes the total CPU usage breakdown of SingleStore and other processes per core.

N/A

MV_SYSINFO_DISK

This view describes the total disk usage breakdown of SingleStore and other processes.

N/A

MV_SYSINFO_MEM

This view describes the total memory usage breakdown of SingleStore and other processes.

N/A

MV_SYSINFO_NET

This view describes the total network usage by interface.

N/A

MV_TASKS

This view provides a detailed profile of all currently running tasks on all nodes of the cluster.

N/A

MV_TRANSACTION_TABLE

Contains distributed transaction metadata.

A row is created when a distributed transaction prepares on the node to which LMV_TRANSACTION_TABLE belongs. The row is deleted when all the nodes that participated in that distributed transaction are aware of that distributed transaction's outcome.

MV_WORKLOAD_MANAGEMENT_STATUS

This view contains information about the workload per aggregator. The statistics available in the STAT and VALUE columns are those displayed by SHOW WORKLOAD MANAGEMENT STATUS.

N/A

OPTIMIZER_STATISTICS

This view contains column statistics calculated on columns when a table is processed by autostats or the ANALYZE command.

N/A

PARAMETERS

This view contains information about parameters.

N/A

PIPELINES

This view stores high-level information about any pipelines that have been created in the cluster. Each row represents a single pipeline.

N/A

PIPELINES_BATCHES

This view contains detailed, low-level information about individual batches as they’re loaded into the database.

Stores the value set by the engine variable pipelines_batches_metadata_to_keep (default setting is 1000 batches)

PIPELINES_BATCHES_SUMMARY

This view contains high-level information about individual batches as they are loaded into the database. It contains one row per batch.

N/A

PIPELINES_CURSORS

This view contains information about a pipeline’s offset ranges. Each row represents a single data source partition.

N/A

PIPELINES_ERRORS

This view contains detailed information about errors that occurred during extraction, transformation, or loading. Each row represents a single error event.

Stores the value set by the engine variable pipelines_errors_retention_minutes (default setting is 1440 minutes)

PIPELINES_FILES

This view stores information about files that have been extracted from a file system-like data source, such as Amazon S3. Each row represents a single file.

N/A

PLANCACHE

This view contains information about all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan.

Tied to in-memory plancache expiration (default setting is 720 minutes)

RANGE_STATISTICS

This view is deprecated and no longer used. This table previously contained information about legacy histograms that have been collected using ANALYZE. Current histograms are shown in the ADVANCED_HISTOGRAMS table instead.

N/A

RESOURCE_POOLS

This view contains information about resource pools.

N/A

ROLE_PRIVILEGES

This view contains information about roles as they relate to privileges in a cluster.

N/A

ROUTINES

This view contains information about routines in a cluster.

N/A

SCHEMA_PRIVILEGES

This view contains information about the privileges in a given cluster.

N/A

SCHEMATA

This view contains information about schemas (databases).

N/A

SITES

This view contains information about sites, and is available with SingleStore.

N/A

STATISTICS

This view contains statistics about table indexes. It displays one row per column per index.

N/A

TABLE_CONSTRAINTS

This view contains information about table constraints.

N/A

TABLE_PRIVILEGES

This view contains information about tables and their related privileges in a given cluster.

N/A

TABLE_STATISTICS / INTERNAL_TABLE_STATISTICS

This view contains information about table statistics. It contains one row per table, per partition. INTERNAL_TABLE_STATICS (an internal use-only table added in v7.8) contains the same columns, and shows information for SingleStore internal metadata tables.

N/A

TABLES

This view contains information about the tables visible to the current node.

N/A

USER_PRIVILEGES

This view contains information about the users and their related privileges in a given cluster.

N/A

USER_VARIABLES

This view contains information about user defined variables. See more about how to define these in User-Defined Variables.

N/A

USERS

This view contains information about users for a given cluster.

N/A

USERS_GROUPS

This view contains information about how groups and users are related for a given cluster.

N/A

VIEWS

This view contains information about the views in a cluster.

N/A

Unused Tables

Unlisted and unused tables included only for backward compatibility.

Table Name

COLUMN_PRIVILEGES

EVENTS

FILES

PLUGINS

PROFILING

REFERENTIAL_CONSTRAINTS

TABLESPACES

TRIGGERS

Last modified: March 13, 2024

Was this article helpful?