Linux中MYSQL通用调优策略

2301_80149646

139人浏览 · 2026-04-11 16:02:52

2301_80149646 · 2026-04-11 16:02:52 发布

1、硬件层相关优化

修改服务器BIOS设置，找到，CPU电源管理，选择Performance Per Wa#t Optimized(DAPC)，发挥CPU最大性能。然后关闭再将C-states和C1E关闭，开启Turbo Boots可以将CPU保持运行全核睿频的状态下。

然后修改BIOS设置中的Memory Frequency(内存频率)，这个选项是控制BIOS内存频率，可以通过此参数节省内存频率以节省电力，但对于跑MySQL的机器来说，省电就算了，还是以性能为主，选择MaximumPerformance(最佳性能)

最后，在内存设置菜单中，启用NodeInterleaving，避免NUMA问题，这个参数是专门为了控制NUMA而设置。NUMA是一种关于多个CpU如何访问内存的架构模型。

2、磁盘I/O相关优化

(1)、使用SSD硬盘，至少获得数百倍甚至万倍的IOPS提升。

(2)、购置阵列卡建议配备CACHE及BBU模块，可明显提升IOPS

这个主要针对机械硬盘，SSD磁盘除外。同时需要定期检查CACHE及BBU模块的健康状况，确保意外时不至于丢失数据。

主流的DEL/HP/BM等服务器厂商，都会在Raid控制卡里都会内置128MB至1GB不等的Cache Memory，而我们对磁盘的读和写操作都会通过事先在Cache Memory中Hit或缓存，这样一来就可以大大提高了实际IO性能.而BBU就是Raid卡中的一个电池备用模块，因为之前我们说到在Raid的环境下很多情况下数据都是通过Cache Memory和磁盘交换的，而Memory本身并无法保障数据持久性，万一电源中断，而数据没来得及fush到物理磁盘上，就会造成数据丢失的悲剧。为此硬件厂商提供了BBU，其中包含了一块锂电池来保障万一电源中断的情况下，Cache Memory中的数据不至于丢失，直至电源恢復。

(3)、磁盘raid级别尽量选择raid10，而不是raid5.

3、文件系统层优化

(1)、使用deadline/noop这两种l/O调度器，不要用cfq

CFQ是完全公平排队I/O调度算法，CFQ适用于系统中存在多任务I/O请求的情况，通过在多进程中轮换，保证了系统VO请求整体的低延迟。但是，对于只有少数进程存在大量密集的I/O请求的情况，会出现明显的I/O性能下降。

NOOP是电梯式调度算法，调度方式十分简单，它是按先来先处理的思路将请求插入到等待队列的尾部。

DEADLINE是截止时间调度算法，它确保了在一个截止时间内服务的请求,这个截止时间是可调整的,而默认读期限短于写期限.这样就防止了写操作因为不能被读取而饿死的现象。Deadline对数据库环境(ORACLERAC,MYSQL等)是最好的选择。

(2)、推荐使用xfs文件系统

不要使用ex13，ext4勉强可用，如果业务量很大的话，一定要用xfs，此外，文件系统在mount时，建议增加:noatimenodiratime,nobarier几个选项(nobarier是xs文件系统特有的)。这几个选项主要是禁止记录文件或目录最近一次访问时间戳。

4、Linux系统内核参数优化

(1) 、修改内核参数 /proc/sys/vm/swappiness

swappiness 的值的大小对如何使用 swap 分区是有着很大的联系。 swappiness =0 的时候表示最大限度使用物理内存, 然后才是 swap 空间 , swappiness=100 的时候表示积极的使用 swap 分区，并且把内存上的数据及时的搬运到 swap 空间里面。 linux 的基本默认设置为60 。也就是说，你的内存在使用到 100-60=40% 的时候，就开始出现现有交换分区的使用。

将 vm . swappiness 设置为5 - 10 左右即可，设置方法为：

echo 10 > / proc / sys/ vm / swappiness

(2) 、调整 vm . dirty_background_ratio 、 vm . dirty_ratio 内核参数

vm . dirty_background_ratio : 这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如 5% ）就会触发 pdflush/flush/kdmflush 等后台回写进程运行，将一定缓存的脏页异步地刷入外存；

vm. dirty_ratio ：这个参数则指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如 10% ），系统不得不开始处理缓存脏页（因为此时脏页数量已经比较多：为了避免数据丢失需要将一定脏页刷入磁盘）；在此过程中很多应用进程可能会出现 I/O 阻塞。

将 vm . dirty_background_ratio 设置为 5 - 10 ，将 vm . dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间 I/O 写，产生严重等待。

cat /proc / sys / vm / dirty_ratio
cat / proc / sys / vm / dirty_background_ratio

5、mysql参数优化建议

(1)、innodb_buffer_pool_size

设置数据和索引缓存的大小, 如果是单实例且绝大多数是InnoDB引擎表的话, 可考虑设置为物理内存的20%-65%左右, 为什么有一个20%呢? 对于<4gb的mysql用服务器来说按照20%系统内存来设置, 如果物理内存是128gb, 此值建议设置为72G。

(2)、innodb_log_file_size（通常100M的大小）

这个值是配置redo log日志的大小, 此参数没有一个绝对的概念, 如果对Innodb数据表有大量的写入操作, 那么选择合适的innodb_log_file_size值对提升MySQL性能很重要。然而设置太大了, 就会增加恢复的时间, 因此在MySQL崩溃或者突然断电等情况会令MySQL服务花很长时间来恢复。

Redo log的空间通过innodb_log_file_size和innodb_log_files_in_group（默认2）参数来调节。将这两参数相乘即可得到总的可用Redo log空间。尽管技术上并不关心你是通过innodb_log_file_size还是innodb_log_files_in_group来调整Redo log空间, 不过多数情况下还是通过innodb_log_file_size来调节。

(3)、innodb_log_buffer_size（最大不超过32M）

这个配置决定了为尚未执行的事务分配的缓存。其默认值 (1MB)一般来说已经够用了, 但是如果事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量, 如果它不是0, 就需要增加innodb_log_buffer_size。缓冲区更大能提高性能, 但意外的故障可能导致数据丢失。

(4)、innodb_flush_log_at_trx_commit

此参数控制事务的提交方式,也就是控制log的新刷新到磁盘的方式。默认值是1。

这个参数只有3个值[0, 1, 2], 性能更高的可以设置为0或是2, 这样可以适当的减少磁盘IO{但会丢失一秒钟的事务}., 游戏库的MySQL建议设置为0。主库语不要更改。

其中:

- 0: log buffer中的数据将以每秒一次的频率写入到log file中,且同时会进行文件系统到磁盘的同步操作,但是每个事务的commit并不会触发任何log buffer到log file的刷新或者文件系统到磁盘的操作;
- 1:默认为1每次事务执行的时候将log buffer中的数据都会写入到log file, 同时也会触发文件系统到磁盘的同步;
- 2: 事务提交会触发log buffer到log file的刷新,但并不会触发磁盘文件系统到磁盘的同步。此外,每秒会有一次文件系统到磁盘同步操作。对于slave节点这个值是可以接受的。

(5)、skip_name_resolve

生产上建议开启成],这样mysql server不会对客户端连接使用反向dns解析,否则经常会出现客户端连上后有timeout现象,如果设成了1带来的问题就是你不能在mysql中使用主机名来对客户端权限进行划分,而是需要使用ip。

(6)、max_connections

此参数用来配置mysql的最大连接数, 如果看到 ‘Too many connections’ 错误, 就表示max_connections的值太低了。根据mysql服务器硬件配置不同, 此值可设置的大小也不同, 生产环境上建议设置不超过20000。max_connections值受系统Linux系统最大打开连接数限制, 因此我们需要做以下2步操作:

1）在 /etc/security/limits.conf 底部增加2行

mysql hard nofile 65535
mysql soft nofile 65535

2）在/usr/lib/systemd/system/mysqld.service（视如何安装mysql所决定, 用编译安装和yum安装会产生path路径不同。）文件最后添加:

LimitNOFILE=65535
LimitNPROC=65535
# systemctl daemon-reload
# systemctl restart mysqld.service

(7)、gtid_mode

gtid_mode: 主从复制时用, 推荐开启成on, 也就是开启了GTID模式, GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识,保存在mysql数据目录下的auto.cnf文件里。TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增。相比传统的复制, GTID复制对于运维更加友好,根据GTID可以知道事务最初是在哪个实例上提交的,产生多少事物等,都可以非常直接的标识出来。

(8)、log_bin

开启二进制日志记录功能,如果你想让数据库服务器充当主节点,那么必须开启二进制日志记录功能。

(9)、tmp_table_size

设置临时表的大小,例如在做高级GROUP BY操作时,会生成临时表。默认系统为32M,如果临时表越来越多,加在一起超过了这个值,那么mysql就会在系统磁盘上创建,这个值不是越大越好,也没有一个合适的值。一开始建议设置为64M,后面根据情况进行调整。

(10)、max_allowed_packet

此参数用来设置mysql server可接受的数据包的大小。

用客户端导入数据的时候,会遇到错误代码: 1153 - Got a packet bigger than 'max_allowed_packet' bytes 终止了数据导入, 这种情况下, 就需要调整此值了。

客户端和服务器均有自己的max_allowed_packet变量, 因此, 如果打算处理大的信息包,必须增加客户端和服务器上的该变量。