如何进行MySQL数据库基础性能调优
发表于 2023/10/18
0
本文我们一起了解一下MySQL的数据库基础性能调优思路，熟悉数据库在鲲鹏平台的基本调优方法。MySQL数据库的基础性能调优通常可以从硬件、操作系统和MySQL应用程序本身3个方面入手，如图3-1所示。
（1）通过调整鲲鹏平台的硬件参数来调优，包括硬盘、网络、内存、CPU几个维度的优化。由于数据库是IO敏感型应用，IO时延对其影响很大。数据库存储通常选择IO时延比较低的硬盘介质，如NVMe、SSD等。网卡时延对带宽、对数据库的影响也比较大，尤其是集群和分布式架构的数据库，对网络时延要求比较高。
（2）通过调整操作系统的参数来调优，例如通过中断绑核的方式把一些后台线程由网络中断绑定在固定的CPU核上，减少上下文的切换，提升应用的性能。
（3）通过调整MySQL数据库应用本身的参数，以及借助鲲鹏BoostKit提供的加速特性来调优，使得数据库在鲲鹏平台上运行的性能更优。
图3-1 MySQL数据库基础性能调优思路
鲲鹏平台硬件调优
您可以根据具体的业务应用场景和需求来对鲲鹏硬件进行调优，同时可以结合性能测试工具进行测试和优化。表3-1中给出了针对鲲鹏服务器BIOS的一些优化手段，可以有效提升服务器性能。
进入鲲鹏服务器BIOS设置界面的具体操作请参见《TaiShan 服务器 BIOS 参数参考(鲲鹏920处理器)》中“进入BIOS界面”的相关内容。
表3-1 鲲鹏平台硬件调优
优化手段 | 优化建议 | 配置方法 |
|---|---|---|
关闭SMMU |
SMMU（System Memory Management Unit）是IO设备与总线桥之间的一个地址转换桥，可以实现虚拟地址到物理地址的转换，同时还可以对内存访问进行权限控制和缓存管理，确保系统内存的安全和高效使用。因为数据库通常会使用大量的内存和IO资源，而SMMU会增加额外的开销和延迟，从而降低系统的性能。因此在数据库场景，开启SMMU并不能获得更好的性能。此外，关闭SMMU还可以减少系统的复杂性和维护成本。因此在鲲鹏平台，建议在BIOS中将SMMU关闭。 |
进入BIOS，选择“Advanced > MISC Config > Support Smmu”，设置为“Disable”。 |
关闭硬件预取 |
硬件预取是通过跟踪指令和数据地址的变化，将指令和地址提前读到Cache里，硬件预取对数据库场景的性能有影响，建议在BIOS中将预取功能关闭。 |
进入BIOS，选择“Advanced > MISC Config > CPU Prefetching Configurtion”，设置为“Disabled”。 |
设置CPU工作模式为“性能模式” |
在数据库场景下，建议在BIOS中将CPU工作模式设置为“性能模式”，提高CPU的运行速度和响应速度，从而提高数据库的处理能力和响应速度。“性能模式”会将CPU的频率提高到最大值，以便更快地处理数据。此外，性能模式还会关闭一些省电功能，如动态频率调节和睡眠状态，以确保CPU在高负载情况下保持高性能。 |
进入BIOS，选择“Advanced > Perfomrance Config > Power Policy”，设置为“Performance”。 |
内存刷新频率 |
内存刷新频率是指将内存中的数据写入磁盘的时间间隔。为了平衡数据的一致性和性能，同时兼顾实际运行的系统负载、数据量等因素，建议在BIOS中将内存刷新频率设置为64ms。 （1）如果内存刷新频率太低，可能会导致数据丢失或不一致。 （2）如果内存刷新频率太高，可能会导致过多的磁盘IO操作，降低系统性能。 在实际应用中，设置为64ms是一个比较合理的值，一方面可以保证数据的一致性，另一方面也不会对系统性能造成太大的影响。 |
进入BIOS，选择“Advanced > Memory Config > Custom Refresh Rate”，设置为“64ms”。 |
操作系统调优
在数据库场景下，针对操作系统进行调优，可以提高数据库的性能和稳定性，本文提供两种常见的操作系统调优方法。
1. 关闭irqbalance
CPU与磁盘、网卡、键盘等外部设备交互时，CPU下发IO请求到这些设备后，相对于CPU的处理能力而言，磁盘、网卡等设备需要较长时间完成请求处理。在请求发出到处理完成这段时间，CPU下发请求后执行其他进程，磁盘等外部设备完成处理后主动告知CPU，这种机制叫做中断。irqbalance会将中断尽可能均匀地分发到多个CPU核，充分利用CPU的多核优势。但在实际的应用场景中，将中断分发到多个核上，并不能发挥多核的优势，反而对性能有一定的影响。因此建议在数据库场景关闭irqbalance，然后通过人工手动绑核进行性能调优。具体配置步骤如下：
步骤 1：请在服务器上执行以下命令关闭irqbalance。
systemctl stop irqbalance.service
systemctl disable irqbalance.service步骤 2：查看irqbalance服务状态是否已关闭。
systemctl status irqbalance.service回显信息中状态为“inactive”即为关闭。
----结束
2. 网卡中断绑核
人工适当对网卡进行中断绑核能够提高网络IO性能以及应用程序的整体性能。如果监控到中断核对应CPU利用率过高，可以参考以下原则调整绑核策略。
（1）中断核/业务核分离。
将中断处理与应用程序分开绑核，可以减少线程切换开销。
（2）中断均匀分布在目标CPU上，尽量使各中断核负载均衡。
（3）中断核负载过大时，增加中断核数量、网卡队列数量。
1）中断核满载，增加核数，减少每核绑定的中断号。
2） 当中断队列不足时，可以通过ethtool -L $DEV combined $number命令增大中断队列。例如以下命令将网卡enp61s0f1的中断队列数调整为32：
ethtool -L enp61s0f1 combined 32您可以执行如下命令查看最大可设置中断队列数。
ethtool -l $DEV预期结果示例：
ethtool -l ens2f1
Channel parameters for ens2f1:
Pre-set maximums:
RX: 0
TX: 0
Other: 1
Combined: 64 //最大中断队列数
Current hardware settings:
RX: 0
TX: 0
Other: 1
Combined: 8 //当前中断队列数通过查看“proc/interrupts”文件来查看网卡的中断号，然后手动将中断绑到不同的CPU核上。绑核具体操作步骤如下：
步骤 1：查找网卡中断号。
中断信息保存在“/proc/interrupts”中，可通过下面任一方式查询：
（1）通过网卡名查询
grep $nic /proc/interrupts | awk '{print $1, $NF}'（2）通过驱动信息、总线地址查询
部分网卡无法通过网卡名在中断表中查询到，可以使用驱动信息及总线地址。
获取网卡驱动及总线地址信息。
ethtool -i $nic通过驱动信息查询网卡中断号。如果同一驱动对应多张网卡，可通过总线地址进一步筛选。
grep $nic_driver /proc/interrupts | awk '{print $1, $NF}'步骤 2：确定目标CPU。
建议将中断绑定到网卡所在NUMA的CPU上。
（1）查看网卡所在NUMA。
cat /sys/class/net/enp61s0f3/device/numa_node（2）查询NUMA对应CPU。
lscpu步骤 3：执行绑核命令。
将步骤2和步骤3中的CPU序号绑定对应中断号。
echo ${cpu1} > /proc/irq/${irq1}/smp_affinity_list echo ${cpu2} > /proc/irq/${irq2}/smp_affinity_list步骤 4：检查绑核情况。您可以通过以下两种方式检查：
（1）查看网络，预期效果是通过预期的网口进行收发包。
sar -n DEV 1（2）执行相关压测命令，通过top等工具观测中断核，预期效果是存在较高的内核占用。
----结束
MySQL数据库参数调优
MySQL数据库参数调优是提高数据库性能的重要手段之一。由于MySQL数据库是动态管理系统，在实际应用中，有时会因参数调整不当而降低MySQL的性能，因此需要通过调节MySQL参数来优化它的性能。MySQL配置文件的默认路径为“/etc/my.cnf”。本文我们主要介绍MySQL InnoDB存储引擎的内存、并发控制、IO相关参数如何进行调整，以达到性能最优，如表3-2所示。
表3-2 MySQL数据库参数调优
参数类型 | 参数名称 | 参数含义 | 优化建议 |
|---|---|---|---|
内存参数 |
innodb_buffer_pool_size |
该参数指定了InnoDB缓冲池的大小。InnoDB缓冲池是一个内存区域，用于存储InnoDB表的数据和索引。 |
增加该参数，可使更多数据Cache在内存，减少IO操作。 |
innodb_buffer_pool_instances |
该参数用于控制InnoDB缓冲池的实例数量。 |
增加缓冲池实例数可以减少内存热点页面的争用，从而提高内存读取的并发性。 |
|
innodb_log_buffer_size |
该参数用于控制InnoDB事务日志缓冲池的大小。 |
（1）如果该参数设置得太小，InnoDB事务日志缓冲池可能会频繁地刷新到磁盘，导致性能下降。 |
|
并发控制参数 |
innodb_thread_concurrency |
该参数用于控制InnoDB存储引擎在执行查询时使用的并发线程数。 |
高并发场景，可调整该参数，减少InnoDB层的资源争用。 |
innodb_spin_wait_delay |
该参数用于控制spin_lock轮询的时间间隔。 |
可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。 |
|
innodb_sync_spin_loops |
该参数用于控制spin_lock轮询的循环次数。 |
可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。 |
|
innodb_spin_wait_pause_multiplier |
该参数用于控制spin_lock轮询间隔随机数。 |
可通过热点函数来对该参数进行优化，尤其在整机单实例场景下，建议将对参数优化，小规格单实例场景默认值即可。 |
|
IO参数 |
innodb_io_capacity |
该参数定义了InnoDB后台线程每秒可用的IO操作数，即IOPS。 |
建议设置为磁盘IOPS总能力的75%（磁盘IOPS是指innodb_page_size大小的数据块时的IOPS）。 |
innodb_io_capacity_max |
该参数定义了InnoDB后台线程每秒可用的最大IO操作数。 |
建议设置为磁盘IOPS总能力的75%（磁盘IOPS是指innodb_page_size大小的数据块时的IOPS），以避免IO操作对系统资源的过度占用。 |
|
innodb_flush_method |
该参数用于指定InnoDB存储引擎刷新数据到磁盘的方式。 |
不同的刷新方式会影响到InnoDB存储引擎的性能和数据的安全性。 |