在上一篇文章《The total number of locks exceeds the lock table size 异常处理》中已经提到了在innodb buffer pool size设置较小遇到大事务时导致大事务执行失败。除了文章到提到的异常，还会伴随另外一个waring或者异常。
在同一个实例的错误日志中发现如下信息：
180306 7:48:41 InnoDB: WARNING: over 67 percent of the buffer pool is occupied by
InnoDB: lock heaps or the adaptive hash index! Check that your
InnoDB: transactions do not set too many row locks.
InnoDB: Your buffer pool size is 1191 MB. Maybe you should make
InnoDB: the buffer pool bigger?
InnoDB: Starting the InnoDB Monitor to print diagnostics, including
InnoDB: lock heap and hash index sizes.
超过67%的buffer pool 空间被lock heap或者AHI占用，检查事务是否设置多太多的行锁，buffer pool只有1191M，需要设置buffer pool更大一些。
提示信息说的很明显，lock heap 或者AHI占用了过多的buffer pool空间（也就是说留给各个block list的空间不足），这里提示的是67%，上一篇文章中，free list + lru list < buffer pool / 4 则会使得事务终止。
那说明在这之前错误日志已经已有提示该问题。现在来分析看看这个warning产生的原因：
还提示信息是下面函数产生（mysql 5.5.9,在mysql 5.7中则是buf_LRU_check_size_of_non_data_objects函数）：
/******************************************************************//**
Returns a free block from the buf_pool. The block is taken off the
free list. If it is empty, blocks are moved from the end of the
LRU list to the free list. 该函数从free list返回一个free block,如果free list 为空，则从LRU list的尾部移除一个到free list，再返回。
@return the free control block, in state BUF_BLOCK_READY_FOR_USE */
UNIV_INTERN
buf_block_t*
buf_LRU_get_free_block(
/*===================*/
buf_pool_t* buf_pool, /*!< in: buffer pool instance */
ulint zip_size) /*!< in: compressed page size in bytes,
or 0 if uncompressed tablespace */
{
buf_block_t* block = NULL;
ibool freed;
ulint n_iterations = 1;
ibool mon_value_was = FALSE;
ibool started_monitor = FALSE;
loop:
buf_pool_mutex_enter(buf_pool);
if (!recv_recovery_on && UT_LIST_GET_LEN(buf_pool->free)
+ UT_LIST_GET_LEN(buf_pool->LRU) < buf_pool->curr_size / 20) { 当前free list + lru list 小于buffer pool size的5%
ut_print_timestamp(stderr);
fprintf(stderr,
" InnoDB: ERROR: over 95 percent of the buffer pool"
" is occupied by\n"
"InnoDB: lock heaps or the adaptive hash index!"
" Check that your\n"
"InnoDB: transactions do not set too many row locks.\n"
"InnoDB: Your buffer pool size is %lu MB."
" Maybe you should make\n"
"InnoDB: the buffer pool bigger?\n"
"InnoDB: We intentionally generate a seg fault"
" to print a stack trace\n"
"InnoDB: on Linux!\n",
(ulong) (buf_pool->curr_size
/ (1024 * 1024 / UNIV_PAGE_SIZE)));
ut_error;
} else if (!recv_recovery_on
&& (UT_LIST_GET_LEN(buf_pool->free)
+ UT_LIST_GET_LEN(buf_pool->LRU))
< buf_pool->curr_size / 3) { 当前free list + lru list 小于buffer pool size的33%
if (!buf_lru_switched_on_innodb_mon) {
/* Over 67 % of the buffer pool is occupied by lock
heaps or the adaptive hash index. This may be a memory
leak! */
ib::warn() << "Over 67 percent of the buffer pool is"
" occupied by lock heaps or the adaptive hash"
" index! Check that your transactions do not"
" set too many row locks. Your buffer pool"
" size is "
<< (buf_pool->curr_size
/ (1024 * 1024 / UNIV_PAGE_SIZE))
<< " MB. Maybe you should make the buffer pool"
" bigger?. Starting the InnoDB Monitor to print"
" diagnostics, including lock heap and hash"
" index sizes.";
buf_lru_switched_on_innodb_mon = true;
srv_print_innodb_monitor = TRUE;
os_event_set(lock_sys->timeout_event);
}
。。。
而数据读取，更新，插入都需要free block，则需要调用该函数获取一个free block。为了保持buffer pool中可用的block (free list + lru list)足够多，每次调用都会判断一次当前的buffer pool的使用情况。
从当时show engine innodb status 的输出:
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1277050880; in additional pool allocated 0
Dictionary memory allocated 482195
Buffer pool size 76159
Free buffers 71
Database pages 25314
Old database pages 9324
Modified db pages 17359
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
得到free list 为71，lru list 为25314 ，而buffer size 大小为76159。（71 + 25314）/76159 = 33%因此触发可上述warning。
而当时执行的语句正是上一篇文章中的insert xxx select xxx 语句：
---TRANSACTION 27A7D9, ACTIVE 7986 sec, process no 10782, OS thread id 140449731196672 inserting
mysql tables in use 2, locked 2
9338873 lock struct(s), heap size 831568312, 49139875 row lock(s), undo log entries 18795462
MySQL thread id 16, query id 8912946 Sending data
INSERT INTO
T_XXX_TMP(
----------------------
可用看出lock heap size 为831568312 = 800M, 831568312 / 1277050880 = 65.12%。 说明lock heap size就已经占用了总buffer pool大小的65%。还有其他的一些内存使用，超过了67%。
当然这里只是warning,如果事务足够大的话，则有可能触发到95%的的error异常。
从这个调用流程来看，事务执行加锁，消耗了大量的buffer pool ，申请free block读取数据，插入数据也需要申请block，然后检测buffer pool的可用空间，触发warning，紧接着执行插入逻辑，
调用buf_LRU_buf_pool_running_out判断buffer pool的可用空间，触发异常，导致事务中断。因此在申请free block和执行插入操作过程中，都会判断buffer pool可用空间，判断的逻辑都是一样，
两者是先后出现的，解决的方式也是一样。拆小事务或者调大innodb buffer poo size。
网友评论