X-Engine usage - PolarDB - Alibaba Cloud Documentation Center

Change table engine to X-Engine

Execute the following statement to change a table's engine from InnoDB to X-Engine.

ALTER TABLE <database name>.<table name> ENGINE xengine;

Note

The ALTER statement blocks write operations (DML and DDL) on the table during the engine conversion.

Create an X-Engine table

To create an X-Engine table in a cluster that uses both X-Engine and InnoDB, specify X-Engine as the storage engine. Sample statement:

CREATE TABLE t1(c1 int primary key , c2 int) ENGINE=xengine;

Adjust memory resource allocation

You can adjust the memory resource ratio based on your business needs on the X-Engine (Warm Data) tab of the cluster's Settings and Management > Data Lifecycle page.

The following table provides recommended memory allocations for three common use cases. Adjust the ratio based on your workload.

Use case	InnoDB memory (%)	X-Engine memory (%)
Use InnoDB for hot data and X-Engine for archived cold data that is rarely accessed.	80	20
Use InnoDB for hot data and X-Engine for archived cold data that is still frequently updated or queried.	50	50
Use InnoDB for a few tables and X-Engine for many tables where data is frequently updated or queried.	20	80

Limitations

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

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
index	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.
character set	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)

Parameters

You can modify the parameters in the table below based on your business requirements. For detailed instructions, see Set cluster and node parameters.

The method for modifying PolarDB cluster parameters differs between the console and a database session. The differences are as follows:

In the PolarDB console
- Compatibility: Some cluster parameters in the PolarDB console have the loose_ prefix for compatibility with MySQL configuration files.
- Procedure: Find and modify the parameters that have the loose_ prefix.
In a database session (using the command line or a client)
- Procedure: When you connect to the database and use the SET command to modify a parameter, remove the loose_ prefix and use the original parameter name.

Category	Parameter	Description	Modifiable	Restart required
Performance	xengine_batch_group_max_group_size	The maximum number of groups in a transaction pipeline.	No	N/A
	xengine_batch_group_max_leader_wait_time_us	The maximum wait time for the transaction pipeline.	No	N/A
	xengine_batch_group_slot_array_size	The maximum batch size of the transaction pipeline.	No	N/A
	xengine_parallel_read_threads	The number of threads used for parallel scans.	Yes	No
	xengine_parallel_wal_recovery	Controls whether to enable parallel WAL recovery.	No	N/A
Memory	xengine_block_cache_size	The size of the block cache for table data and indexes.	Yes	No
	xengine_row_cache_size	The size of the row cache.	No	N/A
	xengine_write_buffer_size	The maximum size of a single memtable.	No	N/A
	xengine_block_size	The size of a data block on disk.	No	N/A
	xengine_db_write_buffer_size	The total size limit of active memtables across all subtables.	No	N/A
	xengine_db_total_write_buffer_size	The total size limit of active and immutable memtables across all subtables.	Yes	No
	xengine_scan_add_blocks_limit	The number of blocks that can be added to the block cache for each range scan request.	Yes	No
Compaction	xengine_flush_delete_percent_trigger	The number of records that triggers a memtable switch.	No	N/A
Lock	xengine_max_row_locks	The maximum number of rows a single SQL request can lock.	No	N/A
Lock	xengine_lock_wait_timeout	The lock wait timeout period.	Yes	No