共计 2443 个字符,预计需要花费 7 分钟才能阅读完成。
一、问题描述
为什么我的 err 日志里面有大量的 show engine innodb status 的记录,我自己并没有开启 innodb_status_output 参数。
二、问题分析
通过查看日志,发现如下输出:
2019-03-21T17:00:02.375231Z 1230497 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (338 search iterations)! 0 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible
that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a
newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below.
Pending flushes (fsync) log: 0; buffer pool: 0. 1446962050 OS file reads, 545881917 OS file writes,
376257282 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.
日志也写得很清楚。应该是 free block 不够了 Innodb 自动开启了。但是我们需要源码验证一下。
三、源码验证
在源码的 buf_LRU_handle_lack_of_free_blocks 函数中我们看到如下:
if ((current_ms > started_ms + 2000)
&& (current_ms > last_printout_ms + 2000)
&& srv_buf_pool_old_size == srv_buf_pool_size) {
ib::warn() << “Difficult to find free blocks in the buffer pool”
” (” << n_iterations << ” search iterations)! “
<< flush_failures << ” failed attempts to”
” flush a page! Consider increasing the buffer pool”
” size. It is also possible that in your Unix version”
” fsync is very slow, or completely frozen inside”
” the OS kernel. Then upgrading to a newer version”
” of your operating system may help. Look at the”
” number of fsyncs in diagnostic info below.”
” Pending flushes (fsync) log: “
<< fil_n_pending_log_flushes
<< “; buffer pool: “
<< fil_n_pending_tablespace_flushes
<< “. ” << os_n_file_reads << ” OS file reads, “
<< os_n_file_writes << ” OS file writes, “
<< os_n_fsyncs
<< ” OS fsyncs. Starting InnoDB Monitor to print”
” further diagnostics to the standard output.”;
last_printout_ms = current_ms;
*mon_value_was = srv_print_innodb_monitor;
*started_monitor = true;
srv_print_innodb_monitor = true;
os_event_set(srv_monitor_event);
这里不仅打印出了日志同时设置了参数 srv_print_innodb_monitor = true; 并且开始 os_event_set(srv_monitor_event); 开启了 monitor 打印线程。那我们看看 srv_print_innodb_monitor 对应什么参数呢。如下:
static MYSQL_SYSVAR_BOOL(status_output, srv_print_innodb_monitor,
PLUGIN_VAR_OPCMDARG, “Enable InnoDB monitor output to the error log.”, NULL, innodb_status_output_update, FALSE);
实际上就是 innodb_status_output 被自动开了。当然如果查看调用可以在上层函数 buf_LRU_get_free_block 中查看到调用,实际上就是在 free list 找不到空闲的 block 的时候会做输出。buf_LRU_get_free_block 还包含了一个块的分配流程大约如下,可自行参考:
- If there is a block in the free list, take it . 如果这里找不到就会自动开启 innodb_status_output
- If no block was in the free list, search from the end of the LRU list and try to free a block there.
- No free block was found: try to flush the LRU list.
: