Exam AWS Certified Database - Specialty topic 1 question 214 discussion

Exam question from Amazon's AWS Certified Database - Specialty

Question #: 214
Topic #: 1

[All AWS Certified Database - Specialty Questions]

A manufacturing company has an. inventory system that stores information in an Amazon Aurora MySQL DB cluster. The database tables are partitioned. The database size has grown to 3 TB. Users run one-time queries by using a SQL client. Queries that use an equijoin to join large tables are taking a long time to run.
Which action will improve query performance with the LEAST operational effort?

A. Migrate the database to a new Amazon Redshift data warehouse.
B. Enable hash joins on the database by setting the variable optimizer_switch to hash_join=on.
C. Take a snapshot of the DB cluster. Create a new DB instance by using the snapshot, and enable parallel query mode.
D. Add an Aurora read replica.

Show Suggested Answer

Suggested Answer: B 🗳️

by mbar94 at Sept. 5, 2022, 6:41 a.m.

Disclaimers:

- ExamTopics website is not related to, affiliated with, endorsed or authorized by Amazon.
- Trademarks, certification & product names are used for reference only and belong to Amazon.

Comments

Submit Cancel

SonamDhingra

Highly Voted 2 years, 9 months ago

Selected Answer: B

B is correct https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.BestPractices.html

upvoted 5 times

...

Pranava_GCP

Most Recent 1 year, 9 months ago

Selected Answer: B

B. Enable hash joins on the database by setting the variable optimizer_switch to hash_join=on https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.BestPractices.html#:~:text=the%20MySQL%20documentation.-,Optimizing%20large%20Aurora%20MySQL%20join%20queries%20with%20hash%20joins,-When%20you%20need

upvoted 1 times

...

fufufu

2 years, 1 month ago

Selected Answer: B

Equal joins between large tables require hash joins. Read replicas do not reduce query times.

upvoted 3 times

...

redman50

2 years, 2 months ago

Selected Answer: D

Option B is not the correct solution because enabling hash joins on the database by setting the variable optimizer_switch to hash_join=on can impact the performance of other queries and may not necessarily improve the performance of the equijoin queries.

upvoted 1 times

...

Ddddppp16

2 years, 3 months ago

Selected Answer: D

Based on the scenario, the LEAST operational effort would be to add an Aurora read replica (option D). This is because adding a read replica is a simple and quick process that does not require any changes to the existing cluster or schema. It allows offloading read traffic to the replica, which can help to reduce the load on the primary cluster and improve query performance.

upvoted 1 times

Ddddppp16

2 years, 3 months ago

Option C, taking a snapshot of the DB cluster and creating a new DB instance with parallel query mode enabled, can also improve query performance. However, this option requires more operational effort as it involves taking a snapshot, creating a new instance, and modifying the instance settings to enable parallel query mode. Additionally, this option may result in increased storage costs due to the creation of a new instance. Enabling hash join on the database by setting the variable optimizer_switch to hash_join=on may improve performance for some specific queries, but it's not a recommended solution for improving performance in general. It is a query-specific tuning parameter that requires careful analysis and testing, as it may not always provide the best performance improvement or may even degrade performance in some cases.

upvoted 1 times

Ddddppp16

2 years, 3 months ago

In contrast, increasing the number of Aurora replicas will distribute the read load across multiple nodes and improve read performance for all queries, not just specific ones. Additionally, creating a read replica is a simpler and more straightforward operation compared to tuning specific queries by modifying database parameters.

upvoted 1 times