How can I troubleshoot why Amazon Redshift switched to one-by-one mode?

4 minute read
0

I want to use an AWS Database Migration Service (AWS DMS) task to migrate data to Amazon Redshift. But my task has latency or data inconsistency issues, and I see log entries such as, “XXXXBulk apply operation failed. Trying to execute bulk statements in 'one-by-one' mode XXXXX”.

Short description

Amazon Redshift is an online analytic processing (OLAP) data warehouse that isn't designed to take frequent transactions because of costs. By default, AWS DMS uses Batch Apply mode to process the changes in batches. If Redshift runs in one-by-one mode, then the DMS task didn't apply changes to the target and caused data inconsistency or latency issues. When you use Batch Apply mode, AWS DMS does the following:

  1. Collects changes from a batch that Batch Apply settings control.
  2. Creates a net changes table that contains all the changes from the batch to the target instance.
  3. Uses an algorithm that groups transactions and applies them in bulk to the target.

When a migration task that replicates data to Amazon Redshift can't apply a batch, AWS DMS doesn't fail the whole batch. AWS DMS breaks down the batch and switches to one-by-one mode to apply transactions. When AWS DMS encounters the transaction that caused the batch to fail, it logs the transaction to the awsdms_apply_exceptions table on the Amazon Redshift target. Then, AWS DMS applies the other transactions in the batch one by one until all transactions from that batch are applied onto the target. Finally, AWS DMS switches back to Batch Apply mode for a new batch and continues to use Batch Apply unless another batch fails.

Resolution

To see if your batch failed and AWS DMS used one-by-one mode, check the AWS DMS task log. Each time a batch fails and AWS DMS switches to one-by-one mode, you see the following log entry:

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

When this happens, AWS DMS sequentially applies transactions onto the target until AWS DMS encounters an issue with any transaction in the batch. If AWS DMS encounters an issue, then it logs the transaction and you see a log entry similar to the following entry:

"[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:5984)"

Note: Unless you specify a control table schema in your AWS DMS task settings, the awsdms_apply_exceptions table is created in the public schema by default.

After AWS DMS logs the transaction, it completes the application of all the transactions from that batch. Then, AWS DMS switches to Batch Apply again. In your logs, you see a message similar to the following one:

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

Transactional changes that you run from an online transaction processing (OLTP) database can affect Amazon Redshift performance. When Batch Apply fails, AWS DMS switches to one-by-one mode. Target latency increases for the duration of the time that AWS DMS runs transactions in one-by-one mode. After AWS DMS switches back to Bulk Apply, the target latency reduces.

To resolve this issue, connect to the Amazon Redshift target. Then, run the following command to get the output from the awsdms_apply_exceptions table to identify the query that caused the batch to fail:

select \* from public.awsdms\_apply\_exceptions order by 4 desc;

After you find the query that caused the batch to fail, review the error. Resolve the issue so that tasks don't move to one-by-one mode.

Related information

Debugging your AWS DMS migrations: What to do when things go wrong

Using an Amazon Redshift database as a target for AWS Database Migration Service

AWS OFFICIAL
AWS OFFICIALUpdated 4 months ago