Lindorm:Lindorm SQL FAQ

How do I resolve or avoid inefficient queries?

Why does a GROUP BY query fail with the error The diff group keys of subPlan is over lindorm.aggregate.subplan.groupby.keys.limit=..., it may cost a lot memory so we shutdown this SubPlan?

Cause:

The number of groups created by the GROUP BY operation is too large. This operation can consume excessive memory resources and increase the instance load. Therefore, LindormTable limits queries that produce many groups in the result set.

Solution:

• Add filter conditions to the query statement to reduce the final number of groups.

• Contact Lindorm technical support (DingTalk ID: s0s3eg3) to increase the threshold for the number of groups.

  Important

  Increasing the threshold for the number of groups might affect the stability of the instance.

• For multidimensional query scenarios, we recommend using a search index. For more information, see Introduction to search indexes.

Why does a SELECT * query on a table with dynamic columns enabled fail with the error Limit of this select statement is not set or exceeds config when select all columns from table with property DYNAMIC_COLUMNS=true?

Cause:

A table with dynamic columns enabled may contain many dynamic columns, and its schema is not fixed. If you perform a full table scan on such a table, this can cause high I/O consumption and increase the instance load. To prevent high load situations, LindormTable places restrictions on query statements for tables with dynamic columns.

Solution:

Add a LIMIT clause to the SELECT statement to limit the number of results returned. For example: SELECT * FROM test LIMIT 10;.

Why does creating a secondary index fail with the error Executing job number exceed, max job number = 8?

Cause:

An instance allows a maximum of eight secondary index build tasks to run concurrently. If eight build tasks are already running, any new attempt to create a secondary index will fail with this error.

Solution:

Avoid creating many secondary indexes simultaneously. To create many indexes, contact Lindorm technical support (DingTalk ID: s0s3eg3).

After deleting a column in LindormTable, why does re-adding a column with the same name fail with the error column is under deleting?

Cause:

To prevent dirty data issues caused by factors such as data types, LindormTable asynchronously cleans up the column's data from memory, hot storage, and cold storage after you delete the column. The system does not allow you to add a new column with the same name until the cleanup process is complete.

Solution:

The data cleanup is performed by the system and can take a long time. You can use the following methods to accelerate the cleanup process. After the cleanup is complete, you can add the column with the same name again.

Assume that the table from which the column was deleted is named dt:

-- Execute the FLUSH operation to force residual data in memory to be flushed to the storage medium.
ALTER TABLE dt FLUSH;

-- Execute the COMPACTION operation to merge and delete data.
ALTER TABLE dt COMPACT;

After creating a secondary index, why does writing data fail with the error Performing put operations with User-Defined-Timestamp in indexed column on MULTABLE_LATEST table is unsupported?

Cause:

If you explicitly specify a custom timestamp when writing data (for example, using the /*+ _l_ts */ HINT in an UPSERT statement), the mutability of both the primary table and the secondary index table must be MULTABLE_ALL. However, for performance reasons, Lindorm defaults the mutability of the primary and index tables to MULTABLE_LATEST. Creating and enabling a secondary index with this configuration triggers a mutability constraint violation, which causes the error.

Solution:

The value of the MUTABILITY parameter cannot be modified after an index table is created. You must first delete the original secondary index.

1. Delete the original secondary index from the primary table.

   -- Disable the original secondary index.
   ALTER INDEX IF EXISTS <original_secondary_index_name> ON <primary_table_name> DISABLED;
   
   -- Delete the original secondary index.
   DROP INDEX IF EXISTS <original_secondary_index_name> ON <primary_table_name>;

   For more information about the DROP INDEX syntax, see Delete a secondary index.

2. Change the value of the MUTABILITY property of the primary table to MUTABLE_ALL.

   ALTER TABLE IF EXISTS <primary_table_name> SET MUTABILITY='MUTABLE_ALL';

3. Create a new secondary index and write data. For more information about the syntax for creating a secondary index, see CREATE INDEX.

   Note

   • For more information about how to write data with a custom timestamp, see Use HINTs to set timestamps for multi-version data management.

   • For more information about the relationship between secondary index mutability constraints and custom timestamps, see Update an index with a custom timestamp.

Why does an SQL query fail with the error Code grows beyond 64 KB?

Cause:

The Lindorm SQL engine uses Just-In-Time (JIT) compilation to execute queries. It dynamically generates bytecode from the physical plan of a query and then compiles and executes it. The Code grows beyond 64KB error indicates that the size of the bytecode for the generated method exceeds the limit allowed by the Java Virtual Machine (JVM). This can happen if a predicate in the SQL query statement is too long or complex, resulting in bytecode that is too large to be executed.

Solution:

Modify the SQL statement to simplify the relevant predicate expressions.

Why does an SQL query fail with the error The estimated memory used by the query exceeds the maximum limit?

Cause:

The SQL engine often consumes large amounts of memory resources when it processes result sets returned from the storage engine, such as during aggregation, sorting, or deduplication. Lindorm SQL is designed for online business scenarios where many queries may run concurrently. To ensure query efficiency in high-concurrency scenarios, the system limits the memory usage of a single query. The current default limit is 8 MB. If this limit is exceeded, a memory overflow exception is triggered.

Solution:

• Optimize the query statement. You can push down operators, such as aggregation and sorting, to the storage engine using indexes. You can also optimize filter conditions to reduce the amount of data that the SQL engine needs to process.

  Note

  You can check the execution plan to see if operators such as aggregation and sorting are pushed down to the storage engine or executed by the SQL engine. For more information, see Interpret an execution plan.

• Adjust the `QUERY_MAX_MEM` memory limit threshold. After you fully evaluate the query throughput, you can adjust the `QUERY_MAX_MEM` configuration item using the ALTER SYSTEM statement. For example: ALTER SYSTEM SET QUERY_MAX_MEM = 8388608;. If your SQL engine version is earlier than 2.9.6.0, contact Lindorm technical support (DingTalk ID: s0s3eg3) to increase the memory limit threshold.

  Important

  If an online application has high query concurrency, increasing this memory limit threshold can increase the memory load on Lindorm. This, in turn, can trigger behaviors such as a forced Full GC and reduce the responsiveness of the entire cluster. Therefore, perform a thorough evaluation before you increase this memory limit. You can retrieve the current value of QUERY_MAX_MEM using the SHOW VARIABLES statement.