DBS-C01 Monitoring and Troubleshooting Practice Test 24 — 15 Questions

Question 1

A company is running a MongoDB database on Amazon EC2. The database is experiencing high disk I/O latency. Which AWS service can be used to monitor the disk I/O metrics at the instance level?

Accepted Answer

Amazon CloudWatch. Amazon CloudWatch provides disk I/O metrics for EC2 instances. Option A is wrong because Amazon RDS is a managed database service. Option C is wrong because Amazon DynamoDB is a NoSQL database service. Option D is wrong because Amazon S3 is an object storage service.

Answer

Amazon RDS

Answer

Amazon DynamoDB

Answer

Amazon S3

Question 2

A company is experiencing slow query performance on an Amazon RDS for MySQL database. The DBA wants to identify the most time-consuming queries. Which TWO actions should the DBA take? (Choose two.)

Accepted Answer

Use Amazon RDS Performance Insights.. Amazon RDS Performance Insights (Option D) provides a database performance tuning and monitoring feature that visualizes database load and identifies the most time-consuming queries by breaking down wait events, SQL statements, and hosts. It directly helps the DBA pinpoint the specific queries causing performance degradation without additional configuration or overhead.

Answer

Enable the audit log.

Answer

Enable the general log and review it.

Answer

Use Amazon RDS Enhanced Monitoring.

Question 3

A company runs an e-commerce platform using Amazon Aurora MySQL with Multi-AZ deployment. The application has a read-heavy workload and uses a mix of SELECT and UPDATE queries. Recently, the company migrated from a db.r5.large to a db.r5.2xlarge instance class to handle increased traffic. However, after the migration, the CPU utilization remains high during peak hours, and the application's page load times have increased. The DBA notices that the 'Read IOPS' metric is high, but the 'Read Latency' metric is low. There is also a high number of 'Select' queries in the database. The application uses a single database endpoint. What should the DBA do to reduce CPU utilization and improve read performance?

Accepted Answer

Create one or more Aurora Replicas and modify the application to use read-only endpoints for SELECT queries.. Creating Aurora Replicas offloads read-only SELECT queries from the primary instance, reducing CPU utilization on the primary and improving overall read throughput. Option A is incorrect because enabling Multi-AZ with one standby replica provides high availability but does not help with read scaling; the standby is not used for reads. Option B is incorrect because Performance Insights is a monitoring tool that helps identify performance bottlenecks but does not directly reduce CPU utilization. Option C is incorrect because simply upgrading to a larger instance class may temporarily alleviate the issue but is not cost-effective and does not address the root cause of read-heavy workload imbalance; adding read replicas scales reads horizontally.

Answer

Enable Multi-AZ with one standby replica.

Answer

Enable Performance Insights and analyze the top SQL.

Answer

Upgrade the instance class to db.r5.4xlarge.

Question 4

A company runs a production Amazon RDS for PostgreSQL Multi-AZ DB instance (db.r5.large) with 500 GB of General Purpose SSD (gp2) storage. The application experiences intermittent latency spikes every 15 minutes. Monitoring shows that during these spikes, the ReadIOPS metric on the primary instance spikes to 5,000 IOPS (the baseline is 1,500 IOPS), and the BurstBalance drops from 100% to 20% then recovers. There is no increase in CPU or connections. The application uses connection pooling with pgBouncer on an EC2 instance. The team has verified that no long-running queries or index scans are causing the spikes. Which action is MOST likely to resolve the intermittent latency?

Accepted Answer

Migrate the storage to gp3 with a baseline of 3,000 IOPS and 125 MB/s throughput.. The latency spikes are caused by gp2 storage burst credit exhaustion. The 500 GB gp2 volume has a baseline of 1,500 IOPS, but the workload spikes to 5,000 IOPS every 15 minutes, rapidly consuming burst credits. Migrating to gp3 provides a baseline of 3,000 IOPS and 125 MB/s throughput without relying on burst credits, thus eliminating the credit exhaustion issue. Option A (read replica) does not resolve the primary instance's write IOPS spikes. Option B (larger instance) does not address storage IOPS limitations; CPU and connections are already normal. Option D (scale storage to 1,000 GB) would increase the gp2 baseline to 3,000 IOPS and provide more burst credits, but gp3 offers a simpler, more cost-effective solution with consistent performance and no credit-based throttling.

Answer

Create a read replica and redirect read traffic to it.

Answer

Increase the DB instance to db.r5.xlarge to improve CPU and network performance.

Answer

Scale the storage to 1,000 GB to increase baseline IOPS and burst credits.

Question 5

A company uses Amazon Aurora MySQL for its e-commerce platform. The DB cluster has one writer and two readers. Recently, the application started showing occasional deadlock errors during order processing. The error logs show: 'Transaction (Process ID 123) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.' The application retries three times before failing. The development team wants to reduce the likelihood of deadlocks. Which three actions should the team take? (Choose three.)

Accepted Answer

Shorten the duration of transactions by committing frequently.. Reduces the time locks are held, decreasing contention. Option C uses a weaker isolation level (READ COMMITTED) that reduces locking overhead compared to REPEATABLE READ. Option E ensures transactions request locks in a consistent order, preventing cycle dependencies. Option B is incorrect because READ UNCOMMITTED can cause dirty reads and is not suitable for e-commerce order processing. Option D is incorrect because increasing retry attempts does not reduce the likelihood of deadlocks; it only allows more retries after a deadlock occurs.

Answer

Switch to READ UNCOMMITTED isolation level to reduce locking.

Answer

Increase the number of retry attempts to 10.

Question 6

A company runs a PostgreSQL database on an Amazon RDS DB instance (db.t3.medium) with 100 GB of General Purpose SSD (gp2) storage. The database is used by a web application that experiences occasional slowdowns. CloudWatch metrics show that the BurstBalance metric for the storage volume drops to 0% during peak usage and then recovers. The average IOPS during peak is 600, and the baseline IOPS for the volume is 300. The team needs a cost-effective solution to eliminate the performance issues. What should the team do?

Accepted Answer

Migrate the storage to gp3 with 3000 baseline IOPS.. The gp2 volume's BurstBalance dropping to 0% indicates the burst bucket is exhausted, and the baseline IOPS for a 100 GB gp2 volume is only 300 IOPS (3 IOPS/GB). Peak demand of 600 IOPS exceeds baseline, so performance degrades once credits run out. Migrating to gp3 provides a flat 3000 baseline IOPS regardless of volume size, eliminating the burst-credit dependency at a lower cost than over-provisioning gp2.

Answer

Upgrade the DB instance to db.t3.large.

Answer

Increase the gp2 volume size to 200 GB.

Answer

Enable Performance Insights to monitor database load.

Question 7

A database specialist is responsible for an Amazon Aurora PostgreSQL DB cluster that supports a production application. Users report that read-only queries routed to Aurora Replicas sometimes return stale data that is several seconds behind the writer. The specialist must determine whether the lag is due to Aurora Replica lag or application read-after-write expectations. Which CloudWatch metric should be monitored to directly quantify Aurora Replica lag?

Accepted Answer

AuroraReplicaLag. AuroraReplicaLag is the precise metric that reports the replication delay in milliseconds for each Aurora Replica. When applications read from replicas, they may see stale data if the replica lags behind the writer. Monitoring this metric allows the specialist to confirm whether the stale reads are caused by replication lag and to take corrective action, such as redirecting reads to the writer or scaling replicas.

Answer

DBLoad

Answer

DiskQueueDepth

Answer

AuroraReplicaLagMaximum

Question 8

A database specialist is troubleshooting an Amazon Aurora PostgreSQL cluster. The application experiences occasional connection timeouts during peak hours. The specialist checks Amazon CloudWatch metrics and sees that 'DatabaseConnections' is high but within the cluster's maximum. They also notice that 'LoginFailures' is zero. Which of the following is the most likely cause of the timeouts?

Accepted Answer

The application's connection pool is not configured correctly, causing connection storms.. The combination of high DatabaseConnections and zero LoginFailures points to connection management issues rather than authentication or network problems. A misconfigured connection pool can cause a surge in connection attempts, leading to timeouts when the database cannot handle the rapid churn. Optimizing the pool settings, such as reducing max connections or increasing idle timeout, can alleviate the issue.

Answer

The security group for the Aurora cluster is blocking connections intermittently.

Answer

The Aurora cluster's reader endpoint is not being used, causing all read traffic to hit the writer.

Answer

The Aurora cluster's writer instance is overloaded due to high CPU utilization.

Question 9

A database specialist is troubleshooting an Amazon RDS for MySQL DB instance that has been experiencing sudden performance degradation. The specialist suspects that a recent schema change or query pattern is causing excessive temporary table usage on disk. Which CloudWatch metric should be examined to identify the number of temporary tables created on disk?

Accepted Answer

CreatedTmpDiskTables. CreatedTmpDiskTables is the CloudWatch metric that specifically tracks the number of internal on-disk temporary tables created by the MySQL server. When queries require temporary tables that exceed the configured in-memory limit (tmp_table_size and max_heap_table_size), MySQL creates them on disk, causing I/O overhead and performance degradation. Monitoring this metric helps the specialist confirm whether disk-based temporary tables are the root cause of the slowdown.

Answer

CreatedTmpTables

Answer

TmpTableSize

Answer

InnodbBufferPoolHitRate

Question 10

A database specialist is investigating intermittent connection timeouts to an Amazon Aurora MySQL cluster. The application logs show errors like 'Too many connections'. The specialist checks Amazon CloudWatch metrics and sees that the DatabaseConnections metric spikes to the maximum allowed by the DB parameter group's max_connections setting during peak traffic, but CPU and memory remain moderate. What is the MOST likely cause of the connection errors?

Accepted Answer

The application is not closing connections properly, leading to a buildup of idle connections that consume the max_connections limit.. The 'Too many connections' error occurs when the number of concurrent connections reaches the max_connections parameter limit. Since CPU and memory are moderate, the database is not overloaded; instead, connections are accumulating, likely due to improper connection handling. Implementing connection pooling or fixing connection leaks will resolve the issue by keeping the connection count below the limit.

Answer

The security group is blocking new connections after a certain threshold, causing timeouts.

Answer

The Aurora cluster's reader endpoint is not being used, causing all read traffic to hit the writer instance.

Answer

The Aurora cluster's writer instance is undersized, causing it to reject new connections when CPU is high.

Question 11

A database specialist manages an Amazon Aurora PostgreSQL cluster. During a batch load, the writer instance experiences a sharp increase in commit latency. The specialist runs `SELECT * FROM aurora_stat_activity;` and observes many sessions in a wait event named `LWLock:BufferContent`, along with a high number of concurrent `INSERT` statements into a single table. The DB cluster parameter group has `max_connections` set to 2000. Which action should the specialist take to reduce the commit latency?

Accepted Answer

Reduce the number of concurrent INSERT sessions by batching or serializing writes to the table.. The LWLock:BufferContent wait event points to contention on shared buffer pages among concurrent writers. When many INSERT sessions target the same table, they compete for the same pages, causing lightweight lock waits and elevated commit latency. Reducing write concurrency through batching or serializing inserts minimizes page-level contention. Increasing max_connections or shared_buffers does not resolve the hot-page contention and may introduce downtime or worsen the issue.

Answer

Increase the value of the `max_connections` parameter in the DB cluster parameter group.

Answer

Modify the table to use unlogged mode so that INSERT operations bypass shared buffers.

Answer

Increase the `shared_buffers` parameter to allocate more memory for caching table pages.

Question 12

A database specialist is monitoring an Amazon RDS for SQL Server DB instance. The specialist needs to identify the amount of free storage space available on the DB instance to prevent storage full issues. Which CloudWatch metric should be used?

Accepted Answer

FreeStorageSpace. FreeStorageSpace is the CloudWatch metric that reports the amount of available storage space in bytes for an Amazon RDS DB instance. Monitoring this metric allows the database specialist to set alarms and proactively increase storage or clean up data before the instance runs out of space, which can cause outages. It is a fundamental metric for storage management across all RDS engines.

Answer

DiskQueueDepth

Answer

FreeableMemory

Answer

ReadIOPS

Question 13

A database specialist manages an Amazon Aurora MySQL DB cluster with a writer and two readers. Users report that analytical queries run on the reader instances have become slower over the past week. The specialist checks Amazon CloudWatch metrics and notices that the metric 'AuroraReplicaLag' for both readers is consistently above 30 seconds. The application uses the reader endpoint for these queries. What is the MOST likely cause of the increased replica lag?

Accepted Answer

The reader instances are undersized for the analytical workload, causing them to fall behind the writer.. AuroraReplicaLag measures the delay between when a write is committed on the writer and when it is visible on a reader. High lag typically indicates that reader instances cannot apply changes quickly enough, often due to resource constraints. Analytical queries are resource-intensive and can compete with replication apply. Scaling up the reader instances provides more CPU and memory to handle both the queries and replication, reducing lag.

Answer

The reader instances are in a different Availability Zone than the writer, causing network latency that increases replication lag.

Answer

The Aurora cluster's backtrack feature is enabled, which is consuming resources on the reader instances.

Answer

The writer instance is experiencing high CPU utilization, causing it to delay sending binlogs to the readers.

Question 14

A database specialist is monitoring an Amazon RDS for SQL Server DB instance. The specialist needs to receive an alert when the free storage space falls below 10% of the allocated storage. Which CloudWatch metric should be used to create an alarm for this condition?

Accepted Answer

FreeStorageSpace. The FreeStorageSpace metric provides the amount of available storage in bytes. By creating a CloudWatch alarm with a threshold set to 10% of the allocated storage, the specialist can be alerted when free space drops below that level. Other metrics like DiskQueueDepth, ReadIOPS, and WriteIOPS measure performance, not storage capacity, and thus are not appropriate for this requirement.

Answer

WriteIOPS

Answer

ReadIOPS

Answer

DiskQueueDepth

Question 15

A database specialist manages an Amazon Aurora PostgreSQL cluster. Users report intermittent application timeouts. The specialist observes that the writer instance's CPU is normal, but the number of active connections is near the maximum. The application uses a connection pool that opens a new connection for each request without closing it. Which action should the specialist take to resolve the issue?

Accepted Answer

Configure the application's connection pool to properly close connections after use and set a maximum pool size.. The application is opening connections without closing them, leading to connection exhaustion. The proper fix is to correct the connection pool configuration to close connections after use and set a maximum pool size. This prevents the leak and ensures connections are available. Monitoring or scaling the instance does not solve the root cause, and increasing max_connections only delays the problem.

Answer

Increase the max_connections parameter in the custom DB cluster parameter group and reboot the writer instance.

Answer

Enable Performance Insights and analyze the database load to identify the queries causing the timeouts.

Answer

Modify the Aurora cluster to use a larger instance class for the writer instance.