MySQL Bugs: #104783: Update parameter such as innodb_log_writer_spin

Bug #104783	Update parameter such as innodb_log_writer_spin_delay will not come into effect
Submitted:	1 Sep 2021 5:16	Modified:	1 Sep 2021 13:02
Reporter:	linhua li (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Update Parameters will not come into effect actually.
Such as innodb_log_writer_spin_delay,
innodb_log_writer_timeout, innodb_log_flusher_spin_delay,
innodb_log_flusher_timeout, innodb_log_write_notifier_spin_delay,
innodb_log_write_notifier_timeout, innodb_log_flush_notifier_spin_delay
and innodb_log_flush_notifier_timeout.

How to repeat:
show global variables like "innodb_log_writer_spin_delay";
result: 10000;
set global innodb_log_writer_spin_delay=20000;
show global variables like "innodb_log_writer_spin_delay";
result: 20000;
it seems there is no problem.
But in log_writer thread,  function: 
inline Wait_stats wait(Stop_condition stop_condition) 
m_spin_delay has still been the old value(10000).
it means new value(20000) will not come into effect or we shutdown server and restart with innodb_log_writer_spin_delay=20000.

Suggested fix:
in function:
inline Wait_stats wait(Stop_condition stop_condition)
we check Parameters such as srv_log_flusher_spin_delay, if it changes, we should refresh immediately.

Hi Mr. li,

Thank you for your bug report.

What you describe is, actually, expected behaviour when setting or changing global configuration variable. It is also fully documented in our Reference Manual, which ( we are sure ) you have read it thoroughly.

As explained in the said Manual, changed global variable will take effect only on the new connections. Some global variable, also, can be changed only on the startup.

Hence, having all this in mind, what is it, exactly, that you are actually reporting ????

take innodb_log_writer_spin_delay for example, it was used in log_writer thread.
when mysql server start:
1.log_writer thread was created. Log_thread_waiting waiting was constructed, which waiting.m_spin_delay=srv_log_writer_spin_delay. for example, waiting.m_spin_delay = srv_log_writer_spin_delay = 10000.
2.mysql server finish starting.
3.set global innodb_log_writer_spin_delay which change to a new value, for example, new value is 20000.
4.we can find that srv_log_writer_spin_delay has been updated into 20000, but waiting.m_spin_delay is still 10000, which seems like innodb_log_writer_spin_delay can change nothing.

code dependent：
void log_writer(log_t *log_ptr) {
  ut_a(log_ptr != nullptr);

  log_t &log = *log_ptr;
  lsn_t ready_lsn = 0;

  log_writer_mutex_enter(log);

  Log_thread_waiting waiting{log, log.writer_event, srv_log_writer_spin_delay,
                             srv_log_writer_timeout};

  Log_write_to_file_requests_monitor write_to_file_requests_monitor{log};

  for (uint64_t step = 0;; ++step) {
    bool released = false;

    auto stop_condition = [&ready_lsn, &log, &released,
                           &write_to_file_requests_monitor](bool wait) {
      if (released) {
        log_writer_mutex_enter(log);
        released = false;
      }

      /* Advance lsn up to which data is ready in log buffer. */
      log_advance_ready_for_write_lsn(log);

      ready_lsn = log_buffer_ready_for_write_lsn(log);

      /* Wait until any of following conditions holds:
              1) There is some unwritten data in log buffer
              2) We should close threads. */

      if (log.write_lsn.load() < ready_lsn || log.should_stop_threads.load()) {
        return (true);
      }

      if (UNIV_UNLIKELY(
              log.writer_threads_paused.load(std::memory_order_acquire))) {
        return (true);
      }

      if (wait) {
        write_to_file_requests_monitor.update();
        log_writer_mutex_exit(log);
        released = true;
      }

      return (false);
    };

    const auto wait_stats = waiting.wait(stop_condition);
    .....
}

suggested fix:
in function:
struct Log_thread_waiting {
  inline Wait_stats wait(Stop_condition stop_condition) {
     .......
  }
}
add code to update m_spin_delay with srv_log_writer_spin_delay like this:
m_spin_delay = srv_log_writer_spin_delay.

Hi Mr. li,

What you suggest is certainly possible.

However, this change will have a ripple effect on other threads, where changes in delays and timeouts could cause negative side effects.

Still, your suggestion is accepted.

Verified as a feature request.