You have successfully provisioned the latest MySQL 5.7 database instance on a physical host, to be added to an existing farm for use in a modern, high volume, ACID-compliant, OLTP website, which…

Question

Options

• AKey Buffer Size
• BInnodb Redo Log Size
• CBinary Log Size
• DBuffer Pool Size
• ESort Buffer Size
• FQuery Cache Size

• A
  44% (16)
• B
  8% (3)
• C
  3% (1)
• D
  14% (5)
• E
  3% (1)
• F
  28% (10)

Explanation

Important note: The stated correct answer (A) appears to conflict with the question's scenario. For an InnoDB-based ACID-compliant OLTP workload - which is what "ACID-compliant" and "OLTP" imply - the correct two answers are B and D. Here's why:

Why B (InnoDB Redo Log Size) is correct: MySQL 5.7's default redo log is only 48MB total. With hundreds of DML transactions per second, the log fills rapidly and forces aggressive, performance-killing checkpoints. Increasing it (e.g., to 1–4GB) dramatically smooths write throughput.

Why D (Buffer Pool Size) is correct: The InnoDB buffer pool defaults to just 128MB - far too small for any production OLTP load. It should typically be set to 70–80% of available RAM, as it caches data pages, indexes, and dirty pages that InnoDB relies on constantly.

Why the others are wrong:

• A (Key Buffer Size) - This is a MyISAM-specific cache. InnoDB doesn't use it; it's irrelevant to an ACID/OLTP InnoDB workload.
• C (Binary Log Size) - Controls individual binary log file rotation size, not a performance bottleneck requiring change at provisioning.
• E (Sort Buffer Size) - Session-level memory for ORDER BY; OLTP workloads rarely sort large result sets.
• F (Query Cache Size) - Actually deprecated in MySQL 5.7 and removed in 8.0; it causes mutex contention under high concurrency and should be disabled, not increased.

Memory tip: Think "InnoDB = Buffer Pool + Redo Log." Those two settings are the first things you touch for any InnoDB production workload. If you see MyISAM, then think Key Buffer.

If your exam source marks A as correct, that answer key likely contains an error - or the question was originally written for a MyISAM context.

No community discussion yet for this question.