If an RDS instance is overloaded, you can vertically split a database or table from this instance into a separate instance. This topic describes how to use the dual-write solution and simple splitting solution to achieve vertical splitting.

Background information

Assume that Database A and Database B are deployed on an RDS instance. The instance is facing bottlenecks because of business growth. If you want to reduce the load on the instance, you can vertically split Database B into a separate instance. For more information, see, Dual-write solution and Simple splitting solution.

Precautions

  • You must create a separate instance as the destination instance. The database account of the destination instance must have the same permissions as the database account of the source instance. For more information about the supported destination instance types, see Overview of data migration scenarios.
    Note We recommend that you create and authorize a database account for the source and destination instances. This allows you to distinguish session information and improve data security.
  • You must add the connection string of the destination instance to the application.
  • If you use the Simple splitting solution, you must pause your business and stop writing data to the database for a short period of time. To minimize the impact on your business when you change and release a program or switch instances, we recommend that you split the database during off-peak hours.

Dual-write solution

Note You can implement phased switchover and minimize the impact on the business. However, you must transform the application so that the application data can be written to Database B on the source and destination instances.
  1. Configure a data migration task for the source and destination instances. Select Database B as the object to be migrated. For more information, see Overview of data migration scenarios.
    Note When you configure the data migration task, you must select Schema Migration, Full Data Migration, and Incremental Data Migration as the migration types.
  2. Wait until the task progress bar shows "Incremental Data Migration" and "The migration task is not delayed" or a delay time of less than 5 seconds.
    Data migration task 1 (Forward)
  3. Check whether the data in Database B is consistent between the source and destination instances.
  4. If the data is consistent, stop the data migration task. For more information, see Stop a data migration task.
    Warning The database accounts that are used for data migration have the read and write permissions. After data is migrated, you must delete the accounts or revoke the write permission to ensure security.
  5. Write the application data to Database B on the source and destination instances at the same time.
  6. Log on to Database B on the source and destination instances and execute one of the following statements to view the session information. Select the statement based on the database type. Make sure that write operations are being performed in a new session. 
    show processlist;
    select * from sys.dm_exec_connections;
    select sid,serial#,username,program,machine,status from v$session;
    select * from pg_stat_activity;
    CLIENT LIST
    use admin
db.runCommand({currentOp: 1, $all:[{"active" : true}]})

    Note The process or session information returned by the preceding statements includes the processes or sessions between DTS and Database B on the source and destination instances.
  7. Write the application data to Database B on the source and destination instances at the same time. Make sure that the source and destination instances keep running for a business period, for example, seven days.
  8. After you test all features related to your business and make sure that no issues are detected, shut down Database B on the source instance.

Simple splitting solution

Note If you use this solution, you do not need to edit the code of your application. However, a rollback failure may occur.
  1. Configure a data migration task for the source and destination instances. Select Database B as the object to be migrated. For more information, see Overview of data migration scenarios.
  2. Wait until the task progress bar shows "Incremental Data Migration" and "The migration task is not delayed" or a delay time of less than 5 seconds.
    Data migration task 1 (Forward)
    Note If you do not select Incremental Data Migration when you configure the data migration task, the task progress bar does not show "Incremental Data Migration". After data is migrated, the migration task automatically stops. In this case, you must pause your business and stop writing data to the source database before running the data migration task. Skip to Step 6 and proceed.
  3. Check whether the data in Database B is consistent between the source and destination instances.
  4. If the data is consistent, stop the data migration task. For more information, see Stop a data migration task.
    Warning The database accounts that are used for data migration have the read and write permissions. After data is migrated, you must delete the accounts or revoke the write permission to ensure security.
  5. Disconnect the application from Database B.
    Note If the business of Database A is affected after the application is disconnected from Database B, you must disconnect the application from the source instance.
  6. Log on to Database B on the source instance and execute one of the following statements to view the session information. Select the statement based on the database type. Make sure that no write operations are being performed in a new session. 
    show processlist;
    select * from sys.dm_exec_connections;
    select sid,serial#,username,program,machine,status from v$session;
    select * from pg_stat_activity;
    CLIENT LIST
    use admin
db.runCommand({currentOp: 1, $all:[{"active" : true}]})
    Note The process or session information returned by the preceding statements includes the process or session between DTS and Database B on the source instance.
  7. Create and start a data migration task in the opposite direction. The task migrates incremental data generated in Database B on the destination instance to Database B on the source instance.

    The data migration task created in this step provides a rollback solution. If an error occurs in the destination database, you can switch workloads to the source database.

    Warning When you configure a data migration task in the opposite direction, you must select only "Incremental Data Migration" in the "Configure Migration Types and Objects" step. Then, you must select the database or table to be migrated back to the source database.
  8. Make sure that the application is disconnected from Database B on the source instance. Verify that data is consistent between Databases B on the source instance and Databases B on the destination instance. Then, switch the database services to Databases B on the destination instance and resume your business.
  9. Log on to Database B on the destination instance and execute one of the following statements to view the session information. Select the statement based on the database type. Make sure that write operations are being performed in a new session. 
    show processlist;
    select * from sys.dm_exec_connections;
    select sid,serial#,username,program,machine,status from v$session;
    select * from pg_stat_activity;
    CLIENT LIST
    use admin
db.runCommand({currentOp: 1, $all:[{"active" : true}]})
    Note The process or session information returned by the preceding statements includes the process or session between DTS and Database B on the source instance.
  10. After you switch workloads to the destination database, make sure that the destination database keeps running for a business period, for example, seven days.
  11. Test all features related to your business and make sure that no issues are detected. Then, shut down Database B on the source instance and stop the data migration task in the opposite direction. For more information, see Stop a data migration task.
    Warning The database accounts that are used for data migration have the read and write permissions. After data is migrated, you must delete the accounts or revoke the write permission to ensure security.