MySql 配置优化

全屏阅读
  • 基本信息
  • 作者:
  • 作者已发布:917篇文章
  • 发布时间:2022年10月03日 9:51:53
  • 所属分类:Mysql优化, PHP+MySql
  • 阅读次数:339次阅读
  • 标签:

优化思路

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

问题定位

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

1、使用top命令查看MySQL进程的CPU及内存使用情况。如果内存占用低,可以考虑进行内存参数优化;如果CPU使用率低,可以考虑进行CPU参数优化。

image.png

2、使用iotop或者iostat分析磁盘IO。关键考虑数据吞吐量和IOPS两个参数,如果吞吐量和IOPS过低,则需要进行磁盘参数优化。数据吞吐量可以通过iotop查看,IOPS可以通过fio测试得出。
使用iotop查看磁盘读写最大的进程

yum install iotop -y
iotop

image.png

使用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

磁盘优化

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

innodb_io_capacity优化。mysql 参数调优之磁盘IO性能相关的innodb_io_capacity_max 和innodb_io_capacity

CPU优化

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

innodb_read_io_threads及innodb_read_io_threads优化。 

配置文件示例

以下配置适用于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

顶一下
(0)
100%
订阅 回复
踩一下
(0)
100%
» 固定链接:恒富网 » 《MySql 配置优化》
» 郑重声明:本文由mpxq168发布,所有内容仅代表个人观点。版权归恒富网mpxq168共有,欢迎转载, 但未经作者同意必须保留此段声明,并给出文章连接,否则保留追究法律责任的权利! 如果本文侵犯了您的权益,请留言。

目前有 0 条留言 其中:访客:0 条, 博主:0 条

给我留言

您必须 [ 登录 ] 才能发表留言!