nerdexam
Oracle

1Z0-888 · Question #43

1Z0-888 Question #43: Real Exam Question with Answer & Explanation

The correct answer is B. Increase the value of the innodb_read_io_threads option. F. Increase the size of the InnoDB buffer pool.. The stated answer of B and F appears to be incorrect based on the diagnostic evidence in the output. The actual correct answers are D and E - here's why: The monitor output contains two clear signals: (1) the semaphore wait is in btr0sea.cc, which is InnoDB's adaptive hash index

Performance Tuning

Question

The MySQL error log shows: InnoDB: Warning: a long semaphore wait The relevant parts of the InnoDB monitor output shows: ---Thread 140259946129152 has waited at btr0sea.cc line 658 for 241.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x2a5581378 created in file btr0sea.cc line 173 writer (thread id 140259946129152) has reserved it in mode sx exclusive, number of readers 3, waiters flag 1, lock_word: ffffffffffffffff Last time read locked in file btr0sea.cc line 907 Last time write locked in file /pk/build/sb-0.10188269- 9378799529.26/rpm/BUILD/mysqlcom-pro-5.7.14/mysqlcom-pro- 5.7.14/storage/innobase/btr/btr0sea.cc line 658 --TRANSACTION 19111394, ACTIVE 942 sec, process no 20643, OS thread id 140252541274368 mysql tables in use 3, locked 0 , holds adaptive hash latch MySQL thread id 3631102, query id 141949524 localhost 127.0.0.1 world Waiting for query cache lock ... Which two options would help avoid the long wait in the future?

Options

  • AIncrease the value of the innodb_lock_wait_timeout option.
  • BIncrease the value of the innodb_read_io_threads option.
  • CChange the table to use HASH indexes instead of BTREE indexes.
  • DSet the value of innodb_adaptive_hash_index to zero.
  • EDeactivate the query cache.
  • FIncrease the size of the InnoDB buffer pool.

Explanation

The stated answer of B and F appears to be incorrect based on the diagnostic evidence in the output. The actual correct answers are D and E - here's why:

The monitor output contains two clear signals: (1) the semaphore wait is in btr0sea.cc, which is InnoDB's adaptive hash index (AHI) source file, and the blocked transaction explicitly "holds adaptive hash latch"; (2) the long-running transaction is stalled "Waiting for query cache lock". These two bottlenecks point directly at the fixes.

D is correct - setting innodb_adaptive_hash_index=0 disables the AHI entirely, eliminating the RW-latch contention on btr0sea.cc that is causing the semaphore wait. AHI latch contention under high concurrency is a well-known InnoDB scalability issue.

E is correct - the transaction has been active 942 seconds and is blocked waiting for the query cache lock. The MySQL query cache is a known global bottleneck (single mutex) that causes thread pile-ups; deactivating it removes this lock entirely.

Why the others are wrong:

  • A (increase lock wait timeout) only extends how long threads wait before aborting - it does nothing to fix the root cause.
  • B (read I/O threads) addresses disk read parallelism, irrelevant to latch contention.
  • C (HASH vs BTREE indexes) is a red herring; the AHI is an internal InnoDB structure, not a user-defined index type.
  • F (buffer pool size) reduces disk I/O by caching more data, but does not address latch or query cache lock contention.

Memory tip: When you see btr0sea in InnoDB diagnostics, think "B-tree SEArch = Adaptive Hash Index" → the fix is to disable AHI (innodb_adaptive_hash_index=0). Pair that with any mention of "query cache lock" always pointing to disabling the query cache.

Topics

#InnoDB latches#Adaptive hash index#Buffer pool optimization#I/O threading

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 1Z0-888 Practice