nerdexam
Oracle

1Z0-888 · Question #45

You are using the Performance Schema to investigate replication on a slave which has a single master. The option slave-parallel-type is set to DATABASE. mysql> SELECT THREAD_ID, threads.NAME…

The correct answer is B. The slave is configured with slave_parallel_workers = 4 E. The slave cannot process the relay log fast enough to use all threads. Four slave worker threads appear in the output (THREAD_IDs 22–25), which directly proves slave_parallel_workers = 4 (B). Workers 22 and 23 show TotalCount = 0, meaning they have never processed a single event since replication started - with all instruments enabled and threads…

Replication

Question

You are using the Performance Schema to investigate replication on a slave which has a single master. The option slave-parallel-type is set to DATABASE. mysql> SELECT THREAD_ID, threads.NAME, SUM(COUNT_STAR) AS TotalCount, SUM (SUM_TIMER_WAIT) AS TotalTime -> FROM performance_schema.events_waits_summary_by_thread_by_event_name -> INNER JOIN performance_schema.threads USING (THREAD_ID) -> WHERE threads.NAME LIKE 'thread/sql/slave_%' -> GROUP BY THREAD_ID, threads.NAME; +-----------+-----------------------+------------+-----------+ | THREAD_ID | NAME | TotalCount | TotalTime | +-----------+-----------------------+------------+-----------+ | 20 | thread/sql/slave_io | 5785 | 654785731193 | | 21 | thread/sql/slave_sql | 3875 | 96931638913 | | 22 | thread/sql/slave_worker | 0 | 0 | | 23 | thread/sql/slave_worker | 0 | 0 | | 24 | thread/sql/slave_worker | 346730 | 7262131209667 | | 25 | thread/sql/slave_worker | 597127 | 15498842906584 | +-----------+-----------------------+------------+-----------+ Assume that all instruments and consumers are enabled and all threads are instrumented. Which two facts can be concluded from the given output?

Options

  • AThe salve has two intermediate relay slaves connected to it.
  • BThe slave is configured with slave_parallel_workers = 4
  • CAt most, two schemas are being updates concurrently.
  • DTHREAD_ID 21 has stopped running.
  • EThe slave cannot process the relay log fast enough to use all threads.
  • FThe server needs more cores to use all slave threads.

How the community answered

(35 responses)
  • A
    20% (7)
  • B
    63% (22)
  • C
    3% (1)
  • D
    3% (1)
  • F
    11% (4)

Explanation

Four slave worker threads appear in the output (THREAD_IDs 22–25), which directly proves slave_parallel_workers = 4 (B). Workers 22 and 23 show TotalCount = 0, meaning they have never processed a single event since replication started - with all instruments enabled and threads instrumented, the only explanation is that the relay log isn't arriving fast enough to dispatch work to all four workers, confirming E.

Why the distractors are wrong:

  • A - Intermediate relay slaves would show additional slave_io and slave_sql threads, not extra slave_worker threads; none appear here.
  • C - The zero counts on workers 22/23 reflect a relay log throughput bottleneck, not a hard limit on schemas; you cannot infer a schema count from idle workers.
  • D - Thread 21 (slave_sql) has TotalCount = 3875, so it has processed events and is running as the coordinator thread.
  • F - The bottleneck is relay log I/O speed (the I/O thread can't feed events fast enough), not CPU core count; more cores wouldn't fix an I/O-bound problem.

Memory tip: Think "4 workers listed = slave_parallel_workers=4; zero-count workers = starved workers = relay log can't keep up." The Performance Schema never lies about what has run - a count of 0 with all instruments enabled means that thread has genuinely never been used.

Topics

#Performance Schema#Parallel Replication#Slave Workers#Replication Bottlenecks

Community Discussion

No community discussion yet for this question.

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