ApsaraDB RDS for MySQL 8.0 supports X-Engine. X-Engine provides better data compression capabilities and reduces disk space costs. This topic describes how to convert the storage engine from InnoDB, TokuDB, or MyRocks to X-Engine.

Background information

X-Engine is an online transaction processing (OLTP) database storage engine that is developed by Alibaba Cloud to suit the needs of PolarDB. This storage engine now is widely used in a number of business systems of Alibaba Group to reduce costs. These include the transaction history database and DingTalk chat history database. In addition, X-Engine is a crucial database technology that empowers Alibaba Group to withstand bursts of traffic that may surge by hundreds of times than usual during Double 11, a shopping festival in China.

For more information, see Best practices of X-Engine.

Note When you create an RDS instance designed to run MySQL 8.0, we recommend that you specify X-Engine as the default storage engine. You can also specify X-Engine for the RDS instance after instance creation by setting the engine parameter to xengine.
Default storage engine

Precautions

  • If the table you want to convert uses InnoDB, make sure that the remaining disk space of your RDS instance is twice the data volume of the table before the conversion. After the conversion to X-Engine, the disk space occupied by the data in the table decreases to 10% to 50% of the disk space occupied before the conversion.
  • If you use solution 1 described in the following sections to perform the conversion, you must reconfigure parameters and restart your RDS instance. Stop your database services before you perform the conversion.
  • If you use solution 2 described in the following sections to migrate all data from your RDS instance to a new RDS instance, you must update the endpoints on your application. We recommend that you perform this migration during off-peak hours.
  • Before you convert the storage engine of your online business, make sure that X-Engine is compatible with SQL.
  • Change the value of the default_storage_engine to xengine after the conversion. This ensures that all of the tables created later use X-Engine.

Solution recommendations

  • If your RDS instance runs MySQL 8.0 (with a kernel version of 20200229 or later), we recommend that you use Solution 1. This way, you do not need to configure various tools.
    Note If the kernel version of your RDS instance does not meet requirements, you can update the kernel version on the Basic Information page. In the Configuration Information section, check whether the Upgrade Version button exists. If the button exists, click it to view and update the kernel version. If the button does not exist, you are already using the latest kernel version. For more information, see Update the kernel version of an ApsaraDB RDS for MySQL instance..
  • If your RDS instance runs MySQL 5.6 or 5.7, we recommend that you use Solution 2.

Solution 1

This solution allows you to enable X-Engine by using a parameter template and then use DDL statements to convert the storage engine to X-Engine. This solution is easy and fast, but requires you to restart your RDS instance. In addition, data manipulation language (DML) operations may be blocked, and migrating large-sized tables is time-consuming.

  1. Log on to the ApsaraDB for RDS console.
  2. In the top navigation bar, select the region where the target RDS instance resides.
    Select a region
  3. Find the target instance and click its ID.
  4. In the left-side navigation pane, click Parameters.
  5. In the upper-left corner of the Editable Parameters tab, click Apply Template. In the dialog box that appears, select MySQL_8.0_X-Engine_High-availability_Default Parameter Template from the Apply Template drop-down list and click OK.
    Note This operation restarts the instance. After the restart, 95% of the memory resources are allocated to X-Engine. Do not use X-Engine and InnoDB at the same time.
    Convert the storage engine
  6. Use DMS to log on to an ApsaraDB for RDS instance.
  7. In the top navigation bar, choose SQL Operations > SQL Window.
  8. Run the following command:
    alter table <The name of the target database>. <The name of the target database> engine xengine;

    Example:

    alter table test.sbtest1 engine xengine;

Solution 2

This solution allows you to synchronize the data of a table in real time from your original RDS instance to a new RDS instance by using Alibaba Cloud Data Transmission Service (DTS). After the data synchronization is complete, you can switch your business to the new RDS instance.

Note The new RDS instance inherits the storage engine of your original RDS instance by default. You must export the SQL statements for table creations, and change the storage engine to X-Engine in the SQL statements. Then, migrate the data to the new X-Engine table.
  1. Perform the following steps to export scripts of all schemas of the original RDS instance.
    1. Log on to the original RDS instance by using Alibaba Cloud Data Management (DMS).
    2. In the top navigation bar, choose Data Operation > Export.
    3. Choose New > Export Database.
    4. Configure parameters as prompted to export scripts of all schemas and click OK. In the dialog box that appears, click Yes.
      Note The dialog box is displayed because Extended Options is selected in the Advanced Options dialog box. You can ignore it.
      Export a schema
  2. Decompress the schema scripts and change InnoDB or TokuDB to X-Engine.
    Modify schema scripts
  3. Purchase a new RDS instance that runs MySQL 8.0 and has the same specifications as the original RDS instance. Make sure that you select the X-Engine parameter template.
    Note When you create an RDS instance, you can use the default X-Engine parameter template to specify X-Engine as the default storage engine. For more information, see Create an ApsaraDB RDS for MySQL instance.
  4. Perform the following steps to import the schema scripts into the new RDS instance.
    1. Log on to the new RDS instance by using DMS.
    2. In the top navigation bar, choose Data Operation > Import.
      Import
    3. On the page that appears, click New Task.
    4. In the dialog box that appears, configure parameters as prompted and click Start.
      Import scripts
      Note After the scripts are imported, you can run the show create table <The name of the target table>; command to verify that the table uses the X-Engine storage engine.
  5. Synchronize data from the original RDS instance to the new RDS instance. For more information, see Configure two-way data synchronization between ApsaraDB RDS for MySQL instances.
    Warning Do not select Initial schema synchronization during synchronization initialization.

Result

After the synchronization is complete, you can check whether the data synchronization is successful. You can then test whether X-Engine is compatible with SQL. If X-Engine is compatible with SQL, you can convert the storage engine of your online business to X-Engine.