MySQL性能优化：CPU、内存与磁盘I/O的影响-CSDN博客

本文链接：https://blog.csdn.net/javaJasonjava/article/details/135186238

什么限制了Mysql的性能

许多不同的硬件都会影响Mysql的性能，最常见的两个瓶颈是CPU和IO资源。当数据可以放在内存中或者可以从磁盘中以足够快的速度读取时，CPU可能出现瓶颈。IO瓶颈一般发生在所需要的数据远远超过有效内存容量的时候。如果应用程序是分布在网络上，或者如果有大量的查询和低延迟的要求，瓶颈可能转移到网络上，而不再是磁盘IO。

如何为Mysql选择CPU

哪个更好：更快的CPU还是更多的CPU

当遇到CPU密集型的工作时，Mysql通常可以从更快的CPU中获益。但这并不是绝对的，还要依赖于负载情况和CPU数量。
一般来说两个都重要。从广义上来说调优服务器可能有以下两个目标：

  1. 低延时：要做到这一点需要高速CPU，因为每个查询只能使用一个CPU。
  2. 高吞吐：如果能同时运行很多查询语句，则可以从多个CPU处理查询中受益。

如果有多路CPU，并且没有并发执行查询语句，Mysql依然可以利用额外的CPU为后台任务（清理InnoDB缓存、网络操作等等）服务。
如果有一个CPU密集型的工作负载，考虑是需要更快的CPU还是更多的CPU的一个因素是查询语句实际在做什么。在硬件层面，一个查询可以在执行或等待。处于等待状态常见的原因是在运行队列中等待、等待锁、等待磁盘或网络。如果是等待锁通常需要更快的CPU；如果在运行队列中等待，那么更多或更快的CPU都有帮助。

平衡内存和磁盘资源

配置大量内存最大的原因其实不是因为可以在内存中保存大量数据：最终目的是避免磁盘IO，因为磁盘IO比在内存中访问数据要慢的多。关键是要平衡内存和磁盘的大小、速度、成本和其他因素。以便为工作负载提供高性能的表现。

随机IO和顺序IO

数据库服务器同时使用顺序和随机IO。随机IO从缓存中受益最多。
顺序读取不能从缓存中受益的一个原因是他们比随机读快，还有以下两个原因：

  1. 顺序IO比随机IO快：顺序操作的执行速度比随机操作快，无论是在内存还是磁盘上。
  2. 存储引擎执行顺序读比随机读快：一个随机读意味着存储引擎必须执行索引操作。通常需要通过B树的数据结构查找，并且和其他值比较。相反，连续读取一般需要遍历一个简单的数据结构，例如链表这样就少了很多工作。

缓存、读和写

如果有足够的内存，就完全可以避免磁盘读取请求。如果所有的数据文件都可以放在内存中，一旦服务器缓存“热”起来，所有的读操作都会在缓存中命中。虽然还是会有逻辑读取，不过物理读取就没有了。但写入是不一样的，写入可以像读一样在内存中完成，但迟早要被写入到磁盘，所以他是需要持久化的。
除了允许写入被延迟，缓存可以允许他们被集中处理，主要通过以下两个途径：

  1. 多次写入，一次刷新：一片数据可以在内存中改变多次，而不需要所有的新值写到磁盘。当数据最终被刷新到磁盘，最后一次物理写之前发生的修改都会被持久化。
  2. IO合并：许多不同部分的数据可以在内存中修改，并且这些修改可以合并在一起，通过一次磁盘操作完成物理写入。

线程

Mysql每个连接使用一个线程，另外还有内部处理线程、特殊用途的线程，以及所有存储引擎创建的线程。无论哪种方式，Mysql都需要大量的线程才能有效地工作。Mysql确实需要内核级线程的支持，而不只是用户级线程，这样才能更有效地使用多个CPU。

内存交换区

当操作系统没有足够的内存而将一些虚拟内存写到磁盘就会发生内存交换。
内存交换对Mysql性能影响是很糟糕的。他破坏了缓存在内存的目的，并且相对于使用很小的内存做缓存，使用交换区的性能更差。
极端场景下，太多的内存交换可能导致操作系统交换空间溢出。即使交换空间没有溢出，频繁的内存交换也会导致整个操作系统无法响应。

操作系统状态

如何阅读vmstat的输出

[root@VM-4-11-centos ~]# vmstat 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 114072 140840 3274972    0    0     0    37    1    1  1  1 98  0  0
 0  0      0 109560 140840 3274988    0    0     0   266  752 1270  1  1 98  1  0
 0  0      0 113756 140840 3275016    0    0     0    14  745 1283  1  1 98  0  0
 0  0      0 112880 140840 3275016    0    0     0    99  685 1212  0  0 97  2  0
 0  0      0 111552 140840 3275036    0    0     0    87  631 1084  1  1 99  0  0

下面我们看 vmstat 输出的一个例子：

procs

r 这一列显示了多少进程正在等待CPU，b 列显示多少进程正在不可中断地休眠。

memory

swpd 列显示多少块被换出到了磁盘。剩下的三个列显示了多少块是空闲的（free），多少块正在被用作缓冲（buff），多少块正在被用作操作系统缓存（cache）。

swap

这些列显示页面交换活动，每秒有多少块正在被换入和换出。他们比监控 swpd 列重要的多。

io

这些列显示有多少块从块设备读取和写出。通常反映了硬盘IO。

system

这些列显示了每秒中断和上下文切换的数量。

cpu

这里列显示所有的cpu时间花费在各类操作的百分比。

如何阅读iostat的输出

[root@VM-4-11-centos ~]# iostat -dx 5
Linux 3.10.0-1160.88.1.el7.x86_64 (VM-4-11-centos) 	12/24/2023 	_x86_64_	(2 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     8.29    0.00    8.76     0.06    74.17    16.95     0.03    3.29    5.66    3.29   1.27   1.11
scd0              0.00     0.00    0.00    0.00     0.02     0.00   102.84     0.00    0.54    0.54    0.00   0.31   0.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00    13.80    0.00    8.40     0.00    92.00    21.90     0.01    1.19    0.00    1.19   1.26   1.06
scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
vda               0.00     6.20    0.00   27.40     0.00   147.20    10.74     0.10    3.61    0.00    3.61   0.18   0.50
scd0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00