Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.

Query Performance, Workload Management, and Statistics Tables

This section includes information schema tables that contain statistics and other data which are useful for monitoring and improving query performance, as well as managing the workload of a cluster.

information_schema.ADVANCED_HISTOGRAMS

This table contains information about histograms that have been collected by automatic statistics or using ANALYZE. For more information about this command and which columns SingleStore collects histograms on, see Statistics, Sampling, and ANALYZE.

Each histogram provides information about the distribution of data in a column, and consists of several buckets - the groups/intervals among which data points are divided. Each histogram bucket is displayed as a row in this table.

Column Name Description
DATABASE_NAME The name of the database.
TABLE_NAME The name of the table.
COLUMN_NAME The name of the column.
TYPE The datatype of the column.
VERSION Internal version number of the histogram format, which may change if the way histograms are stored is changed. Its current value is 100.
BUCKET_COUNT The number of buckets in the histogram.
BUCKET_INDEX The index for a given bucket within its histogram.
RANGE_MIN The minimum value in a given bucket.
RANGE_MAX The maximum value in a given bucket.
UNIQUE_COUNT The number of distinct data values that are in a given bucket.
CARDINALITY The number of rows in a given bucket.
UNIQUE_COUNT_CUMULATIVE The cumulative number of distinct data values in this column, up to the current bucket index.
CARDINALITY_CUMULATIVE The cumulative number of rows in this column, up to the current bucket index.

information_schema.AGGREGATE_FUNCTIONS

This table contains information about user defined aggregate functions (UDAF). See further information about how to define aggregate functions in CREATE AGGREGATE.

Column Name Description
AGGREGATE_NAME The name of a given UDAF.
AGGREGATE_SCHEMA The related schema for a given UDAF.
NUM_PARAMETERS The number of parameters required for a given UDAF.
STATE_TYPE The type of state for a given UDAF: array, record, or scalar.
RETURN_TYPE The return type for a given UDAF.
INITIALIZE_FUNCTION The name of the related user defined function (UDF) being executed as a the INITIALIZE_FUNCTION for a given UDAF.
ITERATE_FUNCTION The name of the related user defined function (UDF) being executed as a the ITERATE_FUNCTION for a given UDAF.
MERGE_FUNCTION The name of the related user defined function (UDF) being executed as a the MERGE_FUNCTION for a given UDAF.
TERMINATE_FUNCTION The name of the related user defined function (UDF) being executed as a the TERMINATE_FUNCTION for a given UDAF.
DEFINER The user who created a given UDAF.

information_schema.COLUMN_STATISTICS

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

Column Name Description
DATABASE_NAME The name of the related database.
SCHEMA_NAME The name of the related database (this is the same as DATABASE_NAME, and exists for MySQL compatibility).
TABLE_NAME The name of the related table.
COLUMN_NAME The name of a given column.
ORDINAL The ID for the related partition.
HOST The hostname or IP address for the related node.
PORT The port number of the related node.
NODE_TYPE The type of node: master aggregator, child aggregator, or leaf.
PARTITION_TYPE The type of partition: master or secondary.
MEMORY_USE The amount of memory, in MB, being used by a given column.
HISTOGRAM This column exists for MySQL compatibility and is always blank.

information_schema.INDEX_STATISTICS

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

Column Name Description
DATABASE_NAME The name of the related database.
TABLE_NAME The name of the related table.
INDEX_NAME The name of a given index.
ORDINAL The partition ID number.
HOST The hostname or IP address of the related node.
PORT The port number of the related node.
NODE_TYPE The type of the related node: leaf, master aggregator, child aggregator.
PARTITION_TYPE The type of partition: master or secondary.
MEMORY_USE The amount of memory, in bytes, used by a given index.

information_schema.LMV_JOIN_OPTIMIZATION_RESULTS

This table 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.

Column Name Description
ACTIVITY_NAME The activity name of the query plan. This corresponds to ACTIVITY_NAME in mv_activities.
QUERY_TEXT The text of the query.
DATABASE_NAME The name of the related database.
TABLE_NAME The name of a table within a given join.
IS_LOCAL Whether or not a given join is local.
SEQ_NUMBER The sequence number of this row among the rows for this query plan.
TABLE_COUNT The number of tables included in a given join.
TABLE_INDEX The position of the table in its join.
FILTER_SELECTIVITY The estimated fraction of rows that satisfy the filter conditions on a given table (not including conditions involving multiple tables, i.e. join conditions)
JOIN_SELECTIVITY The estimated fraction of rows that satisfy the join conditions.
TABLE_CARD The estimated rowcount of the table.
CARD_POST_FILTER The estimated rowcount of the table after filter conditions on this table only.
CARD_POST_JOIN The estimated rowcount of the join so far after joining this table.
COST_SO_FAR The estimated cost of executing a given join so far, according to the optimizer’s cost model.

information_schema.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. This table has the LMV suffix, meaning it is a local view per node. Each node in a given cluster has its own version of this table with node-specific data.

Column Name Description
QUERY_TEXT The related query text for a given PROFILE.
ACTIVITY_NAME An ID shared by all instances of the same activity.
PROFILE_JSON The PROFILE in JSON object form.
START_TIME The timestamp at which a given query was started.
END_TIME The timestamp at which a given query was ended.
DURATION_MS The total runtime, in milliseconds, of a given query.
COMPILE_TIME_MS The time, in milliseconds, it took for a given query to compile.

information_schema.MV_BACKUP_HISTORY

This table stores information about backups that have been made.

Column Description
BACKUP_ID A unique identifier for the backup.
CLUSTER_NAME Name of the cluster that carried out the backup.
CLUSTER_ID Unique id that can identify a cluster (see show cluster id).
DATABASE_NAME Name of the database that was backed up.
START_TIMESTAMP Timestamp at which the backup started.
END_TIMESTAMP Timestamp at which the backup completed.
NUM_PARTITIONS Number of partitions for the backup.
BACKUP_PATH Path to where the backup file was placed after backup completed.
CHECKSUM Concatenated CRC32C checksum of all backup files, see Verifying Backup Files.
STATUS Status of backup - Success or Failure.
SIZE Size of all backup files, in megabytes.
INCR_BACKUP_ID A unique identifier for an incremental backup. If the backup is a full backup instead of an incremental backup, incr_backup_id is NULL.
TYPE Type of backup. Possible values are FULL, INCR_INIT, INCR_DIFF, SPLIT_PARTITIONS, or an empty string. An empty string indicates that the type of backup is unknown because the backup was performed in earlier versions of SingleStore DB, when the backup type was not recorded in the MV_BACKUP_HISTORY table.

Note: information_schema.MV_BACKUP_HISTORY is populated only when the BACKUP DATABASE command is run to create a database backup.

information_schema.MV_BACKUP_STATUS

This table stores information about the status of ongoing backups.

Column Description
ORIGIN_NODE_ID The unique id of the origin node.
DATABASE_NAME Name of the database being backed up.
TARGET The local or network filesystem being targeted for the backup.
ROWSTORE_PROGRESS Progress backing up rowstore represented as a number between 0 and 100.
COLUMNSTORE_PROGRESS Progress backing up columnstore represented as a number between 0 and 100.
STATUS Where the backup is currently.
STEP_EST_SEC_REMAIN The estimated remaining time in seconds for the current step.

Note: information_schema.MV_BACKUP_STATUS is populated only when the BACKUP DATABASE command is run to create a database backup.

information_schema.MV_PROCESSLIST

This table contains information about currently running threads on a cluster for all nodes. Alternatively, information_schema.PROCESSLIST shows this information per node. This information is surfaced by the SHOW PROCESSLIST command.

Column Name Description
NODE_ID The ID of the node a given process is being run on.
ID The unique ID of a given process.
USER The user who issued the process command.
HOST The hostname or IP address of the related node.
DB The related database.
COMMAND The type of command associated with this process.
TIME The runtime, in seconds.
STATE The state of a given process.
INFO Additional information about a given process, including the query text.
RPC_INFO Low level information about this process shown as a JSON string.
PLAN_ID The ID of the related query plan for a given process.
TRANSACTION_STATE running if there is an open transaction and a query running. open if there is an open transaction with no query running. NULL if there is no open transaction.
ROW_LOCKS_HELD The number of row locks being held.
PARTITION_LOCKS_HELD The column is deprecated, as are partition level locks. The value for this column is always 0.
RESOURCE_POOL The resource pool a given process is drawing from.
REASON_FOR_QUEUEING The reason a given process is queued.

information_schema.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. This view counts over all queries (rows) in the in-memory plancache in order to distinguish which of these contain applicable columns. This can be useful for examining which columns included in these queries should have autostats enabled for the future. See further information about this, and how to set autostats in Statistics, Sampling, and ANALYZE

Note that this view displays the columns that did not have histograms at the time each plan was compiled, so it will continue to display such columns even after histograms are collected. You can filter to find only the columns that currently do not have histograms by joining against information_schema.OPTIMIZER_STATISTICS, looking for columns where RANGE_STATS is false.

Column Name Description
DATABASE_NAME The name of the related database.
TABLE_NAME The name of the related table.
COLUMN_NAME The name of a given column for which a prospective histogram has been identified.
USAGE_COUNT The number of times the column was used in queries where a histogram could have been utilized, counting once per query per occurrence of the table.

information_schema.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. This is similar to MV_PROSPECTIVE_HISTOGRAMS, but MV_QUERY_PROSPECTIVE_HISTOGRAMS displays one row per column per query, while MV_PROSPECTIVE_HISTOGRAMS displays one row per column, and aggregates over all queries.

This view counts over all queries (rows) in the in-memory plancache in order to distinguish which of these contain applicable columns. This can be useful for examining which columns included in these queries should have autostats enabled for the future. See further information about this, and how to set autostats in Statistics, Sampling, and ANALYZE.

Column Name Description
ACTIVITY_NAME The ID of the query.
DATABASE_NAME The name of the related database.
TABLE_NAME The name of the related table.
COLUMN_NAME The name of a given column for which a prospective histogram has been identified.
USAGE_COUNT The number of times the column was used where a histogram could have been utilized, counting once per occurrence of the table in the query.

information_schema.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; for more information about these, see Using the Workload Manager.

Column Name Description
NODE_ID The ID of a given node.
IP_ADDR The IP address of the related node.
PORT The port number of the related node.
TYPE The type of the related node: leaf, master aggregator, or child aggregator.
STAT The statistic being collected.
VALUE The value of a given statistic.

information_schema.OPTIMIZER_STATISTICS

This table contains column statistics calculated on columns when a table is processed by autostats or the ANALYZE command. It displays one row per column per table. The values displayed here are estimates used for query optimization, they are not exact.

Column Name Description
DATABASE_NAME The name of the related database.
TABLE_NAME The name of the related table.
COLUMN_NAME The name of the related column.
ROW_COUNT The number of rows in a given table.
NULL_COUNT The number of null values stored within a given column.
CARDINALITY The number of unique values stored within a given column.
AUTOSTATS_ENABLED Whether or not autostats are currently enabled for a given column.
AUTOSTATS_OUTDATED Whether or not autostats are considered too out-of-date to be used currently.
AUTOSTATS_ROW_COUNT This column is deprecated and no longer used and its value is always NULL. See ROW_COUNT instead.
AUTOSTATS_NULL_COUNT This column is deprecated and no longer used and its value is always NULL. See NULL_COUNT instead.
AUTOSTATS_CARDINALITY This column is deprecated and no longer used and its value is always NULL. See CARDINALITY instead.
ADVANCED_HISTOGRAMS Whether advanced histograms are collected on this column.
LEGACY_HISTOGRAMS Whether legacy histograms are collected on this column. This column is now deprecated.
RANGE_STATS Whether histograms (of either type) are collected on this column .
SAMPLE_SIZE The sample size collected to build histograms on this column.
LAST_UPDATED The timestamp at which autostats were last updated for a given column.

information_schema.PLANCACHE

This table contains information about all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan. It shows all plans that are currently in SingleStore’s in-memory plancache - plans are removed from this table, and statistics and counters are reset to zero, when the plan expires (after being unused for the setting of the engine variable plan_expiration_minutes), when the node restarts, or when the plan must be recompiled or reloaded (e.g. when the table is altered).

See also SHOW PLANCACHE, which displays a subset of this information.

Column Name Description
DATABASE_NAME Context database selected with USE <database_name> when query was compiled.
QUERY_TEXT Query text with all numeric and string parameters replaced by tags (depending on parameter and/or query type). @ is used for integers and ^ for string parameters. ? is always used for INSERT queries.
PLAN_ID The unique ID of the query plan.
PLAN_TYPE The type of query plan: interpreted or compiled.
COMMITS The number of successful executions of a given query.
ROLLBACKS Number of unsuccessful executions of a given query (i.e. cases where query was aborted or encountered runtime errors).
ROWCOUNT The cumulative number of rows returned by a SELECT query or the cumulative number of rows inserted, updated, or deleted for an INSERT, UPDATE, or DELETE query.
EXECUTION_TIME The cumulative time, in milliseconds, spent executing a given query.
AVAERAGE_EXEC_TIME The average execution time of a given query plan.
LOGBUFFER_TIME NULL for SELECT queries, otherwise the cumulative time (in milliseconds) spent waiting to reserve space in the transaction buffer for this query. A larger transaction buffer and faster disk can help reduce it.
LOGFLUSH_TIME NULL for SELECT queries, otherwise the cumulative time (in milliseconds) spent waiting until changes made by this query are flushed to disk. A faster disk can help reduce it.
ROWLOCK_TIME NULL for SELECT queries, otherwise the cumulative time (in milliseconds) spent waiting to acquire exclusive row locks.
STREAMED_ROWS Cumulative number of rows at the cluster level streamed from leaves and processed by a given SELECT query.
LEAFNETWORK_TIME Cumulative time, in milliseconds, at the cluster level spent waiting for results from leaves. This includes the time spent executing queries on the leaves.
WORKLOAD_MANAGEMENT_QUEUED_TIME The amount of time, in milliseconds, a given query spent queued by Workload Management.
RESOURCE_POOL_QUEUED_TIME The amount of time, in milliseconds, a given query spent queued by Resource Governance.
MPL_PATH The path to the plan files on disk.
QUEUED_TIME The total amount of time, in milliseconds, a given query spends queued.
LAST_EXECUTED The timestamp at which a given query was last executed.
AVERAGE_MEMORY_USE The average amount of memory used to execute this query. Any temporary memory allocation needed to execute a query (including those for hash tables, sorts, result tables, etc.) is tracked here.
PLAN_WARNINGS Any warnings associated with a given query plan.
PLAN_INFO Additional information about the query plan, in JSON format.
OPTIMIZER_NOTES Information about the query plan used by the optimizer, in JSON format.
CPU_TIME The amount of CPU time, in milliseconds, spent executing the query.
AVERAGE_MAX_MEMORY_USE This is the maximum memory use of each run, averaged across all runs of the same query plan.
ACTIVITY_NAME The ID of the related activity.
PLAN_VARIABLES The values of all engine variables that affect a given query plan.

information_schema.RANGE_STATISTICS

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

information_schema.STATISTICS

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

Column Name Description
TABLE_CATALOG This value is always def.
TABLE_SCHEMA The name of the related schema (database) that a given table belongs to.
TABLE_NAME The name of the related table for a given index.
NON_UNIQUE If the index can contain duplicates, this is 1. If the index cannot contain duplicates, this is 0.
INDEX_SCHEMA The name of the schema (database) that a given index belongs to. This value is the same as TABLE_SCHEMA.
INDEX_NAME The name of a given index. This is primary if the index is a primary key.
SEQ_IN_INDEX The sequential number of a column within a given index.
COLUMN_NAME The name of a column within a given index.
COLLATION If a given column is sorted ascending within an index, this value is A. If the column is sorted descending, it is D. If the column is not sorted, it is NULL.
CARDINALITY This column is unused and is present only for compatibility reasons, its value is always 0.
SUB_PART This column is unused and is present only for compatibility reasons, its value is always NULL.
PACKED This column is unused and is present only for compatibility reasons, its value is always NULL.
NULLABLE Whether a given column can contain NULL values.
INDEX_TYPE The type of index.
COMMENT This column is unused and is present only for compatibility reasons, its value is always empty.
INDEX_COMMENT This column is unused and is present only for compatibility reasons, its value is always empty.

information_schema.TABLE_STATISTICS

This table contains information about table statistics. It contains one row per table, per partition.

This information can be useful for Detecting and Resolving Data Skew.

Column Name Description
DATABASE_NAME The name of the related database.
TABLE_NAME The name of the related table.
ORDINAL The partition ID number.
HOST The hostname or IP address for the related node.
PORT The port number of the related node.
NODE_TYPE The type of the related node: leaf, master aggregator, or child aggregator.
PARTITION_TYPE The type of partition: master or secondary.
ROWS The number of rows in a given table and partition.
MEMORY_USE The memory use, in MB, of a given table and partition.
STORAGE_TYPE The storage type of a given table and partition.
ROWS_IN_MEMORY The number of rows stored in memory for a given table and partition.

information_schema.USER_VARIABLES

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

Column Name Description
VARIABLE_NAME The name of a given user defined variable.
VARIABLE_VALUE The value of a given user defined variable.
VARIABLE_TYPE The datatype of a given user defined variable.