When you use Lindorm SQL to query data in wide tables or create indexes, errors may occur or queries may fail to meet the performance expectation. This topic provides solutions to the questions that are frequently asked during the usage of Lindorm SQL.
The questions and solutions described in this topic are applicable only to LindormTable.
How do I resolve or avoid inefficient queries?
What is an inefficient query? What are the characteristics of inefficient queries?
If a query needs to scan the entire table to obtain the results because the conditions specified in the query cannot effectively use existing primary keys or indexes, the query is considered inefficient.
If LindormTable returns the following error message after a query statement is executed, the query is an inefficient query: This query may be a full table scan and thus may have unpredictable performance. For example, a table named test contains a primary key that includes the p1, p2 and p3 columns, in which p1 is the first primary key column. The following statement is executed to query data from the table: SELECT * FROM test WHERE p2=10;. The WHERE clause in the statement does not contain conditions on the first primary key column p1. Therefore, this query is considered inefficient.
By default, Lindorm detects and blocks inefficient queries because these queries may degrade the performance stability of databases.
Why is a query considered inefficient even if conditions on primary key columns or index columns are specified in the WHERE clause?
The columns of primary keys and secondary indexes in LindormTable are matched based on the same principle as federated indexes in MySQL. In a primary key or index that contains multiple columns, the conditions specified in a query statement are matched from the first (leftmost) column of the primary key or index. If the conditions of a query do not contain the first column of the primary key or index, the query does not hit the primary key or the secondary index. In this case, the query is also considered inefficient.
For example, the primary key of a table named test contains the p1, p2, and p3 columns and p1 is the first column. LindormTable matches the conditions of a query from the p1 column. If you execute the SELECT * FROM test WHERE p2<30; statement to query data, the query does not hit the primary key and is performed on all data in the table because the first primary key column p1 is not contained in the query conditions.
How do I avoid inefficient queries?
We recommend that you perform the following operations to avoid inefficient queries in your business:
Add conditions on the table primary key in the WHERE clause or ensure that the query conditions hit the first primary key column or index column of the table.
Redesign the primary key of the table to avoid queries that need to scan large amounts of data. For more information, see Design primary keys for Lindorm wide tables.
Create secondary indexes for the table. For more information, see Secondary indexes.
If multi-dimensional queries are performed on multiple columns of a table, you can create a multi-dimensional search index for the table to accelerate the queries. For more information, see SearchIndex.
Add the following hint to an inefficient query statement to forcibly execute the query:
/*+ _l_allow_filtering_ */. Example:SELECT /*+ _l_allow_filtering_ */ * FROM dt WHERE nonPK=100;ImportantIf you forcibly execute an inefficient query, the performance stability of the database may be degraded. Exercise caution when you perform this operation.
What do I do if the 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 error is reported when I use the GROUP BY clause in a query?
Causes:
The GROUP BY operation separates the query results into a large number of groups, which consume large amounts of memory and increase the load on the instance. Therefore, the query is limited by the LindormTable.
Solution:
Add filter conditions to the query statement to reduce the number of groups in the query results.
Contact the technical support (DingTalk ID: s0s3eg3) to increase the maximum number of groups in the query results.
ImportantIf the maximum number of groups in the query results is increased, the instance may become unstable in performance.
For multi-dimensional queries, we recommend that you use search indexes. For more information, see Overview.
What do I do if the Limit of this select statement is not set or exceeds config when select all columns from table with property DYNAMIC_COLUMNS=true error is reported when I execute a SELECT * query on a table with dynamic columns enabled?
Causes:
A table with dynamic columns enabled is not fixed in schema and may contain a large number of dynamic columns. Queries performed on all data in such tables consume a large number of I/O resources and increase the load on the instance. Therefore, LindormTable limits the queries performed on tables with dynamic columns enabled.
Solution:
Add the LIMIT clause to the SELECT statement to limit the number of returned data rows. Example: SELECT * FROM test LIMIT 10;
What do I do if the Executing job number exceed, max job number = 8 error is reported when I create a secondary index?
Causes:
Only eight secondary indexes can be created for an instance at the same time. If eight secondary indexes are being created at a point of time, you cannot create another secondary index.
Solution:
We recommend that you do not create a large number of secondary indexes at the same time. To create a large number of secondary indexes, contact the technical support (DingTalk ID: s0s3eg3).
What do I do if the column is under deleting error is reported when I add a column after deleting an existing column with the same name?
Causes:
After you delete a column, LindormTable asynchronously cleans up the data of the column from the memory, hot storage, and cold storage to avoid dirty data caused by factors such as data types. Before the data of the column is completely deleted, you cannot add a new column with the same name.
Solutions:
The data of a deleted column is automatically cleaned up, which may take a long time. We recommend that you perform the FLUSH or COMPACT operation to accelerate the data cleanup. After the data is completed cleaned up, you can add a new column with the same name as the deleted column.
In the following sample statements, the name of the column that needs to be deleted is dt:
-- Perform the FLUSH operation to forcibly flush data in the memory to the storage.
ALTER TABLE dt FLUSH;
-- Perform the COMPACT operation to merge and delete data.
ALTER TABLE dt COMPACT;The
FLUSHsyntax is supported in Lindorm SQL 2.7.1 and later versions. For more information about how to view the version of Lindorm SQL, see SQL versions.The
FLUSHoperation andCOMPACToperation are performed asynchronously. The data cleanup may still be in process even if the preceding sample statements are successfully executed. You may still need to wait until the data is completely cleaned up.If you perform the
COMPACToperation on a table that contains large amounts of data, a large number of system resources are consumed. Therefore, we recommend that you perform the COMPACT operation during off-peak hours.
What do I do if the Performing put operations with User-Defined-Timestamp in indexed column on MULTABLE_LATEST table is unsupported error is returned when I write data to a secondary index that I just created?
Causes:
If you explicitly specify a custom timestamp when you write data, the mutability between the base table and secondary index must be MULTABLE_ALL. For example, if you use the /*+ _l_ts */ hint to specify a custom timestamp when you execute an UPSERT statement to write data, the MUTABILITY attribute value of both the base table and the secondary index must be MULTABLE_ALL. However, the mutability of the base table and secondary index is set to MULTABLE_LATEST by default for better performance. In this case, an error is returned when you create and use a secondary index due to the constraints on mutability.
Solutions:
The MUTABILITY attribute value of an index cannot be modified after the index is created. Therefore, you must delete the secondary index that you have created.
Delete the existing secondary index from the base table.
-- Disable the existing secondary index. ALTER INDEX IF EXISTS <Existing secondary index name> ON <Base table name> DISABLED; -- Delete the existing secondary index. DROP INDEX IF EXISTS <Existing secondary index name> ON <Base table name>;For more information about the
DROP INDEXsyntax, see Delete a secondary index.Set the MUTABILITY attribute of the base table to
MUTABLE_ALL.ALTER TABLE IF EXISTS <Base table name> SET MUTABILITY='MUTABLE_ALL';Create a new secondary index and write data to the index. For more information about the syntax used to create a secondary index, see CREATE INDEX.
NoteFor more information about how to specify custom timestamps when you write data, see Use hints to implement data versioning.
For more information about the constraints on the mutability of secondary indexes and their relationship between custom timestamps, see Index updates based on custom timestamps.