问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

innodb buffer pool size 设置过小导致的问题

发布网友 发布时间:2022-09-21 10:56

我来回答

1个回答

热心网友 时间:2023-11-17 20:42

在上一篇文章《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。
声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com
手机垃圾清理使用哪个软件最好? 适合新手用的手机清理软件 苹果手机怎么把号码黑名单里? VUE视频相机使用教程 教你简单几步拍出大片 如何使用佳能相机录像 相机录像具体操作教程分享 羊头捣蒜做法图解 服刑期间领取养老金是否构成犯罪 狗狗胰腺炎最怕的三种蔬菜是什么 怎么喂食 银行卡给专业的贷款公司做流水有风险吗? 电话号可直接贷款吗,他知道我的实名电话,还用paos机刷了我的银行卡一... spring定时任务 pool-size设置多少 executor的pool-size和scheduler的pool-size的区别 hibernate中pool_size是什么属性 初一上册历史第一单元知识归纳总结 七年级上册历史第一单元的知识要点总结 有关verilog 模块里的变量调用 退火黑铁丝的规格 电流功率和安培是怎么换算的,1&#178;的电线怎么知道能过多大的电流 螺纹wire size是什么意思 hc28家具品牌介绍?家具怎么选? 货号2205FNV和2205GNV有什么区别 gnv是什么意思 GNV和GN2哪个好 河北20岁小伙突发痛风,只能坐轮椅就诊,导致痛风的因素有哪些? 老人腿脚不便,每天都坐轮椅,腿疼难以忍受, 为什么有人讲弹跳练完,老了以后腿疼啊?还说坐轮椅 我是美术学专业的学生,请问我考心理咨询师三级有那些条件 北京欢迎你中谢霆锋身边那个女的是? 请问谁知道西门子2128手机广告的女主角是谁? f什么时候发v的音 所有播放软件都不能听歌,为什么 工行汽车贷款好办吗 使用eclipse连接hdfs遭受拒绝怎么办 有没有自主充值有折扣的传奇霸主手游 windows下配置Eclipse连接hdfs错误:Listing folder content... 原始传奇(正版授权)哪里有折扣? 原始传奇(正版授权)哪里折扣最低? 传奇演义哪里折扣最低? 洗车工每日工作总结 洗车工每月工作总结 洗车实习总结500字 我是一个洗了四个多月的洗车工写工作总结和认识怎么写 猕猴桃怎么挑选才甜 请问一下索尼fe镜头什么意思 请问索尼fe镜头什么意思 张雪峰推荐未来专业有哪些?张雪峰建议男生选的专业名单(2022年参考) 2021年最没前景的十大专业-2021张雪峰建议选的专业 人民日报为什么点名批评了张雪峰的“职场毒鸡汤”? 如何看待张雪峰称考研头铁的人很多?该如何正确看待未来的职业规划? 脸皱在一起是什么猫