DVA-C02 Troubleshooting and Optimization Practice Question
A developer is using Amazon RDS for MySQL and notices that the database performance has degraded. The developer suspects that slow queries are the cause. Which THREE actions should the developer take to identify and address the slow queries?
⚠ Common exam trap
DVA-C02 often tests the difference between diagnostic actions (slow query log, Performance Insights, CloudWatch metrics) and remediation/scaling actions (resize instance, read replica), so candidates who pick scaling options as 'fixes' miss the intent of identifying slow queries.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Enable the slow query log in RDS and review the logs.
Option A is correct because enabling the MySQL slow query log on RDS captures queries exceeding long_query_time, and these logs can be downloaded or published to CloudWatch Logs for review to pinpoint offending SQL. Option C is correct because Performance Insights provides a database load view (DB load by wait event and SQL) so the developer can identify the top SQL statements and waits causing degradation. Option D is correct because RDS console CloudWatch metrics such as CPUUtilization and ReadIOPS/WriteIOPS help correlate resource saturation with suspected slow queries and confirm the bottleneck. Option B is not appropriate as a first step because resizing the instance masks symptoms without identifying the slow queries and may not resolve inefficient SQL. Option E is also not appropriate because a read replica offloads read traffic but does not diagnose or fix the slow queries themselves.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Enable the slow query log in RDS and review the logs.
Why this is correct
The slow query log records every SQL statement that takes longer than the `long_query_time` threshold to execute, capturing the exact query text, execution time, lock time, and rows examined. Enabling it via the RDS parameter group (`slow_query_log=1`) is the most direct way to pinpoint which specific statements are causing the observed slowdown, allowing targeted optimization such as adding indexes or rewriting the query. This makes it the definitive first step for diagnosing slow queries at the statement level rather than relying on inferred metrics.
- ✗
Increase the DB instance size to improve performance.
Why it's wrong here
Increasing the DB instance size (scaling up) adds more CPU, memory, and I/O capacity, but it does not reveal which queries are slow or why they are slow; it merely gives the database more headroom to run inefficient statements. This can temporarily mask the performance issue while incurring higher costs, and if the root cause is a poorly designed query or missing index, the problem will persist or reappear once the workload grows. Scaling should be a remediation after identifying the bottleneck, not a diagnostic action.
- ✓
Enable Performance Insights to analyze database performance.
Why this is correct
Performance Insights gives a real-time and historical visualization of database load, breaking down wait events and listing the top SQL statements by load so you can correlate high latency with specific queries. It is a correct and valuable diagnostic aid, especially for capturing the impact of queries that repeatedly consume resources, but it relies on the Performance Schema and aggregates load rather than logging every individual slow statement. Unlike the slow query log, it does not automatically report the exact execution time and lock time of each statement beyond a threshold, so it serves as a complement rather than a replacement.
- ✓
Use the RDS console to review metrics for high CPU or IOPS usage.
Why this is correct
Reviewing the RDS CloudWatch metrics for high CPU, IOPS, or DatabaseConnections in the console reveals whether the instance is resource-constrained, which helps narrow down the type of problem (e.g., compute-bound vs. storage-bound). However, these aggregate metrics cannot tell you which specific SQL statement is responsible; a single heavy query can spike CPU usage, but the metric alone will not identify its text or pattern. This approach is useful for correlation and triage, but it is not sufficient for finding the exact query to fix.
- ✗
Create a read replica to offload read traffic.
Why it's wrong here
Creating a read replica establishes a separate read-only copy of the database that can serve SELECT traffic, which can reduce load on the primary instance only if the slowdown is caused by a high volume of read queries. It does not diagnose the root cause of slow queries, and if the bottleneck is a slow write, an inefficient join, or lock contention on the primary, a read replica will have no effect or could even worsen matters by adding replication overhead. Replicas are a scaling strategy, not a diagnostic tool, and should be introduced only after logs confirm that read load is the actual issue.
Visual reference
Go deeper
Related to this question
About these practice questions
One of 1,135 original DVA-C02 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
3 more ways this is tested on DVA-C02
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A developer is troubleshooting a slow-performing Amazon RDS for MySQL database. Which TWO actions should the developer take to improve query performance?
medium- A.Delete unused indexes to reduce write overhead.
- B.Enable Multi-AZ deployment for better read performance.
- ✓ C.Increase the instance size to provide more CPU and memory.
- ✓ D.Enable the slow query log to identify poorly performing queries.
- E.Delete the binary log files to free up storage.
Why C: Option C is correct because a slow-performing RDS for MySQL instance is often constrained by CPU, memory, or IOPS, and vertically scaling to a larger instance class provides more vCPU, RAM, and baseline EBS throughput, which directly improves query execution and buffer pool caching. Option D is correct because enabling the MySQL slow query log (via the slow_query_log and long_query_time parameters in a custom parameter group) captures queries exceeding the threshold, letting the developer identify and then optimize the specific poorly performing SQL statements. Option A is not appropriate because deleting indexes generally hurts read performance and only marginally reduces write overhead, and unused indexes are not the typical cause of slow queries. Option B is wrong because Multi-AZ is a high-availability/failover feature that maintains a synchronous standby, not a read-scaling mechanism, so it does not improve read performance. Option E is wrong because purging binary logs only frees storage and does not address query performance, and it can break point-in-time recovery and replication.
Variation 2. A developer is troubleshooting a slow Amazon RDS MySQL database query. The query is frequently executed and takes 5 seconds to complete. Which AWS service should the developer use to analyze the query performance?
easy- A.AWS CloudTrail
- ✓ B.Amazon RDS Performance Insights
- C.Amazon CloudWatch Logs
- D.AWS X-Ray
Why B: Amazon RDS Performance Insights is the correct service to analyze query performance on an RDS MySQL database. It provides a dashboard that visualizes database load and helps identify the top SQL statements, wait events, and users consuming the most resources. This allows the developer to pinpoint the slow query and understand its impact.
Variation 3. A developer is troubleshooting a slow-running query on an Amazon RDS for MySQL database. The query is used by a reporting application and takes over 30 seconds to complete. The database is a db.r5.large instance with 200 GB of gp2 storage. Which TWO actions should the developer take to improve query performance?
medium- A.Terminate idle connections to free up resources.
- ✓ B.Review the slow query log to identify the query and its execution plan.
- C.Increase the allocated storage to 500 GB to improve I/O performance.
- ✓ D.Add appropriate indexes to the tables involved in the query.
- E.Enable Multi-AZ deployment for better read performance.
Why B: Option B is correct because the MySQL slow query log captures queries exceeding the long_query_time threshold (default 10 seconds), and reviewing it along with EXPLAIN output reveals the query's execution plan, helping pinpoint full table scans, missing indexes, or inefficient joins. Option D is correct because adding appropriate indexes on the columns used in WHERE, JOIN, and ORDER BY clauses lets MySQL satisfy the query with index lookups instead of full table scans, which is the most direct fix for a slow reporting query. Option A is not appropriate because idle connections consume minimal resources and terminating them does not address query execution inefficiency. Option C is not appropriate because increasing gp2 storage size only raises the baseline IOPS (3 IOPS/GB) and burst balance; it does not fix a poorly optimized query and is a costly workaround. Option E is not appropriate because Multi-AZ is a high-availability feature that maintains a standby replica for failover, not a read-scaling mechanism, so it does not improve query performance.
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Amazon Web Services exam blueprint
This DVA-C02 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DVA-C02 exam.