影响MySQL之IO性能最重要的几个参数

最新推荐文章于 2025-01-13 23:23:02 发布

原创最新推荐文章于 2025-01-13 23:23:02 发布 · 6.6k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#MYSQL #性能

MySQL高级及性能优化专栏收录该内容

71 篇文章

订阅专栏

本文深入解析MySQL中sync_binlog、innodb_flush_logs_at_trx_commit、innodb_flush_method等核心性能优化参数，以及innodb_buffer_pool_size、innodb_io_capacity等关键配置，旨在帮助用户了解如何通过合理设置这些参数，实现MySQL性能的显著提升。

1、sync_binlog
二进制日志文件binlog的刷新写入方式，这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于sync_binlog参数的各种设置的说明如下：
1) sync_binlog=0，
当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。如果没刷新到磁盘前系统宕机，则会丢失最后的binlog内容，但是此参数性能最佳
2) sync_binlog=n，
当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

2、 innodb_flush_logs_at_trx_commit
InnoDB引擎事务日志文件的刷新写入方式，这个参数对InnoDB引擎的写入性能来说是到头重要的，有以下几种设置：
   1) =1时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。最安全的方式，但是速度最慢。
2) =2时，在每个事务提交时，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。
3) =0时，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务

3、innodb_flush_method
这个参数控制着innodb数据文件及redo og的打开、刷写模式，有以下几种设置：
   1) 默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
   2) 为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件，通常比较慢。
   3) 为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log，在Linux上使用Direct IO，可以显著提高速度，特别是在RAID系统上，避免额外的数据复制和double buffering（mysql buffering 和OS buffering）。

任何数据库，只要涉及到持久化，就与上面这三个方面的参数有极大关系，包括NOSQL与内存数据库，有时NOSQL与内存数据库之所以比MYSQL快，与这方面的设置也有很大关系

其实，在大部分场景下，如果某个产品宣称自己的写读tps超过其他存储n倍，一般来说都是从k-v这个角度入手进行优化的，主要入手的点是树的数据结构优化和锁的细化，一般都能在一些特定的场景获得5-10倍的性能提升。

4、innodb_buffer_pool_size
这是Innodb最重要的一个配置参数，这个参数控制Innodb本身的缓大小，也影响到，多少数据能在缓存中。建议该参数的配置在物理内存的70％－80％之间。

5、innodb_io_capacity
这个参数控制Innodb checkpoint时的IO能力，一般可以按一块SAS 15000转的磁盘200个计算，6块盘的SAS做的Raid10这个值可以配到600即可。如果是普通的SATA一块盘只能按100算。(innodb-plugin, Percona有这个参数)

MYSQL对这方面的设置比较保守，因为它要充分保证数据在任何异常情况下都不能有丢失，这就要求事务提交时，日志必须被最终完全的刻到磁盘上，不可以到任何的缓存上。如果，innodb_buffer_pool_size参数设置的够大，能够容纳整个表，并且把上面三个参数设置成最优设置（如：sync_binlog=0，innodb_flush_logs_at_trx_commit=0，sync_binlog=O_DIRECT），那么性能会有10-50倍的提高