Exam AWS Certified Database - Specialty topic 1 question 206 discussion

A Dropping indexes on the source will not help; this is intended for the target.

By default, AWS DMS loads eight tables at a time. You might see some performance improvement by increasing this slightly when using a very large replication server, such as a dms.c4.xlarge or larger instance. However, at some point, increasing this parallelism reduces performance. If your replication server is relatively small, such as a dms.t2.medium, we recommend that you reduce the number of tables loaded in parallel. For a full load task, we recommend that you drop primary key indexes, secondary indexes, referential integrity constraints, and data manipulation language (DML) triggers. Or you can delay their creation until after the full load tasks are complete. You don't need indexes during a full load task, and indexes incur maintenance overhead if they are present. Because the full load task loads groups of tables at a time, referential integrity constraints are violated. Similarly, insert, update, and delete triggers can cause errors, for example if a row insert is triggered for a previously bulk loaded table. Other types of triggers also affect performance due to added processing.

A is not recommended for smaller instances like t2/t3. C is not correct because they will run in default settings which is transactional mode. D is distractor as it is not relevant for performance.

x A. Actually, for a medium replication instance it's recommended to decrease the number of tables that are loaded in parallel... It's not evident that this helps. It probably would for very large instances. B. Dropping all indexes on the source tables - YES that clearly helps, because "Indexes, triggers, and referential integrity constraints can affect your migration performance and cause your migration to fail." x C. Change the processing mode from the batch optimized apply option to transactional mode. - NO, the opposite would be good for performance (batch mode). x D. Enable Multi-AZ on the target database while the full load task is in progress. - Nice try - a distractor. Multi-AZ increases reliability, but that's not a direct boost to performance really. Hence, B should be the right answer. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

A. Increase the number of tables that are loaded in parallel. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.Performance

A. Increase the number of tables that are loaded in parallel. - that MIGHT help B. Drop all indexes on the source tables.- dropping indexes on the destination tables would help, but not on the source tables C. Change the processing mode from the batch optimized apply option to transactional mode. - actually it is quite the opposite - the purpose of transactional mode is to preserve data consistency at all time, which is not required here D. Enable Multi-AZ on the target database while the full load task is in progress. - that would slow down the migration since it would consume CPU and IO for other purposes than data migration So A it is...

xA. Increase the number of tables that are loaded in parallel. - weak instance xB. Drop all indexes on the source tables. - should be on destination C. Change the processing mode from the batch optimized apply option to transactional mode. - database is already off-line and full load mode so temporarily lapse is very much NOT a concern as eventually it gets caught at the end. xD. Enable Multi-AZ on the target database while the full load task is in progress. - it slow down the process

So i dont get it , A -- may be helpful B - DMS doesnt take indexes into account during migration , unless they are manually created on target C - transaction mode is default setting - no need to switch it from batch D- Not helpful

To indicate the maximum number of tables to load in parallel, set the MaxFullLoadSubTasks option. The default is 8; the maximum value is 49. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html

There is no much confusion on the query.. Most of the action items are for the target DB as per the URL.. Answer should be C..

A : Correct XB Drop index on target DB XC Using batch XD. Not help

B is correct

Optimizing change processing By default, AWS DMS processes changes in a transactional mode, which preserves transactional integrity. If you can afford temporary lapses in transactional integrity, you can use the batch optimized apply option instead. This option efficiently groups transactions and applies them in batches for efficiency purposes. Using the batch optimized apply option almost always violates referential integrity constraints. So we recommend that you turn these constraints off during the migration process and turn them on again as part of the cutover process.

C is incorrect as it's mentioned in questions that DMS is running with default setting and default is transactional mode. D will have no effect on performance. A will help but dms.t3.medium is not strong enough to handle excess parallelism. It may in fact degrade performance. With all these I feel B seems to be answer. Even with B I guess best practice will be to drop indexes on Destination side and not on source. But It's mentioned database will be inactive I believe dropping indexes on source and recreating later can help. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.Performance

For a full load task, we recommend that you drop primary key indexes, secondary indexes, referential integrity constraints, and data manipulation language (DML) triggers. Or you can delay their creation until after the full load tasks are complete. You don't need indexes during a full load task, and indexes incur maintenance overhead if they are present. Because the full load task loads groups of tables at a time, referential integrity constraints are violated. Similarly, insert, update, and delete triggers can cause errors, for example if a row insert is triggered for a previously bulk loaded table. Other types of triggers also affect performance due to added processing. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

B, t3 medium still too weak to handle large parallel workload.

I go with A, its mentioned as Full load task and not CDC, so as per this link, it should be A https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.Performance