MySQL并不孤单的存在—硬件环境的限制与优化
之所以写这篇文章也是因为前几天出的一个问题，当时业务感觉到卡顿，并且伴随着锁超时的报错。最后通过分析发现是由于磁盘I/Q繁忙导致SQL耗时增加，部分锁竞争激烈的热数据出现了锁等待和锁超时。由此可见，系统的硬件环境对数据库整体性能的影响也是非常大的，MySQL在运行环境中并不是孤立存在的，它的整体性能往往受限于系统最薄弱的环节，今天想和大家分享下，都有哪些系统指标会对数据库的整体性能产生影响，我们又如何进行分析。 CPU 在2000年前后，博客盛行的时代，OLTP（Online Transaction Processing）型的数据库对CPU的要求并不高，当时的业务并发量较低，也很少有排序、分组、连接等非常耗CPU的操作，随着互联网业务的高速发展，双11、618以及关键节日会有一些抢购、秒杀活动，这时候业务访问密集，并发ni需求大，并发执行的SQL很多，这时候对CPU的要求相应也提高了，就需要核数更多的CPU。MySQL可以通过innodb_thread_concurrency来限制并发线程的数量，保护系统不被hang住，一般是cpu核数的4倍。 根据多年的DBA经验，如果你的CPU突然之间升高，多半是因为数据量增大到一定层度，数据在内存中的排序、分组、join等动作消耗CPU增高导致，这时烂SQL就会浮出水面了，可以通过以下方法精确定位是哪个SQL引起。 1.首先是常用的top命令 （简单粗暴最有效），它可以对进程和线程进行实时监控。 top-14:13:24up214days,16:30,28users,loadaverage:0.61,0.44,0.27 Tasks:2941total,1running,2927sleeping,12stopped,1zombie Cpu(s):4.6%us,4.2%sy,0.0%ni,90.3%id,0.7%wa,0.0%hi,0.1%si,0.0%st Mem:16335708ktotal,16109152kused,226556kfree,486544kbuffers Swap:16777212ktotal,1325068kused,15452144kfree,4629420kcached PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND 17468efs20020.1g7.4g17mS45.847.60:16.73java 18281 mysql 20 0 5029m 442m 7148 S 3.9 2.8 0:48.18 mysqld 第一行显示了当前时间，系统运行的时间，已登录的用户数量，以及系统的平均负载。平均负载分别包含1分钟、5分钟、15分钟的平均负载。 第二行显示了进程信息，有多长进程正在运行、睡眠、已停止和僵死。 第三行就是CPU信息了， 4.6%us-表示用户空间占用CPU的比例。 4.2%sy-内核空间占用CPU的比例。 90.3%id-空闲CPU的比例。 0.7%wa-IO等待占用CPU的比例。 0.0%hi-硬中断占用CPU的比例。 0.1%si-软中断占用CPU的比例 第四行和第五行是内存和swap的总量及使用情况。 最后，下一部分显示当前正在运行的进程的详细列表。 PID：进程IP USER:进程所有者的用户名 PR:进程的优先级 VIRT：进程所使用的虚拟内存总量 RES：进程当前使用的物理内存量 SHR：进程与其他进程共享的内存量 S：进程状态（D=可中断睡眠，R=进行中，S=睡眠，T=已跟踪或已停止，Z=僵死） %CPU：进程正在使用的CPU时间份额 %MEM：进程正在使用的可用物理内存份额 TIME+：进程启动后已使用的总CPU时间 COMMAND：进程的命令行名称 2.输入H， 可以按照显示线程状态。 3.输入P， 可以按照cpu的使用时间份额进行排序，这时候我们就可以看下是否有超过70%-90%以上的线程了。 4.登录mysql，执行以下命令 select * from performance_schema.threads where THREAD_OS_ID=4461 \G 内存 正确的分配和使用内存对MySQL来说至关重要，数据的修改、缓存、排序、分组以及内部管理等动作大部分都是在内存中完成的，内存的大小最能直接反应数据库的性能。下面我们一起来看下MySQL数据库服务器上都有哪些需要内存的地方。 InnoDB缓冲池（Buffer Pool） 数据库再启动的时候，会划出很大一块内存区，作为数据缓冲区，用来缓存InnoDB表的数据、索引、插入缓存、数据字典等信息，这就是innodb_buffer_pool，InnoDB严重依赖于缓冲池，但是也不是说bufferpool越大越好，因为预热和关闭都会花费很长时间，还是要根据数据大小来设定，一般设置为物理内存的50%-80%。 连接需要的内存 MySQL会为每个连接都分配一些内存，主要有sort_buffer（排序）、join_buffer（表连接）、read_buffer（表顺序扫描的缓存）、read_rnd_buffer（随机读缓冲区，用于mrr）等缓存区。 线程缓存 为了不频繁的创建、销毁线程，MySQL一般会先缓存一些线程。当有新的连接上来时，直接从线程池中分配一个线程给新的连接，当连接关闭时，如果线程池中还有空间的话会把线程放回缓存，如果没有空间的话，就会销毁该线程。缓存的线程数量由thread_cache_size参数控制。 操作系统保留内存 操作系统也需要保留足够的内存，可以通过free –m命令查看swap的使用情况来判断内存是否够用。 关于内存的使用情况可以通过vmstat命令分析，可以分析cpu使用率、内存使用情况、虚拟内存交换情况、IO读写情况。 [root@node1~]#vmstat23#每隔2秒采集一次，一共采集三次 procs-----------memory-------------swap-------io------system-------cpu----- rbswpdfreebuffcachesisobiboincsussyidwast 0009976483536375416002133071009910 0009973283536375416000031650010000 0 0 0 99732 83536 375416 0 0 0 0 29 66 0 0 100 0 0 procs（进程） r:表示运行队列中进程的数量，就是有多少个进程真的分配到cpu，当这个值超过了cpu数目，就会出现cpu瓶颈了。 b:等待IO的进程数量，即阻塞的进程数。 memory（内存） swpd: 表示虚拟内存的已使用的大小，如果大于0，表示物理内存不足了，如果不是程序内存泄漏的原因，该升级内存了。 free：空闲的物理内存大小。 buff：用作缓冲的内存大小。 cache：用作缓存的内存大小。 swap（交换区） Si：每秒从磁盘读入虚拟内存的大小，如果这个值大于 0，表示物理内存不够用或者内存泄露了，要查找消耗内存进程解决掉。 So：每秒虚拟内存写入磁盘的大小 IO bi：每秒读取的的块数。 bo：每秒写入的块数。 如果bi和bo经常不等于0，表示内存不足。 system（系统） in：每秒 cpu 的中断次数，包括时间中断。 cs：每秒上下文切换次数 CPU（百分比表示） us：用户进程占用cpu时间百分比 sy：系统进程占用cpu时间百分比，如果太高，表示系统调用时间长，例如IO操作频繁。 id：空闲cpu时间百分比，如果r经常大于4，并且id经常少于40，表示cpu的负载很重。 wa：IO等待时间百分比，过高时，说明io等待比较严重，可能是由于磁盘大量随机访问造成的，也有可能是磁盘的带宽出现瓶颈。 磁盘I/O 我们之前一直在强调MySQL的顺序读写在性能上要比随机读写高出很多，这是因为现在大多数数据库使用的是机械硬盘，在机械硬盘进行随机读写时需要来回移动磁头，这样就需要耗费长时间的磁头旋转和移动来查找。近几年出现的固态硬盘，因其低延迟、低功耗等特性得到了广泛的应用，如果你使用了固态硬盘，可以通过增加innodb_io_capacity参数来充分利用固态硬盘带来的高IOPS特性。 关于针对磁盘IO异常的排查，可以通过iostat命令进行分析。 [root@node1~]#iostat-x5 Linux2.6.32-696.el6.x86_64(node1)07/13/2020_x86_64_(1CPU) avg-cpu:%user%nice%system%iowait%steal%idle 3.250.007.2726.450.0063.03 Device:rrqm/swrqm/sr/sw/srsec/swsec/savgrq-szavgqu-szawaitr_awaitw_awaitsvctm%util sda77.5440.3748.037.823332.45385.4566.574.0472.389.07461.075.6131.32 scd00.380.000.380.003.050.008.000.000.560.560.000.560.02 scd1 0.00 0.00 0.36 0.00 2.86 0.00 8.00 0.00 1.11 1.11 0.00 1.11 0.04 关于avg-cpu一栏，主要是CPU的平均使用情况： %user：用户层面使用CPU的情况。 %system：系统层面使用CPU的情况，如果该值较高，多半是IO出了问题。因为MySQL的IO请求发出后，最终是由系统的IO进程完成，假如读写很高的话，sys就很繁忙。 %iowait：IO等待情况，iowait 很高的话表示浪费cpu资源。 %idle：cpu 空闲时间。 system和iowait一般结合着看，表示系统IO的情况，如果iowait很高的话，说明cpu花太多的时间在IO等待上，说明系统的IO成为瓶颈了，iowait一般希望小于5%，如果大于25%就说明有问题了。Idle一般希望大于25%，希望有25%的时间是空闲的。 关于Device一栏，描述了IO的具体使用情况。 rrqm/s：合并读，合并读一般为0，如果值很高的话，说明系统有大量的全表扫描。因为 数据库的特点是随机读（oltp交易系统），所以两个读被合并的概率很低，所以如果出现大量的合并读，说明系统在全盘扫描。 wrqm/s：合并写，如果系统在做批量的insert，并且是按照主键顺序递增插入的时候，该值会比较高。 r/s：每秒读。 w/s：每秒写。每秒读+每秒写=IOPS，可以看出系统是读还是写为主。 rsec/s：每秒读扇区的数量。每个扇区是 512 字节。 wsec/s：每秒写扇区的数量。rsec/s+ wsec/s就是IO的吞吐量。 avgrq-sz：平均每次IO操作的扇区数。 avgqu-sz：平均等待处理的IO请求队列长度 await：平均每次IO请求的等待时间。 svctm：每一个请求的服务时间（单位毫秒），反应了io性能，5、6ms表示io性能还可以，可以降到1ms以下。 %util：繁忙度，周期内用于IO操作的时间比例，即IO队列非空的时间比率。如果io不高，但繁忙度高，说明磁盘有问题。 网络带宽 网络在整个系统中充当了桥梁和道路的作用，所以的数据都是通过网络进行传输的。有时候业务反馈系统慢了，这时候我们就要分析到底是慢在哪了，这时候除了分析应用和数据库的日志之外，我们还可以通过tcpdump命令进行抓包分析。具体tcpdump的使用可以参考文章《tcpdump在MySQL中的应用》。 其他系统限制 1.关闭selinux vi /etc/selinux/config 把SELINUX=enforcing改成SELINUX=disabled， 修改完成之后要重启操作系统。 2.关闭防火墙 redhat6 查看防火墙状态：service iptables status 临时关闭防火墙：service iptables stop 永久关闭防火墙：chkconfig iptables off redhat7及以上 查看防火墙状态：systemctl status firewalld 临时关闭防火墙：systemctl stop firewalld 永久关闭防火墙：systemctl disable firewalld 3.关闭numa numa（Non-Uniform Memory Access）译为非一致性内存访问，在这种架构下一个CPU和一组内存构成一个node，每个CPU访问自己node下的内存。对于MySQL来说，它是单进程多线程，会造成MySQL用一颗cpu的内存不够用了，但是还不能用其他node的内存，只能去用swap空间的，所以MySQL不适合运行在NUMA结构的服务器上。 关闭NUMA的方式 redhat6 vi /etc/grub.conf 在Kernel ... rhgb quiet后加上numa=off，保存后重启. 查看是否生效,numactl --hardware只有一个节点 redhat7及以上 vi /etc/default/grub 添加numa=off 重新生成/etc/grub2.cfg : grub2-mkconfig -o /etc/grub2.cfg 重启系统:reboot 确认是否已经关闭numa： dmesg |grep -i numa 4.调整swap swap分区即交换区，是在系统的物理内存不够用的时候，把硬盘内存中的一部分空间释放出来，以供当前运行的程序使用。swap的调整对Linux服务器的性能至关重要。 我们一般通过修改swapiness的值来调整系统如何分配swap, 这个值在 0-100 之间，0 表示最大限度的使用物理内存，当物理内存耗尽才使用 swap 空间，有可能导致系统内存溢出，出现OOM的错误，从而导致MySQL被强制kill掉。100 表示积极的使用 swap 空间，linux 系统默认是 60，可以修改为10。 修改方式： vi/etc/sysctl.conf 加入vm.swappiness = 10。 #执行下面命令生效 sysctl -p /etc/sysctl.conf 5.文件句柄和进程的最大数量限制 通过ulimt –a可以查看文件的最大句柄数和进程的最大数量限制。 Linux默认的最大文件句柄数是1024，表示单个进程最多可以访问1024个文件句柄，超出文件句柄限制会报错：“too many open files”。 max user processes主要用来限制每个用户的最大processes数量，超出数量就会报错：“resource temporarily unavailable”。 主要通过修改以下两个配置文件修改。 vi /etc/security/limits.conf #新增以下内容(注意必须有空格) *softnofile65535 *hardnofile65535 *softnproc65535 * hard nproc 65535 vi /etc/security/limits.d/20-nproc.conf #新增以下内容(注意必须有空格) *softnproc65535 * hard nproc 65535 为了方便大家交流，我建了一个微信群，大家可以在群里聊技术、聊理想、聊生活，欢迎加入！ 本文分享自微信公众号 - MySQL数据库技术栈（Mysqltechnology）。如有侵权，请联系 support@oschina.cn 删除。本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
