生产环境CPU利用率超过90%告警分析
项目组反馈CPU利用率超过90%，几乎每天都有发生，具体时间不固定。发生时间可能几分钟。由于发生时间较短，无法时时查看现场，通过监控，分析DB2留给我们的历史监控信息。数据库版本V9.5。
通过管理视图抓取CPU过高的sql语句，按TOTAL_EXEC_TIME做排序，SQL如下所示。
| NUM_EXECUTIONS ROWS_READ USER_CPU STMT_TEXT -------------------- -------------------- --------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 19014452 91835858369678 15947190.87147800000 select * from ( SELECT AccessJnlNo,AccessState,AccessDate, ROW_NUMBER() OVER(order by AccessDate desc) AS ROWNUM FROM pjnlaccess WHERE (USERSEQ=?) and (ACCESSSTATE= ?) ) as A where A.ROWNUM <= ? 6786233 34190177516243 5657178.12333100000 SELECT count(*) FROM PJNLACCESS WHERE (USERSEQ=? ) and (ACCESSSTATE= ?) 1775471 1043473416401 163080.85686400000 select AccessJnlNo,AccessState,AccessDate from ( SELECT AccessJnlNo,AccessState,AccessDate, ROW_NUMBER() OVER(order by AccessDate desc) AS ROWNUM FROM ejnlaccess WHERE (USERSEQ=?) and (ACCESSSTATE= ?) ) as A where A.ROWNUM <= ? 775823 532458929788 84785.79413200000 SELECT count(*) FROM ejnlaccess WHERE (USERSEQ=?) and (ACCESSSTATE= ?) 787382 37051 38575.53311900000 SELECT distinct a.USERID from PUSER a,PACCOUNT b, PUSERACCOUNT c where (a.USERSEQ = c.USERSEQ) and (b.ACSEQ = c.ACSEQ) and (b.ACNO= ?) and a.UserState in ('0','2','9') and b.acstate = '0' 385272 52002476753 10402.26767900000 select e.ERRORCOUNT,e.LASTERRLOGINTIME from PERRORCOUNT e,PUSER u where u.USERSEQ=? and e.USERSEQ=u.USERSEQ 26202889 16745376 7692.61906000000 update PJNLQUERY set TRSCOUNT = TRSCOUNT + ? where TRANSDATE = ? and (TRANSCODE = ?) and (DEPTSEQ=?) and (UserSeq=?) and (CifSeq=?) 4676233 2832470405 3298.62605700000 select distinct a.PRDGRPID, c.PRDID from PUSERPRODUCTGROUP a,BANKPRODUCTGROUP b, PRODUCTGROUPPRODUCT c where b.PRDGRPID = c.PRDGRPID and a.PRDGRPID = b.PRDGRPID and a.BANKSEQ=b.BANKSEQ and b.PRDGRPSTATE='0' and a.USERSEQ=? 113062 15430831720 3105.11363300000 UPDATE PERRORCOUNT e SET e.ERRORCOUNT =? WHERE e.USERSEQ = ? 8608367 1059652738 2416.64943200000 select concat(concat( BANKCODE ,'/') , BANKAGENT) as BANKID,BANKNAME from IBPSRTGSNODE order by BANKCODE |
根据SQL语句截取前三个较长的时间，对应的值分别是
| TOTAL_USR_CPU_TIME TOTAL_SYS_CPU_TIME TOTAL_EXEC_TIME 983992 961229 18219111 809618 571942 6812423 180811 361096 206225 |
可以看出USR_CPU和SYS_CPU都很高，并且有的SYS_CPU还大于USR_CPU，一般情况下这非常不合理。也可以观察到USR_CPU+SYS_CPU的时间要远小于TOTAL_SYS_CPU，CPU利用率过高问题可能有两部分引起，一部分USR_CPU，另一部分是SYS_CPU。
(1) 先查看第一部分USR_CPU。
在 DB2 数据库系统监控中，经常会遇到 CPU 资源使用过高问题，造成此类问题的原因很多，但最主要的原因有两个，第一是过多的逻辑 I/O 读取，第二就是过多的排序 (Sort)，这两个因素有时也称为 CPU 的两大杀手。所谓过多的逻辑 I/O 读取，通常指发生在缓冲池中的表扫描。排序是指对某些数据按照某个 ( 或某些 ) 字段从大到小或从小到大排列的过程。如果排序字段上没有索引，或者 DB2 认为索引的开销比表扫描更大时，DB2 就会对数据进行排序。
除 Order By 之外，在以下操作中也可能引起排序：
1. 对于包含 DISTINCT,,GROUP BY, HAVING，INTERSECT，EXCEPT，UNION 等操作的 SQL 语句，如果没有索引满足所取的行的顺序要求，或者优化器认为排序的代价低于索引扫描，就需要进行排序；
2. 对于包含 Max/Min/Sum/Cube/Rollup/Rank 等聚集函数，DB2 会把语句重写为一个使用排序的嵌套子查询；
3. 对于 Reorg, Create Index 等操作，需要对表数据进行排序才能完成；
4. 对于查询计划中的 Dynamic Bitmap Index ANDing（IXAND），Hash Join（HSJOIN）等操作的生成的 Hash 表会放在 SORTHEAP 中。
通常情况下，大量的排序会对性能造成极大影响。比如，排序会导致很高的 CPU 使用时间；增加 SQL 执行时间；增加锁超时和死锁发生的机率；排序会严重消耗有限的内存空间；排序溢出会引起临时表空间的频繁 I/O 等。
SQL语句监控，如下所示。
| STMT_SORTS SORTS_PER_EXECUTION STMT_TEXT -------------------- -------------------- ---------------------------------------------------------------------------------------------------- 16227383 0 select * from ( SELECT AccessJnlNo,AccessState,AccessDate, ROW_NUMBER() OVER(order by AccessDa 11415456 1 select BusinessId, BusinessName, BusinessRange, BusinessURL URLType, Layout 10670311 0 select * from (select NoticeSeq, NoticeSubject,NoticeContent,SubmitDate, ROW_NUMBER() OVER(order b 8607960 0 select concat(concat( BANKCODE ,'/') , BANKAGENT) as BANKID,BANKNAME from IBPSRTGSNODE o 4676008 0 select distinct a.PRDGRPID, c.PRDID from PUSERPRODUCTGROUP a,BANKPRODUCTGROUP b, PRODUCTGROUPPRO 2373449 0 select count(*) from (select NoticeSeq, NoticeSubject,NoticeContent,SubmitDate, ROW_NUMBER() OVER( 2373449 1 select BusinessId, BusinessName, BusinessRange, BusinessURL, URLType, Layout 1610434 0 select * from (select NoticeSeq, NoticeSubject,NoticeContent,SubmitDate, ROW_NUMBER() OVER(order b 1421692 0 select AccessJnlNo,AccessState,AccessDate from ( SELECT AccessJnlNo,AccessState,AccessDate, ROW 787352 0 SELECT distinct a.USERID from PUSER a,PACCOUNT b, PUSERACCOUNT c where (a.U 763459 0 select distinct PRDID,PRDGRPID from EUSERPRODUCT where (USERSEQ=?) 711717 1 select a.ACNO, a.ACSEQ, a.ACNAME, a.ACALIAS, a.CURRENCY, 193984 0 select a.jnlno from pjnl a,PPAYPROJNL b,paccount c where a.jnlno=b.jnlno and a.acs 184526 2 select JNLNO, ROUTERJNLNO, TRANSDATE, TRANSTIME, TRANSCODE 158095 0 select * from ( SELECT AccessJnlNo,AccessState,AccessDate , ROW_NUMBER() OVER(order by AccessDa |
通过上面可以发现STMT_SORTS 为16227383 的SQL正是之前消耗CPU最多的SQL。
通过SNAPSHOT抓取sort，有效索引读Rows read /Rows selected的比值为34810，远大于OLTP≤5的指标，Asynchronous pool data page reads= 502184140也说明在OLTP交易型系统中存在全表扫描引起的prefeth，数据库监控如下：
| Total Private Sort heap allocated = 0 Total Shared Sort heap allocated = 37 Shared Sort heap high water mark = 3111171 Post threshold sorts (shared memory) = 17132 Total sorts = 114396882 Total sort time (ms) = 7044634 Sort overflows = 635381 Active sorts = 2
Rows deleted = 90298415 Rows inserted = 136504707 Rows updated = 58974724 Rows selected = 3829807764 Rows read = 133318821577236
Asynchronous pool data page reads = 502184140
|
根据之前按时间抓取SQL进行比较，选出SQL语句相关的表，查看是有的索引，有索引是否索引失效。可以通过db2expln或db2exfmt命令查看cost和执行情况，若没有合适的索引，通过db2advis 命令推荐索引，再次通过db2expln或db2exfmt获取执行计划对比前后cost和执行情况，结果一目了然，此问题已反馈给项目组，等待具体信息。
(2) 看第二部分SYS_CPU较高。
没有抓到当时现场信息，系统当前计算内存为67%，非计算内存为7%，CPU较低。查看bufferpool共有四个bufferpool，其中BUFF16K和BUFF4K两个没有使用，共占用352.5M空间。GJBUFPOOL的索引的HitRatio为86.93%。内存设置除GJBUFPOOL的索引的HitRatio为86.93%相对较低外，其他正常。
| Address Id Name PageSz PA-NumPgs CurrentSz PostAlter SuspndTSCt Automatic 0x07000000234FA2C0 1 IBMDEFAULTBP 4096 1923154 1923154 1923154 0 False 0x07000000234FA6E0 2 BUFF4K 4096 10000 10000 10000 0 False 0x07000000234FAB00 3 BUFF16K 16384 20000 20000 20000 0 False 0x07000001A52476C0 4 GJBUFPOOL 32768 3000 3000 3000 0 False 0x07000000234F9240 4096 IBMSYSTEMBP4K 4096 16 16 16 0 False 0x07000000234F9660 4097 IBMSYSTEMBP8K 8192 16 16 16 0 False 0x07000000234F9A80 4098 IBMSYSTEMBP16K 16384 16 16 16 0 False 0x07000000234F9EA0 4099 IBMSYSTEMBP32K 32768 16 16 16 0 False
Bufferpool Statistics for all bufferpools (when BUFFERPOOL monitor switch is ON):
BPID DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio 1 2204837145*499052054 99.98% 49574242 0 100.00% 1928013753*51817715 99.73% 0 0 00.00% 2 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00% 3 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00% 4 565939920 12164040 97.85% 0 0 00.00% 64517582 8433214 86.93% 0 0 00.00% 4096 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00% 4097 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00% 4098 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00% 4099 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00% |
一般系统上下文切换过高、数据库连接数过大和系统级别内存不足都有可能导致SYS_CPU较高。查看表空间GJDATA和GJIDX表空间对应的bufferpoolid为4，该文件系统有大量读取或写入，类型为文件系统，发现文件系统缓存为打开状态。
表空间信息如下所示：
| Tablespace Configuration: Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg Name 0x0700000188BE9060 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 SYSCATSPACE 0x0700000188BE98E0 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 TEMPSPACE1 0x0700000188BEE160 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 USERSPACE1 0x0700000188BEE9E0 3 DMS Large 4096 4 Yes 4 1 1 Off 1 0 3 SYSTOOLSPACE 0x0700000188BEF260 4 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 IBSDATA 0x070000019C4C0080 5 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 IBSINDEX 0x070000019C4C0720 6 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 IBSLONGDATA 0x070000019C4C0DC0 7 SMS UsrTmp 4096 4 Yes 4 1 1 On 1 0 3 SYSTOOLSTMPSPACE 0x07000001A4C302A0 8 DMS Large 32768 32 Yes 32 4 4 On 1 0 31 GJDATA 0x07000001A4C2DDA0 9 DMS Large 32768 32 Yes 32 4 4 On 1 0 31 GJIDX
Tablespace Statistics: Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM State MinRecTime NQuiescers 0x0700000188BE9060 0 24576 24572 19600 0 4972 19600 0x00000000 0 0 0x0700000188BE98E0 1 1 1 1 0 0 0 0x00000000 0 0 0x0700000188BEE160 2 8192 8160 96 0 8064 96 0x00000000 0 0 0x0700000188BEE9E0 3 8192 8188 196 0 7992 196 0x00000000 1293520419 0 0x0700000188BEF260 4 1335296 1335264 1327424 0 7840 1327424 0x00000000 1385229532 0 0x070000019C4C0080 5 638976 638944 638496 0 448 638496 0x00000000 1381601683 0 0x070000019C4C0720 6 6225920 6225888 6222752 0 3136 6222752 0x00000000 0 0 0x070000019C4C0DC0 7 1 1 1 0 0 0 0x00000000 0 0 0x07000001A4C302A0 8 65536 65504 27360 0 38144 27360 0x00000000 1388088069 0 0x07000001A4C2DDA0 9 12800 12768 9792 0 2976 9792 0x00000000 1346376714 0 Containers: Address TspId ContainNum Type TotalPgs UseablePgs StripeSet Container 0x0700000188BE9760 0 0 File 24576 24572 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000000/C0000000.CAT 0x0700000188BE9F80 1 0 Path 1 1 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000001/C0000000.TMP 0x0700000188BEE860 2 0 File 8192 8160 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000002/C0000000.LRG 0x0700000188BEF0E0 3 0 File 8192 8188 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000003/C0000000.LRG 0x0700000188BEF960 4 0 File 1335296 1335264 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000004/C0000000.LRG 0x0700000188BEFB40 5 0 File 638976 638944 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000005/C0000000.LRG 0x0700000188BEFD20 6 0 File 6225920 6225888 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000006/C0000000.LRG 0x070000019C4C1460 7 0 Path 1 1 0 /ebdbdata/uibsDB/db2inst1/NODE0000/UIBS/T0000007/C0000000.UTM 0x0700000025377220 8 0 File 65536 65504 0 /ebdbdata/uibsDB/GJDATA32K 0x0700000025370520 9 0 File 12800 12768 0 /ebdbdata/uibsDB/GJIDX32K |
缓存 I/O
当应用程序发出一个访问磁盘中的数据的请求时，操作系统可通过两种方式处理该请求：一种是直接从磁盘检索该数据，另一种是利用一个缓存区域，以便保留经常访问的数据以供快速访问。对每个请求执行物理 I/O 会影响性能，而在内存中拥有一个缓存经常访问的数据的缓冲区可改进数据检索过程。
文件缓冲区缓存的用途是通过将经常访问的数据放在主要内存中，最小化物理磁盘 I/O 的频率。结果是，在缓存命中比率很高时，文件缓冲区缓存的使用可非常有效地减少磁盘 I/O。这种数据检索方法常常称为缓存 I/O。当应用程序向操作系统发出一个读取请求时，操作系统首先在文件缓冲区缓存中查找需要的数据。如果在这里找到该数据，就会从缓存获取并写入应用程序缓冲区，无需进入磁盘。然而，如果请求的数据未在文件系统缓存中，操作系统必须从磁盘获取数据，并将它存储在文件系统缓存中，之后再将它传输到应用程序缓冲区。
并发 I/O
AIX 平台上的 JFS2 文件系统中引入的两项功能：直接 I/O (DIO) 和并发 I/O (CIO)。x86、x64 和 POWER 架构上的 Linux 发行版 SLES 9 及更高版本和 RHEL 4 及更高版本同时提供了对 DIO 和 CIO 的支持。直接 I/O 通过直接将数据从磁盘复制到合适的应用程序缓冲区，消除了与缓存 I/O 关联的双重缓冲。但是，直接 I/O 执行了写入序列化和 Inode 锁定来维护数据完整性。
另一方面，并发 I/O 提供了对数据的直接访问能力，无需这种额外的缓冲和锁定。这进而允许文件系统提供通常与原始设备关联的优势和性能益处。顺便说一下，通过消除缓冲和锁定获得性能提升，这是为什么这些设备难以管理，却常常用在文件系统上作为数据存储的首选原始设备的主要原因之一。随着并发 I/O 的引入，文件系统有可能会在原始设备中实现更快的性能和更加简便的管理。
正如前面所提及的，Inode 锁支持在文件级别执行写入序列化，以在使用缓存 I/O 和直接 I/O 的情况下维护数据的一致性。并发 I/O 绕过了这种 Inode 锁，允许多个线程同时对同一个文件执行读取和写入操作。
借助 DB2，也可以通过 CREATE TABLESPACE 和 ALTER TABLESPACE SQL 语句的 NO FILESYSTEM CACHING 选项在数据库级别上实现 CIO 。当使用这些选项时，DB2 Database Manager 为识别的表空间发出适当的 O-CIO 调用。
在 DB2 9.1 中引入此选项之前，启用 CIO 的惟一方式是使用 CIO 选项装载文件系统。而且当以这种方式在基本文件系统级别上实现 CIO 时，该文件系统上的每个文件将得使用CIO 打开。
但是，使用 CREATE TABLESPACE 和 ALTER TABLESPACE 语句的 NO FILESYSTEM CACHING 选项允许DB2 Database Manager 确定哪些文件要使用 CIO 打开。因为 CIO 消除了双重缓冲和不必要的文件锁定，所以具有 I/O 限制和 CPU 限制的应用程序都会从 CIO 的使用中获益。而且在 DB2 环境中实现 CIO 可释放 DB2 服务器上的内存和 CPU 资源。这是产生SYS_CPU较高的原因。
结合以上问题：
（1） 根据项目组反馈的表和索引信息，查看表是否有索引，是否走索引；若没有索引或没有合适索引，建立合适索引解决USR_CPU过高问题。
（2） GJDATA和GJIDX表空间关闭文件系统缓存，解决SYS_CPU过高问题。
（3） 与项目组核实，若BUFF16K和BUFF4K两个bufferpool不用可以删除，释放内存。
（4） 增加GJBUFPOOLbufferpool 使索引的HitRatio 大于95%，减少索引IO。
参考：
http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1204concurrent/
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞10作者其他文章
评论 0 · 赞 3
评论 7 · 赞 3
评论 5 · 赞 0
评论 4 · 赞 0
评论 0 · 赞 0
添加新评论15 条评论
2014-11-27 11:19
2014-11-27 10:15
2014-09-02 09:24
2014-08-31 15:51
2014-03-02 07:48
2014-02-26 22:27
2014-01-11 09:24
2014-01-03 16:48
2014-01-03 11:13
2014-01-01 00:35
2013-12-31 22:04
2013-12-31 15:31
2013-12-31 14:52
2013-12-31 14:49
2013-12-31 14:35