PolarDB:IMCI FAQ

How to use the IMCI feature of PolarDB for MySQL?

How to check the status of an IMCI

Confirm IMCI usage and view execution plan

*************************** 1. row ***************************
IMCI Execution Plan (max_dop = 8, max_query_mem = 3435134976):
Project | Exprs: temp_table3.lineitem.L_ORDERKEY, temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT), temp_table3.orders.O_ORDERDATE, temp_table3.orders.O_SHIPPRIORITY
  Sort | Exprs: temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT) DESC,temp_table3.orders.O_ORDERDATE ASC
    HashGroupby | OutputTable(3): temp_table3 | Grouping: lineitem.L_ORDERKEY orders.O_ORDERDATE orders.O_SHIPPRIORITY | Output Grouping: lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY | Aggrs: SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT)
      HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_ORDERKEY = lineitem.L_ORDERKEY
        HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_CUSTKEY = customer.C_CUSTKEY
          CTableScan | InputTable(0): orders | Pred: (orders.O_ORDERDATE < 03/24/1995 00:00:00.000000)
          CTableScan | InputTable(1): customer | Pred: (customer.C_MKTSEGMENT = "BUILDING")
        CTableScan | InputTable(2): lineitem | Pred: (lineitem.L_SHIPDATE > 03/24/1995 00:00:00.000000)
1 row in set (0.04 sec)

Troubleshoot queries not using IMCI

An IMCI accelerates a query only if several conditions are met: an IMCI exists on the queried tables, the query's estimated cost exceeds a specific threshold, and the query is routed to the read-only IMCI node. If a query is not using an IMCI, follow these troubleshooting steps:

1. Verify that the query is routed to the read-only IMCI node.

   Use the SQL Explorer and Audit feature to verify that the query was routed to the read-only IMCI node.

   If you use a cluster endpoint with automatic traffic splitting enabled, PolarProxy automatically routes queries with an estimated cost higher than the imci_ap_threshold value to the read-only IMCI node. You can also force a query to be routed to the read-only IMCI node by adding the /*FORCE_IMCI_NODES*/ hint before the SELECT keyword. Example:

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

   For more information, see Configure automatic traffic splitting.

   Note

   To ensure that queries are always routed to a read-only IMCI node, create a new endpoint that connects directly to it.

2. Check if the query cost exceeds the threshold.

   On a read-only IMCI node, the optimizer estimates the cost of a query. If the estimated cost is higher than the cost_threshold_for_imci parameter, the query uses an IMCI. Otherwise, it uses a standard row index.

   After confirming that the query was routed to the read-only IMCI node, if the execution plan still does not show IMCI usage, the estimated query cost might be too low. Check the estimated cost of the last executed query by viewing the Last_query_cost_for_imci variable:

   EXPLAIN SELECT * FROM t1;
   SHOW STATUS LIKE 'Last_query_cost_for_imci';

   If the estimated execution cost of an SQL statement is less than the preset cost_threshold_for_imci, you can consider adjusting cost_threshold_for_imci . For example, use a hint to adjust the preset threshold for a single SQL statement:

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

3. Check if all columns used in the query are covered by an IMCI.

   Use the built-in stored procedure dbms_imci.check_columnar_index() to check whether an IMCI has been created on the tables in a query. Example:

   CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

   If the query references columns that are not covered by an IMCI, the stored procedure returns a list of the uncovered tables and columns. If all columns are covered, the procedure returns an empty result set.

4. Check for unsupported SQL features.

   Review the Limits to confirm that all features in your query are supported by IMCI.

If all these checks pass, the query should use an IMCI.

Can a read-only IMCI node use a row index?

Yes. A read-only IMCI node is a standard read-only node with the IMCI feature enabled. Therefore, it can use both IMCIs and a standard row index. The optimizer chooses which index to use based on the cost_threshold_for_imci value.

SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;

How to create a suitable IMCI for a query

For a query to use an IMCI, all columns that the query references must be covered by the IMCI. If some columns are not covered, you can add them to an IMCI by using the CREATE TABLE or ALTER TABLE statement. PolarDB for MySQL provides several built-in stored procedures to help with this process.

dbms_imci.columnar_advise('<query_string>');

PolarDB for MySQL IMCI Does it support single-node parallel query? If so, how can you customize the degree of parallelism for a specific SQL query?

Parallel query on a single node is enabled by default. Run EXPLAIN to view the execution plan, where the max_dop field indicates the actual degree of parallelism used. To customize the degree of parallelism for a specific query, you can set the imci_max_dop parameter at the session level before executing the query. For example:

set imci_max_dop=8; explain select xxxx

High resource usage and monitoring

• By default, IMCI is configured to execute a single query in parallel, which can use all available CPU resources. When multiple queries run concurrently, the internal database scheduler manages resources by dynamically reducing the CPU and memory limits for each query. As a result, the average CPU and memory usage on a read-only IMCI node is typically higher than on other nodes. You can control the maximum degree of parallelism (the maximum number of CPU cores) for a single query by adjusting the imci_max_dop parameter.

• We recommend setting the monitoring alert threshold to 70% for CPU usage and 90% for memory usage.

• PolarDB for MySQL allows you to use different specifications for different nodes within the same cluster. You can scale the read-only IMCI node up or down independently. We recommend that a read-only IMCI node have at least 8 CPU cores and 16 GB of memory.

PolarDB for MySQL 5.6/5.7: Is IMCI supported?

No. The IMCI feature is not supported in PolarDB for MySQL 5.6 or 5.7. It is supported only in PolarDB for MySQL 8.0 and later versions.

IMCI limitations and MySQL compatibility

IMCI is fully compatible with MySQL syntax. However, some less common query features are not yet fully supported, such as certain spatial data type expressions, full-text index, and some forms of correlated subqueries. Queries that use these features cannot be accelerated by an IMCI and will automatically fall back to a standard row index. For a detailed list of limitations, see Limits.

Using IMCI with INSERT/CREATE AS SELECT

IMCIs can only be used for queries on read-only nodes, while INSERT and CREATE statements can only be executed on the primary node. Therefore, to accelerate the SELECT part of an INSERT INTO SELECT or CREATE TABLE AS SELECT statement, you must use the IMCI ETL feature. For more information, see Use IMCI to accelerate ETL.

Pricing and fees

The IMCI feature itself is free of charge. However, to use it, you must add a dedicated read-only node with IMCI enabled. You are billed for this new read-only node and any additional storage consumed by the IMCIs you create.

Standard read-only nodes do not support IMCI.

Storage requirements

IMCI stores data in a columnar format, enabling a high compression ratio. Compared to row-based storage, IMCIs can achieve a compression ratio of 3:1 to 10:1, typically resulting in an additional storage footprint of only 10% to 30% of the original table size.

View IMCI storage usage

• For PolarDB for MySQL cluster versions 8.0.1.1.32 and earlier, query the imci_columns system table in information_schema to view the storage space and compression ratio of a table with an IMCI. For example, to check the storage space and compression ratio for the table named test in the test database, run the following SQL statement:

  SELECT
    SCHEMA_NAME, TABLE_NAME,
    SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
    SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
    SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE,
    SUM(RAW_DATA_SIZE) / SUM(FILE_SIZE) AS COMPRESS
  FROM
    information_schema.imci_columns
  WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

• For PolarDB for MySQL cluster versions 8.0.1.1.33 and later, query the imci_data_files system table in information_schema to view storage space and the imci_columns system table to view the compression ratio. For example, to check the storage space and compression ratio for the table named test in the test database, run the following SQL statements:

  • View the storage space used by the test table with an IMCI:

    SELECT
        SCHEMA_NAME, TABLE_NAME,
        SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
        SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
        SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE
    FROM
        INFORMATION_SCHEMA.IMCI_DATA_FILES
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

  • View the column compression ratio:

    SELECT
        SCHEMA_NAME, TABLE_NAME,
         SUM(RAW_DATA_SIZE) / SUM(CMP_DATA_SIZE) AS COMPRESS_RATIO
    FROM
        INFORMATION_SCHEMA.IMCI_COLUMNS
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

The following table describes the parameters in the preceding SQL statements.

Instant DDL with IMCI

• In PolarDB for MySQL versions earlier than 8.0.1.1.42 and 8.0.2.2.23, adding a column to a table with a table-level IMCI does not use the instant DDL logic. This is because the operation requires changing the IMCI structure and rebuilding the index data.

• In PolarDB for MySQL 8.0.1.1.42 and later, and 8.0.2.2.23 and later, instant DDL is supported on tables with a table-level IMCI. This feature is not compatible with the rebuild mode of earlier versions. You must set the imci_enable_add_column_instant_ddl parameter to OFF and ensure the table has a primary key.

View or delete auto-created IMCIs

SELECT * FROM  information_schema.imci_autoindex_executed;

The process for deleting an IMCI created by autoindex is the same as for one created manually:

ALTER TABLE t1 comment 'columnar=0';

ALTER TABLE performance with IMCI

Adding or dropping columns typically involves rebuilding the table data. If the table has an IMCI, the IMCI data must also be rebuilt. This rebuilding process writes to the Redo log. Because an IMCI often covers many columns, the amount of Redo log data generated is proportional to the size of the original table data. This results in a higher I/O volume compared to rebuilding a table without an IMCI, causing the operation to take longer.

Impact on write performance

Creating an IMCI has a minimal impact on write performance, typically less than 5%. Sysbench tests using the oltp_insert workload show a performance decrease of about 3% after an IMCI is created.

Supported transaction isolation levels

IMCI supports the READ_COMMITTED and REPEATABLE_READ transaction isolation levels.

Fuzzy query acceleration

Yes. IMCI provides excellent acceleration for fuzzy query use cases and supports operations like LIKE PRUMER, NGRAM LIKE, and SMID LIKE. In addition, the columnar full-text index feature also provides acceleration for fuzzy query use cases.