之所以写这篇文章也是因为前几天出的一个问题,当时业务感觉到卡顿,并且伴随着锁超时的报错。最后通过分析发现是由于磁盘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=僵