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 workspace.

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 workspace. 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

CORRELATED_COLUMN_STATISTICS

This view contains information about correlated columns.

The column correlation data is based on metadata rows that are persisted within the user database. The lifetime of these rows is tied to the referenced user objects -- when those objects are dropped or deleted, the rows referencing those objects are dropped from the correlation view.

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 workspaces.

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 workspace.

N/A

LOAD_DATA_ERRORS

This table is where the errors are logged when the LOAD DATA command is used with the ERRORS HANDLE clause.

N/A

MV_ACTIVE_DISTRIBUTED_TRANSACTIONS

This view shows internal information on distributed transactions that are currently active in the system.

N/A

MV_ACTIVITIES

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

N/A

MV_ACTIVITIES_CUMULATIVE

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

For query activity, it is tied to in-memory plancache expiration (default setting is 720 minutes). For database activity, it is tied to the lifetime of the database.

However, this is reset on node restart.

MV_ACTIVITIES_EXTENDED

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

N/A

MV_ACTIVITIES_EXTENDED_CUMULATIVE

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

For query activity, it is tied to in-memory plancache expiration (default setting is 720 minutes). For database activity, it is tied to the lifetime of the database.

However, this is reset on node restart.

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 workspace.

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_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_DATABASES

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.

N/A

MV_BOTTOMLESS_STATUS

This view gives an overview of all the unlimited storage databases in a workspace, 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 workspace.

N/A

MV_BT

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

N/A

MV_CACHED_BLOBS

This view stores information about currently cached blobs.

N/A

MV_CLOUD_ATTACHED_DATABASES

This view shows information about all the attached databases and branches.

N/A

MV_CLOUD_BRANCHES

This view displays all databases and branches, and their properties.

N/A

MV_CLOUD_DUPLICATION_STATUS

This view provides Information about the status of the current duplication process.

N/A

MV_CLOUD_PER_COMPUTE_REMOTE_STATS

This view provides information about how much storage usage is associated to each compute session.

N/A

MV_CLOUD_REMOTE_STATS

This view provides information about the total storage usage per storage_ID.

N/A

MV_CLUSTER_STATUS

This view stores information about partitions within a workspace.

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 workspace.

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 workspaces 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 workspace.

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 workspace’s nodes.

N/A

MV_GLOBAL_VARIABLES

This view contains information about the global variables that are set on the workspace’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 Helios nodes in the workspace.

N/A

MV_PLANCACHE

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

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

MV_PROCESSLIST

This view contains information about currently running threads on a workspace 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 Helios.

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 workspace.

N/A

MV_QUERY_ACTIVITIES_EXTENDED_CUMULATIVE

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

Tied to in-memory plancache expiration (default setting is 720 minutes) and reset on node restart.

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 operations.

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 workspace.

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 Helios and other processes per node.

N/A

MV_SYSINFO_CPULIST

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

N/A

MV_SYSINFO_DISK

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

N/A

MV_SYSINFO_MEM

This view describes the total memory usage breakdown of SingleStore Helios 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 workspace.

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_VERSION_HISTORY (and lmv_version_history_extended)

This view shows an aggregate view of every upgrade the workspace has gone through.

This information is historical and remains unless intentionally deleted.

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 workspace. 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)

PROJECTIONS

This view contains information about projections.

N/A

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

RESOURCE_POOL_PRIVILEGES

This view contains information about resource pool grants and privileges.

N/A

ROLE_PRIVILEGES

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

N/A

ROUTINES

This view contains information about routines in a workspace.

N/A

SCHEMA_PRIVILEGES

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

N/A

SCHEMATA

This view contains information about schemas (databases).

N/A

SITES

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

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 workspace.

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 workspace.

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 workspace.

N/A

USERS_GROUPS

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

N/A

VIEWS

This view contains information about the views in a workspace.

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: September 18, 2024

Was this article helpful?