bufferpool 如何计算大小?

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

缓冲池是内存中的一块主要区域用来缓存InnoDB的表和索引数据。 缓冲池可以将经常使用的数据保存在内存用于直接访问。在专用数据库服务器中, 高达80%的物理内存可以用来作为缓冲池。

Buffer Pool LRU Algorithm

The buffer pool is managed as a list using a variation of the LRU algorithm. When room is needed to add a new page to the buffer pool, the least recently used page is evicted and a new page is added to the middle of the list. This midpoint insertion strategy treats the list as two sublists:

At the head, a sublist of new ("young" )pages that were accessed recently

At the tail, a sublist of old pages that were accessed less recently

缓冲池是用LRU算法来进行管理的。 当缓冲池中要插入一个新页时, 缓冲池中最近最少使用的页将会被驱逐,新页将会被添加到中间列表中。中间插入策略将列表分成两个子列表:

在头部, 是比较新的列表,会被经常使用。

在尾部,是比较旧的列表,被使用的比较少。

The algorithm keeps frequently used pages in the new sublist. The old sublist contains less frequently used pages; these pages are candidates for eviction.

LRU算法会将经常访问的页保持在新子列表中。 老的子列表中包含访问比较少的页,这些页会作为被驱逐的页候选。

Buffer Pool Configuration

You can configure the various aspects of the buffer pool to improve performance.

可以从很多方面配置缓冲池来提高性能。

Ideally, you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. See

可以通过提高缓冲池的大小来提高数据库性能。 缓冲池越大能容纳的数据页越多,InnoDB就越像一个页游内存数据库。

On 64-bit systems with sufficient memory, you can split the buffer pool into multiple parts to minimize contention for memory structures among concurrent operations. For details

在64位操作系统,在内存充足的情况下可以将缓冲池分为多个部分。

You can keep frequently accessed data in memory regardless of sudden spikes of awww.sangpi.comctivity from operations that would bring large amounts of infrequently accessed data into the buffer pool. For details,

可以将经常访问的数据在缓冲池中做持久化。

You can control how and when to perform read-ahead requests to prefetch pages into the buffer pool asynchronously in anticipation that the pages are needed soon. For details, see 缓冲池可以做预读。

You can control when background flushing occurs and whether or not the rate of flushing is dynamically adjusted based on workload. For details, see

可以控制缓冲池的刷新频率机刷新比例。

You can configure how InnoDB preserves the current buffer pool state to avoid a lengthy warmup period after a server restart. For details, see

可以保存缓冲池的状态,避免长时间重启。

Monitoring the Buffer Pool Using the InnoDB Standard Monitor
InnoDB Standard Monitor output, which can be accessed using provides metrics regarding operation of the buffer pool. Buffer pool metrics are located in the BUFFER POOL AND MEMORY section of InnoDB Standard Monitor output:

可以通过show engine innodb status;命令去监控缓冲池的状态。 缓冲池的指标可以重BUFFER POOL AND MEMORY 部分查看:

bufferpool 如何计算大小?

上一篇:同步代码到Gitee


下一篇:分享MSSQL、MySql、Oracle的大数据批量导入方法及编程手法细节