ColumnStore Information Schema Tables

You are viewing an old version of this article. View the current version here.

MariaDB ColumnStore has three Information Schema tables that expose information about the table and column storage. These tables were added in version 1.0.6 of ColumnStore.

COLUMNSTORE_TABLES

The first table is the INFORMATION_SCHMEA.COLUMNSTORE_TABLES. This contains information about the tables inside ColumnStore. The table layout is as follows:

ColumnDescription
TABLE_SCHEMAThe database schema for the table
TABLE_NAMEThe table name
OBJECT_IDThe ColumnStore object ID for the table
CREATION_DATEThe date the table was created
COLUMN_COUNTThe number of columns in the table
AUTOINCREMENTThe start autoincrement value for the table set during CREATE TABLE

Note: Tables created with ColumnStore 1.0.4 or lower will have the year field of the creation data set incorrectly by 1900 years.

COLUMNSTORE_COLUMNS

The INFORMATION_SCHEMA.COLUMNSTORE_COLUMNS table contains information about every single column inside ColumnStore. The table layout is as follows:

ColumnDescription
TABLE_SCHEMAThe database schema for the table
TABLE_NAMEThe table name for the column
COLUMN_NAMEThe column name
OBJECT_IDThe object ID for the column
DICTIONARY_OBJECT_IDThe dictionary object ID for the column (NULL if there is no dictionary object
LIST_OBJECT_IDPlaceholder for future information
TREE_OBJECT_IDPlaceholder for future information
DATA_TYPEThe data type for the column
COLUMN_LENGTHThe data length for the column
COLUMN_POSITIONThe position of the column in the table, starting at 0
COLUMN_DEFAULTThe default value for the column
IS_NULLABLEWhether or not the column can be set to NULL
NUMERIC_PRECISIONThe numeric precision for the column
NUMERIC_SCALEThe numeric scale for the column
IS_AUTOINCREMENTSet to 1 if the column is an autoincrement column
COMPRESSION_TYPEThe type of compression (either "None" or "Snappy")

COLUMNSTORE_EXTENTS

This table displays the extent map in a user consumable form. An extent is a collection of details about a section of data related to a columnstore column. A majority of columns in ColumnStore will have multiple extents and the columns table above can be joined to this one to filter results by table or column. It also contains more details about the extent in table such as file size information. The table layout is as follows:

ColumnDescription
OBJECT_IDThe object ID for the extent
OBJECT_TYPEWhether this is a "Column" or "Dictionary" extent
LOGICAL_BLOCK_STARTColumnStore's internal start LBA for this extent
LOGICAL_BLOCK_ENDColumnStore's internal end LBA for this extent
MIN_VALUEThis minimum value stored in this extent
MAX_VALUEThe maximum value stored in this extent
WIDTHThe data width for the extent
DBROOTThe DBRoot number for the extent
PARTITION_IDThe parition ID for the extent
SEGMENT_IDThe segment ID for the extent
BLOCK_OFFSETThe block offset for the data file, each data file can contain multiple extents for a column
MAX_BLOCKSThe maximum number of blocks for the extent
HIGH_WATER_MARKThe last block committed to the extent (starting at 0)
STATEThe state of the extent (see below)
STATUSThe availability status for the column which is either "Available", "Unavailable" or "Out of service"
DATA_SIZEThe uncompressed data size for the extent calculated as (HWM+1) * BLOCK_SIZE
FILENAMEThe full path and filename for the extent file, multiple extents for the same column can point to this file with different BLOCK_OFFSETs
FILE_SIZEThe disk file size for the extent

Notes:

  1. The state is "Valid" for a normal state, "Invalid" if a cpimport has completed but the table has not yet been accessed (min/max values will be invalid) or "Updating" if there is a DML statement writing to the column
  2. In ColumnStore the block size is 8192 bytes
  3. By default ColumnStore will write create an extent file of 256*1024*WIDTH bytes, if this is too small then for uncompressed data it will create a file of the maximum size for the extent (MAX_BLOCKS * BLOCK_SIZE). Snappy always compression adds a header block.
  4. Object IDs of less than 3000 are for internal tables and will not appears in the COLUMNSTORE_TABLES or COLUMNSTORE_COLUMNS tables

Useful Queries

The following are queries that could be considered useful with the information_schmea tables.

Column File Size By Table

This gives a total disk size of the column extents for each table, excluding dictionary sizes:

select c.table_name, sum(e1.file_size) extent_file_size from columnstore_columns c join columnstore_extents e1 on c.object_id = e1.object_id and e1.block_offset=0 group by c.table_name;

Note: This excludes the hidden internal tables.

Dictionary File Size By Table

This gives the total size for the dictionary data for a table:

select c.table_name, sum(e1.fsize) dict_file_size from columnstore_columns c join (select object_id, sum(file_size) fsize from columnstore_extents where block_offset=0 group by object_id) e1 on c.dictionary_object_id = e1.object_id group by c.table_name;

Note: This excludes the hidden internal tables.

Total File Size

This returns the total size of all the extent files including hidden internal tables:

select sum(file_size) file_size from columnstore_extents where block_offset=0;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.