Examkin

Question

Difficulty: HardManaging Storage and Database Solutions

Your organization operates a production Cloud SQL for MySQL instance that supports a mission-critical transactional application. During peak hours, complex analytical reporting queries involving multi-table SQL joins cause severe CPU throttling on the primary instance, impacting transaction processing. You must resolve the performance bottleneck for reporting workloads while maintaining real-time transactional data access, ensuring no schema changes are required, and minimizing operational overhead. What should you do?

  1. Create a Cloud SQL read replica, configure the reporting application to query the read replica's IP address, and maintain write operations on the primary instance.Answer
  2. B
    Export the transactional tables daily to a Cloud Storage Archive bucket and configure BigQuery external tables to execute the reporting queries.
  3. C
    Migrate the database tables to Cloud Bigtable to handle high-throughput analytical reporting and transactional queries within a single NoSQL cluster.
  4. D
    Enable High Availability (HA) failover on the primary Cloud SQL instance so that read traffic is automatically load balanced to the standby instance.

Answer

Create a Cloud SQL read replica, configure the reporting application to query the read replica's IP address, and maintain write operations on the primary instance.
Creating a Cloud SQL read replica allows read-heavy reporting queries to be processed independently from the primary instance. This eliminates CPU contention on the primary database, maintains relational SQL join functionality, provides near real-time data access via asynchronous replication, and requires zero schema migration overhead.

Step-by-Step Solution

1
Analyze the workload requirements
The reporting queries require real-time data access, relational capabilities (multi-table SQL joins), and relief of CPU pressure on the primary transactional database.
Identifying that the workload is read-heavy and relational narrows down valid architecture options.
2
Evaluate Cloud SQL scaling capabilities
Cloud SQL supports read replicas, which asynchronously replicate data from the primary instance to serve read-only queries.
Deploying a read replica offloads reporting queries without changing database schemas or affecting primary instance transaction performance.
3
Reject non-viable alternatives
High Availability standby instances do not process read traffic; Cloud Bigtable does not support relational SQL joins; Cloud Storage Archive incur high retrieval costs for daily access.
Eliminating options that break operational or architectural constraints confirms the optimal choice.

Key Concept

Managing Cloud SQL Read Replicas for Operational Performance Optimization
Rate this question

Topics

Managing Storage and Database Solutions