&The code checking the dirty pages percentage is a part of srv_master_thread() function. Everytime when dirty pages level is outpassing the requested value the buf_flush_batch() function is called to flush dirty pages to the disk storage. Let's get a more close look on the master thread code, and I've splitted it in 3 parts:
PART-1
From this part the buf_flush_batch() is called if dirty pages level outpassed the required value:
/***********************************************************************
The master thread controlling the server. */
os_thread_ret_t
srv_master_thread(
/*==============*/
...
if (UNIV_UNLIKELY(buf_get_modified_ratio_pct()
> srv_max_buf_pool_modified_pct)) {
/* Try to keep the number of modified pages in the
buffer pool under the limit wished by the user */
n_pages_flushed = buf_flush_batch(BUF_FLUSH_LIST,
PCT_IO(100),
ut_dulint_max);
/* If we had to do the flush, it may have taken
even more than 1 second, and also, there may be more
to flush. Do not sleep 1 second during the next
iteration of this loop. */
skip_sleep = TRUE;
}
...
PART-2
From this part the buf_flush_batch() is called if I/O activity was low during last 10 sec and innodb_extra_dirty_writes is enabled (enabled by default):
...
/* ---- We perform the following code approximately once per
10 seconds when there is database activity */
#ifdef MEM_PERIODIC_CHECK
/* Check magic numbers of every allocated mem block once in 10
seconds */
mem_validate_all_blocks();
#endif
/* If i/os during the 10 second period were less than 200% of
capacity, we assume that there is free disk i/o capacity
available, and it makes sense to flush srv_io_capacity pages.
Note that this is done regardless of the fraction of dirty
pages relative to the max requested by the user. The one second
loop above requests writes for that case. The writes done here
are not required, and may be disabled. */
n_pend_ios = buf_get_n_pending_ios() + log_sys->n_pending_writes;
n_ios = log_sys->n_log_ios + buf_pool->n_pages_read
+ buf_pool->n_pages_written;
if (srv_extra_dirty_writes &
n_pend_ios < 3 && (n_ios - n_ios_very_old < PCT_IO(200))) {
srv_main_thread_op_info = "flushing buffer pool pages";
buf_flush_batch(BUF_FLUSH_LIST, PCT_IO(100), ut_dulint_max);
srv_main_thread_op_info = "flushing log";
/* No fsync when srv_flush_log_at_trx_commit 1 */
log_buffer_flush_maybe_sync();
srv_async_flush++;
}
...
Part-3
The master thread is looping here on purge:
...
/* We run a full purge every 10 seconds, even if the server
were active */
n_pages_purged = 1;
last_flush_time = time(NULL);
while (n_pages_purged) {
if (srv_fast_shutdown && srv_shutdown_state > 0) {
goto background_loop;
}
srv_main_thread_op_info = "purging";
n_pages_purged = trx_purge();
current_time = time(NULL);
if (difftime(current_time, last_flush_time) > 1) {
srv_main_thread_op_info = "flushing log";
log_buffer_flush_to_disk();
last_flush_time = current_time;
srv_sync_flush++;
}
}
...
Initially what I supposed:
- the function buf_get_modified_ratio_pct() is for sure was tested over several years now, so very small probability it contains any bug...
- as well it uses the same functions to calculate the dirty pages percentage as "show innodb status" command, so the returned value should be correct...
- as well it's hard to believe there is an error in comparing condition values :-)
- the only thing is possible -- probably the amount of flushed data is not big enough ???...
So, my initial intention was to trace the flushing frequency involved within PART-1 and PART-2 of code and see less or more what kind of values will be observed, etc. For starting all I've just added a few fprintf( stderr, ...) into the code to print the values when any of 2 flushing condition is matched. Messages then are written within a mysqld log file and may be monitored by a simple "tail -f mysqld.log". What I've expected is by auto-incrementing flushing to bring the dirty pages level to some stable values first, and then to see how better it may be improved...
Now, check first you're sitting well in your chair! :-))
READY?..
WHAT KIND OF VALUES DO YOU THINK WHERE PRINTED WITHIN A LOG FILE ?...
???
???
For all my big surprise...
NOTHING AT ALL WAS PRINTED!!!....
???
WHY ?....
???
JUST BECAUSE THE PART-1 NOR THE PART-2 ARE NEVER REACHED!!
Hard to believe?.. ;-))
Once workload is started, processing is NEVER LEAVING THE PART-3 LOOP !!!
All what is doing the Master Thread is looping on trx_purge() ...
Hard to believe, but it's so...
