MySQL优化--OS和硬件

你的名字 2021-03-18 15:10 603阅读 0赞

背景：  
MySQL优化是永恒的主题，DBA的存在意义有很大一部分原因是因为要优化MySQL。我们应该从各个层面去优化数据库。本节从操作系统和硬件的角度去做出优化。  
硬件层相关优化：  
一 . CPU相关：  
在服务器的BIOS设置中，可调整下面的几个配置，目的是发挥CPU最大性能，或者避免经典的NUMA问题：  
1、**选择Performance Per Watt Optimized(DAPC)模式，**发挥CPU最大性能，跑DB这种通常需要高运算量的服务就不要考虑节电了； 那么什么是DAPC模式呢？还有哪些模式呢？   
——  linux 会通过BIOS对硬件配置许多选项供用户选择，比如一些选项参数具有节能作用。但在降低能耗的同时也意味着性能的下降。所以根据需求选择合适的对BIOS的调改可以提高服务器的性能。在CPU方面典型的有HT（超线程）技术，以及Turbo boost（根据系统负载智能调控CPU主频）。其实对于跑MySQL的机器来说不介意开启。   
下面给出dell的白皮书一个关于BIOS选项参数设置的pdf下载链接供查阅,你想要的全都有：｀  
[http://www.google.com.hk/url?url=http://en.community.dell.com/cfs-file/\_\_key/telligent-evolution-components-attachments/13-4491-00-00-20-24-87-40/12g\_5F00\_bios\_5F00\_tuning\_5F00\_for\_5F00\_performance\_5F00\_power.pdf&rct=j&frm=1&q=&esrc=s&sa=U&ved=0ahUKEwizlfTBy6HLAhXHVZQKHbywBR4QFggeMAI&usg=AFQjCNFjgO7HsuMmOBahht-4g3wI6vZyYw][http_www.google.com.hk_url_url_http_en.community.dell.com_cfs-file_key_telligent-evolution-components-attachments_13-4491-00-00-20-24-87-40_12g_5F00_bios_5F00_tuning_5F00_for_5F00_performance_5F00_power.pdf_rct_j_frm_1_q_esrc_s_sa_U_ved_0ahUKEwizlfTBy6HLAhXHVZQKHbywBR4QFggeMAI_usg_AFQjCNFjgO7HsuMmOBahht-4g3wI6vZyYw]  
里面涉及到不少 BIOS 层面的东西。主要分为 "System Profile Setting" 以及 "Memory setting" 和 "Processor Setting" 这三大块。 对于 DELL 的 BIOS 来说，一共有 5 类不同的 profile setting。默认是 "performance per watt Optimized(dapc)"，这种profile相比其他来说比较耗能，但是对于性能多少可能有一丢丢的益处。

**2、关闭C1E和C States等选项，目的也是为了提升CPU效率；**  
这两个参数是节能选项，开启以后会对某些profile的能耗具有明显的节约例如performance，总之用来环保还是不错的，用来跑数据库的话建议关掉。  
**3、Memory Frequency（内存频率）选择Maximum Performance（最佳性能）；**  
这个选项控制BIOS内存频率。管理的最大内存频率的变量包括最大额定频率的内存，每通道的内存数量，处理器的选择等。在需要额外的功率节省的情况下，可以通过此参数节省内存频率以节省电力。  
**4、内存设置菜单中，启用Node Interleaving，避免NUMA问题；**  
那么首先要明白什么是NUMA，请参考下面这篇文章： [http://cenalulu.github.io/linux/numa/][http_cenalulu.github.io_linux_numa]   ， 这个参数是专门为了控制NUMA而设置的，具体可以参考上面的pdf，具体我也不是很懂。

二 . 磁盘I/O相关  
**1、使用SSD或者PCIe SSD设备，至少获得数百倍甚至万倍的IOPS提升**；  
这个说法耳朵都快听出茧了，具体的过程和结果网上有很多，其实这个只有自己实际经历过了才明白SSD的好处。  
**2、购置阵列卡同时配备CACHE及BBU模块，可明显提升IOPS（主要是指机械盘，SSD或PCIe SSD除外。同时需要定期检查CACHE及BBU模块的健康状况，确保意外时不至于丢失数据）；**  
****无 论DELL/HP/IBM等服务器厂商，都会OEM一些Raid控制器在实现Raid功能，而为了保障和提升读写性能，Raid控制卡里都会内置 128MB 至 1GB不等的Cache Memory，而我们对磁盘的读和写操作都会通过事先在Cache Memory中Hit或缓存，这样一来就可以大大提高了实际IO性能.    而BBU就是Raid卡中的一个电池备用模块，因为之前我们说到在Raid的环境下很多情况下数据都是通过Cache Memory和磁盘交换的，而Memory本身并无法保障数据持久性，万一电源中断，而数据没来得及flush到物理磁盘上，就会造成数据丢失的悲剧。为 此硬件厂商提供了BBU和TBBU，其中包含了一块锂电池来保障万一电源中断的情况下，Cache Memory中的数据不至于丢失，直至电源恢復。  
**3、有阵列卡时，设置阵列写策略为WB，甚至FORCE WB（若有双电保护，或对数据安全性要求不是特别高的话），严禁使用WT策略。并且闭阵列预读策略，基本上是鸡肋，用处不大；**  
****WT 即 Write Through和 WB 即 Write Back 。  
Write Through和Write Back是阵列卡Cache的两种使用方式，也称为透写和回写。当选用write through方式时，系统的写磁盘操作并不利用阵列卡的Cache，而是直接与磁盘进行数据的交互。而write Back方式则利用阵列Cache作为系统与磁盘间的二传手，系统先将数据交给Cache，然后再由Cache将数据传给磁盘。  
**4、尽可能选用RAID-10，而非RAID-5；**  
在其他配置相同的情况下，RAID10比RAID5的innodb的性能稍微高那么一丢丢。那么问题来了，RAID5和RAID10的区别是什么呢？自己看去，哪那么多问题！  
[http://arstechnica.com/features/2000/05/raid][http_arstechnica.com_features_2000_05_raid]。  
**5、使用机械盘的话，尽可能选择高转速的，例如选用15KRPM，而不是7.2KRPM的盘，不差几个钱的**；  
  
系统层相关优化：  
一. 文件系统相关  
1、在使用SSD的前提下，使用deadline/noop这两种I/O调度器，千万别用cfq（它不适合跑DB类服务）；  
Linux内核2.6开始引入了全新的IO调度子系统。Linux内核提供了CFQ(默认), deadline和noop三种IO调度器。  
CFQ实现了一种QoS的IO调度算法。该算法为每一个进程分配一个时间窗口，在该时间窗口内，允许进程发出IO请求。通过时间窗口在不同进程间的移动，保证了对于所有进程而言都有公平的发出IO请求的机会。同时CFQ也实现了进程的优先级控制，可保证高优先级进程可以获得更长的时间窗口。CFQ适用于系统中存在多任务I/O请求的情况，通过在多进程中轮换，保证了系统I/O请求整体的低延迟。但是，对于只有少数进程存在大量密集的I/O请求的情况，会出现明显的I/O性能下降。  
NOOP调度器十分简单，其只拥有一个等待队列，每当来一个新的请求，仅仅是按先来先处理的思路将请求插入到等待队列的尾部。其应用环境主要有以下两种：一是物理设备中包含了自己的I/O调度程序，比如SCSI的TCQ；二是寻道时间可以忽略不计的设备，比如SSD等。  
 DEADLINE调度算法主要针对I/O请求的延时而设计，每个I/O请求都被附加一个最后执行期限。该算法维护两类队列，一是按照扇区排序的读写请求队列；二是按照过期时间排序的读写请求队列。如果当前没有I/O请求过期，则会按照扇区顺序执行I/O请求；如果发现过期的I/O请求，则会处理按照过期时间排序的队列，直到所有过期请求都被发射为止。在处理请求时，该算法会优先考虑读请求。当系统中存在的I/O请求进程数量比较少时，与CFQ算法相比，DEADLINE算法可以提供较高的I/O吞吐率。  
   
**2、使用xfs文件系统，千万别用ext3；ext4勉强可用，但业务量很大的话，则一定要用xfs**；  
在其他配置相同的情况下，XFS具有明显优势，具体测试结果可参照，其实很多时候只有自己真正用了才能体会到区别：[http://imysql.cn/2015/07/14/xfs-vs-ext4-fileio-and-tpmc-under-pciessd.shtml][http_imysql.cn_2015_07_14_xfs-vs-ext4-fileio-and-tpmc-under-pciessd.shtml]  
**3、文件系统mount参数中增加：noatime, nodiratime, nobarrier几个选项（nobarrier是xfs文件系统特有的）**；  
Linux 下面挂载文件系统的时候设置 noatime 可以显著提高文件系统的性能。默认情况下，Linux ext2/ext3 文件系统在文件被访问、创建、修改等的时候记录下了文件的一些时间戳，比如：文件创建时间、最近一次修改时间和最近一次访问时间。因为系统运行的时候要访 问大量文件，如果能减少一些动作（比如减少时间戳的记录次数等）将会显著提高磁盘 IO 的效率、提升文件系统的性能。Linux 提供了 noatime 这个参数来禁止记录最近一次访问时间戳。  
二、 内核参数相关：  
**1. 将vm.swappiness设置为5-10左右即可，甚至设置为0（RHEL 7以上则慎重设置为0，除非你允许OOM kill发生），以降低使用SWAP的机会**；  
swappiness的值的大小对如何使用swap分区是有着很大的联系的。swappiness=0的时候表示最大限度使用物理内存，然后才是 swap空间，swappiness＝100的时候表示积极的使用swap分区，并且把内存上的数据及时的搬运到swap空间里面。linux的基本默认 设置为60。也就是说，你的内存在使用到100-60=40%的时候，就开始出现有交换分区的使用。大家知道，内存的速度会比磁盘快很多，这样子会加大系统io，同时造的成大量页的换进换出，严重影响系统的性能，所以我们在操作系统层面，要尽可能使用内存，对该参数进行调整。  
使用cat /proc/sys/vm/swappiness命令可以查看此系统参数值  
**2、将vm.dirty\_background\_ratio设置为5-10，将vm.dirty\_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待（和MySQL中的innodb\_max\_dirty\_pages\_pct类似）**；  
**vm.dirty\_background\_ratio:**  
这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如5%）就会触发pdflush/flush/kdmflush等后台回写进程运行，将一定缓存的脏页异步地刷入外存；

**vm.dirty\_ratio:**  
而这个参数则指定了当文件系统缓存脏页数量达到系统内存百分之多少

时（如10%），系统不得不开始处理缓存脏页（因为此时脏页数量已经比较多，为了避免数据丢失需要将一定脏页刷入外存）；在此过程中很多应用进程可能会因为系统转而处理文件IO而阻塞。

之前一直 错误的以为dirty\_ratio的触发条件不可能达到，因为每次肯定会先达到vm.dirty\_background\_ratio的条件，后来才知道自 己理解错了。确实是先达到vm.dirty\_background\_ratio的条件然后触发flush进程进行异步的回写操作，但是这一过程中应用进程 仍然可以进行写操作，如果多个应用进程写入的量大于flush进程刷出的量那自然会达到vm.dirty\_ratio这个参数所设定的坎，此时操作系统会 转入同步地处理脏页的过程，阻塞应用进程。

也就是说，  
dirty\_ratio是属于强制性的回写,也就是说当一个内存区的脏页达到这个比例时就会触发内核内存管理把脏页强制回写的流程,但dirty\_background\_ratio是属于软性的行为,因为这是透过pdflush内核线程进行的流程,可以在后台执行对这些脏页面回写,并不会因此影响到当下正在执行中的过程。 所以看Linux内核中预设的比例是脏页达到5%的比例时就会先透过pdflush内核线程进行回写,当脏页达到10%比例时,就等于是一个很严重的状况,此时就会在平衡脏页面流程中触发强制的回写,让系统可以回复到原本预设合理的状态。

可以使用  
cat /proc/sys/vm/dirty\_ratio 的方式来查看此值  
具体其他的vm参数可以参考此文：[http://www.cnblogs.com/yuzhaoxin/p/4083612.html][http_www.cnblogs.com_yuzhaoxin_p_4083612.html]  
**3. 将net.ipv4.tcp\_tw\_recycle、net.ipv4.tcp\_tw\_reuse都设置为1，减少TIME\_WAIT，提高TCP效率**；

net.ipv4.tcp\_tw\_recycle = 1  
表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。否则的话linux对处于TIME-WAIT状态的socket连接会保持一段时间。这样的话，当有DDOS等攻击时，发送大量伪造的TCP连接请求，常用假冒的IP或IP号段发来海量的请求连接的第一个握手包（SYN包），被攻击服务器回应第二个握手包（SYN+ACK 包），因为对方是假冒IP，对方永远收不到包且不会回应第三个握手包。导致被攻击服务器保持大量SYN\_RECV状态的“半连接”，并且会重试默认5次回 应第二个握手包，塞满TCP等待连接队列，资源耗尽（CPU满负荷或内存不足），让正常的业务请求连接不进来。  
net.ipv4.tcp\_tw\_reuse = 1  
表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；

[http_www.google.com.hk_url_url_http_en.community.dell.com_cfs-file_key_telligent-evolution-components-attachments_13-4491-00-00-20-24-87-40_12g_5F00_bios_5F00_tuning_5F00_for_5F00_performance_5F00_power.pdf_rct_j_frm_1_q_esrc_s_sa_U_ved_0ahUKEwizlfTBy6HLAhXHVZQKHbywBR4QFggeMAI_usg_AFQjCNFjgO7HsuMmOBahht-4g3wI6vZyYw]: http://www.google.com.hk/url?url=http://en.community.dell.com/cfs-file/__key/telligent-evolution-components-attachments/13-4491-00-00-20-24-87-40/12g_5F00_bios_5F00_tuning_5F00_for_5F00_performance_5F00_power.pdf&rct=j&frm=1&q=&esrc=s&sa=U&ved=0ahUKEwizlfTBy6HLAhXHVZQKHbywBR4QFggeMAI&usg=AFQjCNFjgO7HsuMmOBahht-4g3wI6vZyYw http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy6ZSjXNmlw56 http://photo.blog.sina.com.cn/showpic.html#blogid=&url=http://album.sina.com.cn/pic/003yQUOezy6ZSlhUsov0f
[http_cenalulu.github.io_linux_numa]: http://cenalulu.github.io/linux/numa/
[http_arstechnica.com_features_2000_05_raid]: http://arstechnica.com/features/2000/05/raid
[http_imysql.cn_2015_07_14_xfs-vs-ext4-fileio-and-tpmc-under-pciessd.shtml]: http://imysql.cn/2015/07/14/xfs-vs-ext4-fileio-and-tpmc-under-pciessd.shtml