PolarDB X-Engine Edition uses X-Engine instead of InnoDB as the default storage engine. X-Engine Edition has a high compression ratio and is suitable for services that do not have high requirements for computing but need to store archived data, such as DingTalk messages. This topic describes how to use X-Engine for PolarDB.
Limits
Limits on resource allocations if X-Engine and InnoDB are used together
When you use X-Engine, 95% of the memory is used as the write cache and block cache to speed up reads and writes. The InnoDB buffer pool does not consume a large amount of memory. We recommend that you do not use tables that use InnoDB to store a large volume of data within a cluster that uses X-Engine. Otherwise, the performance of the cluster may deteriorate due to a low cache hit ratio. We recommend that you make sure that all tables use X-Engine when you use X-Engine for PolarDB.
Limits on features
The following table describes the limits on X-Engine.
Category
Feature
Description
SQL features
Foreign key
Not supported
Temporary table
Not supported
Generated Column
Not supported
Handler API
Not supported
Column properties
Maximum column size
(LONGBLOB/LONGTEXT/JSON)
32 MB
GIS data type
Not supported. X-Engine does not support the following GIS data types: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEMOMETRYCOLLECTION.
Indexing
Hash index
Not supported
Spatial index
Not supported. X-Engine does not support creation or use of full-text indexes.
Transaction
Transaction isolation levels
Supports the following two isolation levels:
Read Committed (RC)
Repeatable Read (RR)
The maximum data volume supported by a transaction
32 MB
Savepoint
Not supported
XA transaction
Not supported
Lock
Lock granularity
Supports table-level and row-level locks.
Gap locks not supported.
Skip Locked
Not supported
Lock Nowait
Not supported
Character set
Character sets supported by non-indexed columns
All
Character sets supported by indexed columns
Latin1 (latin1_bin)
GBK (gbk_chinese_ci and gbk_bin)
UTF-8 (utf8_general_ci and utf8_bin)
UTF-8MB4 (utf8mb4_0900_ai_ci, utf8mb4_general_ci, and utf8mb4_bin)
Primary/secondary replication
Binary log formats
Supports the following formats:
stmt
row
mixed
NoteThe default binary log format is the RAW format. The stmt and mixed formats may cause data security issues in specific concurrency scenarios.
NoteBy default, the features in X-Engine that are not listed in the preceding table are the same as those in InnoDB.
Limits on large transactions
X-Engine does not support large transactions. If the number of rows modified in a transaction is equal to or greater than 10,000, X-Engine enables the
commit in middle
feature. This way, X-Engine internally commits the transaction and starts a sub-transaction to continue to perform the transaction. However, thecommit in middle
feature may not ensure the atomicity of transactions. Therefore, note the following limits:Assume that you want to start a transaction to insert a large amount of data. During the insertion, a part of the data has been committed due to the
commit in middle
feature. Then, the committed data can be queried by other requests.Assume that you want to modify a large amount of data in a single transaction. If data has been partially committed due to the
commit in middle
feature, the transaction cannot be rolled back.DROP TABLE t1; CREATE TABLE t1(c1 int primary key , c2 int)ENGINE=xengine; BEGIN; call insert_data(12000); // 12,000 rows are inserted and a commit in middle operation is triggered. As a result, the first 10,000 rows of data are committed. rollback;// Only the last 2,000 rows can be rolled back. SELECT COUNT(*) FROM t1; // The committed 10,000 rows of data can still be queried. +----------+ | COUNT(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec)
Assume that you want to modify and delete a large amount of data in a transaction. The DELETE operation cannot read the committed rows in this transaction due to the
commit in middle
feature. Therefore, the newly committed data cannot be deleted.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 deletion triggers a commit in middle operation, and the two rows of data inserted by the current transaction are not deleted. commit; SELECT * FROM t1; +-------+-------+ | c1 | c2 | +-------+-------+ | 10001 | 10001 | | 10002 | 10002 | +-------+-------+ 2 rows in set (0.00 sec)
Convert tables from InnoDB to X-Engine
Connect to the database by using DMS, a client, or a CLI. For more information, see Connect to a cluster.
Execute the following statement to switch the storage engine of a table from InnoDB to X-Engine:
ALTER TABLE <database name>.<table name> ENGINE xengine;
NoteWhen you use the
ALTER
command to convert a table from InnoDB to X-Engine, the DML and DDL operations which write data in the table are blocked.
Create a table in an X-Engine Edition cluster
When you create a table in an existing X-Engine Edition cluster, X-Engine is the default storage engine. Sample statement:
CREATE TABLE test_arc.t1 (id int PRIMARY KEY,c1 varchar(10));
After you create the table, data is stored in X-Engine. You can use the table in the same way that you use a table in InnoDB.
NoteYou can also create InnoDB tables in an X-Engine Edition cluster. For example, if you use Data Transmission Service (DTS) to migrate data, the storage engine of the table may still be InnoDB. To convert the table from InnoDB to X-Engine, see Convert table from InnoDB to X-Engine.
You can run the following command to view the default engine of the current database:
SHOW VARIABLES LIKE '%default_storage_engine%';
To create an X-Engine table in a newly-purchased dual-engine (InnoDB and X-Engine) cluster, you need to specify X-Engine as the storage engine when you create the table. Sample statement:
CREATE TABLE t1(c1 int primary key , c2 int) ENGINE=xengine;
Parameters
You can modify the parameters that allow modifications based on your business requirements. For more information, see Specify cluster and node parameters.
All parameters in the table have been added the MySQL configuration file compatibility prefix
loose_
in the PolarDB console.
Category | Parameter | Description | Allow modification | Restart after modification |
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 pending time of a transaction pipeline. | No | N/A | |
xengine_batch_group_slot_array_size | The maximum batch size of a transaction pipeline. | No | N/A | |
xengine_parallel_read_threads | The number of parallel read threads. | Yes | No | |
xengine_parallel_wal_recovery | Parallel recovery. | No | N/A | |
Memory | xengine_block_cache_size | The size of the read block cache. | No | N/A |
xengine_row_cache_size | The size of the row cache. | No | N/A | |
xengine_write_buffer_size | The maximum size of a memory table. | No | N/A | |
xengine_block_size | The size of the data block on a disk. | No | N/A | |
xengine_db_write_buffer_size | The maximum size of the active memory tables in all subtables. | No | N/A | |
xengine_db_total_write_buffer_size | The maximum size of the active memory tables and immutable memory tables in all subtables. | No | N/A | |
xengine_scan_add_blocks_limit | The number of blocks that can be added to the block cache during each range-based scan request. | Yes | No | |
compaction | xengine_flush_delete_percent_trigger | If the number of records in a memory table exceeds the value of this parameter, the xengine_flush_delete_record_trigger parameter takes effect on the memory table. | No | N/A |
Lock | xengine_max_row_locks | The maximum number of rows that can be locked in a single SQL request. | No | N/A |
xengine_lock_wait_timeout | The time-out period of a lock wait. | Yes | No |