问
当咱们应用一个事务操作很多数据时, MySQL 有时会报错: The total number of locks exceeds the lock table size
依据官网文档, 咱们须要调大 buffer pool 的大小:
本期试验, 咱们来摸索一下锁用得多与 buffer pool 大小的关系
试验
咱们用老办法建一个数据库, 并将 buffer pool 大小调整到了最小值 5M, 不便咱们复现问题
当初来模仿一个用锁特地多的事务:
咱们还是用老办法让表翻倍, 来不停地占用锁.
看一下成果:
咱们能够通过 information_schema.INNODB_TRX 来查看事务应用了多少锁, 解释一下上图中标记的这几个状态:
- trx_tables_locked: 该事务锁了几张表
- trx_rows_locked: 该事务锁了多少数据行
- trx_lock_structs: 该事务一共用到了多少个锁构造. 一个锁构造用于锁住多个表或多个行
- trx_lock_memory_bytes: 该事务的锁构造一共用了多少内存
再来看看 buffer pool 的状态:
解释一下 Buffer pool 的这两个状态:
- total 是 Buffer pool 的总页数
- misc 是 Buffer pool 中非数据页的页数
咱们持续造数据, 让该事务应用的锁越来越多, 再来看看状态:
与最后的状态相比, 该事务应用的锁的内存增长了 (1269968 – 24784 =) 1245184 字节 = 1216 k, 而 buffer pool 非数据页多应用了 (84-8 =) 76 页, 每页 16k, 总共 1216 k
也就是说, 该事务的锁内存均分自 buffer pool .
咱们持续造数据, 造到报错为止, 不停查看状态:
能够看到, 产生报错时, 事务会回滚. 回滚前, 该事务的锁占用的内存大小是 3842256 字节, 也就是 (3842256 / 1024 / 16 =) 234.5 页, 此时 buffer pool 使用率大略是 (234.5 / 320 =) 73%
咱们的试验到此结束, 通过此试验咱们晓得:
- 如果事务用到了很多锁, 那么锁构造应用的内存是从 Buffer pool 中调配的
- Buffer pool 使用率比拟高时, 就会报错: The total number of locks exceeds the lock table size
对于 Buffer pool 应用到多少, 会进行报错, 咱们参考如下 MySQL 源码:
咱们能够看到这个实践阈值是 75%, 当 Buffer pool 中 非数据页的大小超过了 75%, 咱们就会再无奈调配更多的锁.
(试验中测定的 73% 与这个实践阈值相差不大)
小贴士
当 Buffer pool 的大小有余时, Buffer pool 能够回收数据页 (洁净页可间接回收, 脏页可刷脏后回收), 而不能间接回收 非数据页.
MySQL 设定这个 75% 的阈值, 也是为了让 Buffer pool 中留一部分空间给数据页, 毕竟 Buffer pool 的最大作用是给数据页做缓存.
对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！