This topic describes how to use the consistency view of In-Memory Column Index (IMCI).
Background information
Based on the architecture of PolarDB that consists of one primary node and multiple read-only nodes, IMCI uses virtual secondary indexes and heterogeneous data to provide analytical processing (AP) capabilities on individual AP read-only nodes. This reduces the impact of AP queries on the primary node of a cluster. However, this method brings difficulties in displaying the status information of IMCIs. Only a very small amount of metadata is stored in the data dictionary. The status information of IMCIs cannot be queried on the primary node and regular read-only nodes. To query the status information of IMCIs, such as the creation status and progress, you need to directly connect to a read-only column store node or a cluster endpoint that enables automatic request distribution among row store and column store nodes. In addition, if a cluster contains multiple AP read-only nodes, the status information queried by using the cluster endpoint may be the status information of a random AP read-only node that cannot be identified. To obtain the status information of all column storage nodes, you need to directly connect to and query each AP read-only node.
To solve this issue, PolarDB provides the global consistency view. After you enable the global consistency view, you can query the status of IMCIs on any node in a PolarDB cluster for which IMCI is enabled. If the PolarDB cluster for which IMCI is enabled contains multiple read-only column store nodes, when you query the status information of IMCIs in the INFORMATION_SCHEMA database, the node to which the query is routed forwards the query to all read-only column store nodes in the cluster to obtain the status information of IMCIs on the those nodes. The query results are aggregated on the node to which the query is first routed and then returned to the query terminal. A column named Server_Id, which is equivalent to the instance ID on the cluster details page in the console, is added to the aggregated result to indicate the node from which the record is returned.
If you connect to a column store node, you can directly query the information of IMCIs in the
INFORMATION_SCHEMAdatabase.If you connect to the endpoint of a multi-node cluster, the query is routed to a specific node by PolarProxy, not by the automatic request distribution among row store and column store nodes.
Supported version
Your PolarDB for MySQL Enterprise Edition cluster must meet the following requirements:
A cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.39 or later.
A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.20 or later.
Parameters
You can run the SET command in a session to enable or disable the global consistency view. The following table describes the parameter of the command:
Parameter | Level | Description |
imci_infoschema_remote_access_method | Session | Enable or disable the global consistency view. Valid values:
|
Related commands
In addition to the non-reserved keyword IMCI, PolarDB adds the following auxiliary commands for querying the status information of IMCIs. See the following description of command syntax:
Query the basic status of IMCIs
Command syntax
SHOW IMCI INDEXES [option]Behavior: Running this command is equivalent to running
SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES;.
Query the extension status of IMCIs
Command syntax
SHOW IMCI INDEXES STATUS [option]Behavior: Running this command is equivalent to running
SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEX_STATS;.
Query the creation status of IMCIs
Command syntax
SHOW IMCI INDEXES RECOVER STATUS [option]Behavior: Running this command is equivalent to running
SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;.
Query the storage status of IMCIs
Command syntax
SHOW IMCI INDEXES FILE [option]Behavior: Running this command is equivalent to running
SELECT * FROM INFORMATION_SCHEMA.IMCI_DATA_FILES;.
Formats of the option parameter
The following table describes the formats supported by the option parameter in the preceding commands:
Format | Description |
FOR table_name | Filters the result by table name. Using this format is equivalent to adding the |
FOR db_name.table_name | Filters the result by database name and table name. Using this format is equivalent to adding the |
FROM db_name or IN db_name | Filters the result by database name. Using this format is equivalent to adding the |
Example
In this example, the cluster contains two read-only column store nodes. The t1, t2, t33, t3, and t4 tables are created in the database. The status of IMCIs in the INFORMATION_SCHEMA.IMCI_INDEXES table is queried in the following scenarios:
The following statements are used in the example to create tables:
CREATE TABLE t1(
col1 INT COMMENT 'COLUMNAR=1',
col2 DATETIME COMMENT 'COLUMNAR=1',
col3 VARCHAR(200)
) ENGINE InnoDB;
CREATE TABLE t2(
col1 INT,
col2 DATETIME,
col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';
CREATE TABLE t33 (a INT, b INT) ENGINE = InnoDB COMMENT 'COLUMNAR=1';
CREATE TABLE t3(
col1 INT,
col2 DATETIME,
col3 VARCHAR(200)
) ENGINE InnoDB;
CREATE TABLE t4 COMMENT 'COLUMNAR=1' SELECT col1, col2 FROM t3;Scenario 1: Query the status of IMCIs in the
INFORMATION_SCHEMA.IMCI_INDEXEStable on the primary node that does not enable IMCI.Run the following command to view the indexes in the cluster:
SELECT * FROM INFORMATION_SCHEMA.imci_indexes;The following result is returned.
+-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ | Server_Id | TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID | STATE | STATE_UPDATE_AT | CHECKPOINT_DATADIR | WRITE_POLICY | +-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ | 28223015 | 1080 | test | t1 | 2 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:26 | ./imci_1/imci_chkp_1080_175 | Tradeoff | | 28223015 | 1081 | test | t2 | 4 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:36 | ./imci_1/imci_chkp_1081_178 | Tradeoff | | 28223015 | 1083 | test | t4 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:55 | ./imci_1/imci_chkp_1083_183 | Tradeoff | | 28223015 | 1086 | test | t33 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:40:58 | ./imci_1/imci_chkp_1086_190 | Tradeoff | | 28223014 | 1080 | test | t1 | 2 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:26 | ./imci_2/imci_chkp_1080_175 | Tradeoff | | 28223014 | 1081 | test | t2 | 4 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:36 | ./imci_2/imci_chkp_1081_178 | Tradeoff | | 28223014 | 1083 | test | t4 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:55 | ./imci_2/imci_chkp_1083_183 | Tradeoff | | 28223014 | 1086 | test | t33 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:40:58 | ./imci_2/imci_chkp_1086_190 | Tradeoff | +-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ 8 rows in set, 1 warning (0.021 sec)Scenario 2: Query the status of IMCIs in the
INFORMATION_SCHEMA.IMCI_INDEXEStable on a read-only node that does not enable IMCI.Run the following command to view the indexes in the cluster:
SELECT * FROM INFORMATION_SCHEMA.imci_indexes;The following result is returned.
+-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ | Server_Id | TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID | STATE | STATE_UPDATE_AT | CHECKPOINT_DATADIR | WRITE_POLICY | +-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ | 28223015 | 1080 | test | t1 | 2 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:26 | ./imci_1/imci_chkp_1080_175 | Tradeoff | | 28223015 | 1081 | test | t2 | 4 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:36 | ./imci_1/imci_chkp_1081_178 | Tradeoff | | 28223015 | 1083 | test | t4 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:55 | ./imci_1/imci_chkp_1083_183 | Tradeoff | | 28223015 | 1086 | test | t33 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:40:58 | ./imci_1/imci_chkp_1086_190 | Tradeoff | | 28223014 | 1080 | test | t1 | 2 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:26 | ./imci_2/imci_chkp_1080_175 | Tradeoff | | 28223014 | 1081 | test | t2 | 4 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:36 | ./imci_2/imci_chkp_1081_178 | Tradeoff | | 28223014 | 1083 | test | t4 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:55 | ./imci_2/imci_chkp_1083_183 | Tradeoff | | 28223014 | 1086 | test | t33 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:40:58 | ./imci_2/imci_chkp_1086_190 | Tradeoff | +-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ 8 rows in set, 1 warning (0.006 sec)Scenario 3: Query the status of IMCIs in the
INFORMATION_SCHEMA.IMCI_INDEXEStable on a read-only column store node.Run the following command to view the indexes in the cluster:
SELECT * FROM INFORMATION_SCHEMA.imci_indexes;The following result is returned.
+-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ | Server_Id | TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID | STATE | STATE_UPDATE_AT | CHECKPOINT_DATADIR | WRITE_POLICY | +-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ | current | 1080 | test | t1 | 2 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:26 | ./imci_2/imci_chkp_1080_175 | Tradeoff | | current | 1081 | test | t2 | 4 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:36 | ./imci_2/imci_chkp_1081_178 | Tradeoff | | current | 1083 | test | t4 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:55 | ./imci_2/imci_chkp_1083_183 | Tradeoff | | current | 1086 | test | t33 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:40:58 | ./imci_2/imci_chkp_1086_190 | Tradeoff | | 28223015 | 1080 | test | t1 | 2 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:26 | ./imci_1/imci_chkp_1080_175 | Tradeoff | | 28223015 | 1081 | test | t2 | 4 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:36 | ./imci_1/imci_chkp_1081_178 | Tradeoff | | 28223015 | 1083 | test | t4 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:36:55 | ./imci_1/imci_chkp_1083_183 | Tradeoff | | 28223015 | 1086 | test | t33 | 3 | 0 | 16384 | 0 | COMMITTED | 2023-12-25 15:40:58 | ./imci_1/imci_chkp_1086_190 | Tradeoff | +-----------+----------+-------------+------------+----------+-----------+-----------+--------+-----------+---------------------+-----------------------------+--------------+ 8 rows in set (0.005 sec)