Mysql 占用cpu资源高的分析

经过从前对mysql的操作经历,先将mysql的铺排难点消弭了,查看msyql是还是不是运维如常,通过查看mysql
data目录里面包车型客车*.err文件(将扩展名改为.txt卡塔尔国记事本查看就能够。如果过大不提出用记事本了,轻巧死掉,能够用editplus等工具轻松的分成上面多少个步骤来解决这么些标题:1、mysql运转如常,也是有希望是大器晚成道设置难题引致2、尽管mysql运行通常,那正是php的一些sql语句招致难题发现,用root客商步向mysql管理mysql
-u root -p输入密码mysql:show processlist 语句,查找负荷最重的 SQL
语句,优化该SQL,比如适当创立某字段的目录。通过那么些命令自身见到原本是有人恶意刷寻找,因为dedecms搜索前边调用寻觅最高的词,引致众两个人用工具刷那些,并且是准期有间隔的,所以将以此php程序改名跳转都形式解决了。当然倘让你的着实是sql语句用了大气的group
by等话语,union联合查询等自然会将mysql的占用率进步。所以就供给优化sql语句,网址尽量生成静态的,日常4W
ip的静态网址,mysql占用率大约为0的。所以那对于程序猿的经历是个思谋。尽量升高mysql品质(MySQL
品质优化的特等20多条资历分享卡塔尔(قطر‎下边是本子之家搜罗的稿子,大家都足以参照他事他说加以考察下MYSQL
CPU 占用 100% 的光景描述 上午帮朋友大器晚成台服务器解除了 Mysql cpu 占用 100%的主题素材。稍收拾了眨眼间间,将资历记录在这里篇随笔里 朋友主机(Windows 2002 + IIS

 (Windows 二零零一 + IIS + PHP + MYSQL 卡塔尔近期 MySQL 服务进度 (mysqld-nt.exe卡塔尔CPU 占用率总为 百分之百 越来越多。此主机有市斤个左右的 database,
分别给十个网址调用。据朋友测验,以致 mysqld-nt.exe cpu
占用奇高的是网址A,风姿罗曼蒂克旦在 IIS 上校此网址结束服务,CPU
占用就降下来了。少年老成启用,则随时升高。
MYSQL CPU 占用 100% 的缓慢解决进度
    明天早上精心检查了刹那间。近期此网站的11日平均日 IP 为二零零一,PageView
为 3万左右。网址A 用的 database 这几天有39个表,记录数 60.1万条,占空间
45MB。按这些数目,MySQL 不只怕占用这么高的能源。
于是在服务器上运转命令,将 mysql 当前的碰着变量输出到文件 output.txt:
d:\web\mysql> mysqld.exe –help >output.txt
发现 tmp_table_size 的值是默许的 32M,于是改善 My.ini, 将
tmp_table_size 赋值到 200M:
d:\web\mysql> notepad c:\windows\my.ini [mysqld]
tmp_table_size=200M
下一场重启 MySQL 服务。CPU 占用有细小下降,早前的CPU 占用波形图是 100%豆蔻年华根直线,今后则在 97%~百分之百之内起伏。那标识调治 tmp_table_size 参数对
MYSQL 品质升高有改革作用。但难点还还未完全化解。
于是乎步入 mysql 的 shell 命令行,调用 show processlist, 查看当前 mysql
使用频繁的 sql 语句:
mysql> show processlist;

  • PHP + MYSQL 卡塔尔(قطر‎近些日子 MySQL 服务进程 (mysqld-nt.exe卡塔尔国 CPU 占用率总为 100%只多不菲。此主机有13个左右的 database,
    分别给12个网址调用。据朋友测量检验,招致 mysqld-nt.exe cpu
    占用奇高的是网址A,风姿潇洒旦在 IIS 中校此网站甘休服务,CPU
    占用就降下来了。生龙活虎启用,则立时提高。 MYSQL CPU 占用 100% 的解决进程后天中午用心检查了弹指间。近些日子此网址的三三日平均日 IP 为贰零零零,PageView 为
    3万左右。网址A 用的 database 近期有四十三个表,记录数 60.1万条,占空间
    45MB。按这几个数目,MySQL 不容许占用这么高的能源。
    于是在服务器上运维命令,将 mysql 当前的情形变量输出到文件 output.txt:
    d:\web\mysql mysqld.exe –help output.txt 发现 tmp_table_size
    的值是暗中认可的 32M,于是改进 My.ini, 将 tmp_table_size 赋值到 200M:
    d:\web\mysql notepad c:\windows\my.ini [mysqld]
    tmp_table_size=200M 然后重启 MySQL 服务。CPU 占用有微小下落,早先的CPU
    占用波形图是 100% 风流倜傥根直线,现在则在 97%~百分之百中间起伏。那评释调节tmp_table_size 参数对 MYSQL 质量进步有改革功能。但难点还未完全缓慢解决。
    于是跻身 mysql 的 shell 命令行,调用 show processlist, 查看当前 mysql
    使用频仍的 sql 语句: mysql show processlist; 每每调用此命令,开掘网址 A
    的五个 SQL 语句日常在 process list 中冒出,其语法如下: SELECT t1.pid,
    t2.userid, t3.count, t1.date FROM _mydata AS t1 LEFT JOIN _myuser AS
    t3 ON t1.userid=t3.userid LEFT JOIN _mydata_body AS t2 ON
    t1.pid=t3.pid OLANDDEGL450 BY t1.pid LIMIT 0,15 调用 show columns
    检查那八个表的构造 : mysql show columns from _myuser; mysql show
    columns from _mydata; mysql show columns from _mydata_body;
    终于意识了难题所在:_mydata 表,只根据 pid 创建了叁个 primary
    key,但并未为 userid 建构目录。而在此个 SQL 语句的率先个 LEFT JOIN ON
    子句中: LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid _mydata 的
    userid 被参预了标准相比较运算。于是我为给 _mydata 表依据字段 userid
    创建了一个目录: mysql ALTE奥迪Q3 TABLE `_mydata` ADD INDEX ( `userid` )建设构造此索引之后,CPU 立时降至了 五分之四左右。看见找到了难点所在,于是检查另多个往往出现在 show processlist 中的
    sql 语句: SELECT COUNT(*) FROM _mydata AS t1, _mydata_key AS t2
    WHERE t1.pid=t2.pid and t2.keywords = ‘孔雀’ 经检查 _mydata_key
    表的构造,开掘它只为 pid 建了了 primary key, 未有为 keywords 建立index。_mydata_key 近来有 33
    万条记下,在并未有索引的景色下对33万条记下实行理文件本检索匹配,不花销大量的
    cpu 时间才怪。看来就是针对这么些表的搜寻出难题了。于是同样为 _mydata_key
    表依据字段 keywords 加上索引: mysql ALTELX570 TABLE `_mydata_key` ADD
    INDEX ( `keywords` 卡塔尔国 营造此索引之后,CPU立即降了下去,在
    四分之二~70%里头震荡。 再次调用 show prosslist,网址A 的sql
    调用就少之又少出今后结果列表中了。但发掘此主机械运输转了多少个 Discuz 的论坛程序,
    Discuz
    论坛的少数个表也存在着那一个主题素材。于是顺手风华正茂并减轻,cpu占用再一次降下来了。(2006.07.09
    附注:关于 discuz
    论坛的宛在最近优化进程,作者后来另写了生龙活虎篇小说,详见:千万级记录的 Discuz!
    论坛导致 MySQL CPU 百分百 的 优化笔记 卡塔尔国 消释 MYSQL CPU 占用 100%的涉世总计 扩充 tmp_table_size 值。mysql
    的布局文件中,tmp_table_size 的暗中同意大小是
    32M。假设一张不经常表超过该大小,MySQL发生壹个 The table tbl_name is full
    格局的谬误,假使您做过多高等 GROUP BY 查询,扩大 tmp_table_size 值。
    那是 mysql 官方关于此选项的批注: tmp_table_size This variable
    determines the maximum size for a temporary table in memory. If the
    table becomes too large, a MYISAM table is created on disk. Try to avoid
    temporary tables by optimizing the queries where possible, but where
    this is not possible, try to ensure temporary tables are always stored
    in memory. Watching the processlist for queries with temporary tables
    that take too long to resolve can give you an early warning that
    tmp_table_size needs to be upped. Be aware that memory is also
    allocated per-thread. An example where upping this worked for more was a
    server where I upped this from 32MB (the default卡塔尔(قطر‎ to 64MB with immediate
    effect. The quicker resolution of queries resulted in less threads being
    active at any one time, with all-round benefits for the server, and
    available memory. 对 WHERE, JOIN, MAX(卡塔尔国, MIN(State of Qatar, O本田CR-VDELX570 BY
    等子句中的条件判断中用到的字段,应该依据其创立索引
    INDEX。索引被用来异常快搜索在贰个列上用风流洒脱特定值的行。未有索引,MySQL必须要首先以第一条记下早先并然后读完全数表直到它寻找相关的行。表越大,花销时间越来越多。假诺表对于查询的列有贰个目录,MySQL能高效达到三个岗位去找寻到数据文件的高级中学级,没有须要酌量全体数据。纵然一个表有1000行,那比顺序读取最少快100倍。全数的MySQL索引(PPAJEROIMA奥迪Q3Y、UNIQUE和INDEX卡塔尔国在B树中存款和储蓄。
    依据 mysql 的开拓文书档案: 索引 index 用于: 飞快找寻相当三个WHERE子句的行业实践统风华正茂(JOINState of Qatar时,从此外表检索行。 对一定的索引列搜索MAX(卡塔尔国或MIN(State of Qatar值
    即便排序或分组在多个可用键的最左侧前缀上进行(比方,OHighlanderDE福睿斯 BY
    key_part_1,key_part_2卡塔尔国,排序或分组多少个表。如若具有键值部分跟随DESC,键以倒序被读取。
    在局地情状中,三个查询能被优化来探究值,不用咨询数据文件。即便对有个别表的具备应用的列是数字型的同不时间结合某个键的最右侧前缀,为了越来越快,值能够从索引树被寻觅出来。
    假定你发生下列SELECT语句: mysql SELECT * FROM tbl_name WHERE
    col1=val1 AND col2=val2;
    借使三个多列索引存在于col1和col2上,适当的行能够一向被收取。若是分别的单行列索引存在于col1和col2上,优化器试图透过决定哪些索引将找到越来越少的行并来寻找更具约束性的目录并且动用该索引取行。
    开拓人士做 SQL 数据表设计的时候,一定要通盘思谋清楚。

几度调用此命令(每秒刷若干遍),发掘网址 A 的八个 SQL 语句平常在 process
list 中冒出,其语法如下:
SELECT t1.pid, t2.userid, t3.count, t1.date FROM _mydata AS t1 LEFT
JOIN _myuser AS t3 ON t1.userid=t3.userid LEFT JOIN _mydata_body AS
t2 ON t1.pid=t3.pid ORDER BY t1.pid LIMIT 0,15

调用 show columns 检查那多个表的构造 :
mysql> show columns from _myuser; mysql> show columns from
_mydata; mysql> show columns from _mydata_body;

毕竟意识了难题所在:_mydata 表,只依照 pid 创立了三个 primary
key,但并从未为 userid 建构目录。而在这里个 SQL 语句的首先个 LEFT JOIN ON
子句中:
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

_mydata 的 userid 被参加了典型相比较运算。于是本身为给 _mydata 表遵照字段
userid 建设构造了叁个索引:
mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )