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

Question 1

Refer to the exhibit. An RDS for PostgreSQL instance shows frequent checkpoints every 10 seconds. What is the most likely cause?

Accepted Answer

The 'max_wal_size' parameter is set too low, causing frequent WAL switches.. Frequent checkpoints in PostgreSQL are often caused by small WAL size settings. The 'max_wal_size' parameter controls the maximum size of WAL files before a checkpoint is triggered. If set too low, the database will perform checkpoints more frequently to recycle WAL segments. Option A is incorrect because autovacuum bloat does not directly cause frequent checkpoints. Option B is incorrect because storage space issues may cause other symptoms, but not specifically checkpoint frequency. Option C is incorrect because concurrent connections may increase workload but do not directly cause frequent checkpoints; the checkpoint timing is driven by WAL generation rate and WAL size limits.

Answer

Autovacuum is not running, causing bloat.

Answer

The instance is running out of storage space.

Answer

There are too many concurrent connections.

Question 2

A database administrator is troubleshooting a sudden increase in read latency on an Amazon RDS for PostgreSQL instance. The instance has 200 GB of General Purpose SSD (gp2) storage with 600 provisioned IOPS. The administrator notices that the average queue depth is consistently above 4. Which action is the MOST effective way to reduce read latency?

Accepted Answer

Migrate to an io1 volume with 3000 provisioned IOPS.. Migrating to an io1 volume with 3000 provisioned IOPS directly increases the available IOPS, addressing the high queue depth and reducing read latency. Option A is incorrect because Multi-AZ does not allow read traffic to the standby; the standby is only used for failover. Option B is incorrect because increasing the instance size may not resolve the I/O bottleneck if the issue is insufficient IOPS. Option D is incorrect because increasing the allocated storage for gp2 increases baseline IOPS (3 IOPS per GB), but the option specifies not changing IOPS, so it would not help; even if it did, 500 GB would provide 1500 IOPS, which may still not be sufficient.

Answer

Enable Multi-AZ deployment to offload reads to the standby.

Answer

Change the instance type to a larger size with more vCPUs.

Answer

Increase the allocated storage to 500 GB without changing IOPS.

Question 3

A company runs an Amazon Aurora MySQL database cluster with one writer and one reader instance. The application experiences intermittent connection timeouts during peak traffic. The DB cluster parameter group has 'connect_timeout' set to 5 seconds. What should a database specialist recommend to reduce connection timeouts?

Accepted Answer

Enable RDS Proxy for the cluster.. Enabling RDS Proxy reduces connection overhead and provides a connection pool, mitigating timeouts. Option A is incorrect because adding readers does not help with writer connection timeouts; the issue is with the writer instance. Option C is incorrect because increasing connect_timeout only delays the timeout, not the root cause. Option D is incorrect because the issue is not related to TLS.

Answer

Add additional reader instances to distribute the load.

Answer

Increase the 'connect_timeout' parameter to 10 seconds.

Answer

Enable IAM database authentication and require TLS.

Question 4

A data warehouse team is migrating from Amazon Redshift to Amazon Redshift RA3 nodes. The current cluster uses 10 DC2.large nodes. The new cluster will use 4 RA3.xlarge nodes. After the migration, the team notices that query performance is significantly slower. Which factor is the MOST likely cause of the performance degradation?

Accepted Answer

The cluster has fewer nodes, reducing parallelism.. Reducing the number of nodes from 10 to 4 decreases the parallelism, even though RA3 nodes have more compute capacity per node. Redshift distributes data across slices, and each node has multiple slices. With fewer nodes, the total number of slices is lower, reducing the degree of parallelism for query execution, which can lead to slower performance for large queries. Option A is incorrect because the distribution style (EVEN vs KEY) affects data distribution but is not the most likely cause here; the question does not indicate a change in distribution style. Option B is incorrect because RA3 nodes use managed storage with ample capacity; disk space is not constrained. Option D is incorrect because RA3 nodes are optimized for large datasets; the issue is not the node type but the reduction in parallelism due to fewer nodes.

Answer

The data distribution style is set to EVEN instead of KEY.

Answer

The cluster does not have enough disk space for the workload.

Answer

RA3 nodes are not optimized for large datasets.

Question 5

A developer accidentally deleted a critical table from an Amazon RDS for MySQL DB instance. Automated backups are enabled with a retention period of 7 days. The deletion occurred 3 hours ago. Which action can restore the table with minimal data loss?

Accepted Answer

Perform a point-in-time restore to a time just before the deletion.. Amazon RDS point-in-time recovery (PITR) allows restoring a DB instance to any second within the backup retention period, enabling recovery to just before the table deletion, minimizing data loss. Option B is incorrect because AWS DMS is a migration tool, not a recovery mechanism for deleted data. Option C is incorrect because restoring from the latest automated snapshot would lose all changes made after that snapshot, including the 3 hours of data before deletion. Option D is incorrect because while MySQL binary logs can be used for point-in-time recovery, RDS manages this through its automated PITR feature; manually replaying binary logs is not a supported operation in RDS.

Answer

Use AWS Database Migration Service to replicate the table from another source.

Answer

Restore the DB instance from the latest automated snapshot.

Answer

Use the MySQL binary log to replay transactions up to the deletion.

Question 6

A company runs a critical Amazon DynamoDB table with on-demand capacity. The table experiences throttling errors (ProvisionedThroughputExceededException) during peak hours. The table has a global secondary index (GSI) with a different partition key. What is the MOST likely cause of the throttling?

Accepted Answer

The global secondary index is throttling due to uneven access patterns.. On-demand DynamoDB tables automatically scale capacity for the base table, but a GSI can throttle writes if its partition key creates hot partitions. The throttling on the base table occurs because the GSI write capacity is exceeded due to uneven access patterns. Options A, C, and D are incorrect because on-demand tables handle base table capacity scaling, hot partitions on the base table would cause throttling on the base table but the issue is with the GSI, and read capacity is not relevant given the write errors.

Answer

The base table's write capacity is insufficient for the workload.

Answer

The partition key design causes hot partitions in the base table.

Answer

The table's read capacity is too low for the read-heavy workload.

Question 7

A company uses Amazon ElastiCache for Redis as a caching layer for a database. The cache cluster has one primary and one replica node. During a maintenance event, the primary node fails and the replica is promoted. After promotion, the application experiences increased latency. What should a database specialist do to reduce the impact of future failovers?

Accepted Answer

Enable cluster mode and distribute the cache across multiple shards.. Enabling cluster mode on the ElastiCache for Redis cluster distributes data across multiple shards, each with its own primary and replica nodes. During a failover, only the data in the failed shard's primary needs to be rebuilt, limiting the cache miss impact. Adding more replicas (Option D) does not reduce the latency impact because a single primary still serves all requests after failover, and the new primary must warm its cache from scratch. Disabling replica reads (Option A) would not address the latency increase. Automatic backups (Option B) do not affect runtime performance after failover.

Answer

Disable replica reads to avoid stale data.

Answer

Enable automatic backups with a retention period of 35 days.

Answer

Add more replica nodes to the cluster.

Question 8

An Amazon RDS for SQL Server instance is running out of storage space. The instance uses 500 GB of Magnetic storage. The database specialist needs to increase storage to 1 TB with minimal downtime. Which solution meets these requirements?

Accepted Answer

Modify the DB instance and increase the allocated storage to 1 TB.. Modifying the DB instance to increase allocated storage is the correct approach because it can be done with minimal downtime. Amazon RDS for SQL Server supports dynamic storage scaling, allowing you to increase the allocated storage size without requiring a snapshot or migration. The instance remains available during the modification, although a brief performance impact may occur. Option A is incorrect because restoring from a snapshot requires significant downtime and does not offer a direct storage increase on the existing instance. Option B is incorrect because converting storage type (e.g., from Magnetic to General Purpose) is a separate modification that also incurs downtime, and it is not necessary to change storage type before increasing size; you can modify storage size directly. Option D is incorrect because using AWS DMS is overkill for a simple storage increase and introduces additional complexity and potential downtime during the migration process.

Answer

Create a snapshot of the DB instance and restore it with larger storage.

Answer

Convert the storage type to General Purpose (gp2) and then increase size.

Answer

Use AWS DMS to migrate the database to a new instance with 1 TB storage.

Question 9

A company is using Amazon Neptune to run graph queries. The cluster has one writer and two reader instances. After a major version upgrade, the query performance degrades for complex traversal queries. The database specialist suspects that the query optimizer is not using indexes effectively. Which action should the specialist take to identify the issue?

Accepted Answer

Enable the Neptune explain plan feature and review the output.. Enabling the Neptune explain plan feature provides detailed information about how queries are executed, including index usage. This helps identify if the query optimizer is failing to use indexes effectively after the upgrade. Option A is incorrect because dropping and recreating indexes is unnecessary and disruptive; indexes are automatically maintained. Option C is incorrect because adding reader instances distributes read load but does not fix query optimization issues. Option D is incorrect because CloudWatch metrics show system-level performance, not query execution details.

Answer

Drop and recreate all indexes to ensure they are up to date.

Answer

Add more reader instances to distribute the query load.

Answer

Check the CloudWatch metrics for CPU utilization and query latency.

Question 10

A company is migrating an on-premises Oracle database to Amazon RDS for Oracle. The database is 2 TB in size and has a high transaction rate. The migration must have minimal downtime. Which migration strategy should a database specialist recommend?

Accepted Answer

Use AWS DMS with ongoing replication to capture changes.. AWS DMS with ongoing replication allows for minimal downtime by continuously replicating changes from the source Oracle database to the target Amazon RDS for Oracle, allowing a cutover with only a brief pause. Option B is incorrect because copying data files to S3 and loading into RDS is not a supported migration path for RDS for Oracle and would require significant downtime. Option C is incorrect because Oracle Data Pump export and import requires the source database to be offline or in read-only mode during export, causing downtime. Option D is incorrect because RMAN backup and restore is not natively supported for RDS for Oracle and would involve complex manual steps and downtime.

Answer

Copy the data files to Amazon S3 and then load into RDS.

Answer

Use Oracle Data Pump to export and import the database.

Answer

Perform an RMAN backup and restore to RDS.

Question 11

Which TWO of the following are valid methods to secure data at rest for an Amazon RDS for MySQL DB instance?

Accepted Answer

Enable encryption at rest using AWS KMS.. (encryption at rest using AWS KMS) is correct because Amazon RDS for MySQL supports encryption at rest using AWS KMS, which encrypts the underlying storage and automated backups. Option C is incorrect because there is no '--enable-encryption' flag in the AWS CLI; the correct parameter to enable encryption during instance creation is '--storage-encrypted'. Therefore, option C does not represent a valid method. Option A is incorrect because SSL/TLS secures data in transit, not at rest. Option D is incorrect because Transparent Data Encryption (TDE) is not supported for MySQL on RDS; it is available for Oracle and SQL Server. Option E is incorrect because Amazon RDS encryption is applied at the storage level, not the table level.

Answer

Enable SSL/TLS for connections to the DB instance.

Answer

Use the '--enable-encryption' flag when creating the DB instance.

Answer

Implement Transparent Data Encryption (TDE) using Oracle-compatible settings.

Answer

Use Amazon RDS encryption at the table level using the ENCRYPT option.

Question 12

Which THREE of the following are best practices for managing Amazon DynamoDB tables with provisioned throughput?

Accepted Answer

Use a composite key design with a sort key to enable efficient querying.. Options C, D, and E are correct. Option A is incorrect because creating a global secondary index on every attribute incurs additional costs and storage, and not every query pattern requires an index; GSIs should be designed based on actual access patterns. Option B is incorrect because DynamoDB automatically manages partitions; manual partition splitting is not supported. Option C is correct because a composite key design (partition key and sort key) enables efficient querying using the sort key for filtering and sorting. Option D is correct because DynamoDB Auto Scaling automatically adjusts read/write capacity based on actual traffic, helping to maintain performance without manual intervention. Option E is correct because DynamoDB Accelerator (DAX) provides in-memory caching for frequently accessed items, reducing read latency and improving performance.

Answer

Create a global secondary index on every attribute to support any query pattern.

Answer

Split hot partitions manually to distribute write traffic.

Question 13

Which TWO of the following are valid actions to take when an Amazon Redshift query is taking longer than expected due to disk-based operations?

Accepted Answer

Redistribute data across nodes using a distribution style that minimizes data movement.. (redistributing data with appropriate distribution style) helps minimize data movement across nodes, reducing disk-based operations like spills. Option E (increasing nodes) adds more memory and CPU, enabling more in-memory processing and reducing disk spills. Option A is incorrect because column compression reduces storage but does not prevent disk-based operations. Option B is incorrect because WLM queues manage concurrency, not query speed. Option D is incorrect because sort keys affect order-based optimizations but not disk spills.

Answer

Use column compression to reduce the amount of data scanned.

Answer

Increase the number of workload management (WLM) query queues.

Answer

Change the sort keys to match the query's ORDER BY clause.

Question 14

A company's production RDS for PostgreSQL instance experienced a failover to the standby replica. After the failover, the application team reports that writes are failing with 'ReadOnlyException: Connection is read-only'. The DB instance status in the RDS console shows 'available'. Which action should the database administrator take to resolve the issue?

Accepted Answer

Update the application connection string to use the new writer endpoint from the RDS console.. After a Multi-AZ failover, the standby becomes the primary, and the old primary becomes a read-only standby. The application must update its connection string to use the new writer endpoint (which automatically points to the new primary) to perform writes. Option B is incorrect because creating a read replica and promoting it is unnecessary; Multi-AZ already provides a standby. Option C is incorrect because rebooting would force another failover but does not change the endpoint. Option D is incorrect because Multi-AZ is already enabled; modifying it again does not resolve the issue.

Answer

Create a read replica and promote it to a new primary.

Answer

Reboot the DB instance to force a failover back to the original primary.

Answer

Modify the DB instance to enable Multi-AZ again.

Question 15

A database administrator notices that Amazon RDS for MySQL is experiencing high CPU utilization during peak hours. The application is read-heavy with many SELECT queries. Which action is most cost-effective to improve performance?

Accepted Answer

Create one or more read replicas and direct read traffic to them.. The most cost-effective solution for a read-heavy, high-CPU workload on Amazon RDS for MySQL is to create one or more read replicas and direct read traffic to them. This offloads SELECT queries from the primary DB instance, reducing CPU utilization without incurring the high cost of upgrading the primary instance. Option B (increasing DB instance class) and Option D (vertical scaling to higher vCPU) are more expensive and not cost-effective. Option C (increasing allocated storage) improves I/O throughput but does not directly reduce CPU utilization.

Answer

Increase the DB instance class to a larger size.

Answer

Increase the allocated storage to improve I/O throughput.

Answer

Scale up the DB instance vertically to a higher vCPU count.