小弟现在负责公司服务器(windows2003),环境配置Apache/2.2.4 + tomcat6
前段时间公司定制了一套jeecms的系统,但是上线之后总是出现mysql的cpu使用率达100%,甚至导致服务器死机自动关机。
最后用mysql性能跟踪器,截取到类似一下的sql语句在查询的时候超过1秒:select article0_.ARTICLE_ID as ARTICLE1_24_, article0_.TITLE as TITLE24_, article0_.SHORT_TITLE as SHORT3_24_, article0_.TITLE_IMG as TITLE4_24_, article0_.CONTENT_IMG as CONTENT5_24_, article0_.TITLE_COLOR as TITLE6_24_, article0_.DESCRIPTION as DESCRIPT7_24_, article0_.TAGS as TAGS24_, article0_.AUTHOR as AUTHOR24_, article0_.ORIGIN as ORIGIN24_, article0_.SORT_DATE as SORT11_24_, article0_.RELEASE_DATE as RELEASE12_24_, article0_.RELEASE_SYS_DATE as RELEASE13_24_, article0_.CHECK_TIME as CHECK14_24_, article0_.DISABLE_TIME as DISABLE15_24_, article0_.VISIT_TOTAL as VISIT16_24_, article0_.VISIT_TODAY as VISIT17_24_, article0_.VISIT_WEEK as VISIT18_24_, article0_.VISIT_MONTH as VISIT19_24_, article0_.VISIT_QUARTER as VISIT20_24_, article0_.VISIT_YEAR as VISIT21_24_, article0_.STAT_DATE as STAT22_24_, article0_.OUTER_URL as OUTER23_24_, article0_.CONTENT_RES_PATH as CONTENT24_24_, article0_.PAGE_COUNT as PAGE25_24_, article0_.TPL_CONTENT as TPL26_24_, article0_.CHECK_STEP as CHECK27_24_, article0_.TOP_LEVEL as TOP28_24_, article0_.COMMENT_COUNT as COMMENT29_24_, article0_.CHECK_OPINION as CHECK30_24_, article0_.RELATED_IDS as RELATED31_24_, article0_.HAS_TITLEIMG as HAS32_24_, article0_.ALLOW_COMMENT as ALLOW33_24_, article0_.IS_BOLD as IS34_24_, article0_.IS_DRAFT as IS35_24_, article0_.IS_RECOMMEND as IS36_24_, article0_.IS_CHECK as IS37_24_, article0_.IS_DISABLED as IS38_24_, article0_.IS_REJECT as IS39_24_, article0_.PARAM1 as PARAM40_24_, article0_.PARAM2 as PARAM41_24_, article0_.PARAM3 as PARAM42_24_, article0_.DEF_STRING_1 as DEF43_24_, article0_.DEF_STRING_2 as DEF44_24_, article0_.DEF_STRING_3 as DEF45_24_, article0_.DEF_STRING_4 as DEF46_24_, article0_.DEF_STRING_5 as DEF47_24_, article0_.DEF_STRING_6 as DEF48_24_, article0_.DEF_STRING_7 as DEF49_24_, article0_.DEF_STRING_8 as DEF50_24_, article0_.DEF_STRING_9 as DEF51_24_, article0_.DEF_LONG_1 as DEF52_24_, article0_.DEF_LONG_2 as DEF53_24_, article0_.DEF_LONG_3 as DEF54_24_, article0_.DEF_LONG_4 as DEF55_24_, article0_.DEF_LONG_5 as DEF56_24_, article0_.DEF_MONEY1 as DEF57_24_, article0_.DEF_MONEY2 as DEF58_24_, article0_.DEF_MONEY3 as DEF59_24_, article0_.DEF_DATE1 as DEF60_24_, article0_.DEF_DATE2 as DEF61_24_, article0_.DEF_DATE3 as DEF62_24_, article0_.DEF_BOOL1 as DEF63_24_, article0_.DEF_BOOL2 as DEF64_24_, article0_.DEF_BOOL3 as DEF65_24_, article0_.NEXT_ID as NEXT66_24_, article0_.PRE_ID as PRE67_24_, article0_.CTTCTG_ID as CTTCTG68_24_, article0_.ADMIN_DISABLE as ADMIN69_24_, article0_.GROUP_ID as GROUP70_24_, article0_.CHANNEL_ID as CHANNEL71_24_, article0_.WEBSITE_ID as WEBSITE72_24_, article0_.CONFIG_ID as CONFIG73_24_, article0_.ADMIN_CHECK as ADMIN74_24_, article0_.ADMIN_INPUT as ADMIN75_24_, article0_.MEMBER_ID as MEMBER76_24_ from ARTI_ARTICLE article0_ where 1=1 and article0_.WEBSITE_ID=1 and article0_.IS_CHECK=1 and article0_.IS_DISABLED=0 order by article0_.RELEASE_DATE desc limit 6
找了很多资料对于mysql也进行过优化,实在搞不清楚还需要做哪些优化,希望哪位大哥能指点小弟!
谢谢
前段时间公司定制了一套jeecms的系统,但是上线之后总是出现mysql的cpu使用率达100%,甚至导致服务器死机自动关机。
最后用mysql性能跟踪器,截取到类似一下的sql语句在查询的时候超过1秒:select article0_.ARTICLE_ID as ARTICLE1_24_, article0_.TITLE as TITLE24_, article0_.SHORT_TITLE as SHORT3_24_, article0_.TITLE_IMG as TITLE4_24_, article0_.CONTENT_IMG as CONTENT5_24_, article0_.TITLE_COLOR as TITLE6_24_, article0_.DESCRIPTION as DESCRIPT7_24_, article0_.TAGS as TAGS24_, article0_.AUTHOR as AUTHOR24_, article0_.ORIGIN as ORIGIN24_, article0_.SORT_DATE as SORT11_24_, article0_.RELEASE_DATE as RELEASE12_24_, article0_.RELEASE_SYS_DATE as RELEASE13_24_, article0_.CHECK_TIME as CHECK14_24_, article0_.DISABLE_TIME as DISABLE15_24_, article0_.VISIT_TOTAL as VISIT16_24_, article0_.VISIT_TODAY as VISIT17_24_, article0_.VISIT_WEEK as VISIT18_24_, article0_.VISIT_MONTH as VISIT19_24_, article0_.VISIT_QUARTER as VISIT20_24_, article0_.VISIT_YEAR as VISIT21_24_, article0_.STAT_DATE as STAT22_24_, article0_.OUTER_URL as OUTER23_24_, article0_.CONTENT_RES_PATH as CONTENT24_24_, article0_.PAGE_COUNT as PAGE25_24_, article0_.TPL_CONTENT as TPL26_24_, article0_.CHECK_STEP as CHECK27_24_, article0_.TOP_LEVEL as TOP28_24_, article0_.COMMENT_COUNT as COMMENT29_24_, article0_.CHECK_OPINION as CHECK30_24_, article0_.RELATED_IDS as RELATED31_24_, article0_.HAS_TITLEIMG as HAS32_24_, article0_.ALLOW_COMMENT as ALLOW33_24_, article0_.IS_BOLD as IS34_24_, article0_.IS_DRAFT as IS35_24_, article0_.IS_RECOMMEND as IS36_24_, article0_.IS_CHECK as IS37_24_, article0_.IS_DISABLED as IS38_24_, article0_.IS_REJECT as IS39_24_, article0_.PARAM1 as PARAM40_24_, article0_.PARAM2 as PARAM41_24_, article0_.PARAM3 as PARAM42_24_, article0_.DEF_STRING_1 as DEF43_24_, article0_.DEF_STRING_2 as DEF44_24_, article0_.DEF_STRING_3 as DEF45_24_, article0_.DEF_STRING_4 as DEF46_24_, article0_.DEF_STRING_5 as DEF47_24_, article0_.DEF_STRING_6 as DEF48_24_, article0_.DEF_STRING_7 as DEF49_24_, article0_.DEF_STRING_8 as DEF50_24_, article0_.DEF_STRING_9 as DEF51_24_, article0_.DEF_LONG_1 as DEF52_24_, article0_.DEF_LONG_2 as DEF53_24_, article0_.DEF_LONG_3 as DEF54_24_, article0_.DEF_LONG_4 as DEF55_24_, article0_.DEF_LONG_5 as DEF56_24_, article0_.DEF_MONEY1 as DEF57_24_, article0_.DEF_MONEY2 as DEF58_24_, article0_.DEF_MONEY3 as DEF59_24_, article0_.DEF_DATE1 as DEF60_24_, article0_.DEF_DATE2 as DEF61_24_, article0_.DEF_DATE3 as DEF62_24_, article0_.DEF_BOOL1 as DEF63_24_, article0_.DEF_BOOL2 as DEF64_24_, article0_.DEF_BOOL3 as DEF65_24_, article0_.NEXT_ID as NEXT66_24_, article0_.PRE_ID as PRE67_24_, article0_.CTTCTG_ID as CTTCTG68_24_, article0_.ADMIN_DISABLE as ADMIN69_24_, article0_.GROUP_ID as GROUP70_24_, article0_.CHANNEL_ID as CHANNEL71_24_, article0_.WEBSITE_ID as WEBSITE72_24_, article0_.CONFIG_ID as CONFIG73_24_, article0_.ADMIN_CHECK as ADMIN74_24_, article0_.ADMIN_INPUT as ADMIN75_24_, article0_.MEMBER_ID as MEMBER76_24_ from ARTI_ARTICLE article0_ where 1=1 and article0_.WEBSITE_ID=1 and article0_.IS_CHECK=1 and article0_.IS_DISABLED=0 order by article0_.RELEASE_DATE desc limit 6
找了很多资料对于mysql也进行过优化,实在搞不清楚还需要做哪些优化,希望哪位大哥能指点小弟!
谢谢
不如先mysql-font运行下这条sql。看会不会cpu 100%。如果会那就查下这sql有没问题咯。。
3.确定哪个操作后,查代码,改代码或存储过程...
good luck !