MySQL 8.0 Reference Manual（读书笔记71节--InnoDB Buffer Pool Configura

落雨出太阳

1.Configuring Multiple Buffer Pool Instances

【目的是未来提高并发，减少竞争】
For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. This feature is typically intended for systems with a buffer pool size in the multi-gigabyte range. Multiple buffer pool instances are configured using the innodb_buffer_pool_instances configuration option, and you might also adjust the innodb_buffer_pool_size value.
When the InnoDB buffer pool is large, many data requests can be satisfied by retrieving from memory. You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pools randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool.
Prior to MySQL 8.0, each buffer pool was protected by its own buffer pool mutex. In MySQL 8.0 and later, the buffer pool mutex was replaced by several list and hash protecting mutexes, to reduce contention.
To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum). This option takes effect only when you set innodb_buffer_pool_size to a size of 1GB or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.
2.Making the Buffer Pool Scan Resistant【rɪˈzɪstənt 有抵抗力的】

设计的目的，就是让高附加值的数据尽可能长时间呆在Buffer Pool中
Rather than using a strict LRU algorithm, InnoDB uses a technique to minimize the amount of data that is brought into the buffer pool and never accessed again. The goal is to make sure that frequently accessed (“hot”) pages remain in the buffer pool, even as read-ahead and full table scans bring in new blocks that might or might not be accessed afterward.
 Newly read blocks are inserted into the middle of the LRU list. All newly read pages are inserted at a location that by default is 3/8 from the tail of the LRU list. The pages are moved to the front of the list (the most-recently used end) when they are accessed in the buffer pool for the first time. Thus, pages that are never accessed never make it to the front portion of the LRU list, and “age out” sooner than with a strict LRU approach. This arrangement divides the LRU list into two segments, where the pages downstream of the insertion point are considered “old” and are desirable victims for LRU eviction.

 You can control the insertion point in the LRU list and choose whether InnoDB applies the same optimization to blocks brought into the buffer pool by table or index scans. The configuration parameter innodb_old_blocks_pct controls the percentage of “old” blocks in the LRU list. The default value of innodb_old_blocks_pct is 37, corresponding to the original fixed ratio of 3/8. The value range is 5 (new pages in the buffer pool age out very quickly) to 95 (only 5% of the buffer pool is reserved for hot pages, making the algorithm close to the familiar LRU strategy).
The optimization that keeps the buffer pool from being churned by read-ahead can avoid similar problems due to table or index scans. In these scans, a data page is typically accessed a few times in quick succession and is never touched again. The configuration parameter innodb_old_blocks_time specifies the time window (in milliseconds) after the first access to a page during which it can be accessed without being moved to the front (most-recently used end) of the LRU list. The default value of innodb_old_blocks_time is 1000. Increasing this value makes more and more blocks likely to age out faster from the buffer pool.
3.Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)

A read-ahead request is an I/O request to prefetch【预读；数据预取】 multiple pages in the buffer pool asynchronously【异步的】, in anticipation【ænˌtɪsɪˈpeɪʃn 期待；预计；预期】 of impending【ɪmˈpendɪŋ 接下来；即将发生的；逼近的】 need for these pages. The requests bring in all the pages in one extent. InnoDB uses two read-ahead algorithms to improve I/O performance:
Linear【lɪniər 线性的】 read-ahead is a technique that predicts what pages might be needed soon based on pages in the buffer pool being accessed sequentially. You control when InnoDB performs a read-ahead operation by adjusting the number of sequential page accesses required to trigger an asynchronous read request, using the configuration parameter innodb_read_ahead_threshold. Before this parameter was added, InnoDB would only calculate whether to issue an asynchronous【eɪˈsɪŋkrənəs  不同时存在或不同时发生的；】 prefetch request for the entire【ɪnˈtaɪər 整个】 next extent when it read the last page of the current extent.
The configuration parameter innodb_read_ahead_threshold controls how sensitive【sensətɪv 敏感的；灵敏的；有悟性的；此处应该解释为有悟性的，智能的】 InnoDB is in detecting patterns of sequential page access. If the number of pages read sequentially【səˈkwɛntʃəli 连续地；有顺序地】 from an extent is greater than or equal to innodb_read_ahead_threshold, InnoDB initiates an asynchronous read-ahead operation of the entire following extent. innodb_read_ahead_threshold can be set to any value from 0-64. The default value is 56. The higher the value, the more strict the access pattern check. For example, if you set the value to 48, InnoDB triggers a linear read-ahead request only when 48 pages in the current extent have been accessed sequentially. If the value is 8, InnoDB triggers an asynchronous read-ahead even if as few as 8 pages in the extent are accessed sequentially. You can set the value of this parameter in the MySQL configuration file, or change it dynamically with the SET GLOBAL statement, which requires privileges sufficient to set global system variables.
Random【ˈrændəm 随机的】 read-ahead is a technique that predicts when pages might be needed soon based on pages already in the buffer pool, regardless of the order in which those pages were read. If 13 consecutive【kənˈsekjətɪv 连续的；连续不断的】 pages from the same extent are found in the buffer pool, InnoDB asynchronously issues a request to prefetch the remaining pages of the extent. To enable this feature, set the configuration variable innodb_random_read_ahead to ON.
The SHOW ENGINE INNODB STATUS command displays statistics to help you evaluate the effectiveness of the read-ahead algorithm. Statistics include counter information for the following global status variables:
• Innodb_buffer_pool_read_ahead
• Innodb_buffer_pool_read_ahead_evicted
• Innodb_buffer_pool_read_ahead_rnd
以上三个状态值，对innodb_random_read_ahead变量的调优，很有用处。
4.Configuring Buffer Pool Flushing

InnoDB performs certain tasks in the background, including flushing of dirty pages from the buffer pool. Dirty pages are those that have been modified but are not yet written to the data files on disk.
In MySQL 8.0, buffer pool flushing is performed by page cleaner threads. The number of page cleaner threads is controlled by the innodb_page_cleaners variable, which has a default value of 4. However, if the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances.-----当你设置的数值大于innodb_buffer_pool_instances数值时，也会自动调整成 等于 innodb_buffer_pool_instances。
Buffer pool flushing is initiated when the percentage of dirty pages reaches the low water mark value defined by the innodb_max_dirty_pages_pct_lwm variable. The default low water mark is 10% of buffer pool pages. A innodb_max_dirty_pages_pct_lwm value of 0 disables this early flushing behaviour.---脏页需要待flushing的定义
The purpose of the innodb_max_dirty_pages_pct_lwm threshold is to control the percentage dirty pages in the buffer pool and to prevent the amount of dirty pages from reaching the threshold defined by the innodb_max_dirty_pages_pct variable, which has a default value of 90. InnoDB aggressively【侵略地；攻击地；有闯劲地;】 flushes buffer pool pages if the percentage of dirty pages in the buffer pool reaches the innodb_max_dirty_pages_pct threshold.
When configuring innodb_max_dirty_pages_pct_lwm, the value should always be lower than the innodb_max_dirty_pages_pct value.
Additional variables permit fine-tuning of buffer pool flushing behavior:---对优化 buffer pool flushing 有影响的几个变量参数
• The innodb_flush_neighbors variable defines whether flushing a page from the buffer pool also flushes other dirty pages in the same extent. ---是否一起刷新相邻页
     • The default setting of 0 disables innodb_flush_neighbors. Dirty pages in the same extent are not flushed. This setting is recommended for non-rotational storage (SSD) devices where seek time is not a significant factor.
     • A setting of 1 flushes contiguous dirty pages in the same extent
     • A setting of 2 flushes dirty pages in the same extent.
When table data is stored on a traditional HDD storage device, flushing neighbor pages in one operation reduces I/O overhead (primarily for disk seek operations) compared to flushing individual pages at different times. For table data stored on SSD, seek time is not a significant factor and you can disable this setting to spread out write operations.
• The innodb_lru_scan_depth variable specifies, per buffer pool instance, how far down the buffer pool LRU list the page cleaner thread scans looking for dirty pages to flush. This is a background operation performed by a page cleaner thread once per second.
A setting smaller than the default is generally suitable for most workloads. A value that is significantly higher than necessary may impact performance. Only consider increasing the value if you have spare I/O capacity under a typical workload. Conversely, if a write-intensive workload saturates your I/O capacity, decrease the value, especially in the case of a large buffer pool.
When tuning innodb_lru_scan_depth, start with a low value and configure the setting upward with the goal of rarely seeing zero free pages. Also, consider adjusting innodb_lru_scan_depth when changing the number of buffer pool instances, since innodb_lru_scan_depth * innodb_buffer_pool_instances defines the amount of work performed by the page cleaner thread each second.
 
The innodb_flush_neighbors and innodb_lru_scan_depth variables are primarily intended for write-intensive workloads. With heavy DML activity, flushing can fall behind if it is not aggressive enough, or disk writes can saturate【sætʃəreɪt 饱和的；】 I/O capacity if flushing is too aggressive. The ideal settings depend on your workload, data access patterns, and storage configuration (for example, whether data is stored on HDD or SSD devices).
Adaptive【əˈdæptɪv 自适应的】 Flushing

InnoDB uses an adaptive flushing algorithm to dynamically adjust the rate of flushing based on the speed of redo log generation and the current rate of flushing. The intent is to smooth overall performance by ensuring that flushing activity keeps pace with the current workload. Automatically adjusting the flushing rate helps avoid sudden dips in throughput that can occur when bursts of I/O activity due to buffer pool flushing affects the I/O capacity available for ordinary read and write activity.
 Sharp checkpoints, which are typically associated with write-intensive【ɪnˈtensɪv 密集的；彻底的】 workloads that generate a lot of redo entries, can cause a sudden change in throughput, for example. A sharp checkpoint occurs when InnoDB wants to reuse a portion of a log file. Before doing so, all dirty pages with redo entries in that portion of the log file must be flushed. If log files become full, a sharp checkpoint occurs, causing a temporary reduction in throughput. This scenario can occur even if innodb_max_dirty_pages_pct threshold is not reached.
The adaptive flushing algorithm helps avoid such scenarios by tracking the number of dirty pages in the buffer pool and the rate at which redo log records are being generated. Based on this information, it decides how many dirty pages to flush from the buffer pool each second, which permits it to manage sudden changes in workload.
The innodb_adaptive_flushing_lwm variable defines a low water mark for redo log capacity. When that threshold is crossed, adaptive flushing is enabled, even if the innodb_adaptive_flushing variable is disabled.
Internal benchmarking has shown that the algorithm not only maintains【meɪnˈteɪnz 维持；保养】 throughput over time, but can also improve overall throughput significantly【sɪɡˈnɪfɪkəntli 显著地；有重大意义的】. However, adaptive flushing can affect the I/O pattern of a workload significantly and may not be appropriate in all cases. It gives the most benefit when the redo log is in danger of filling up. If adaptive flushing is not appropriate to the characteristics of your workload, you can disable it. Adaptive flushing controlled by the innodb_adaptive_flushing variable, which is enabled by default.---虽然有好处，但也不是在所有的场景下都适合。
---影响adaptive flushing的因素
innodb_flushing_avg_loops defines the number of iterations【迭代;次数;重复进行】 that InnoDB keeps the previously calculated snapshot of the flushing state, controlling how quickly adaptive flushing responds to foreground workload changes. A high innodb_flushing_avg_loops value means that InnoDB keeps the previously calculated snapshot longer, so adaptive flushing responds more slowly. When setting a high value it is important to ensure that redo log utilization does not reach 75% (the hardcoded limit at which asynchronous flushing starts), and that the innodb_max_dirty_pages_pct threshold keeps the number of dirty pages to a level that is appropriate for the workload.
Systems with consistent workloads, a large log file size (innodb_log_file_size), and small spikes that do not reach 75% log space utilization should use a high innodb_flushing_avg_loops value to keep flushing as smooth as possible. For systems with extreme load spikes or log files that do not provide a lot of space, a smaller value allows flushing to closely track workload changes, and helps to avoid reaching 75% log space utilization.
Be aware that if flushing falls behind, the rate of buffer pool flushing can exceed the I/O capacity available to InnoDB, as defined by innodb_io_capacity setting. The innodb_io_capacity_max value defines an upper limit on I/O capacity in such situations, so that a spike【spaɪk 尖峰；尖刺】 in I/O activity does not consume the entire I/O capacity of the server.
The innodb_io_capacity setting is applicable to all buffer pool instances. When dirty pages are flushed, I/O capacity is divided equally among buffer pool instances.
Limiting Buffer Flushing During Idle Periods

As of MySQL 8.0.18, you can use the innodb_idle_flush_pct variable to limit the rate of buffer pool flushing during idle periods, which are periods of time that database pages are not modified. The innodb_idle_flush_pct value is a percentage of the innodb_io_capacity setting, which defines the number of I/O operations per second available to InnoDB. The default innodb_idle_flush_pct value is 100, which is 100 percent of the innodb_io_capacity setting. To limit flushing during idle periods, define an innodb_idle_flush_pct value less than 100.
Limiting page flushing during idle periods can help extend the life of solid state storage devices. Side【saɪd 侧面；一边；】 effects of limiting page flushing during idle periods may include a longer shutdown time following a lengthy idle period, and a longer recovery period should a server failure occur.
 
---《Configuring Multiple Buffer Pool Instances》《Making the Buffer Pool Scan Resistant》《Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)》《Configuring Buffer Pool Flushing》
https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-midpoint_insertion.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-read_ahead.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-flushing.html
 

来源:https://www.cnblogs.com/xuliuzai/p/18092144
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！