PolarDB Archive Database uses X-Engine as the default storage engine instead of InnoDB. Archive Database has a high compression ratio and is applicable to services that do not have high requirements for computing but need to store archived data, such as DingTalk messages. This topic describes the Archive Database feature of PolarDB.
The Archive Database is supported by only PolarDB for MySQL 8.0. For more information, see Archive Database.
Create a PolarDB Archive Database (X-Engine)
You can create a PolarDB Archive Database in the same way you create a PolarDB cluster. You only need to set Compatibility to MySQL 8.0 and set Edition to Archive Database (High Compression Ratio) on the buy page. For more information, see Purchase a pay-as-you-go cluster.
Create tables in Archive Database
You can create tables in PolarDB Archive Database in the same way you create tables in a database that uses the InnoDB engine. The default engine for Archive Database is X-Engine. If no engine is specified when you create a table, a table that uses the X-Engine engine is created. For example, run the following command to create a table with no engine specified:
CREATE TABLE test_arc.t1 (id int PRIMARY KEY,c1 varchar(10));
show create table command to view the details of the statement:
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.
- You can also create InnoDB tables in PolarDB Archive Database. For example, when you use Data Transmission Service (DTS) to migrate data, the tables to be migrated may still use InnoDB. To convert tables from InnoDB to X-Engine, see Convert tables from InnoDB, TokuDB, or MyRocks to X-Engine.
- You can run the following command to view the default engine of the current database:
show variables like '%default_storage_engine%';
- 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 much 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 X-Engine performance may deteriorate due to a low cache hit ratio. We recommend that you make sure that all tables use the X-Engine engine when you use PolarDB Archive Database. Otherwise, the performance may deteriorate.
- Limits on engine features
The following table describes the limits on X-Engine.
Category Feature Description SQL features Foreign key Not supported. Temporary table Not supported. Partition table Not supported. X-Engine does not support the creation, addition, deletion, modification, or query of partitions. Generated Column Not supported. Handler API Not supported. Column properties
Maximum column size
32MB GIS data type Not supported. X-Engine does not support the following GIS data types: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEMOMETRYCOLLECTION. Index Hash index Not supported. Spatial index Not supported. X-Engine does not support creating or using full-text indexes. Transaction Transaction isolation level 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 Available soon. Lock Lock granularity
- Supports table-level and row-level locks.
- GAP locks are not supported.
Skip Locked Not supported. Lock Nowait Not supported. Character set Character sets supported by non-indexed columns Supported. 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:
Note The default binary log format is the row format. The stmt and mixed formats may cause data security issues in specific concurrency scenarios.Note By default, the features that are not listed in X-Engine are the same as those in the InnoDB.
- Limits on large transactions
X-Engine does not support large transactions. When the number of rows modified in a transaction is equal to or greater than 10,000, X-Engine enables the
commit in middlefeature. This way, X-Engine internally commits the transaction and starts a sub-transaction to continue to perform the transaction. However, the
commit in middlefeature does not follow the atomicity of transactions in a strict sense. 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 submitted due to the
commit in middlefeature. Then, the inserted data can be queried by other requests.
- Assume that you want to start a transaction to modify more than 10,000 rows of data.
The transaction on which X-Engine enables
commit in middlecannot 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 be queried. +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec)
- Assume that you want to start a transaction to modify and delete a large amount of
data. The DELETE operation cannot read the inserted rows in this transaction due to
commit in middlefeature. Therefore, the newly inserted 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)
- 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 submitted due to the
- You can modify the table parameters based on your business requirements. For more information, see Specify cluster parameters.
- All parameters in the table have been added the MySQL configuration file compatibility
loose_in the PolarDB console.
|Category||Parameter||Description||Modifiable||Restart after the parameter 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|
|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.||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 timeout period of a lock wait.||Yes||No|