mysql 8.0 版本innodb 内存管理锁 buffer pool mutex 变化

最新推荐文章于 2023-02-27 18:41:52 发布

lixora

最新推荐文章于 2023-02-27 18:41:52 发布

阅读量575

点赞数

分类专栏： MySql 文章标签： java servlet 蓝桥杯

本文链接：https://blog.csdn.net/lixora/article/details/126764550

版权

MySql 专栏收录该内容

83 篇文章 2 订阅

订阅专栏

mysql 8.0 由原来的一个大buffer pool mutex被拆分成多个为free_list, LRU_list, zip_free, 和zip_hash单独使用mutex:

BufListMutex    LRU_list_mutex; /*!< LRU list mutex */
BufListMutex    free_list_mutex;/*!< free and withdraw list mutex */
BufListMutex    zip_free_mutex; /*!< buddy allocator mutex */
BufListMutex    zip_hash_mutex; /*!< zip_hash mutex */
ib_mutex_t    flush_state_mutex;/*!< Flush state protection mutex */

MySQL :: WL#8423: InnoDB: Remove the buffer pool mutex

虽然buffer pool 的锁由一个变成多个，但是仍然不建议生产环境在线resize buffer pool (innodb_buffer_pool_size)，还是会导致session hang；

##原因：

From the doc: 
Active transactions and operations performed through InnoDB APIs should be completed before resizing the buffer pool. When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to the rule is that concurrent access to the buffer pool is permitted while the buffer pool is defragmented and pages are withdrawn when buffer pool size is decreased. A drawback of allowing concurrent access is that it could result in a temporary shortage of available pages while pages are being withdrawn.

From the code:
/* Acquire all buffer pool mutexes and hash table locks */
  /* TODO: while we certainly lock a lot here, it does not necessarily
  buy us enough correctness, see a comment at buf_block_align. */
  for (ulint i = 0; i < srv_buf_pool_instances; ++i)
    mutex_enter(&(buf_pool_from_array(i)->LRU_list_mutex));
  for (ulint i = 0; i < srv_buf_pool_instances; ++i)
    hash_lock_x_all(buf_pool_from_array(i)->page_hash);
  for (ulint i = 0; i < srv_buf_pool_instances; ++i)
    mutex_enter(&(buf_pool_from_array(i)->zip_free_mutex));
  for (ulint i = 0; i < srv_buf_pool_instances; ++i)
    mutex_enter(&(buf_pool_from_array(i)->free_list_mutex));
  for (ulint i = 0; i < srv_buf_pool_instances; ++i)
    mutex_enter(&(buf_pool_from_array(i)->zip_hash_mutex));
  for (ulint i = 0; i < srv_buf_pool_instances; ++i)
    mutex_enter(&(buf_pool_from_array(i)->flush_state_mutex));

MySQL :: WL#8423: InnoDB: Remove the buffer pool mutex