MySQL 8.0 之 I/O优化参数

在MySQL的InnoDB存储引擎中，InnoDB的主线程和其他线程在后台执行各种任务，其中大多数与I/O相关，例如从缓冲池中刷新脏页，以及将更改从更改缓冲区写入适当的二级索引，日志体系刷新。如：binlog ， redo ，undo 和一些大型操作临时表楼盘等操作。维持MySQL数据库的高性能I/O性能是一个关键的因素。虽然目前的硬件设备I/O有很大的提升，但随着数据量增加和大批量操作的加入，会在I/O上出现瓶颈。

MySQL的I/O瓶颈通常指的是数据库服务器的磁盘I/O性能不足以满足数据库操作的需求。这可能表现为查询响应时间慢、写入操作缓慢或者服务器负载高。
I/O性能不足出现的MySQL错误提示如下：

##错误日志
[ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server
because it appears to be hung.

##SHOW PROCESSLIST中 Status状态
mysql> SHOW PROCESSLIST;
+----+-------+-----------+------+---------+------+---------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+------+---------+------+---------------------------+------------------+
| 12 | tester| localhost | test | Query | 0 | Waiting for handler commit| insert... |
+----+-------+-----------+------+---------+------+---------------------------+------------------+
1 row in set, 1 warning (0.00 sec)


MySQL的I/O过高通常指的是磁盘I/O操作频繁且占用大量资源，导致性能下降。这种情况可能由以下原因引起：

• 频繁的读写操作：查询、插入、更新、删除等导致大量磁盘读写。
• 慢查询：执行时间长的查询会占用更多I/O资源。
• 不当的索引策略：索引查询可以减少I/O，但不当的索引策略或缺失索引会导致全表扫描增加I/O。
• 数据表分区或分片不当：数据分布不均导致I/O分布不均。
• 系统资源不足：磁盘I/O性能差。

下面介绍，在资源有限的情况下，如何优化MySQL的I/O参数。

优化参数

针对MySQL的I/O性能瓶颈问题，在无法提升硬件资源的情况下，可以采用参数调整策略来尝试解决或缓解：

1.innodb_purge_threads清除线程（1~32）

当使用SQL语句删除一行时，InnoDB不会立即从数据库中物理删除该行。只有当InnoDB在Undo 日志里标识为删除时，行及其索引记录才会被物理删除。此删除操作仅在多版本并发控制（MVCC）或回滚不再需要该行之后发生，称为purge清除。
一般采取4或默认值即可。清除滞后，可以通过SHOW ENGINE INNODB STATUS输出的TRANSACTIONS部分中的History列表长度值。

mysql> SHOW ENGINE INNODB STATUS\G
...
------------
TRANSACTIONS
------------
Trx id counter 403479
Purge done for trx's n:o < 403478 undo n:o < 0 state: running but idle
History list length 10


建议小于CPU核数，按照经验保证在4~16。

2.innodb_flush_neighbors

指定从InnoDB缓冲池中刷新一个页面是否也会刷新相同范围内的其他脏页面。MySQL数据操作（tablespace -> segment -> extent(64个page，1M) -> page）
0：在相同（same extent）范围内的脏页不会被刷新。
1：在相同（same extent）的范围内刷新连续的脏页。
2：在相同（same extent）的范围内刷新脏页。
备注：在老式的存储设备上时（HDD，SAA），与在不同时间刷新单个页面相比，在一次操作中刷新这样的相邻页面减少了I/O开销（主要用于磁盘寻道操作）。对于存储在SSD上的表数据，寻道时间不是一个重要因素。

建议：当IO压力大时，改成0值。

3.innodb_lru_scan_depth（默认值1024，100~2*32-1）

InnoDB缓冲池实例指定了页面清理器线程在缓冲池LRU页面列表中扫描脏页面的深度。innodb_lru_scan_depth*innodb_buffer_pool_instances定义了页面清理线程每秒执行的工作量。
建议：当IO压力大时，改成512值。

备注：innodb_flush_neighbors和innodb_lru_scan_depth参数主要用于写密集型工作负载。在DML活动频繁的情况下，如果冲洗不够积极，则可能会落后；如果冲洗过于积极，则磁盘写入可能会使I/O容量饱和。

4.innodb_read_io_threads & innodb_write_io_threads（1~64）

InnoDB使用后台线程为各种类型的I/O请求提供服务。使用innodb_read_io_threads和innodb_write_io_threads配置参数来配置为数据页上的读写I/O提供服务的后台线程的数量。每个后台线程最多可以处理256个挂起的I/O请求。
可以通过SHOW ENGINE innodb STATUS输出中看到I/O thread挂起读写请求，判断是否增加如上线程数。

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 (read 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 (write 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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
1154 OS file reads, 480 OS file writes, 83 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------


建议：当IO压力大时，改成512 或 256 值。

5.innodb_use_native_aio

InnoDB使用Linux上的异步I/O子系统。该选项仅适用于Linux系统，并且在默认情况下处于启用状态。
如果InnoDB检测到潜在问题，例如tmpdir位置、tmpfs文件系统和不支持tmpfs上AIO的Linux内核的组合，则此选项也可能在启动期间自动禁用。
建议：当IO压力大时，改成0值。

一般在同一台服务器机器上运行多个这样的实例，可能会超出Linux系统的容量限制。如错误信息里：

EAGAIN: The specified maxevents exceeds the user's limit of available events.


可以通过向/proc/sys/fs/aio-max-nr写入更高的限制来解决此错误。
备注：如果InnoDB检测到潜在问题，例如tmpdir位置、tmpfs文件系统和不支持tmpfs上AIO的Linux内核的组合，则此选项也在启动期间自动禁用。

6.innodb_adaptive_hash_index

MySQL中自适应哈希是MySQL内部维护的哈希堆表。当数据更改变更时，因为有些数据驻扎在自适应哈希堆表里，同时要处理自适应哈希表，有可能会导致I/O等待时间过长，影响整体MySQL服务响应。
建议：关闭自适应哈希。

7.sync_binlog

0：禁止MySQL服务器将二进制日志同步到磁盘。有操作系统控制刷新binlog文件。
1：在提交事务之前将binlog同步到磁盘。这是最安全的设置，但由于磁盘写入次数的增加，可能会对性能产生负面影响。在电源故障或操作系统崩溃的情况下，binlog中丢失的事务仅处于准备状态。这允许自动恢复例程回滚事务，从而保证binlog中不会丢失任何事务。
N，其中N是0或1以外的值：在收集了N个binlog提交组后，将binlog同步到磁盘。在电源故障或操作系统崩溃的情况下，服务器可能已提交未刷新到二进制日志的事务。值越高，性能越好，但数据丢失的风险也会增加。
可以按照实际负载IO情况进行调整。

建议：可以按照IO情况设置100~1000值。

8.innodb_flush_log_at_trx_commit

InnoDB在每次事务提交时，如何处理未刷入（flush）的重做日志信息（redo log）。它是 InnoDB 确保 ACID 属性中的持久性（Durability）的关键因素。当数据库发生故障，如崩溃或者断电，这项设置可以保护您的数据不会丢失。

备注：0和2值未刷新日志的事务可能会在崩溃中丢失。

建议：当I/O压力大时，可以设置成0值。特别是主从延迟高 或 binlog回放场景中。

9.innodb_io_capacity 和 innodb_io_capacity_max

每秒可用于Innodb后台任务的I/O操作数（IOPS），max是IOPS的最大数量。
对于单个常规磁盘驱动器sas，建议设置在200到400之间。对于高端的总线连接SSD，考虑更高的设置，如2500
对于不同的硬盘可以按照如下设置：

SAS 200~1000 ，SSD 2000~5000 ，PCI-E 10000-50000


在单台服务器上多个实例的情况，需要合理分配设置。
建议：按照硬盘类型和IOPS设置。

10.innodb_flush_method

将数据刷新到InnoDB数据文件和Redo log的方法.

类Unix系统:

Win系统：

建议：在Linux下I/O负载高采用fsync或O_DSYNC方式。

11.innodb_redo_log_capacity&innodb_Log_buffer_size

innodb_redo_log_capacity参数是MySQL 8.0.30之后使用（之前innodb_log_file_size和innodb__log_file_in_group）重做日志文件所占用的磁盘空间量，有21个临时文件组成。通过InnoDB的Buffer的
日志缓冲区（innodb_Log_buffer_size参数大小）内容会定期刷新到磁盘。更大的innodb_Log_buffer_size和Redo文件使大事务无需在事务提交之前将重做日志数据写入磁盘。因此，如果有更新、插入或删除许多行的事务，那么增加日志缓冲区的大小可以节省磁盘I/O。
建议：innodb_Log_buffer_size的大小 > innodb_redo_log_capacity的单个文件，最好2倍的大小。

知识点

1.异步IO

在Linux操作系统中，AIO（Asynchronous I/O）是一种异步输入/输出的机制，允许程序在进行文件操作时不需要等待操作完成就可以继续执行其他任务。AIO的使用能够提高系统的效率和性能，尤其对于需要大量I/O操作的程序来说非常重要。
在一个程序中如果涉及到磁盘的IO操作时,有两种情况

1. 程序等待IO操作完成，CPU再接下来处理程序的其他部分（等待IO的时间段内，CPU处于Idle Waiting状态）。
2. 程序不等待IO操作完成，允许CPU处理接下来的其他任务（或者理解为允许CPU处理接下来的不依赖于IO完成的任务）。
   显然，第一种情况，虽然CPU负载较低，CPU的资源白白的浪费了,也就是同步IO。第二种情况更有利于CPU的充分利用,这就是异步IO(asynchronous IO)

在Linux系统中，查看异步I/O情况如下,有一个参数叫做aio-max-nr，它用来限制系统中同时进行的最大AIO操作数量。这个参数的设置可以影响系统的性能和稳定性，因此需要根据实际情况进行调整。

#文件提供了系统范围异步 I/O 请求现在的数目
shell# cat /proc/sys/fs/aio-max-nr
65536
#文件是所允许的并发请求的最大个数。
shell# cat /proc/sys/fs/aio-nr
2305


如下设置：

echo "fs.aio-max-nr = 65535" >> /etc/sysctl.conf
sysctl -p


2.刷新数据方式

fsync 函数主要用来确保文件系统中的数据更新能被立即写入磁盘。当程序执行写操作时，fsync 函数可以强制将缓冲区中的数据写入磁盘，从而避免因为系统崩溃或其他原因导致的数据丢失。
fsync对指定的文件起作用，它传输内核缓冲区中这个文件的数据到存储设备中，并阻塞直到存储设备响应说数据已经保存好了。fsync对文件数据与文件元数据都有效。文件的元数据可以理解为文件的属性数据，比如文件的更新时间，访问时间，长度等。

fdatasync和fsync类似，两者的区别是，fdatasync不一定需要刷新文件的元数据部分到存储设备。
是否需要刷新文件的元数据，是要看元数据的变化部分是否对之后的读取有影响，比如文件元数据的访问时间st_atime和修改时间st_mtime变化了，fdatasync不会去刷新元数据数据到存储设备，因为即使这个数据丢失了不一致了，也不影响故障恢复后的文件读取。但是如果文件的长度st_size变化了，那么就需要刷新元数据数据到存储设备。

O_DSYNC的效果相当于是每次write后自动调用fdatasync。
O_DIRECT，绕过缓冲区高速缓存，直接IO。使用直接IO需要遵守的一些限制：

• 用于传递数据的缓冲区，其内存边界必须对齐为块大小的整数倍
• 数据传输的开始点，即文件和设备的偏移量，必须是块大小的整数倍
• 待传递数据的长度必须是块大小的整数倍。
  不遵守上述任一限制均将导致EINVAL错误。