PROFESSIONAL-CLOUD-DATABASE-ENGINEER · Question #133
You are the Cloud Database Engineer for a retail analytics startup that runs a production Cloud SQL for MySQL instance supporting a product search API. Over the last 72 hours the average query…
The correct answer is B. Review the slow query log and create or refine indexes for the worst performing queries. The diagnostic clue is that CPU and memory metrics remain stable despite a nearly 3× increase in query latency. Stable CPU rules out compute saturation; stable memory rules out buffer pool pressure. This pattern is a classic indicator of missing or suboptimal indexes: the…
Question
You are the Cloud Database Engineer for a retail analytics startup that runs a production Cloud SQL for MySQL instance supporting a product search API. Over the last 72 hours the average query latency during busy periods rose from about 90 ms to roughly 260 ms while CPU and memory metrics remain stable. You need a single step that most directly improves query execution without changing application code. What should you do?
Options
- AAdd more read replicas to the Cloud SQL instance
- BReview the slow query log and create or refine indexes for the worst performing queries
- CConvert tables from InnoDB to MyISAM
- DScale the Cloud SQL instance to a larger machine type with more vCPU and memory
How the community answered
(32 responses)- A6% (2)
- B53% (17)
- C13% (4)
- D28% (9)
Explanation
The diagnostic clue is that CPU and memory metrics remain stable despite a nearly 3× increase in query latency. Stable CPU rules out compute saturation; stable memory rules out buffer pool pressure. This pattern is a classic indicator of missing or suboptimal indexes: the database is doing full or large partial table scans that consume little CPU individually but take much longer to execute. Option B directly targets this root cause by reviewing the slow query log to find the worst offenders and then creating or refining indexes to replace those scans with efficient index seeks. Option A (read replicas) distributes read load but does not make any individual query faster. Option C (converting to MyISAM) is counterproductive: MyISAM lacks row-level locking and crash recovery, is deprecated in MySQL 8.0, and Cloud SQL does not support it for new tables. Option D (larger machine type) would help if CPU or memory were the bottleneck, but the metrics confirm they are not.
Topics
Community Discussion
No community discussion yet for this question.