DBS-C01 Management and Operations Practice Test 6 — 15 Questions

Question 1

A company is running an Amazon RDS for MySQL DB instance. The database performance has degraded over time. The DBA suspects that the issue is due to a high number of connections that are in a 'sleep' state from a legacy application. What is the MOST effective solution to automatically terminate idle connections?

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

Accepted Answer

Modify the 'wait_timeout' parameter in the DB parameter group to a lower value.. The 'wait_timeout' parameter in MySQL automatically closes idle connections after the specified number of seconds. Reducing this value from the default (28800 seconds) will terminate sleeping connections sooner and free up resources. Option B is incorrect because increasing 'max_connections' does not terminate idle connections; it only allows more connections, which could worsen the problem. Option C is incorrect because Amazon RDS Proxy manages connection pooling and reduces connection overhead but does not automatically terminate idle connections; it reuses connections but does not kill them. Option D is incorrect because running 'SHOW PROCESSLIST' and manually killing connections is not automated and requires human intervention, so it is not the most effective solution.

Answer

Increase the 'max_connections' parameter to accommodate more idle connections.

Answer

Enable Amazon RDS Proxy to manage connection pooling and automatically close idle connections.

Answer

Use a script to run 'SHOW PROCESSLIST' and manually kill idle connections.

Question 2

A SysOps administrator is tasked with monitoring the free storage space on all Amazon RDS DB instances. Which AWS service should be used to set up an alarm that sends an email notification when free storage space falls below a threshold?

Accepted Answer

Amazon CloudWatch with an alarm on the FreeStorageSpace metric and an SNS topic.. Amazon CloudWatch provides the `FreeStorageSpace` metric for RDS DB instances, which reports the amount of available storage in bytes. You can create a CloudWatch alarm on this metric and configure it to send a notification via an Amazon SNS topic when the value falls below a specified threshold, enabling proactive monitoring of storage capacity.

Answer

AWS CloudTrail to monitor storage events.

Answer

AWS Config to track storage configuration changes.

Answer

Amazon Inspector to check for storage vulnerabilities.

Question 3

A company has a production Amazon DynamoDB table with on-demand capacity. The table experiences occasional throttling due to hot partitions. The operations team wants to implement a solution to identify the specific partition keys causing the throttling. What is the MOST efficient approach?

Accepted Answer

Enable Amazon CloudWatch Contributor Insights for DynamoDB.. Amazon CloudWatch Contributor Insights for DynamoDB is the most efficient solution because it automatically analyzes DynamoDB request metadata to identify the most frequently accessed partition keys, including those causing throttling. It provides near real-time top-N contributor reports without requiring application changes, custom logging, or additional stream processing, making it purpose-built for diagnosing hot partition issues.

Answer

Enable AWS X-Ray tracing on the application and analyze traces.

Answer

Enable VPC Flow Logs and use CloudWatch Logs Insights to query the logs.

Answer

Enable DynamoDB Streams and process the stream records to identify hot keys.

Question 4

A data engineer is troubleshooting a slow-running query on an Amazon Redshift cluster. The query involves large table joins and aggregations. Which diagnostic step should be taken FIRST to understand the query execution plan and identify bottlenecks?

Accepted Answer

Run the EXPLAIN command on the query to review the execution plan.. The EXPLAIN command is the first and most direct step to understand how Redshift plans to execute a query, including join types, data distribution, and aggregation strategies. It reveals the execution plan without running the query, allowing the engineer to identify bottlenecks like nested loop joins or missing sort key optimization before any other diagnostic step.

Answer

Monitor the WLM queue metrics for the query.

Answer

Check the SVV_TABLE_INFO view for table distribution and sort keys.

Answer

Query the STL_QUERY system table to review the query text.

Question 5

A company runs an Amazon Aurora MySQL DB cluster with one writer and two readers. The application experiences increased read latency. The DBA wants to offload read traffic from the writer instance. Which configuration change should be made to the application?

Accepted Answer

Modify the application to use the reader endpoint for read queries.. The reader endpoint for an Aurora MySQL cluster automatically load-balances read-only connections across all available reader instances. By modifying the application to use the reader endpoint for read queries, read traffic is offloaded from the writer instance, reducing read latency on the writer. This is the standard AWS-recommended pattern for separating read and write workloads in Aurora.

Answer

Modify the application to use an individual instance endpoint for each reader.

Answer

Create a custom endpoint that includes both writer and readers.

Answer

Modify the application to use the cluster endpoint for all queries.

Question 6

A database administrator is using AWS DMS to migrate an on-premises Oracle database to Amazon RDS for PostgreSQL. The migration has been running for several hours, but the full load phase is taking much longer than expected. The CPU utilization on the DMS replication instance is consistently below 10%. What is the MOST likely cause of the slow performance?

Accepted Answer

The target RDS instance is throttling write operations due to low IOPS.. When the target RDS instance has insufficient IOPS, write operations are throttled, causing a bottleneck that slows down the full load phase. Even though the DMS replication instance's CPU is low (below 10%), the target database cannot keep up with the incoming data, leading to increased latency and reduced throughput. This is a common performance issue in DMS migrations where the target's provisioned IOPS are exhausted, especially during large data loads.

Answer

The source database has large LOBs that are being transferred in full LOB mode.

Answer

The target database engine is not compatible with DMS.

Answer

The DMS task is not configured to use parallel tables.

Question 7

A company uses Amazon ElastiCache for Redis as a caching layer for its e-commerce application. Recently, the cache hit ratio has dropped significantly, causing increased database load. The operations team needs to identify which cache keys are being evicted. What should they do?

Accepted Answer

Monitor the 'Evictions' metric in Amazon CloudWatch for the ElastiCache cluster.. The 'Evictions' metric in Amazon CloudWatch directly reports the number of keys evicted from the ElastiCache for Redis cluster due to memory pressure. A drop in cache hit ratio often correlates with increased evictions, and monitoring this metric allows the operations team to identify the rate at which keys are being removed. This is the standard, built-in way to observe eviction activity without additional configuration.

Answer

Check the ElastiCache event history for eviction events.

Answer

Enable the 'INFO' command output to be logged to CloudWatch Logs.

Answer

Enable the Redis slow-log to capture eviction commands.

Question 8

A developer needs to restore an Amazon RDS for PostgreSQL DB instance to a specific point in time within the retention period. What must be enabled for this operation to be possible?

Accepted Answer

Automated backups with a retention period greater than 0.. Automated backups with a retention period greater than 0 are required for point-in-time recovery (PITR) in Amazon RDS. Option A is incorrect because deletion protection prevents accidental deletion but does not enable PITR. Option B is incorrect because read replicas are used for read scaling and disaster recovery, not for PITR. Option D is incorrect because Multi-AZ provides high availability but is not a prerequisite for PITR.

Answer

Deletion protection enabled.

Answer

A read replica in the same region.

Answer

Multi-AZ deployment.

Question 9

A company is running a MongoDB-compatible Amazon DocumentDB cluster. The application is experiencing high write latency during peak hours. The cluster has one writer and two readers. The DBA suspects that the issue is due to a large number of indexes on the collection. What should the DBA do to verify this hypothesis without affecting production?

Accepted Answer

Create a clone of the cluster using the 'Clone Cluster' feature and test index changes on the clone.. Cloning the cluster using the 'Clone Cluster' feature creates a separate, isolated environment where index changes can be tested without affecting production. Option A is incorrect because taking a snapshot and restoring to a new cluster is time-consuming and does not provide a real-time copy of the current state for testing. Option B is incorrect because AWS DMS is designed for database migration, not for testing index performance. Option C is incorrect because enabling the profiler captures slow queries but does not allow testing of index changes; it only helps identify existing issues.

Answer

Take a snapshot of the cluster and restore it to a new cluster to test index changes.

Answer

Use AWS DMS to replicate data to a test cluster and perform index testing there.

Answer

Enable the profiler on the production cluster to capture slow queries and analyze the index usage.

Question 10

A company is using Amazon DynamoDB with on-demand capacity. The operations team wants to monitor for throttled read requests. Which metric from Amazon CloudWatch should be used to set up an alarm for read throttling?

Accepted Answer

ReadThrottleEvents. 'ReadThrottleEvents' is a CloudWatch metric that directly indicates throttled read requests on a DynamoDB table. For write throttling, the correct metric is 'WriteThrottleEvents', but it is not among the options given. Therefore, only one option correctly answers the question as revised.

Answer

ConsumedWriteCapacityUnits

Answer

ThrottledPutRecords

Answer

ProvisionedReadCapacityUnits

Answer

ThrottledGetRecords

Question 11

A database administrator is responsible for managing an Amazon RDS for Oracle DB instance. The DBA needs to ensure that all changes to the DB instance's security group rules are logged for audit purposes. Which TWO services can be used together to achieve this? (Select TWO.)

Accepted Answer

AWS CloudTrail. AWS CloudTrail logs API calls made to the AWS account, including changes to security group rules (e.g., AuthorizeSecurityGroupIngress). By sending CloudTrail logs to Amazon CloudWatch Logs, you can create metrics filters and alarms to monitor and alert on specific API calls, enabling audit logging of security group rule changes. AWS Config tracks configuration changes but does not provide real-time log streaming to CloudWatch Logs for API calls. VPC Flow Logs capture network traffic, not API calls. Amazon Inspector is a vulnerability assessment service, not for logging API calls.

Answer

Amazon Inspector

Answer

AWS Config

Answer

Amazon VPC Flow Logs

Question 12

A company is migrating its on-premises PostgreSQL database to Amazon Aurora PostgreSQL. The migration must have minimal downtime. Which THREE steps should be taken as part of the migration plan? (Select THREE.)

Accepted Answer

Perform a test migration to validate the process.. Performing a test migration validates the entire process, including schema compatibility, data integrity, and replication lag, before the actual cutover. This reduces the risk of unexpected failures during the live migration, which is critical for achieving minimal downtime.

Answer

Enable Multi-AZ on the Aurora cluster before migration.

Answer

Disable automated backups on the Aurora cluster to improve performance.

Question 13

A company runs a production Amazon RDS for PostgreSQL database with automated backups enabled. A database administrator accidentally dropped a critical table. The administrator wants to restore the table from a point in time before the drop. The database is 1 TB in size and the recovery point objective (RPO) is 5 minutes. Which approach minimizes downtime?

Accepted Answer

Use the point-in-time recovery feature to restore the database to a new DB instance at a time before the drop, then use pg_dump to export the table and import it into the production database.. Amazon RDS Point-in-Time Recovery (PITR) allows restoring to any second within the backup retention window, enabling a restore to just before the table was dropped. After restoring to a new DB instance, pg_dump can export the specific table, and then pg_restore or psql can import it into the production database. This minimizes downtime by avoiding a full database restore and only moving the single dropped table.

Answer

Restore the automated backup from S3 to a new EC2 instance running PostgreSQL, then export the table and import it into the production database.

Answer

Restore the database from the most recent manual snapshot to a new instance, then use pg_dump to extract the table and import it into the production database.

Answer

Create a read replica from the production database, stop replication, and use pg_dump to extract the table from the replica and import it into the production database.

Question 14

A company needs to ensure that all changes to an Amazon RDS DB instance's security group are logged for auditing purposes. Which AWS service should be enabled?

Accepted Answer

AWS CloudTrail. AWS CloudTrail is the correct service because it records API calls made to the AWS environment, including actions like AuthorizeSecurityGroupIngress and RevokeSecurityGroupEgress that modify security group rules. When enabled, CloudTrail logs all changes to Amazon RDS security groups as management events, providing an immutable audit trail that can be stored in Amazon S3 for compliance and auditing purposes.

Answer

Amazon RDS Event Subscriptions

Answer

AWS Config

Answer

VPC Flow Logs

Question 15

A company has a production Amazon Aurora MySQL DB cluster with one writer and two reader instances. The application uses a custom connection pool that uses the writer endpoint for all database calls. The application is experiencing increased latency during peak hours. A database specialist suggests using the reader endpoint for read-only queries. What change is required on the application side to implement this recommendation?

Accepted Answer

Update the application's connection logic to use the cluster endpoint for writes and the reader endpoint for reads.. The application needs to differentiate between read and write queries and send read queries to the reader endpoint while write queries continue to use the writer endpoint. No changes to the Aurora cluster are needed; Aurora automatically handles connectivity to reader instances via the reader endpoint. The cluster endpoint remains unchanged for writes.

Answer

Modify the Aurora cluster to enable load balancing for the reader endpoint.

Answer

Create a new custom endpoint for read-only queries and use it instead of the reader endpoint.

Answer

Replace the writer endpoint with the reader endpoint in the connection pool configuration.