You inherited a busy InnoDB OLTP Instance with 100 schemas and 100 active users per schema. - Total dataset size is 200G with an average schema size of 2G. - The data is transient and is not backed…

Question

Options

• Atable_open_cache = 64
• Binnodb_buffer_pool_instances=64 innodb_buffer_pool_size=200G
• Clog_bin=mysql-bin sync_binlog=10 binlog_format=ROW
• Dinnodb_flush_method=O_DIRECT
• Emax_heap_table_size = 2G tmp_table_size=2G
• Fquery_cache_size = 2G query_cache_enabled=1
• Ginnodb_flush_log_at_trx_commit=0

• A
  69% (25)
• C
  14% (5)
• D
  11% (4)
• F
  6% (2)

Explanation

A (table_open_cache=64) is catastrophically low for 100 schemas × many tables × 10,000 potential concurrent users - MySQL constantly opens and closes file descriptors, which is expensive; a value in the thousands is appropriate here. B (innodb_buffer_pool_size=200G) on a 256G server leaves only ~56G for the OS, 10,000 connection/sort/join buffers, and other MySQL memory, making OOM pressure and swapping almost inevitable. E (tmp_table_size=2G / max_heap_table_size=2G) means each of ~10,000 concurrent sessions can claim up to 2G of RAM for in-memory temp tables - a recipe for exhausting all available memory. G (innodb_flush_log_at_trx_commit=0) defers log flushing to once per second, causing bursty I/O spikes and log-buffer stalls under high-concurrency writes rather than smooth, per-commit flushing.

The distractors are wrong for these reasons: C is fine - sync_binlog=10 is a moderate durability trade-off, not catastrophic. D is actually beneficial - O_DIRECT prevents double-buffering between InnoDB's buffer pool and the OS page cache, which is recommended practice. F looks harmful (query cache causes global mutex contention at scale) but query_cache_enabled is not a valid MySQL parameter name, so the setting would never apply; the correct parameter is query_cache_type.

Memory tip: Think "Too Small, Too Big, Too Big, Too Lazy" - table cache too small (A), buffer pool too big for the host RAM (B), temp table RAM limit too big per-session (E), log flushed too lazily (G).

No community discussion yet for this question.