All Products
Search
Document Center

PolarDB:Usage notes

Last Updated:Mar 08, 2024

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

    Note

    The default binary log format is the RAW format. The stmt and mixed formats may cause data security issues in specific concurrency scenarios.

    Note

    By 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, the commit 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 thecommit 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

  1. Connect to the database by using DMS, a client, or a CLI. For more information, see Connect to a cluster.

  2. 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;
    Note

    When 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.

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

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