All Products
Search
Document Center

ApsaraDB RDS:Change the storage engine from InnoDB, TokuDB, or MyRocks to X-Engine

Last Updated:Oct 27, 2023

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 change 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. X-Engine is widely used in a number of business systems of Alibaba Group to reduce costs. These business systems 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 the following topics:

Note

This topic describes how to change the storage engine of an existing table from InnoDB, TokuDB, or MyRocks to X-Engine. When you create an RDS instance that is designed to run MySQL 8.0, we recommend that you specify X-Engine as the default storage engine. After the RDS instance is created, you can also specify X-Engine for the RDS instance by setting the engine parameter to X-Engine. For more information, see Usage notes.

Usage notes

  • If the existing table uses InnoDB, make sure that the remaining disk space of your RDS instance is twice the data volume of the table. After the storage engine is changed to X-Engine, the disk space that is occupied by the table decreases to 10% to 50% of the original disk space that is occupied by the table before the change.

  • If you use Solution 1 in this topic, you must reconfigure parameters and restart your RDS instance. We recommend that you stop your database service before you change the storage engine.

  • If you use Solution 2 in this topic 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 the operation during off-peak hours.

  • Before you change the storage engine, make sure that X-Engine is compatible with SQL.

  • We recommend that you change the value of the default_storage_engine parameter to xengine after the change. This ensures that all newly created tables use X-Engine.

Solution recommendations

  • If your RDS instance runs MySQL 8.0 with a minor engine 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 minor engine version of your RDS instance does not meet your business requirements, you can perform the following operations to update the minor engine version: Go to the Basic Information page of your RDS instance and click Update Minor Engine Version in the Configuration Information section. If Update Minor Engine Version is not displayed, you are using the latest minor engine version. For more information, see Update the minor engine version.

  • If your RDS instance runs a different major engine version, such as MySQL 5.6 or MySQL 5.7, we recommend that you use Solution 2.

Solution 1

This solution allows you to enable X-Engine by using a parameter template. Then, you can execute DDL statements to change the storage engine to X-Engine. This solution is easy and fast. However, this solution triggers an instance restart. The engine change process temporarily blocks write operations but does not affect your queries.

Warning

This operation triggers an instance restart. After the restart, 95% of the memory resources are allocated to X-Engine. Do not use X-Engine and InnoDB at the same time.

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
  2. In the left-side navigation pane of the page that appears, click Parameters.

  3. In the upper-left corner of the Modifiable Parameters tab, click Apply Template. In the dialog box that appears, select MySQL_8.0_X-Engine_High-availability_Default Parameter Template and click OK.

  4. Use Data Management (DMS) to log on to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

  5. Execute the following statement to change the storage engine of a table on the SQL Console tab:

    alter table <The name of the database in which the table resides>.<The name of the table> engine xengine;

    Examples

    alter table test.sbtest1 engine xengine;

Solution 2

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

Note

The new RDS instance inherits the storage engine of your RDS instance by default. You must export the SQL statements that are used to create tables. In addition, you must change the storage engine to X-Engine in these SQL statements. Then, you can migrate the data to the new X-Engine tables.

  1. Use DMS to log on to your RDS instance and export all the schemas of your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance and Export databases.

  2. Decompress the schemas and change the storage engine from InnoDB or TokuDB to X-Engine. The following figure shows how to change the storage engine from InnoDB to X-Engine. You can use the same method to change the storage engine from TokuDB to X-Engine.

    修改脚本
  3. Create an RDS instance that runs MySQL 8.0 and has the same specifications as your original RDS instance. Make sure that you select the X-Engine parameter template.

    Note

    When you create the 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. Use DMS to log on to the new RDS instance and import the schemas into the new RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance and Import data.

  5. Synchronize data from your RDS instance to the new RDS instance. For more information, see Configure two-way synchronization between MySQL databases.

    Important

    In the Advanced Settings step, do not select Initial Schema Synchronization.

Result

After the synchronization is complete, you can check whether the data synchronization is successful. Then, you can test the compatibility between X-Engine and SQL. If X-Engine is compatible with SQL, you can change the storage engine of tables to X-Engine.