MySQL的缓存

InnoDB Buffer Pool

一些重要的参数

1
2
3
4
5
6
7
8

-- buffer pool size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'

-- 实例个数
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances'

-- 每个实例按 chunk 分成若干个
SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size'

注意
innodb_buffer_pool_size 必须是 innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size 的整数倍，或者相等
否则系统会自动调整值的

采用 LRU的方式将buffer pool 中的page组织起来
分为 young区 和 old两个区域
一开始先加入到 old 区的开头，如果超过了一定的阈值，则放到 young区

整个buffer pool 按照实例个数分为若干个
每个实例又包含 N个 chunk，这其中包含多个链表

• free链表
• flush链表
• LRU链表
• 其他链表

每个链表包含一个 list node base，执行控制块的头、尾部
控制块包含 表空间信息、page信息
这样可以通过 hash方式快速定位到具体的page

buffer pool相关的参数

其他一些参数

• innodb_old_blocks_time，默认1000ms， 从old区到 young的等待时间，也就是old区过了1秒后才能到 young区
• innodb_old_blocks_pct， 默认37 ，表示old区占用 37%，剩下的63% 是young
• innodb_read_ahead_threshold，顺序访问一个extent，会异步读取默认 56个page
• innodb_random_read_ahead，默认关闭，这是随机预读取
• innodb_lru_scan_depth，定期从链表尾部扫描 N个page，如果有脏页则刷磁盘
• innodb_flush_neighbors，是否刷新邻居page
• innodb_io_capacity_max，每秒后台刷新的脏页
• innodb_adaptive_flushing，刷新脏页的自适应模式
• innodb_max_dirty_pages_pct，当系统的脏页超过一定比例后，就开始刷新

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

字段解释

Doublewrite Buffer

参考一张Mysql的InnoDB架构图

开启双写

1

SHOW VARIABLES LIKE 'innodb_doublewrite';

8.0 将双写缓冲区单独拿了出来

操作系统的默认Page大小是 4K

1

getconf PAGESIZE

MySQL的默认page是 16K

1

SHOW VARIABLES LIKE 'innodb_page_size';

数据库的page大小比 OS的要大，所以无法保证原子性
1个数据库page对应4个OSpage，如果数据库的page写一半宕机了，对于OS层来说只写了两个page
对于数据库来说这个page相当于不完整，后面都没法恢复
因为各种MySQL的各种类型page都是 16K的

Doublewrite Buffer工作流程

执行过程：
1、页数据先memcopy到DWB的内存里
2、DWB的内存里的数据页，会先刷到DWB的磁盘上
3、DWB的内存里的数据页，再刷到数据磁盘存储.ibd文件上

因为要写两次，第一次是内存写到磁盘 DWB磁盘上，这是顺序写
第二次是从内存写到 ibd 文件，这是随机写
按照官网的说法，虽然是写了两次，但并不是两倍的 I/O开销，因为写入到 DWB磁盘上的是大量的顺序I/O效率很高

1
2
3
4
5
6
7

SHOW GLOBAL STATUS LIKE '%dblwr%';

-- Innodb_dblwr_pages_written
-- 记录写入到DWB中的页数量

-- Innodb_dblwr_writes
-- 记录DWB写操作的次数

MySQL 8.x 相关的参数

1

SHOW VARIABLES LIKE '%double%';

默认有两个双写文件

• 刷新列表双写文件的默认大小是InnoDB页面大小 * 双写页面字节
• LRU列表双写文件用于从缓冲池LRU列表中刷新的页面。它还包含用于单页刷新的插槽。LRU列表双写文件的默认大小是InnoDB页面大小 *（双写页面 +（512 缓冲池实例数）

Doublewrite文件名具有以下格式：
以下双写文件是为页面大小为16KB和单个缓冲池的MySQL实例创建的：

1

#ib_丨page_size_file_丨number.dblwr

Log Buffer

这里存储的是 redo log 的缓存

redo log 默认是两个文件，按照环形的方式组织

一些重要的参数

Redo Log三层结构

• InnoDB一项很重要的内存结构（In-Memory Structure），即我们的Log Buffer（日志缓冲区），这一层，是MySQL应用程序用户态控制。
• 操作系统文件系统的缓冲区（FS Page Cache），这一层，是操作系统OS内核态控制。
• 落盘的物理日志文件

提交事务时的三种方案

• 策略一：最佳性能（innodb_flush_log_at_trx_commit=0）
  处理过程：每隔一秒，才将Log Buffer中的数据批量write入FS Page Cache，同时MySQL主动fsync
  缺点：这种策略，如果数据库奔溃，有一秒的数据丢失

• 策略二：强一致（innodb_flush_log_at_trx_commit=1）
  处理过程：每次事务提交，都将Log Buffer中的数据write入FS Page Cache，同时MySQL主动fsync。这种策略，是InnoDB的默认配置，为的是保证事务ACID特性
  缺点：这种策略，性能较其余两种策略较差

• 策略三：折衷（innodb_flush_log_at_trx_commit=2）
  处理过程：每次事务提交，都将Log Buffer中的数据write入FS Page Cache；每隔一秒，MySQL主动将FS Page Cache中的数据批量fsync
  缺点：这种策略，如果操作系统奔溃，最多有一秒的数据丢失。（因为OS也会fsync，MySQL主动fsync的周期是一秒，所以最多丢一秒数据。磁盘IO次数不确定，因为操作系统的fsync频率并不是MySQL能控制的）

Adaptive Hash Index

建立一个表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE `student` (  
	`student_id` INT NOT NULL AUTO_INCREMENT COMMENT '学生编号',  
	`student_name` VARCHAR(20) NOT NULL COMMENT '学生姓名',  
	`address` VARCHAR(100) DEFAULT '北京市' COMMENT '家庭住址',  
	`extra` VARCHAR(50) DEFAULT NULL COMMENT '额外信息',  
	`remark` TINYTEXT COMMENT '备注',  
	PRIMARY KEY (`student_id`),  
	UNIQUE KEY `uniq_extra` (`extra`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

ALTER TABLE `student` ADD INDEX `idx_address`(`address`) USING HASH;

SHOW INDEXES FROM `student`;

结果用的还是 B Tree索引

相关参数

1

innodb_adaptive_flushing

解释

• innodb_adaptive_hash_index，是否启用或禁用InnoDB 自适应哈希索引
• innodb_adaptive_flushing，指定是否动态调整冲洗速度 脏页在 InnoDB 缓冲池中，根据工作负载。动态调整刷新率旨在避免I/O活动的爆发。默认情况下启用此设置
• innodb_adaptive_flushing_lwm，定义表示启用自适应刷新的重做日志容量百分比的低水位线。（默认值10，最小值0，最大值70）
• innodb_adaptive_hash_index_parts，对自适应哈希索引搜索系统进行分区
• innodb_adaptive_max_sleep_delay，允许根据当前工作量InnoDB自动调整innodb_thread_sleep_delay up或down的值。任何非零值都可以自动、动态地调整innodb_thread_sleep_delay值，直至达到innodb_adaptive_max_sleep_delay选项中指定的最大值。该值表示微秒数。此选项在具有16个以上InnoDB线程的繁忙系统中很有用

普通索引的访问过程

自适应hash索引的访问过程

自适应 hash索引的状态

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SHOW ENGINE INNODB STATUS\G

-------------------------------------
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
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

Adaptive Hash Index限制

• 只能用于等值比较，例如=、<=>、IN、AND等
• 无法用于排序
• 有冲突可能
• MySQL自动（“自适应”）管理，人为无法干预

Change Buffer

InnoDB的整体架构图，内存和磁盘都有
对于不在buffer pool中的page，如果是非唯一索引，可以不用将其从磁盘上读出来
而是在内存中生成一个临时page，用作记录，然后将 redo log顺序写入磁盘
这样本来的随机I/O 就被转为顺序I/O 了

两种情况

• 如果要访问的page正好在buffer pool中
• 如果不在buffer pool中

对于第一种情况，直接更新buffer pool就可以了，再加上一次redo log顺序写操作

第二种情况，如果是唯一索引就需要读磁盘了

但对于非唯一索引，其实可以不用读磁盘
而仅仅记录缓冲变更（Buffer Changes），等未来数据被读取时
再将数据合并（Merge）恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能

加入优化后的效果

• 在写缓冲中记录这个操作，一次内存操作
• 写入redo log，一次磁盘顺序写操作

Change Buffer触发时机

• 有一个后台线程，会认为数据库空闲时；
• 数据库缓冲池不够用时；
• 数据库正常关闭时；
• redo log写满时

Change Buffer 不适用场景

• 数据库都是唯一索引
• 写入一个数据后，会立刻读取它

Change Buffer适用场景

• 数据库大部分是非唯一索引
• 业务是写多读少，或者不是写后立刻读取

Change Buffer的重要的参数

• innodb_change_buffer_max_size，配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%
• innodb_change_buffering，配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等

InnoDB 参数汇总

参考

• InnoDB Standard Monitor and Lock Monitor Output
• Buffer Pool
• 谨慎设置innodb_io_capacity_max
• InnoDB Buffer Pool详解
• InnoDB 中的缓冲池(Buffer Pool)
• InnoDB的杀手锏BufferPool
• show engine innodb status 输出结果解读
• Doublewrite Buffer
• MySQL各种“Buffer”之Doublewrite Buffer
• 详解MySQL InnoDB之UNDO Log
• MySQL各种“Buffer”之Adaptive Hash Index
• Adaptive Hash Index
• MySQL各种“Buffer”之Change Buffer