innoDB 内存优化

innodb 缓存机制
innodb 用一块内存区做io缓存池.该缓存池不仅用来缓存innodb的索引块,而且缓存innodb的数据块.
innodb 缓存池逻辑有free list,flush list,lru list 组成.
free list 空闲的缓存块列表
flush list 需要刷新到磁盘的缓存块列表
lru list innodb 正在使用的缓存块. innodb buffer pool的核心.
lru 算法; 将lru list 分为 young sublist 和old sublist, 数据冲磁盘读入时,会将该缓存块插入到lru list 的’中点’,即old sublist 的头部,经过一定的时间访问,(由 innodb_old_blocks_time 系统参数决定),该数据块将会有old sublist 转移到young sublist 头部. 也就是整个lru list 的头部. 随时间推移,young sublist 和old sublist 中较少被访问的缓存块将移动到尾部. 需要淘汰数据块时,优先淘汰尾部数据. 这种设计同样为了防止偶尔被访问的索引块将访问频繁的热块淘汰.
脏页的刷新存在于flush 和lru list . lru 也存在可以刷新的脏页.这里可以直接刷新. 默认 BP(innodb_buffer_pool)中不存在可以的数据页的时候.会扫描lru list 的尾部的innodb_lru_scan_depth 个数据页(默认1024个数据页).进行相关的刷新操作. 淘汰的数据页会立刻放入到free list 中去.
通过调整buffer pool大小,改变young sublist 和old sublist的分配比例.控制脏数据的刷新活动,使用多个innodb 缓存池 等方法优化innodb 性能

innodb 数据页大小16k.

innodb_buffer_pool_size 设置.
innodb_buffer_size的值越大,缓存命中率越高.访问innodb表需要的磁盘io就越少.性能也就越高.
1.一般专用的数据库服务器上,可以将80%的物理内存分配给innodb_buffer_pool.但一定要注意避免设置过大而导致页交换
查看 buffer_poll 使用情况
show status like ‘innodb_buffer%’;
Variable_name | Value |
±————————————–±————————————————-+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170821 17:20:03 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 5842417 |
| Innodb_buffer_pool_bytes_data | 95722160128 |
| Innodb_buffer_pool_pages_dirty | 8930 |
| Innodb_buffer_pool_bytes_dirty | 146309120 |
| Innodb_buffer_pool_pages_flushed | 559620864 |
| Innodb_buffer_pool_pages_free | 10241 |
| Innodb_buffer_pool_pages_misc | 45222 |
| Innodb_buffer_pool_pages_total | 5897880 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 9626 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 27123370943 |
| Innodb_buffer_pool_reads | 8506 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 13232653001
*缓存池命中率.=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)100

调整old sublist 大小 innodb_old_blocks_pct
在lru list 中,old sublist 的比例有系统innodb_old_blocks_pct 决定,取值范围5-95,默认37.约等于(3/8).
查看命令: show variables like ‘innodb_old_blocks_pct%’;
例如: 在没有较大表扫描或索引扫描的情况下,如果young/s的值很低,可能就需要适当增大innodb_old_blocks_pct的值或者减小innodb_old_blocks_time的值. 提高热点数据缓存
调整innodb_old_blocks_time 的设置
innodb_old_blocks_time 参数决定了缓存数据块有old到young的快慢.当一个缓存数据块被插入到midpoint 后,至少要在old sublist 停留过innodb_old_blocks_time(ms)后,才有可能被转移到new sublist. –进行大表扫描时需调大innodb_old_blocks_time值,避免表扫描污染buffer pool的情况.
根据innodb monitor 输出的信息来调整innodb_old_blocks_time 的值. 在进行表扫描是,如果non-youngs/s 很低,应考虑将innodb_old_blocks_time 调大,以防止扫描将真正的热数据淘汰.

调整缓存池数量,减少内部对缓存池数据结构的争用 innodb_buffer_pool_instances
指定的缓存平分到 instances个buffer pool
控制innodb buffer 刷新,延长数据缓存的时间,减缓磁盘io
在innodb 找不到干净的可用的缓存页或者检查点被触发的情况下,innodb的后台线程就会开始把’脏的缓存页’回写到磁盘文件中,这个过程叫缓存的刷新.
影响参数.
innodb_max_dirty_pages_pct,控制缓存池中脏页的最大比例.默认75%.如果脏页到达该值,innodb 后台线程将开始缓存刷新.
innodb_io_capacity. 代表磁盘系统的io能力.一定程度上该值代表磁盘每秒可完成的i/o次数. 默认值200.对于转速低的磁盘.如7200rpm的磁盘. 可以调节该值到100,对于固态硬盘和由多个磁盘组成的盘振,可以适当调大该值.
innodb_io_capacity 决定一批刷新脏页的数量,当脏页比例到innodb_max_dirty_pages_pct时,innodb 大约将 innodb_io_capacity个已改变的缓存页刷新到磁盘.当脏页比例小于max_dirty_pages_pct时,如果innodb_adaptive_flushing的设置为true. innodb 将根据函数buf_flush_get_desired_flush_rate 返回的重做日志产生的速度来确定要刷新的脏页数.
可以根据monitor的值来调整innodb_max_dirty_pages_pct 和io_capaity. 例如: 若ininodb_buffer_pool_wait_free 值增长较快,则说明innodb经常在等待空闲缓存页,如果无法增大缓存池,那应将innodb_max_dirty_pages_pct值调小,或将io_capacity值提高.以加快脏页的刷新.
innodb doublewrite.
默认开启innodb的双写功能对性能的影响不大.
如果要求超高性能,又能容忍极端情况下的少量数据丢失的应用. 可以通过配置文件中增加innodb_doublewrite=0 参数设置来关闭doublewrite.
————————————————
版权声明：本文为CSDN博主「ciyuacca」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_38098312/article/details/102502444