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

Question 1

A company runs an Amazon Redshift cluster with 8 dc2.large nodes for its data warehouse. The data engineering team loads data daily using COPY commands from S3. Recently, the load times have increased significantly. The cluster's CloudWatch metric 'CPUUtilization' is high during the load. The administrator runs the STL_LOAD_ERRORS table and finds no errors. The SVL_S3LOG shows that the COPY command is scanning many small files. The data in S3 is stored as 10,000 small CSV files (each ~100 KB). Which action will MOST improve the COPY performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Consolidate the small files into fewer, larger files (e.g., 100 files of 10 MB each). Consolidating many small files into fewer, larger files reduces the overhead of opening and processing numerous small files during the COPY command. Redshift performs better with larger files (e.g., 64 MB to 1 GB) because it can parallelize the load across slices more efficiently. Option A is incorrect because the MANIFEST option helps with specifying files but does not address the root cause of many small files. Option B is incorrect because JSON format typically increases parsing overhead compared to CSV. Option D is incorrect because changing the distribution style to ALL does not improve COPY performance; it affects query performance after data is loaded.

Answer

Use the MANIFEST option to specify the files explicitly

Answer

Use the JSON format instead of CSV to reduce parsing overhead

Answer

Change the table's distribution style to ALL to avoid data redistribution

Question 2

A company is using Amazon RDS for MySQL and has enabled Enhanced Monitoring. The database administrator wants to identify the top contributors to disk I/O. Which THREE metrics from Enhanced Monitoring should they examine?

Accepted Answer

DirtyBufferFlushRate. (DirtyBufferFlushRate) is correct because it indicates the rate at which dirty (modified) buffers are flushed from memory to disk, directly contributing to disk writes and I/O. Option D (WriteOps) is correct as it measures the number of write operations performed on disk, a direct contributor to disk I/O. Option E (PhysicalReads) is correct because it represents read operations that actually hit the disk (i.e., cache misses), contributing to disk I/O. Options B (NetworkThroughput) and C (LogicalReads) are incorrect: NetworkThroughput relates to network traffic, not disk I/O; LogicalReads are reads satisfied from the buffer pool (memory), not disk.

Answer

NetworkThroughput

Answer

LogicalReads

Question 3

A company is using Amazon RDS for MySQL with Multi-AZ deployment. The application team reports intermittent connection timeouts. CloudWatch shows increased DatabaseConnections and CPU Utilization during peak hours. Which action should the database specialist take to troubleshoot the issue?

Accepted Answer

Enable slow query log and analyze queries.. Enabling the slow query log allows the database specialist to identify long-running or inefficient queries that contribute to high CPU utilization and increased database connections, leading to connection timeouts during peak hours. Option A is incorrect because Enhanced Monitoring provides additional metrics (e.g., OS-level metrics) but does not directly address the root cause of the timeouts; it is useful for deeper analysis but not the primary troubleshooting action. Option C is incorrect because creating a read replica and redirecting read traffic reduces load on the primary for read operations, but the issue likely involves write-intensive or poorly optimized queries affecting the primary; while it might alleviate some load, it is not a direct troubleshooting step to identify the cause. Option D is incorrect because failing over to the standby instance is for high availability and disaster recovery, not for resolving performance issues; it would restart the database but not fix the underlying queries or resource contention.

Answer

Add enhanced monitoring to collect additional metrics.

Answer

Create a read replica and redirect read traffic.

Answer

Failover to the standby instance to refresh connections.

Question 4

A developer is troubleshooting an application that writes to an Amazon ElastiCache for Redis cluster. The application occasionally fails with 'OOM command not allowed when used memory > maxmemory'. What is the most likely cause?

Accepted Answer

The cluster's maxmemory-policy is set to noeviction.. The error 'OOM command not allowed when used memory > maxmemory' occurs when Redis has reached its configured memory limit and the eviction policy is set to 'noeviction', which prevents any further writes. Option B is correct because 'noeviction' causes Redis to return an error instead of evicting keys. Option A is incorrect because the 'maxclients' limit produces a different error ('max number of clients reached'). Option C is incorrect because keyspace notifications do not affect memory limits. Option D is incorrect because cross-slot commands relate to cluster mode, not memory errors.

Answer

The cluster's maxclients limit has been reached.

Answer

The cluster has too many keyspace notifications enabled.

Answer

The cluster is in cluster mode and cross-slot commands are used.

Question 5

A company uses Amazon Aurora MySQL. They notice that the DB cluster's failover took longer than expected during a recent primary instance failure. CloudWatch shows Failover latency of 120 seconds. Which configuration change would most likely reduce the failover time?

Accepted Answer

Configure the application to use the cluster endpoint with Aurora JDBC driver's fast failover feature.. Configuring the application to use the cluster endpoint with the Aurora JDBC driver's fast failover feature allows the driver to quickly detect the new writer after a failover and reconnect, significantly reducing the perceived failover time by avoiding DNS propagation delays. Option A is incorrect because increasing the instance class improves performance but does not affect failover speed. Option B is incorrect because backup retention period is unrelated to failover latency. Option C is incorrect because Aurora MySQL is already Multi-AZ by design; enabling Multi-AZ is not a separate configuration step.

Answer

Increase the instance class of the primary and replica instances.

Answer

Increase the backup retention period to 35 days.

Answer

Enable Multi-AZ on the DB cluster.

Question 6

A database specialist is troubleshooting a performance issue on a self-managed PostgreSQL database that they plan to migrate to Amazon RDS. The database has a high number of 'idle in transaction' connections. What is the impact of these connections on the database?

Accepted Answer

They hold locks and prevent cleanup of dead tuples, leading to bloat.. Idle-in-transaction connections hold locks and prevent PostgreSQL's autovacuum from cleaning up dead tuples, leading to table bloat and performance degradation. Option A is wrong because idle transactions do not cause constant polling; CPU usage remains low. Option C is wrong because they do not prevent new connections from being established unless the max_connections limit is reached. Option D is wrong because idle transactions do not significantly increase write-ahead logging.

Answer

They increase CPU usage due to constant polling.

Answer

They prevent new connections from being established.

Answer

They cause increased disk I/O from write-ahead logging.

Question 7

A company is using Amazon DynamoDB with provisioned capacity. They notice an increase in throttled write requests. The workload consists of writes to a single partition key. Which TWO actions would help reduce throttling?

Accepted Answer

Increase the provisioned write capacity units.. Options B and C are correct. Increasing provisioned write capacity (B) directly addresses throttling by allowing more writes per second. Enabling DynamoDB auto scaling with adaptive capacity (C) automatically adjusts capacity based on traffic and helps handle uneven access patterns, including hot partitions. Option A (adding a global secondary index with a different partition key) does not reduce throttling on the base table; it only provides an alternative query path. Option D (DAX) is a read cache and does not assist with write throttling. Option E (write sharding) can help distribute writes across partitions, but the question asks for two actions from the given list; B and C are the most direct and effective.

Answer

Add a global secondary index with a different partition key.

Answer

Use DynamoDB Accelerator (DAX) for write caching.

Answer

Implement write sharding by adding a suffix to the partition key.

Question 8

A company is monitoring an Amazon RDS for Oracle instance. CloudWatch alarms show that FreeableMemory is consistently below 256 MB. The database has high read and write I/O. Which THREE steps should the database specialist take to diagnose the issue?

Accepted Answer

Check the MemoryPressure and LogFileSyncDuration metrics in CloudWatch.. Options A, B, and E are correct diagnostic steps. A: Checking MemoryPressure and LogFileSyncDuration helps identify memory pressure and I/O bottlenecks. B: Reviewing V$MEMORY_TARGET_ADVICE provides Oracle's memory sizing recommendations. E: Querying V$SGASTAT and V$PGASTAT reveals detailed memory allocation within SGA and PGA. Option C (enabling storage auto scaling) addresses storage, not memory. Option D (increasing instance class) is a remediation step, not a diagnostic step.

Answer

Enable storage auto scaling to increase allocated storage.

Answer

Increase the DB instance class to allocate more memory.

Question 9

A company uses Amazon ElastiCache for Redis. They want to monitor cache hit ratio. Which TWO metrics should be used to calculate the cache hit ratio?

Accepted Answer

CacheHits. Cache hit ratio is calculated as CacheHits / (CacheHits + CacheMisses). Therefore, the two metrics needed are CacheHits (option B) and CacheMisses (option D). Options A, C, and E are not used in this calculation: GetTypeCmds counts total get commands, Evictions indicates memory pressure, and CurItems shows the number of items in the cache.

Answer

GetTypeCmds

Answer

Evictions

Answer

CurItems

Question 10

A social media company runs a multi-region application on Amazon DynamoDB with global tables. The application is deployed in us-east-1 and eu-west-1. Recently, they enabled DynamoDB Streams on the table to trigger an AWS Lambda function for real-time analytics. The Lambda function runs in us-east-1. After enabling streams, they notice that the Lambda function is triggered multiple times for the same update, and the application's write latency in eu-west-1 has increased. The company has strict latency requirements. A database specialist is asked to resolve the issue. What should the database specialist recommend?

Accepted Answer

Modify the Lambda function to check the 'awsRegion' attribute in the stream record and ignore records from other regions.. Global tables replicate writes across regions, and each write generates a stream record. The Lambda function should filter on the 'awsRegion' attribute in the stream record to avoid processing the same update from different regions. Option A is wrong because increasing Lambda concurrency doesn't prevent duplicate processing. Option B is wrong because using a dedicated stream for each region is not supported. Option D is wrong because disabling streams in eu-west-1 would break the replication.

Answer

Increase the Lambda function's reserved concurrency to handle duplicate events faster.

Answer

Create a separate Lambda function in eu-west-1 and use a local stream.

Answer

Disable DynamoDB Streams on the eu-west-1 replica table.

Question 11

A retail company uses Amazon RDS for PostgreSQL as the backend for its e-commerce platform. During a flash sale, the database experienced high CPU utilization and increased the number of active connections. The application team reported that some queries timed out. The database specialist reviewed the slow query log and found that several queries were performing sequential scans on large tables due to missing indexes. The specialist created the necessary indexes, but the issue persists for some queries. Upon further investigation, the specialist notices that the query planner is still choosing sequential scans for some queries. What should the database specialist do to ensure the query planner uses the indexes?

Accepted Answer

Run the ANALYZE command to update table statistics.. Running ANALYZE updates the table statistics used by the query planner, allowing it to make informed decisions about index usage. Without updated statistics, the planner may still choose sequential scans even after indexes are created. Option A is incorrect because increasing maintenance_work_mem speeds up index creation but does not affect query planning. Option B is incorrect because decreasing random_page_cost might make indexes more attractive, but it is a global setting that could have unintended consequences and does not address the root cause of stale statistics. Option D is incorrect because disabling sequential scans (enable_seqscan=off) forces index usage but can lead to suboptimal plans, especially if the index is not the most efficient access method.

Answer

Increase maintenance_work_mem to speed up index creation.

Answer

Decrease the random_page_cost to make indexes more attractive.

Answer

Set enable_seqscan to off to force index usage.

Question 12

A startup is using Amazon ElastiCache for Redis to cache session data. They deployed a single Redis node (cache.t3.micro) in us-west-2. The application reports high latency when reading session data. CloudWatch metrics show CPUUtilization at 90% and Evictions at 100 per minute. The cache hit ratio is 80%. The database specialist suspects the node is overloaded. What should the specialist do to improve performance?

Accepted Answer

Scale up to a larger node type, such as cache.m5.large.. Scale up to a larger node type, such as cache.m5.large. The current node (cache.t3.micro) is overloaded, as indicated by high CPU utilization (90%) and frequent evictions (100/min). Scaling up provides more CPU and memory resources, reducing evictions and lowering latency. Option B (add a read replica) does not help because the issue is on the primary node; read replicas are for scaling read-heavy workloads on a primary, but here the primary is overloaded. Option C (enable cluster mode) is not suitable for a single-node setup and adds complexity; it is intended for partitioning data across multiple shards. Option D (decrease TTL) would reduce memory usage but may increase cache misses and does not address the CPU bottleneck; it could actually worsen performance by requiring more database reads.

Answer

Add a read replica to offload read traffic.

Answer

Enable cluster mode and add more shards.

Answer

Decrease the TTL for session keys to reduce memory usage.

Question 13

A company runs a document management system using Amazon S3 and Amazon DynamoDB. The application writes document metadata to DynamoDB and stores the document in S3. Recently, users report that occasionally documents are saved in S3 but the corresponding metadata is missing in DynamoDB. The application writes to DynamoDB first, then to S3. If the S3 upload fails, the application retries. The database specialist suspects a transaction consistency issue. The application is running on multiple EC2 instances behind an Application Load Balancer. What should the specialist recommend to ensure that both the metadata and document are stored consistently?

Accepted Answer

Use DynamoDB transactions to write metadata and initiate S3 upload within the same transaction.. Using DynamoDB transactions ensures atomicity; if the S3 upload fails, the transaction can be rolled back. Option B is wrong because DynamoDB Streams with Lambda introduces eventual consistency and possible duplicates. Option C is wrong because writing to S3 first still leaves inconsistency if DynamoDB fails. Option D is wrong because idempotency tokens help with duplicates but not atomicity.

Answer

Use DynamoDB Streams to trigger an AWS Lambda function that performs the S3 upload.

Answer

Reverse the order: upload to S3 first, then write to DynamoDB.

Answer

Implement an idempotency key in the application to retry the entire operation.

Question 14

A financial services company is using Amazon Aurora MySQL as its primary database. The database has a table 'transactions' that receives high inserts during business hours. The table is partitioned by date. Recently, the application team noticed an increase in lock wait timeouts. The database specialist reviewed the InnoDB status and found that there are frequent gap locks on the 'transaction_date' column. The isolation level is REPEATABLE READ. What should the specialist do to reduce lock waits while maintaining data consistency?

Accepted Answer

Change the transaction isolation level to READ COMMITTED.. In REPEATABLE READ isolation level, InnoDB uses gap locks on non-unique indexes to prevent phantom reads, which can cause lock wait timeouts. Changing to READ COMMITTED eliminates gap locks for non-unique indexes because it only uses row-level locks (no gap locks). This reduces lock contention. Option A is incorrect: adding an index on transaction_date does not eliminate gap locks if the index is non-unique; gap locks still occur. Option B is incorrect: increasing innodb_lock_wait_timeout only increases the time a transaction waits for a lock, it does not prevent the lock from happening. Option C is incorrect: modifying the partition key does not affect the locking mechanism at the row level.

Answer

Add a secondary index on transaction_date.

Answer

Increase the innodb_lock_wait_timeout parameter.

Answer

Modify the partitioning key to use a hash-based partition.

Question 15

A company uses Amazon DynamoDB as a session store for a web application. The application uses a TTL attribute to expire old sessions. The company noticed that expired sessions are not being deleted promptly, causing the table size to grow and increasing costs. The TTL attribute is defined as 'expire_time' with a Unix epoch timestamp. The database specialist verified that TTL is enabled. What should the specialist do to ensure expired sessions are deleted in a timely manner?

Accepted Answer

Increase the provisioned write capacity on the table to allow TTL to delete items faster.. TTL deletion can be delayed if the table has a high write rate; increasing provisioned write capacity can allocate more resources to the background deletion process, allowing expired items to be removed more quickly. Option A is wrong because changing the attribute type to String would not resolve the deletion delay (the TTL attribute should be a Number). Option C is wrong because there is no 'ttl_deletion_lag' parameter in DynamoDB. Option D is wrong because using a Lambda function to scan and delete expired items is unnecessary and inefficient compared to properly tuning TTL.

Answer

Change the TTL attribute type to String format.

Answer

Configure the 'ttl_deletion_lag' parameter to a lower value.

Answer

Create an AWS Lambda function that scans the table and deletes expired items.