Enable X-Engine

Updated at:

This topic describes how to enable the X-Engine Edition, as well as the conditions and precautions for the enabling process.

Prerequisites

  • Engine version must meet the following requirements:

    • 8.0.1.1.31 or later.

    • 8.0.2.2.12 or later.

    To check the cluster version, see Checking the engine version.

  • Multi-master Cluster (Limitless) does not support enabling the X-Engine Edition.

  • The specification of the read-write (RW) node of the cluster must be at least 8 GB. The specifications of the read-only (RO) nodes in the cluster must be greater than or equal to the specification of the RW node.

Usage notes

  • X-Engine Edition has limitations on engine features and large transactions.

    Limits

    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, utf8_unicode_ci)

    • utf8mb4 (utf8mb4_0900_ai_ci, utf8mb4_general_ci, utf8mb4_bin, utf8mb4_unicode_ci)

    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 X-Engine Edition automatically restarts the cluster. During the restart, transient connection interruptions of up to 30 seconds occur. As a best practice, perform this operation during off-peak hours and make sure that your application has a reconnection mechanism.

  • After you enable the X-Engine Edition:

    • To avoid access exceptions for tables already stored in the X-Engine Edition, this feature cannot be disabled.

    • To join a Global Database Network (GDN), the cluster must run engine version 8.0.2.2.37 or later.

Parameters

After you enable the X-Engine Edition, if you need to archive the partitions of a partitioned table to the X-Engine format, you must manually set the following kernel parameters on the Parameters page of the cluster. For the complete parameter descriptions and usage limits of hybrid partitions, see Create a hybrid partition.

Parameter

Level

Description

loose_polar_allow_create_hybrid_partition

Global

Specifies whether to allow creating or archiving partitioned tables that contain partitions of engines other than InnoDB. Valid values:

  • OFF (default): Creating partitioned tables that contain partitions of engines other than InnoDB is not allowed. All partitions must use the InnoDB engine.

  • ON (recommended): Creating partitioned tables that contain partitions of engines other than InnoDB is allowed. You can specify the X-Engine engine at the partition level.

Note

The preceding parameters apply only to clusters of version 8.0.2 with revision version 8.0.2.2.34.1 or later.

Procedure

You can enable the high-compression engine (X-Engine) by using either of the following two methods.

Method 1: Enable the high-compression engine (X-Engine) with one click on an existing cluster

  1. Log in to the PolarDB console,In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。

  2. In the left-side navigation pane, choose Settings and Management > Data Lifecycle, and on the X-Engine (Warm Data) tab, click Enable.

  3. Note
    • If you cannot see the X-Engine (Warm Data) tab, check whether your cluster meets the Prerequisites.

    • After the high-compression storage engine is enabled for the cluster, it cannot be disabled. Enabling it requires restarting the cluster, during which 1 to 2 transient connection interruptions of up to 30 seconds occur.

  4. In the dialog box, you can customize the memory resource proportion of the X-Engine Edition based on your business scenario, and then click Enable Now.

    The following table lists the memory resource proportion configurations of storage engines in three typical scenarios for your reference. You can configure the proportions based on your actual business scenario.

    Applicable scenario

    InnoDB engine memory resource proportion (%)

    X-Engine memory resource proportion (%)

    The InnoDB engine stores hot data, the X-Engine engine stores cold data, and the archived cold data is rarely accessed.

    80

    20

    The InnoDB engine stores hot data, the X-Engine engine stores cold data, and the archived cold data is still updated or queried.

    50

    50

    The InnoDB engine stores a small number of tables, the X-Engine engine stores a large number of tables, and the data is updated or queried.

    20

    80

    Note
    • After the high-compression engine (X-Engine) is enabled, you can adjust the memory resource proportion at any time based on your business scenario on the Settings and Management > Data Lifecycle page of the cluster, on the X-Engine (Warm Data) tab.

    • The memory resource proportion of X-Engine can be set in the range of 10% to 90%. If you use the X-Engine engine to store all tables and have no InnoDB engine tables, you can set the X-Engine memory resource proportion to 90%.

Method 2: Select the high-compression engine (X-Engine) when you purchase a new cluster

When you purchase a new PolarDB for MySQL cluster, you can set the storage engine to InnoDB & X-Engine, and use the slider at X-Engine Memory Usage to set the storage proportion between X-Engine and InnoDB. The range is 10% to 90%. For example, when the value is set to 50%, the prompt below shows X-Engine:4 GB;InnoDB:4 GB. See Purchase a cluster for more information about the Storage Engine option.

Note

After the high-compression engine (X-Engine) is enabled, you can adjust the memory resource proportion at any time based on your business scenario on the Settings and Management > Data Lifecycle page of the cluster, on the X-Engine (Warm Data) tab.