Innodb: 自动开启打印show engine status到err日志

一、问题描述

为什么我的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.

Linux公社的RSS地址 ： https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址： https://www.linuxidc.com/Linux/2019-04/157971.htm