Wondering why the Amazon Redshift switched to one-by-one mode during the DMS task? We can help you to troubleshoot this!

Here at Bobcares, our AWS customers often report similar queries as a part of our AWS Support Services.

Today, let’s see how our Support Engineers troubleshoot this one-by-one mode switching during the AWS DMS task.

 

CDC replication: BatchApply and one-by-one mode

 
Before we step into the troubleshooting of a failed task that switched to one-by-one mode, let’s discuss how Batch Apply works and what is its importance in the replication phase.

AWS DMS is a single-threaded process. At a time the thread can handle only a certain number of transactions, so the thread can’t keep the target in sync with the source depending on the rate of changes on the source. In one-by-one mode, AWS DMS applies one transaction after other on the target instance.

By default, AWS DMS uses Batch Apply mode to process the changes on target using batches.

If we run a task with BatchApply mode, DMS processes changes in the following way:

1. DMS collects the changes in batch from the source DB transaction logs.
2. DMS creates a table called the net changes table, with all changes from the batch.
3. It applies an algorithm that brings out all changes from the net changes table to the actual target table.

The database such as Amazon Redshift running transactions in a serial manner, so the batch apply mode is optimal instead of the one-by-one mode. But sometimes due to the issues within a batch, instead of failing the whole batch, AWS DMS switches to one-by-one mode.

During one-by-one mode transactions, when AWS DMS encounters the transaction that caused the batch to fail, it logs the transaction in the awsdms_apply_exceptions table. When the application of the whole batch is completed, the Amazon Redshift switches back to BatchApply mode.
 

Amazon Redshift switched to one-by-one mode during the DMS task

 
Now let’s discuss how our support techs identify the issue from AWS DMS logs and how to resolve them in order to prevent tasks from moving to one-by-one mode.

1. Sign in to the AWS Management Console and open the AWS DMS console.
2. Select the Database migration tasks and then select the migration task.
3. Under Overview details, select View logs.

By checking the AWS DMS task log, we can see the following message if batch failed and AWS DMS used one-by-one mode for the transaction:

[TARGET_APPLY ]I: Bulk apply operation failed. Trying to execute bulk statements in 'one-by-one' mode (bulk_apply.c:2000)

Then the AWS DMS applies one-by-one mode transactions until AWS DMS encounters any transaction issue in the batch. If AWS DMS face an issue, it logs the transaction as follows:

[TARGET_APPLY ]W: Source changes that would have had no impact were not applied to the target database. Refer to the 'awsdms_apply_exceptions' table for details. (endpointshell.c:5000)

After that, when the application of the whole batch is completed, the AWS DMS switches back to BatchApply mode.

[TARGET_APPLY ]I: Switch back to bulk apply mode (bulk_apply.c:4000)

Amazon Redshift is an OLAP database and is optimized to run complex queries. However, Redshift performance can be affected when running transactional changes from an OLTP database. As a result, when Batch Apply fails and AWS DMS switches to one-by-one mode.
 

Fix: Amazon Redshift switched to one-by-one mode

 
To fix the Amazon Redshift switched to one-by-one mode during DMS task, first connect to the Amazon Redshift target. Then, get the output from the awsdms_apply_exceptions table to identify the query and the error that caused the batch to fail:

select * from public.awsdms_apply_exceptions order by 4 desc;

After finding the query that caused the batch to fail like update conflicts or constraint violations, then we can resolve the conflicts inorder to prevent tasks from moving to one-by-one mode.

[Need help to resolve AWS related errors? We’d be happy to assist]
 

Conclusion

 
To conclude, today we saw how our Support Techs troubleshoot the Amazon Redshift switching to one-by-one mode during the AWS DMS task.