MySQL性能调优指南

调优概述

调优原则

性能调优一方面在系统设计之初,需考虑硬件的选择,操作系统的选择,基础软件的选择;另一方面,包括每个子系统的设计,算法选择,如何使用编译器的选项,如何发挥硬件最大的性能等等。

在性能优化时,必须遵循一定的原则,否则,可能无法得到正确的调优结果。主要有以下几个方面:

对性能进行分析时,需要从多方面分析系统的资源瓶颈的地方,当系统某一方面性能低时,可能并非自身造成,而是其他方面造成的结果。如CPU利用率是100%时,很能是内存容量太小,因为CPU忙于处理内存调度。

  • 调整时,一次只能对影响性能的某方面的一个参数进行调整,当多个参数同时调整时,无法确定性能的影响是由哪些参数所造成的。

  • 在进行系统性能分析时,性能分析工具本身会占用一定的系统资源,如CPU、内存等,所以分析工具本身运行可能会导致系统某方面的资源瓶颈情况更加严重。

  • 必须保证调优后的程序运行正确。

  • 调优过程是持续的过程,每一次调优的结果都会反馈到后续的版本开发中去。

  • 性能调优不能以牺牲代码的可读性和可维护性为代价。

调优思路

性能优化首先要较为精准的定位问题,分析系统性能瓶颈,然后根据其性能指标以及所处层级选择优化的方式方法。

如下介绍MySQL数据库具体的调优思路和分析过程,如下图所示。

调优分析思路如下:

  • 多数情况下压测流量无法完全进入到服务端,在网络上可能会出现由于各种规格(带宽、最大连接数、新建连接数等)限制,导致压测结果达不到预期。
  • 查看关键指标是否满足要求,如果不满足,需要确定出现问题的位置,一般情况下,服务器端存在问题可能性较大,客户端问题也可能存在问题(该情况可能性较小)。。
  • 针对服务器端问题,需要定位硬件相关指标,如CPU,Memory,Disk I/O,Network I/O,如果是硬件指标有问题,需要进行深入的分析。。
  • 如果硬件指标都没有问题,需要查看数据库相关指标,如等待事件、内存命中率等。
  • 如果以上指标都正常,应用程序的算法、缓冲、缓存、同步或异步可能有问题,需要具体深入的分析。

硬件调优

目的

针对不同的服务器硬件设备,通过设置BIOS中的一些高级配置选项,可以有效提升服务器性能。

方法

以下方法针对鲲鹏服务器进行调优,X86,例如Intel服务器,可选择保持默认BIOS配置。

  1. 关闭SMMU(鲲鹏服务器特有)。

  2. 重启服务器过程中,单击Delete键进入BIOS,选择“Advanced > MISC Config”,单击Enter键进入。

  3. 将“Support Smmu”设置为“Disable” 。

    注:此优化项只在非虚拟化场景使用,在虚拟化场景,则开启SMMU。

  4. 关闭预取

    • 在BIOS中,选择“Advanced>MISC Config”,单击Enter键进入。

    • 将“CPU Prefetching Configuration”设置为“Disabled”,单击F10键保存退出。

操作系统调优

网卡中断绑核

目的

通过关闭irqbalance服务,使用手动绑定网卡中断到部分专用核上,隔离网卡中断请求和业务请求,可以有效提升系统的网络性能。

方法

对于不同的硬件配置,用于绑中断的最佳CPU数量会有差异,例如,鲲鹏920 5250上最佳CPU数量为5,可通过观察这5个CPU的使用情况以决定是否再调整用于绑中断的CPU数量。

以下脚本是在华为鲲鹏920 5250处理器+ Huawei TM280 25G网卡上的MySQL的最佳绑中断设置,其中第一个参数$1是网卡名称,第二个参数$2是队列数目5,第三个参数$3是网卡对应的总线名,可以用ethtool -i <网卡名>查询出:

#!/bin/bash
eth1=$1
cnt=$2
bus=$3
ethtool -L $eth1 combined $cnt
 
irq1=`cat /proc/interrupts| grep -E ${bus} | head -n$cnt | awk -F ':' '{print $1}'`
irq1=`echo $irq1`
cpulist=(91 92 93 94 95) # 根据所选定的用于处理中断请求的核修改
c=0
forirq in $irq1
do
  echo ${cpulist[c]} "->" $irq
  echo ${cpulist[c]} > /proc/irq/$irq/smp_affinity_list
  let "c++"
done

注:若采用下述的gazelle调优方法,则无需执行本节操作。

NUMA绑核

目的

通过NUMA绑核,减少跨NUMA访问内存,可以有效提升系统的访存性能。

方法

基于前一节的网卡中断设置,在鲲鹏920 5250上,MySQL启动命令前设置NUMA绑核范围为剩余其它核,即0-90,其中$mysql_path为MySQL的安装路径:

numactl -C 0-90 -i 0-3 $mysql_path/bin/mysqld --defaults-file=/etc/my.cnf &

注:若采用下述的gazelle调优方法,则无需使用本节手段。

调度参数调优

目的

在高负载场景下,CPU利用率不能达到100%,深入分析每个线程的调度轨迹会发现内核在做负载均衡时,通常无法找到一个合适的进程来迁移,导致CPU在间断空闲负载均衡失败,空转浪费CPU资源,通过使能openEuler调度特性STEAL模式,可以进一步提高CPU利用率,从而有效提升系统性能。(当前该特性仅在openEuler 20.03 SP2版本及之后版本支持)

方法

1)在/etc/grub2-efi.cfg中内核系统启动项末尾添加参数sched_steal_node_limit=4,修改为如下图所示:

修改完成后,reboot重启生效。

2)设置STEAL模式

重启后设置STEAL调度特性如下:

echo STEAL > /sys/kernel/debug/sched_features

大页调优

目的

TLB(Translation Lookaside Buffer)为页表(存放虚拟地址的页地址和物理地址的页地址的映射关系)在CPU内部的高速缓存。TLB的命中率越高,页表查询性能就越好,内存页面越大。相同业务场景下的TLB命中率越高,访问效率提高,可以有效提升服务器性能。

方法

  • 调整内核的内存页大小。

    通过命令getconf PAGESIZE查看当前系统的内存页大小,如果大小是4096(4K),则可通过修改linux内核的内存页大小来使用更大的内存页,需要在修改内核编译选项后重新编译内核。简要步骤如下:

    1. 执行make menuconfig

    2. 选择PAGESIZE大小为64K(Kernel Features–>Page size(64KB)。

    3. 编译和安装内核。

Gazelle协议栈调优

目的

原生内核网络协议栈层次深,开销较大,且系统调用的成本也较高。通过gazelle用户态协议栈替代内核协议栈,且通过hook posix接口,避免系统调用带来的开销,能够大幅提高应用的网络I/O吞吐能力。

方法

1)安装依赖包。

配置openEuler的yum源,直接使用yum命令安装

yum install dpdk libconfig numactl libboundsheck libcap gazelle

2)使用root权限安装ko。

网卡从内核驱动绑为用户态驱动的ko,根据实际情况选择一种。mlx4和mlx5网卡不需要绑定vfio或uio驱动。

#若IOMMU能使用
modprobe vfio-pci
#若IOMMU不能使用,且VFIO支持noiommu
modprobe vfio enable_unsafe_noiommu_mode=1
modprobe vfio-pci
#其它情况
modprobe igb_uio

3)dpdk绑定网卡。

将所使用的业务网卡(如下以enp3s0为例)绑定到步骤2选择的驱动,为用户态网卡驱动提供网卡资源访问接口。

#拔业务网卡enp3s0
ip link set enp3s0 down

#使用vfio-pci
dpdk-devbind -b vfio-pci enp3s0 

#使用igb_uio
dpdk-devbind -b igb_uio enp3s0

4)大页内存配置。

Gazelle使用大页内存提高效率。使用root权限配置系统预留大页内存,根据实际情况,选择一种页大小,配置足够的大页内存即可。如下默认每个内存节点配置2G大页内存,每个大页 2M。

#配置2M大页内存:系统静态分配 2M * 1024*4 = 8G
echo 8192 > /sys/kernel/mm/hugepages/hugepages-2048kB/nr_hugepages

#查看配置结果
grep Huge /proc/meminfo

5)挂载大页内存。

创建一个目录,供lstack进程访问大页内存时使用,操作步骤如下:

mkdir -p /mnt/hugepages-gazelle
chmod -R 700 /mnt/hugepages-gazelle
mount -t hugetlbfs nodev /mnt/hugepages-gazelle -o pagesize=2M

6)应用程序使用gazelle。

使用LD_PRELOAD预加载Gazelle动态库,GAZELLE_BIND_PROCNAME环境变量用于指定MySQL进程名:

GAZELLE_BIND_PROCNAME=mysqld LD_PRELOAD=/usr/lib64/liblstack.so $mysql_path/bin/mysqld --defaults-file=/etc/my.cnf --bind-address=192.168.1.10 &

其中bind-address为服务端业务网卡ip,需同gazelle配置文件的host_addr保持一致。

7)修改gazelle配置文件。

使能gazelle需要根据硬件环境及软件需求定制gazelle配置文件/etc/gazelle/lstack.conf,示例如下:

dpdk_args=["--socket-mem", "2048,2048,2048,2048", "--huge-dir", "/mnt/hugepages-gazelle", "--proc-type", "primary", "--legacy-mem", "--map-perfect"]

use_ltran=0
kni_switch=0
low_power_mode=0
listen_shadow=1

num_cpus="18,38,58,78 "
host_addr="192.168.1.10"
mask_addr="255.255.255.0"
gateway_addr="192.168.1.1"
devices="aa:bb:cc:dd:ee:ff"

各参数说明如下:

  • --socket-mem表示给每个内存节点分配的内存,默认2048M,示例中表示4个内存节点,每个内存节点分配2G(2048)。
  • –huge-dir为先前建立的挂载了大页内存的目录。
  • num_cpus记录lstack线程绑定的cpu编号,可按NUMA选择CPU。
  • host_addrmask_addrgateway_addrdevices分别表示业务网卡的IP地址、掩码、网关地址和mac地址。 其中,参数--socket-mem表示给每个内存节点分配的内存,默认2048M,例子中表示4个内存节点,每个内存节点分配2G(2048);参数--huge-dir为先前建立的挂载了大页内存的目录;num_cpus记录lstack线程绑定的cpu编号,可按NUMA选择CPU。参数host_addrmask_addrgateway_addrdevices分别表示业务网卡的IP地址、掩码、网关地址和mac地址。

更详细的使用指导详见:Gazelle用户指南

MySQL调优

数据库参数调优

目的

通过调整数据库的参数配置,可以有效提升服务器性能。

方法

默认配置文件路径为/etc/my.cnf,可使用如下配置文件参数启动数据库:

[mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid
 
[client]
socket=/data/mysql/run/mysql.sock
default-character-set=utf8

[mysqld]
basedir=/usr/local/mysql
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
socket=/data/mysql/run/mysql.sock
port=3306
user=root
default_authentication_plugin=mysql_native_password
ssl=0 #关闭ssl
max_connections=2000 #设置最大连接数
back_log=2048 #设置会话请求缓存个数
performance_schema=OFF #关闭性能模式
max_prepared_stmt_count=128000
 
#file
innodb_file_per_table=on #设置每个表一个文件
innodb_log_file_size=1500M #设置logfile大小
innodb_log_files_in_group=32 #设置logfile组个数
innodb_open_files=4000 #设置最大打开表个数

#buffers
innodb_buffer_pool_size=230G #设置buffer pool size,一般为服务器内存60%
innodb_buffer_pool_instances=16 #设置buffer pool instance个数,提高并发能力
innodb_log_buffer_size=64M #设置log buffer size大小

#tune
sync_binlog=1 #设置每次sync_binlog事务提交刷盘
innodb_flush_log_at_trx_commit=1 #每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去
innodb_use_native_aio=1 #开启异步IO
innodb_spin_wait_delay=180 #设置spin_wait_delay 参数,防止进入系统自旋
innodb_sync_spin_loops=25 #设置spin_loops 循环次数,防止进入系统自旋
innodb_spin_wait_pause_multiplier=25 #设置spin lock循环随机数
innodb_flush_method=O_DIRECT #设置innodb数据文件及redo log的打开、刷写模式
innodb_io_capacity=20000 # 设置innodb 后台线程每秒最大iops上限
innodb_io_capacity_max=40000 #设置压力下innodb 后台线程每秒最大iops上限
innodb_lru_scan_depth=9000 #设置page cleaner线程每次刷脏页的数量
innodb_page_cleaners=16 #设置将脏数据写入到磁盘的线程数
table_open_cache_instances=32 #设置打开句柄分区数
table_open_cache=30000 #设置打开表的数量

#perf special
innodb_flush_neighbors=0 #检测该页所在区(extent)的所有页,如果是脏页,那么一起进行刷新,SSD关闭该功能
innodb_write_io_threads=16 #设置写线程数
innodb_read_io_threads=16 #设置读线程数
innodb_purge_threads=32 #设置回收已经使用并分配的undo页线程数
innodb_adaptive_hash_index=0

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

表1 数据库调优参数

参数名称参数含义优化建议
innodb_thread_concurrencyInnoDB使用操作系统线程来处理用户的事务请求。建议取默认值为0,它表示默认情况下不限制线程并发执行的数量。
innodb_read_io_threads执行请求队列中的读请求操作的线程数。根据CPU核数及读写比例进一步更改来提高性能。
innodb_write_io_threads执行请求队列中的写请求操作的线程数。根据CPU核数及读写比例进一步更改来提高性能。
innodb_buffer_pool_instances开启多个内存缓冲池,把需要缓冲的数据hash到不同的缓冲池中,这样可以并行的内存读写建议设置8~32。
innodb_open_files在innodb_file_per_table模式下,限制Innodb能打开的文件数量。建议此值调大一些,尤其是表特别多的情况。
innodb_buffer_pool_size缓存数据和索引的地方。通常建议内存的60%左右。
innodb_log_buffer_size缓存重做日志。默认值是64M,建议通过查看innodb_log_wait,调整innodb_log_buffer_size大小。
innodb_io_capacityinnodb 后台线程每秒最大iops上限。建议为IO QPS总能力的75%。
innodb_log_files_in_group重做日志组的个数。-
innodb_log_file_size重做日志文件大小。如果存在大量写操作,建议增加日志文件大小,但日志文件过大,会影响数据恢复时间。 如果是非生产环境,测试极限性能时,尽量调大日志文件。 如果是商用场景,需要考虑数据恢复时间,综合折中后设置日志文件大小。
innodb_flush_methodLog和数据刷新磁盘的方法: datasync模式:写数据时,write这一步并不需要真正写到磁盘才算完成(可能写入到操作系统buffer中就会返回完成),真正完成是flush操作,buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。 O_DSYNC模式:写日志操作是在write这步完成,而数据文件的写入是在flush这步通过fsync完成。 O_DIRECT模式:数据文件的写入操作是直接从mysql innodb buffer到磁盘的,并不用通过操作系统的缓冲,而真正的完成也是在flush这步,日志还是要经过OS缓冲。建议O_DIRECT模式。
innodb_spin_wait_delay控制轮询的间隔。根据真实场景调试,直到看不到spin_lock热点函数等。优化建议180。
innodb_sync_spin_loops控制轮询的循环次数。根据真实场景调试,直到看不到spin_lock热点函数等。优化建议25。
innodb_spin_wait_pause_multiplier控制轮询间隔随机数。根据真实场景调试,直到看不到spin_lock热点函数等。默认值50,优化建议25-50。
innodb_lru_scan_depthLRU列表的可用页数量。默认值是1024,非生产环境,测试极限性能可以适当调大,减少checkpoint次数。
innodb_page_cleaners刷新脏数据的线程数。建议与innodb_buffer_pool_instances相等。
innodb_purge_threads回收undo的线程数。-
innodb_flush_log_at_trx_commit不管有没有提交,每秒钟都写到binlog日志里. 每次提交事务,都会把log buffer的内容写到磁盘里去,对日志文件做到磁盘刷新,安全性最好。 每次提交事务,都写到操作系统缓存,由OS刷新到磁盘,性能最好。非生产环境,测试极限性能,可以设置为0。
innodb_doublewrite是否开启二次写。非生产环境,测试极限性能,可以设置为0,关闭二次写。
ssl是否开启安全连接。安全连接对性能影响较大,非生产环境,测试极限性能,可以设置为0,商用场景,根据客户需求调整。
table_open_cache_instancesMySQL 缓存 table 句柄的分区的个数。建议设置16-32。
table_open_cacheMysqld打开表的数量。建议设置成30000。
skip_log_bin是否开启binlog。非生产环境,测试极限性能在参数文件中增加此参数,关闭binlog选项(添加至配置文件中: skip_log_bin #log-bin=mysql-bin)。
performance_schema是否开启性能模式。非生产环境,测试极限性能设置为OFF,关闭性能模式。

数据库内核调优

目的

数据库内核优化是指通过修改MySQL数据库源码,提升数据库性能。使用数据库内核优化patch,需重新编译数据库。

方法

MySQL数据库内核优化分为两个不同的场景,一个是OLTP场景,一个是OLAP场景,不同的场景采用不同的优化patch。

OLTP场景是指主要面向交易的处理系统,以小的事物及小的查询为主,快速响应用户操作。OLTP内核优化patch参考MySQL细粒度锁优化

OLAP场景是指主要对用户当前及历史数据进行分析、查询和生成报表,支持管理决策。OLAP内核优化patch参考MySQL OLAP并行优化

文档捉虫

“有虫”文档片段

问题描述

提交类型 issue
有点复杂...
找人问问吧。
PR
小问题,全程线上修改...
一键搞定!
问题类型
规范和低错类

● 错别字或拼写错误;标点符号使用错误;

● 链接错误、空单元格、格式错误;

● 英文中包含中文字符;

● 界面和描述不一致,但不影响操作;

● 表述不通顺,但不影响理解;

● 版本号不匹配:如软件包名称、界面版本号;

易用性

● 关键步骤错误或缺失,无法指导用户完成任务;

● 缺少必要的前提条件、注意事项等;

● 图形、表格、文字等晦涩难懂;

● 逻辑不清晰,该分类、分项、分步骤的没有给出;

正确性

● 技术原理、功能、规格等描述和软件不一致,存在错误;

● 原理图、架构图等存在错误;

● 命令、命令参数等错误;

● 代码片段错误;

● 命令无法完成对应功能;

● 界面错误,无法指导操作;

风险提示

● 对重要数据或系统存在风险的操作,缺少安全提示;

内容合规

● 违反法律法规,涉及政治、领土主权等敏感词;

● 内容侵权;

您对文档的总体满意度

非常不满意
非常满意
提交
根据您的反馈,会自动生成issue模板。您只需点击按钮,创建issue即可。