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.

Prerequisites

The Archive Database is supported by only PolarDB for MySQL8.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 Create a PolarDB for MySQL cluster.

1

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));

Run the show create table command to view the details of the statement:

2

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.

Note
  • 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

  • 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

    (LONGBLOB/LONGTEXT/JSON)

    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:
    • stmt
    • row
    • mixed
    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 middle feature. This way, X-Engine internally commits the transaction and starts a sub-transaction to continue to perform the transaction. However, the commit in middle feature 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 middle feature. 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 middle 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 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 the commit in middle feature. 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)

Parameters

Note
  • 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 prefix 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
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. 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