The answer is to increase innodb_buffer_pool_size to 80% of available memory. This is the most effective initial tuning action because buffer pool contention and high disk reads per second are classic symptoms of an undersized InnoDB buffer pool, meaning the working set of data and indexes exceeds the allocated memory. For a dedicated Cloud SQL MySQL OLTP instance with 120 GB memory, setting the buffer pool to 96 GB maximizes cached pages in memory, directly reducing physical I/O and contention without introducing side effects like connection overhead. On the Google Professional Cloud Database Engineer exam, this scenario tests your understanding of InnoDB memory architecture and the 80% rule for dedicated instances—a common trap is choosing to increase query cache or thread concurrency, which do not address the root cause of insufficient memory for the working set. Remember the 80/20 rule: for OLTP on Cloud SQL MySQL, the buffer pool should be 80% of memory, leaving 20% for OS and other processes.
PCDE Monitor and optimize database performance Practice Question
This PCDE practice question tests your understanding of monitor and optimize database performance. The scenario asks you to isolate a root cause — eliminate options that address a different problem before choosing. After answering, compare your reasoning against the explanation and wrong-answer breakdown below. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
You are optimizing a Cloud SQL for MySQL instance for an OLTP application. You observe frequent buffer pool contention and high disk reads per second. The instance has 16 vCPUs and 120 GB memory. What is the most effective initial tuning action?
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Increase innodb_buffer_pool_size to 80% of available memory.
The correct answer is C because buffer pool contention and high disk reads per second are classic symptoms of an undersized InnoDB buffer pool. For a dedicated Cloud SQL for MySQL OLTP instance with 120 GB memory, increasing innodb_buffer_pool_size to 80% (96 GB) maximizes cached data and index pages in memory, reducing physical disk I/O and contention. This directly addresses the root cause—insufficient memory for the working set—without introducing side effects like connection overhead or query cache invalidation.
Key principle: Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
✗
Increase the max_connections parameter to handle more concurrent sessions.
Why it's wrong here
More connections increase contention, not improve disk reads.
✗
Reduce max_heap_table_size to avoid in-memory temp tables spilling to disk.
Why it's wrong here
This reduces memory for temp tables, possibly increasing disk use.
✓
Increase innodb_buffer_pool_size to 80% of available memory.
Why this is correct
This allows more data in memory, reducing disk I/O.
Related concept
Read the scenario before looking for a memorised answer.
✗
Enable query cache to cache SELECT results.
Why it's wrong here
Query cache is deprecated and can cause contention in OLTP workloads.
Common exam traps
Common exam trap: answer the scenario, not the keyword
Google Cloud often tests the misconception that increasing max_connections or enabling the query cache is a universal performance fix, but the trap here is that buffer pool contention and high disk reads specifically point to an undersized innodb_buffer_pool_size, not to connection limits or caching of SELECT results.
Detailed technical explanation
How to think about this question
The InnoDB buffer pool is a memory region that caches table and index data; when it is too small for the working set, frequently accessed pages are evicted and must be re-read from disk, causing high disk reads and buffer pool latch contention. Setting innodb_buffer_pool_size to 80% of available memory (96 GB) is a best practice for dedicated database instances, leaving headroom for other memory consumers like connection buffers and the operating system. In a real-world scenario, an OLTP application with a 100 GB working set on a 120 GB instance would see disk read latency drop from tens of milliseconds to microseconds after this tuning, as nearly all hot data fits in memory.
KKey Concepts to Remember
Read the scenario before looking for a memorised answer.
Find the constraint that changes the correct option.
Eliminate answers that are true in general but not in this case.
TExam Day Tips
→Watch for words such as best, first, most likely and least administrative effort.
→Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option.
Real-world example
How this comes up in practice
A cloud solutions architect for a retail company is evaluating services for a new workload. The correct answer here reflects best practice for the specific scenario described — not a general cloud recommendation. Answer the scenario, not the keyword: identify the specific constraint before choosing the most familiar-sounding option. Cloud exam questions reward reading the constraint carefully: the same technology can be right or wrong depending on the use case.
What to study next
Got this wrong? Here's your next step.
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
Monitor and optimize database performance — This question tests Monitor and optimize database performance — Read the scenario before looking for a memorised answer..
What is the correct answer to this question?
The correct answer is: Increase innodb_buffer_pool_size to 80% of available memory. — The correct answer is C because buffer pool contention and high disk reads per second are classic symptoms of an undersized InnoDB buffer pool. For a dedicated Cloud SQL for MySQL OLTP instance with 120 GB memory, increasing innodb_buffer_pool_size to 80% (96 GB) maximizes cached data and index pages in memory, reducing physical disk I/O and contention. This directly addresses the root cause—insufficient memory for the working set—without introducing side effects like connection overhead or query cache invalidation.
What should I do if I get this PCDE question wrong?
Identify which exam domain this question belongs to, review the core concept, then practise similar questions from the same domain.
What is the key concept behind this question?
Read the scenario before looking for a memorised answer.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.