CNDBA社区
Mysql InnoDB性能参数—体系结构参数
自我笔记….
1、IO thread
MySQL5.5.X版本以上,innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4
mysql> show variables like 'innodb_version'/G
*************************** 1. row ***************************
Variable_name: innodb_version
Value: 5.7.20
1 row in set, 1 warning (0.00 sec)
mysql> show variables like 'innodb_%io_threads'/G
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
Value: 4
*************************** 2. row ***************************
Variable_name: innodb_write_io_threads
Value: 4
2 rows in set, 1 warning (0.00 sec)
这两个参数不支持动态改变，需要把该参数加入my.cnf里，修改完后重启MySQL服务，允许值的范围是1～64
假如CPU是2颗8核的，那么可以设置：
innodb_read_io_threads = 8
innodb_write_io_threads = 8
如果数据库的读操作比写操作多，那么可以设置：
innodb_read_io_threads = 10
innodb_write_io_threads = 6
调整完成后，你可以用命令“show engine innodb status/G;”来查看调整结果并且读线程ID总是小于写线程ID，如下所示：
mysql>show engine innodb status/G
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
2、Purege Thread
PureThread(MySQL5.5.X版本以上)用于事务提交后，Undolog可能不在需要，回收已经使用并分配的Undo页，该工作原本由Master Thread进行，通过参数配置文件配置启用独立的PureThread，减轻Master Threads工作,提升存储引擎的性能,默认值为4
mysql> show variables like 'innodb_purge_threads'/G
*************************** 1. row ***************************
Variable_name: innodb_purge_threads
Value: 4
1 row in set, 1 warning (0.00 sec)
该参数加入my.cnf里，修改完后重启MySQL服务
3、内存
3.1、缓冲池
缓冲区缓存数据页类型：索引页、数据页、Undo页、插入缓冲(INsert buffer)、自适应哈希索引(AHI)、锁信息以及数据字典信息等，主要是索引页跟数据页
innodb_buffer_pool_size: 缓冲池设置大小一般为物理内存的40%-75%
innodb_buffer_pool_instance:缓冲实例数：主要按照每个页的哈希值平均分配到不同的缓冲实例中，减少数据库内部的资源竞争，增加数据库并发，默认值1
mysql> show variables like 'innodb_buffer_pool_instances'/G
*************************** 1. row ***************************
Variable_name: innodb_buffer_pool_instances
Value: 1
1 row in set, 1 warning (0.14 sec)
3.2、LRU列表、Free列表以及刷新列表
innodb_old_blocks_pct: 该参数主要用于控制新读取的页插入LRU列表距尾端百分之多少的位置，默认值是37，表示距离尾端37%，目的是防止新访问读取的页并非热页而直接放置于热端，导致部分热页被刷出
mysql> show variables like 'innodb_old_blocks_pct'/G
*************************** 1. row ***************************
Variable_name: innodb_old_blocks_pct
Value: 37
1 row in set, 1 warning (0.00 sec)
innodb_old_blocks_time: 用于表示页读取到innodb_old_blocks_pct指定参数位置后，需要等待多久才会被加入LRU列表的热端（常见操作：索引或者数据扫描操作需要大量的数据页，扫描到指定参数位置后，等待多久才把这些冷端页加入LRU列表热端,尽可能防止之前的热端页过早刷出) 默认值1000
mysql> show variables like 'innodb_old_blocks_time'/G
*************************** 1. row ***************************
Variable_name: innodb_old_blocks_time
Value: 1000
1 row in set, 1 warning (0.00 sec)
若用户预估自己活跃热点数据超过63%，那么可以在执行SQL语句前，设置innodb_old_blocks_pct参数减少热点数据被刷出的概率
mysql>set global innodb_old_blocks_pct=20;
该设置表明，尽可能保留LRU列表中80%的热点数据页
mysql>show engine innodb status/G
输出中有个重要的观察变量：Buffer pool hit rate 表示缓冲池的命中率（两个值相除），通常该值不应该小于95%，若小于95%，则有可能是全表扫描引起LRU列表被污染，即LRU列表中的页处于热端不热，冷端不冷的现象
innodb_lru_scan_depth: 该参数用于控制LRU列表中可用页的数量，默认值1024，若小于该值，Innodb存储引擎可能会将LRU列表尾端页移除，若为脏页，则进行Checkpoint,称为FLUSH_LRU_LIST_CheckPoint
mysql> show variables like 'innodb_lru_scan_depth'/G
*************************** 1. row ***************************
Variable_name: innodb_lru_scan_depth
Value: 1024
1 row in set, 1 warning (0.00 sec)
3.3、重做日志缓冲
innodb_log_buffer_size: 该参数表示重做日志缓冲区的大小，每个版本的默认值可能有所不同
刷新重做日志到磁盘情况：
1、Master thread每秒刷新一定的重做日志
2、事务提交
3、重做日志缓冲区可用空间小于1/2
mysql> show variables like 'innodb_log_buffer_size'/G
*************************** 1. row ***************************
Variable_name: innodb_log_buffer_size
Value: 16777216
1 row in set, 1 warning (0.00 sec)
一般16M可以满足绝大部分的应用
3.4、Checkpoint
mysql> show variables like 'innodb_max_dirty_pages_pct'/G
*************************** 1. row ***************************
Variable_name: innodb_max_dirty_pages_pct
Value: 75.000000
1 row in set, 1 warning (0.00 sec)
该参数值表示当缓冲池脏页数量超过75%，强制checkpoint，刷新部分脏页到磁盘，目的是为了保证缓冲池有足够可用的页，INnodb1.0.x版本之前默认为90，之后版本默认值75
3.5、Change Buffer
InnoDB从1.0.x版本引入Change BUffer 可视为Insert Buffer升级版，（INNODB 性能上的提升)
innodb_change_buffer_max_size： 该参数控制Change Buffer最大使用内存数量，默认值为25，表示最多使用1/4的缓冲池内存空间，最大参数有效值为50(即最大使用1/2的缓冲池内存空间)
mysql> show variables like 'innodb_change_buffer_max_size'/G
*************************** 1. row ***************************
Variable_name: innodb_change_buffer_max_size
Value: 25
1 row in set, 1 warning (0.00 sec)
通过SHOW ENGINE INNODB STATUS查看Change Buffer状态
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
3.6、DoubleWrite
DoubleWrite原理：当发生写失效时，先通过页的副本还原该页，再应用重做日志
功能目的是为防止部分写失效(即数据库发生宕机，数据页是16K，但宕机时只写入4K，其他写入失败称为部分写失效)，导致数据丢失
功能实现流程：
1、数据页需要刷新到磁盘时，先通过函数memcpy函数复制数据到DoubleWrite缓冲区gon(大小2M)
2、再通过DouBlewrite缓冲区分流，先写共享表空间中的doublewrite(连续的每个DoubleWrite大小为1M)，再然后调用fsync函数，立刻同步磁盘（该过程是顺序的，1M 1M的顺序写）
恢复流程：
1、从共享表空间中找到损坏页的副本，COPy到表空间数据文件中，还原损坏页
2、再应用重做日志完成恢复
DoubleWrite当前运行情况查看：
mysql> show global status like 'innodb_dblwr%'/G
*************************** 1. row ***************************
Variable_name: Innodb_dblwr_pages_written
Value: 6325194
*************************** 2. row ***************************
Variable_name: Innodb_dblwr_writes
Value: 100399
2 rows in set (0.04 sec)
Innodb_dblwr_pages_written： 表明DoubleWrite 写入数据页数
Innodb_dblwr_writes： 表明写入上面数据页数，实际写入次数
基本符合64:1，若系统高峰期Innodb_dblwr_pages_written：Innodb_dblwr_writes比值远小于64:1，则说明系统写入压力并不是很高
用户若需要统计生产数据库在生产环境下写入的数据量，可根据Innodb_dblwr_pages_written参数值进行统计，
写入数据量=该参数值 * 16KB
3.7、刷新邻接页
原理：当刷新一个脏页，InnoDB存储殷勤会检测该页所在区中所有页，若是脏页，则一起刷新
有点：通过AIO可以将多个IO写入操作合并为一个IO操作，对于传统机械硬盘有着显著的性能优势
INNODB 1.2.x版本后，提供参数innodb_flush_neighbors控制（5.7.20 默认开启）
mysql> show variables like 'innodb_flush_neighbors'/G
*************************** 1. row ***************************
Variable_name: innodb_flush_neighbors
Value: 1
1 row in set, 1 warning (0.00 sec)
对于传统的机械硬盘建议启用该特性，若为IOPS较高的固态硬盘则建议关闭该特性
innodb_flush_neighbors参数设置为0，表示关闭，设置为1，则表示开启
版权声明：本文为博主原创文章，未经博主允许不得转载。
