服务器环境见下表
服务器环境 |
||||
序号 |
名称 |
版本 |
内存 |
CPU |
1 |
Windows Server 2008 64bit |
R2 |
16G |
2 |
2 |
Mysql |
5.7 |
256M |
2 |
一、问题现象
1、服务器CPU使用率不稳定,波动比较明显,偶尔飙升至100%,内存使用率稳定在10%左右。
2、MYSQL相应异常缓慢,部分查询结果等待时间极长,切无法出数据。
二、检查思路
1、CPU飙升应该是有大的查询语句在执行,使用show full processlist检查查询语句。
2、内存只用到10%,应该是安装时使用了mysql的默认参数导致,需要提升mysql的相关参数。
3、收集用户反馈检查部分表和数据是否有异常。
三、处理方法
1、首先使用show full processlist 得到占用大量CPU时间的查询语句
4 root test.test.com:49357 x3 Query 23 Sending data SELECT T.R_R_ID, T.RECORD_LOCK, T.NORM_VALUE, T.URGE_PERIOD,\n\t\t E.ENTITY_ID, E.PARENT_ID, E.NAME ENTITY_NAME, E.NOTE ENTITY_NOTE, E.TYPE_ID ENTITY_TYPE,\n\t N.NORM_ID, N.DEF_CODE NORM_CODE, N.TEST_COND, N.CALCULATE_MATH, N.CHINESE_MATH,\n\t D.NAME NORM_NAME, D.DATA_TYPE, CD.NAME UNIT_NAME\n\t FROM TEST_NORM N\n\t INNER JOIN TEST_NORM_DEFIN D ON D.NORM_DEFIN_ID = N.NORM_DEFIN_ID\n\t INNER JOIN TEST_ENTITY E ON E.CODE = N.ENTITY_CODE\n\t LEFT JOIN SYS_GENERAL_CODE CD ON CD.ENCODE_VALUE = D.UNIT_NAME AND CD.KEY_VALUE LIKE 'DWMC_%'\n\t LEFT JOIN (SELECT DISTINCT RR.*\n\t \t\t\t FROM (\n\t \t\t\t SELECT DISTINCT A.R_R_ID,A.R_U_INFO_ID,A.ENTITY_ID,A.RECORD_LOCK,A.NORM_CODE,A.NORM_VALUE,A.URGE_PERIOD \n\t \t\t\t FROM TEST_REPORT_RECORD A\n\t \t\t\t \t\tWHERE A.URGE_PERIOD_YEAR IN (2017)\n\t\t\t\t \t\tAND A.URGE_PERIOD_MONTH IN (9)\n\t\t\t\t \t\tAND A.URGE_PERIOD_DAY IN (20)\n\t\t\t\t \t\t \n\t\t\t\t \t\tAND A.NORM_CODE IN('SJA_FDL_D_D_D_SUM_M_MANL','SJA_GDL_D_D_D_SUM_M_CTST','SJA_SCCYDL_D_D_D_SUM_M_MANL','SJA_JJDL_D_D_D_SUM_M_MANL','SJA_ZGFH_D_D_D_MAX_M_MANL','SJA_PJFH_D_D_D_PAV_M_CTST','SJA_FHLV_D_D_D_PAV_M_CTST','SJA_FHXS_D_D_D_PAV_M_CTST','SJA_HYM_D_D_D_SUM_M_MANL','SJA_HYY_D_D_D_SUM_M_MANL','SJA_FDZJZB_D_D_D_SUM_M_MANL','SJA_FDYMH_D_D_D_SUM_M_CTST','SJA_FDRYH_D_D_D_SUM_M_CTST','SJA_FDBZMHZ_D_D_D_PAV_M_CTST','SJA_GDBZMHZ_D_D_D_PAV_M_CTST','SJA_MPRL_D_D_D_SUM_M_CTST','SJA_KCM_D_D_D_SIN_M_MANL','SJA_HLKC_D_D_D_SUM_M_CTST','SJA_JJKCM_D_D_D_SUM_M_CTST','SJA_MFHKYTS_D_D_D_SUM_M_MANL','SJA_LYXS_D_D_D_SUM_M_CTST','SJA_YXXS_D_D_D_PAV_M_CSST','SJA_KDCL_D_D_D_PAV_M_MANL','SJA_CLXS_D_D_D_PAV_M_CTST','SJA_JZYXQK_D_D_D_SIN_M_MANL','SJA_RBTBR_D_D_D_SIN_M_MANL','SJA_RBTJFZR_D_D_D_SIN_M_MANL')\n\t\t\t\t \t\t \n\t\t\