热门搜索:
您现在的位置是：首页 > 文章详情

MySQL并不孤单的存在—硬件环境的限制与优化

日期：2020-07-20点击：502
    之所以写这篇文章也是因为前几天出的一个问题，当时业务感觉到卡顿，并且伴随着锁超时的报错。最后通过分析发现是由于磁盘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:24 up 214 days, 16:30, 28 users,  load average: 0.61, 0.44, 0.27 Tasks: 2941 total,   1 running, 2927 sleeping,  12 stopped,   1 zombie 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:  16335708k total, 16109152k used,   226556k free,   486544k buffers Swap: 16777212k total,  1325068k used, 15452144k free,  4629420k cached   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND  17468 efs       20   0 20.1g 7.4g  17m S 45.8 47.6   0:16.73 java  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 ~]# vmstat 2 3     #每隔2秒采集一次，一共采集三次 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st  0  0     0  99764  83536 375416   0    0    21    3   30   71  0  0 99  1  0  0  0     0  99732  83536 375416   0    0    0     0   31   65  0  0 100  0  0 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 -x 5 Linux 2.6.32-696.el6.x86_64 (node1) 07/13/2020 _x86_64_(1 CPU) avg-cpu:  %user   %nice %system %iowait  %steal   %idle            3.25    0.00    7.27   26.45    0.00   63.03 Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util sda              77.54    40.37   48.03    7.82  3332.45   385.45    66.57     4.04   72.38    9.07  461.07   5.61  31.32 scd0              0.38     0.00    0.38    0.00     3.05     0.00     8.00     0.00    0.56    0.56    0.00   0.56   0.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
#新增以下内容(注意必须有空格) 
 
 * soft nofile 65535 * hard nofile 65535 * soft nproc 65535 * hard nproc 65535
vi /etc/security/limits.d/20-nproc.conf
#新增以下内容(注意必须有空格) 
 
 * soft nproc 65535 * hard nproc 65535


为了方便大家交流，我建了一个微信群，大家可以在群里聊技术、聊理想、聊生活，欢迎加入！


本文分享自微信公众号 - MySQL数据库技术栈（Mysqltechnology）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

原文链接：https://my.oschina.net/u/3678773/blog/4411932
关注公众号

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。

持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

转载内容版权归作者及来源网站所有，本站原创内容转载请注明来源。

相关文章

    文章评论

    共有0条评论来说两句吧...

    文章二维码

    扫描即可查看该文章

    点击排行

    推荐阅读

    最新文章