Enable X-Engine

Prerequisites

• Kernel version must meet the following conditions:

  • 8.0.1.1.31 or later.

  • 8.0.2.2.12 or later.

  To check your cluster version, see Query the kernel version.

• Multi-master Cluster (Limitless) does not support the High-Compression Engine (X-Engine).

• The cluster's primary node must have at least 8 GB of memory, and each read-only node must have specifications equal to or greater than those of the primary node.

• The cluster is not part of a global database network (GDN).

Usage notes

• The high-compression engine (X-Engine) has limitations related to its engine and large transaction functionality.

  Limitations

  Engine feature limitations

  Category	Feature	Description
  SQL features	foreign key	Not supported
  	temporary table	Not supported
  	generated column	Not supported
  	handler API	Not supported
  Columns and column properties	Maximum number of columns per table	10,000
  	maximum record length	256 MB
  	GIS data type	X-Engine does not support any GIS-related data types, including geometry, point, linestring, polygon, multipoint, multilinestring, multipolygon, and geometrycollection.
  index	hash index	Not supported
  	spatial index	Not supported. Features for full-text indexes are also not supported.
  transaction	transaction isolation level	The following two isolation levels are supported: Read committed (RC) Repeatable read (RR)
  	Maximum rows per transaction	By default, a single transaction can contain up to 100,000 rows. You can change this limit by using the loose_xengine_bulk_load_size parameter.
  	savepoint	Not supported
  	XA transaction	Internal XA transactions are supported.
  lock	lock granularity	Table-level locks and row-level locks are supported. Gap locks are not supported.
  	Skip Locked	Not supported
  	Lock Nowait	Not supported
  character set	Supported character sets for non-indexed columns	All character sets and collations are supported for non-indexed columns.
  	Supported character sets for indexed columns	latin1 (latin1_bin) gbk (gbk_chinese_ci, gbk_bin) utf8 (utf8_general_ci, utf8_bin) utf8mb4 (utf8mb4_0900_ai_ci, utf8mb4_general_ci, utf8mb4_bin)
  primary/secondary replication	binary log format	The following three formats are supported: stmt row mixed Note The default format is row. Using the stmt or mixed formats can compromise data security in specific high-concurrency scenarios.

  Note

  For any features not listed in this topic, X-Engine behaves identically to InnoDB.

  Large transaction limitations

  X-Engine does not support large transactions. When a transaction modifies 10,000 or more rows, X-Engine enables the commit in middle feature. This feature causes X-Engine to internally commit the current transaction and start a new sub-transaction to continue the operation. However, commit in middle does not strictly guarantee transactional atomicity. Be aware of the following implications:

  • If you start a transaction to insert a large volume of data, the commit in middle feature may commit a portion of the data before the transaction is complete. This partially committed data becomes visible to other concurrent transactions.

  • You cannot fully roll back a transaction that the commit in middle feature has partially committed.

    DROP TABLE t1;
    CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine;
    BEGIN;
    call insert_data(12000); // Insert 12,000 rows to trigger commit in middle. The first 10,000 rows are committed.
    rollback;// The rollback operation can only undo the last 2,000 rows.
    SELECT COUNT(*) FROM t1; // The query still finds the 10,000 committed rows.
    +----------+
    | COUNT(*) |
    +----------+
    |    10000 |
    +----------+
    1 row in set (0.00 sec)

  • Due to the commit in middle feature, if a transaction inserts and then deletes a large number of rows, the DELETE operation might not see the newly inserted rows.

    DROP TABLE t1;
    CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine;
    call insert_data(10000);
    BEGIN;
    INSERT INTO t1 VALUES(10001,10001), (10002,10002);
    DELETE FROM t1 WHERE c1 >= 0; // The DELETE operation triggers commit in middle, causing it to miss the rows inserted within the same transaction.
    commit;
    SELECT * FROM t1;
    +-------+-------+
    | c1    | c2    |
    +-------+-------+
    | 10001 | 10001 |
    | 10002 | 10002 |
    +-------+-------+
    2 rows in set (0.00 sec)

• Enabling the High-Compression Engine (X-Engine) automatically restarts the cluster. The restart process causes a connection interruption that lasts for up to 30 seconds. We recommend that you perform this operation during off-peak hours and ensure that your application has a reconnection mechanism.

• After you enable X-Engine:

  • To prevent access exceptions for tables stored in the high-compression engine (X-Engine), you cannot disable this feature.

  • The cluster cannot join a GDN.

Procedure

You can enable X-Engine in one of two ways.