MySQL性能调优指南
调优概述
调优原则
性能调优一方面在系统设计之初，需考虑硬件的选择、操作系统的选择、基础软件的选择；另一方面，包括每个子系统的设计、算法选择、如何使用编译器的选项、如何发挥硬件最大的性能等等。
在性能优化时，必须遵循一定的原则，否则，可能无法得到正确的调优结果。主要有以下几个方面：
对性能进行分析时，需要从多方面分析系统的资源瓶颈的地方，当系统某一方面性能低时，可能并非自身造成，而是其他方面造成的结果。如CPU利用率是100%时，可能是内存容量太小，因为CPU忙于处理内存调度。
调整时，一次只能对影响性能的某方面的一个参数进行调整，当多个参数同时调整时，无法确定性能的影响是由哪些参数所造成的。
在进行系统性能分析时，性能分析工具本身会占用一定的系统资源，如CPU、内存等，所以分析工具本身运行可能会导致系统某方面的资源瓶颈情况更加严重。
必须保证调优后的程序运行正确。
调优过程是持续的过程，每一次调优的结果都会反馈到后续的版本开发中去。
性能调优不能以牺牲代码的可读性和可维护性为代价。
调优思路
性能优化首先要较为精准的定位问题，分析系统性能瓶颈，然后根据其性能指标以及所处层级选择优化的方式方法。
MySQL数据库具体的调优思路和分析过程如下图所示：
调优分析思路如下：
- 多数情况下压测流量无法完全进入到服务端，在网络上可能会出现由于各种规格（带宽、最大连接数、新建连接数等）限制，导致压测结果达不到预期。
- 查看关键指标是否满足要求，如果不满足，需要确定出现问题的位置，一般情况下，服务器端存在问题可能性较大，客户端也可能存在问题（该情况可能性较小）。
- 针对服务器端问题，需要定位硬件相关指标，如CPU、Memory、Disk I/O、Network I/O，如果是硬件指标有问题，需要进行深入的分析。
- 如果硬件指标都没有问题，需要查看数据库相关指标，如等待事件、内存命中率等。
- 如果以上指标都正常，应用程序的算法、缓冲、缓存、同步或异步可能有问题，需要具体深入的分析。
硬件调优
目的
针对不同的服务器硬件设备，通过设置BIOS中的一些高级配置选项，可以有效提升服务器性能。
方法
以下方法针对鲲鹏服务器进行调优，X86，例如Intel服务器，可选择保持默认BIOS配置。
关闭SMMU（鲲鹏服务器特有）。
重启服务器过程中，单击Delete键进入BIOS，选择“Advanced > MISC Config”，单击Enter键进入。
将“Support Smmu”设置为“Disable” 。
注意：此优化项只在非虚拟化场景使用，在虚拟化场景，则开启SMMU。
关闭预取。
在BIOS中，选择“Advanced>MISC Config”，单击Enter键进入。
将“CPU Prefetching Configuration”设置为“Disabled”，单击F10键保存退出。
操作系统调优
注意：若采用下述的gazelle调优方法，则无需执行本节操作。
网卡中断绑核
目的
通过关闭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
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内核的内存页大小来使用更大的内存页，需要在修改内核编译选项后重新编译内核。简要步骤如下：执行
make menuconfig。选择PAGESIZE大小为64K（Kernel Features-->Page size(64KB）。
编译和安装内核。
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_addr、mask_addr、gateway_addr和devices分别表示业务网卡的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_concurrency | InnoDB使用操作系统线程来处理用户的事务请求。 | 建议取默认值为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_capacity | innodb 后台线程每秒最大iops上限。 | 建议为IO QPS总能力的75%。 |
| innodb_log_files_in_group | 重做日志组的个数。 | - |
| innodb_log_file_size | 重做日志文件大小。 | 如果存在大量写操作，建议增加日志文件大小，但日志文件过大，会影响数据恢复时间。 如果是非生产环境，测试极限性能时，尽量调大日志文件。 如果是商用场景，需要考虑数据恢复时间，综合折中后设置日志文件大小。 |
| innodb_flush_method | Log和数据刷新磁盘的方法： 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_depth | LRU列表的可用页数量。 | 默认值是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_instances | MySQL 缓存 table 句柄的分区的个数。 | 建议设置16-32。 |
| table_open_cache | Mysqld打开表的数量。 | 建议设置成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并行优化。
