Configuring the Rate of InnoDB Buffer Pool Flushing
来源:互联网 发布:农业大数据平台 编辑:程序博客网 时间:2024/05/16 19:49
本文转载自:http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-adaptive_flushing.html
Configuring the Rate of InnoDB
Buffer Pool Flushing
InnoDB performs certain tasks in the background, including flushing of dirty pages (those pages that have been changed but are not yet written to the database files) from thebuffer pool, a task performed by the master thread. Currently, InnoDB aggressively flushes buffer pool pages if the percentage of dirty pages in the buffer pool exceedsinnodb_max_dirty_pages_pct
.
InnoDB uses an algorithm to estimate the required 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 buffer flush activity keeps up with the need to keep the buffer pool “clean”. Automatically adjusting the rate of flushing can help to avoid sudden dips in throughput, when excessive buffer pool flushing limits the I/O capacity available for ordinary read and write activity.
InnoDB uses its log files in a circular fashion. Before reusing a portion of a log file, InnoDB flushes to disk all dirty buffer pool pages whose redo entries are contained in that portion of the log file, a process known as asharp checkpoint. If a workload is write-intensive, it generates a lot of redo information, all written to the log file. If all available space in the log files is used up, a sharp checkpoint occurs, causing a temporary reduction in throughput. This situation can happen even though innodb_max_dirty_pages_pct
is not reached.
InnoDB uses a heuristic-based algorithm to avoid such a scenario, by measuring the number of dirty pages in the buffer pool and the rate at which redo is being generated. Based on these numbers, InnoDB decides how many dirty pages to flush from the buffer pool each second. This self-adapting algorithm is able to deal with sudden changes in the workload.
Internal benchmarking has also shown that this algorithm not only maintains throughput over time, but can also improve overall throughput significantly.
Because adaptive flushing can significantly affect the I/O pattern of a workload, theinnodb_adaptive_flushing
configuration parameter lets you turn off this feature. The default value forinnodb_adaptive_flushing
is TRUE
, enabling the adaptive flushing algorithm. You can set the value of this parameter in the MySQL option file (my.cnf
ormy.ini
) or change it dynamically with theSET GLOBAL
command, which requires the SUPER
privilege.
For more information about InnoDB I/O performance, see Section 8.5.7, “Optimizing InnoDB
Disk I/O”.
注:近期参加MySQL运维学习,老师推荐该文章作为学习和技术提高的扩展阅读,先记录到自己的博客中,随后慢慢消化、学习、提高。本文与MySQL数据库 “性能优化”主题有关。
- Configuring the Rate of InnoDB Buffer Pool Flushing
- 14.3.3.2 Configuring the Rate of InnoDB Buffer Pool Flushing 配置 InnoDB Buffer Pool 刷新频率
- 14.6.3.5 Configuring InnoDB Buffer Pool Flushing
- 14.4.3.5 Configuring InnoDB Buffer Pool Flushing 配置InnoDB Buffer Pool 刷新:
- The InnoDB Buffer Pool
- 14.4.3.6 Fine-tuning InnoDB Buffer Pool Flushing 微调 InnoDB Buffer Pool 刷新:
- 14.4.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead) 配置InnoDB Buffer pool 预读
- 14.6.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead) 配置InnoDB Buffer pool 预取
- 14.4.8 Configuring the InnoDB Master Thread IO Rate 配置InnoDB Master Thread I/O Rate
- 8.10.1 The InnoDB Buffer Pool
- 8.10.1 The InnoDB Buffer Pool
- 14.4.3.1 The InnoDB Buffer Pool
- 14.6.3.1 The InnoDB Buffer Pool
- 14.6.8 Configuring the InnoDB Master Thread IO Rate 配置InnoDB 主线程IO 速率:
- Innodb buffer pool struct
- innodb buffer pool
- InnoDB Buffer Pool 说明
- innodb buffer pool
- 转职的道路
- The InnoDB Buffer Pool
- 黑马程序员-java基础
- 在linux下运行sfml example
- 使用bootstrap设计的后台管理界面
- Configuring the Rate of InnoDB Buffer Pool Flushing
- K&R快速排序的解释
- Optimizing InnoDB Disk I/O
- 十个iOS面试问题
- jemalloc
- 【Leetcode长征系列】Single Number
- 设计并实现数据仓库ETL过程(IBM讲座)
- base64二进制图片在JSP页面解析
- 解决ajax返回乱码的问题