MySql 配置优化

优化思路针对MySQL优化，无非是涉及到内存、IO、CPU的优化，该文档将依次从这三个方面来介绍。问题定位如何定位是内存、IO、CPU当中的哪一个环节的问题。可以参考如下步骤：使用top命令查看MySQL进程的CPU及内存使用情况。如果内存占用低，可以考虑进行内存参数优化；如果CPU使用率低，可以考虑进行CPU参数优化。使用iotop或者iostat分析磁盘IO。关键考虑数据吞吐量和IOPS两个参

邪意、懵生

4951人浏览 · 2022-01-27 11:12:50

邪意、懵生 · 2022-01-27 11:12:50 发布

优化思路

针对MySQL优化，无非是涉及到内存、IO、CPU的优化，该文档将依次从这三个方面来介绍。

问题定位

如何定位是内存、IO、CPU当中的哪一个环节的问题。可以参考如下步骤：

使用top命令查看MySQL进程的CPU及内存使用情况。如果内存占用低，可以考虑进行内存参数优化；如果CPU使用率低，可以考虑进行CPU参数优化。
使用iotop或者iostat分析磁盘IO。关键考虑数据吞吐量和IOPS两个参数，如果吞吐量和IOPS过低，则需要进行磁盘参数优化。数据吞吐量可以通过iotop查看，IOPS可以通过fio测试得出。
使用iotop查看磁盘读写最大的进程
```
yum install iotop -y
iotop
```
使用iostat分析磁盘IO占用
```
yum install sysstat -y
iostat -x -m -d 2 10
```
使用fio进行磁盘IOPS测试。注意:命令中的/tmp/iotest需要改为MySQL数据文件所在的磁盘
```
yum install fio -y
fio -name=randread-4K -rw=randrw -rwmixwrite=50 -bs=4K -filename=/tmp/iotest -size=1G -iodepth=32 -ioengine=libaio -direct=1 -runtime=100 -invalidate=1 -time_based
rm -rf /tmp/iotest
```
如上图所示，系统总IOPS为read(35.9k)+write(35.9k)=total(71.8k)，即总IOPS为70000左右。系统总吞吐量为read(140MB)+write(140MB)=total(140MB)，即总吞吐量为280MB左右。

内存优化

在常见的内存优化场景中，一般会涉及到内存大小设置及缓存命中率的问题。一般的优化方式有：

innodb_buffer_pool_size优化。Mysql优化之innodb_buffer_pool_size篇(opens new window)

磁盘优化

在常见的磁盘优化场景中，一般会涉及到MySQL磁盘IOPS设置的问题。一般的优化方式有：

innodb_io_capacity优化。mysql 参数调优(6)之磁盘IO性能相关的innodb_io_capacity_max 和innodb_io_capacity(opens new window)

CPU优化

在常见的磁盘优化场景中，一般会涉及到MySQL读写线程设置的问题。一般的优化方式有：

innodb_read_io_threads及innodb_read_io_threads优化。MySQL 8.0.16 调优指南（鲲鹏920）(opens new window)

配置文件示例

以下配置适用于16核16GB内存，IOPS约为30000的服务器，单独部署MySQL的情况。

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 12G
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 1G
sort_buffer_size = 200M
read_rnd_buffer_size = 200M

default-authentication-plugin=mysql_native_password

socket=/var/lib/mysql/mysql.sock

max_connections=1200
wait_timeout=1800 
interactive_timeout=7200


log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

skip_log_bin
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_io_capacity=15000
innodb_io_capacity_max=30000