InnoDB 使用缓冲池(buffer pool)管理内存,缓冲池中的内存页有三种状态:
第一种是,还没有使用的;
第二种是,使用了并且是干净页;
第三种是,使用了并且是脏页。
InnoDB 的策略是尽量使用内存,因此对于一个长时间运行的库来说,未被使用的页面很少。
而当要读入的数据页没有在内存的时候,就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉:如果要淘汰的是一个干净页,就直接释放出来复用;但如果是脏页呢,就必须将脏页先刷到磁盘,变成干净页后才能复用。
所以,刷脏页虽然是常态,但是出现以下这两种情况,都是会明显影响性能的:
.一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;
.日志写满,更新全部堵住,写性能跌为 0,这种情况对敏感业务来说,是不能接受的。
所以,InnoDB 需要有控制脏页比例的机制,来尽量避免上面的这两种情况。
刷脏页的控制策略
接下来说说 InnoDB 脏页的控制策略,以及和这些策略相关的参数。
首先,你要正确地告诉 InnoDB 所在主机的 IO 能力,这样 InnoDB 才能知道需要全力刷脏页的时候,可以刷多快。
这就要用到 innodb_io_capacity 这个参数了,它会告诉 InnoDB 你的磁盘能力。
这个值我建议你设置成磁盘的 IOPS。
磁盘的 IOPS 可以通过 fio 这个工具来测试,下面的语句是我用来测试磁盘随机读写的命令:
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
其实,因为没能正确地设置 innodb_io_capacity 参数,而导致的性能问题也比比皆是。之前,就曾有其他公司的开发负责人找我看一个库的性能问题,说 MySQL 的写入速度很慢,TPS 很低,但是数据库主机的 IO 压力并不大。经过一番排查,发现罪魁祸首就是这个参数的设置出了问题。
他的主机磁盘用的是 SSD,但是 innodb_io_capacity 的值设置的是 300。于是,InnoDB 认为这个系统的能力就这么差,所以刷脏页刷得特别慢,甚至比脏页生成的速度还慢,这样就造成了脏页累积,影响了查询和更新性能。
虽然我们现在已经定义了“全力刷脏页”的行为,但平时总不能一直是全力刷吧?毕竟磁盘能力不能只用来刷脏页,还需要服务用户请求。所以
接下来,我们就一起看看 InnoDB 怎么控制引擎按照“全力”的百分比来刷脏页。
这个问题可以这么想,如果刷太慢,会出现什么情况?首先是内存脏页太多,其次是 redo log 写满。
所以,InnoDB 的刷盘速度就是要参考这两个因素:
一个是脏页比例,
一个是 redo log 写盘速度。
InnoDB 会根据这两个因素先单独算出两个数字。
参数 innodb_max_dirty_pages_pct 是脏页比例上限,默认值是 75%。InnoDB 会根据当前的脏页比例(假设为 M),算出一个范围在 0 到 100 之间的数字
InnoDB 每次写入的日志都有一个序号,当前写入的序号跟 checkpoint 对应的序号之间的差值,我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字,这个计算公式可以记为 F2(N)。F2(N) 算法比较复杂,你只要知道 N 越大,算出来的值越大就好了。
InnoDB 会在后台刷脏页,而刷脏页的过程是要将内存页写入磁盘。所以,无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页,还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句,都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。
要尽量避免这种情况,你就要合理地设置 innodb_io_capacity 的值,并且平时要多关注脏页比例,不要让它经常接近 75%
其中,脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的,具体的命令参考下面的代码:
mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时,这个查询就可能要比平时慢了。而 MySQL 中的一个机制,可能让你的查询会更慢:在准备刷一个脏页的时候,如果这个数据页旁边的数据页刚好是脏页,就会把这个“邻居”也带着一起刷掉;而且这个把“邻居”拖下水的逻辑还可以继续蔓延,也就是对于每个邻居数据页,如果跟它相邻的数据页也还是脏页的话,也会被放到一起刷。
在 InnoDB 中,innodb_flush_neighbors 参数就是用来控制这个行为的,值为 1 的时候会有上述的“连坐”机制,值为 0 时表示不找邻居,自己刷自己的。
找“邻居”这个优化在机械硬盘时代是很有意义的,可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百,相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。
而如果使用的是 SSD 这类 IOPS 比较高的设备的话,我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈,而“只刷自己”,就能更快地执行完必要的刷脏页操作,减少 SQL 语句响应时间。
在 MySQL 8.0 中,innodb_flush_neighbors 参数的默认值已经是 0 了。
分享到:
相关推荐
这里我们不一一介绍,如果你发现 redo log 推进的非常快,为了避免用户线程陷入刷脏,可以通过调大 innodb_io_capacity_max 来解决,该参数限制了每秒刷新的脏页上限,调 大该值可以增加 Page cleaner 线程每秒的...
InnoDB存储引擎中默认每个页的大小为16KB,可通过参数innodb_page_size将页的大小设置为4K、8K、16K,在MySQL中可通过如下命令查看页的大小: mysql> show variables like 'innodb_page_size'; 而系统一个磁盘块的...
6. **事务信息**:InnoDB支持多版本并发控制(MVCC),在页中会存储与事务相关的元数据,如事务ID、回滚指针等。这些信息对于实现读已提交、可重复读和串行化等隔离级别至关重要。 7. **记录**:InnoDB中的记录包含...
InnoDB的缓冲池用于缓存数据页和索引页,减少磁盘I/O。调整缓冲池大小和配置,如预读(Prefetching)策略,可以显著提升性能。 6. 事务日志与崩溃恢复 InnoDB使用重做日志(Redo Log)和回滚段(Rollback ...
该算法考虑了系统平均刷脏页速度和日志速度,动态调整每次需要刷新的脏页数量,从而更高效地推进检查点。 3. **Page Cleaner**: - 优化了页面清理器的工作机制,使得脏页的刷新更加平滑且可预测,提高了整体性能...
innodb_flush_log_at_trx_commit参数控制了InnoDB如何刷新事务日志。通常,InnoDB使用一个后台线程“log thread”在磁盘上通过fsync()命令来刷新日志。文章还提到了Redo日志文件组中文件的数量,通常是2到3个。 9. ...
InnoDB作为MySQL中最常用且最重要的存储引擎,负责处理事务处理、数据完整性以及并发控制等关键任务,因此对它的深入理解对于数据库管理员和开发者至关重要。 InnoDB存储引擎是MySQL中的默认存储引擎,特别适合需要...
4.4 InnoDB数据页结构 5. 索引与算法 5.1 概述 5.2 数据结构与算法 5.3 B+树索引 5.4 B+树索引的分裂 5.5 Cardinality值 5.6 全文索引 6. 锁 - 实现事务的隔离性 6.1 什么是锁 6.2 lock和latch 6.3 ...
为了充分发挥InnoDB的优势并针对具体的业务需求进行优化,以下是一些常见的调优策略: 1. **调整`my.cnf`配置文件**: - `innodb_buffer_pool_size`:根据服务器内存大小调整,通常建议分配服务器总内存的50%-80%...
通过这个工具,我们可以查看InnoDB的页类型、页头信息、记录、B树结构等关键元素,这对于理解数据的存储和检索过程至关重要。 InnoDB存储引擎中的日志系统是其核心特性之一。redo log(重做日志)是保证事务持久性...
InnoDB思维导图
本书的内容包括了InnoDB的存储引擎概述、内部架构、工作机制、性能优化、事务处理、恢复机制、内存管理、磁盘I/O、数据字典、索引设计、锁和并发控制等核心内容。本书还详细介绍了InnoDB的内部存储格式、物理存储...
解析数据行内部单向链表策略 数据库记录行在页内查询探索分析 在InnoDB存储引擎的世界里,数据存储和管理就像是一座繁忙而有序的城市。在这座城市中,每一块16KB大小的土地都被称为一个“页”(Page),而这些页是...
总结,《InnoDB官方文档中文翻译版》详尽地阐述了InnoDB的各个方面,包括其核心功能、工作原理以及优化策略,是学习和使用InnoDB不可或缺的参考资料。通过深入理解这些知识点,开发者和DBA可以更好地管理和优化MySQL...
《深入解析MySQL Innodb页结构:py_innodb_page.tar.gz》 MySQL作为一个广泛使用的开源关系型数据库管理系统,其InnoDB存储引擎在处理事务、行级锁定等方面表现出色。InnoDB内部的数据存储机制是基于页(Page)的,...
3. **innodb_data_file_path**:这个参数不仅指定InnoDB数据文件的路径,还控制其初始大小、最大大小和自动扩展策略。例如,可以通过配置指定每个数据文件的初始大小、扩展量和最大限制,以适应数据的增长。 4. **...
InnoDB实现了行级锁定,相较于页级或表级锁定,这允许更高的并发性能,因为只锁定涉及的特定行,减少了锁定资源的竞争。 4. **外键约束** InnoDB支持外键,这允许建立表与表之间的参照完整性,确保数据的一致性...
本篇文章将深入探讨InnoDB存储引擎中的索引大小问题,包括其影响因素、限制以及优化策略。 首先,我们来了解InnoDB索引的基本结构。InnoDB主要使用B+树(B-tree)作为索引的数据结构,无论是主键索引还是辅助索引。...
InnoDB为表提供了行级锁定和外键约束的特性,并且通过MVCC(多版本并发控制)机制支持高并发读写操作。 2. InnoDB架构特性 InnoDB的架构设计具备以下特点: - Buffer Pool(缓冲池):它是InnoDB中最重要的组成部分...