MYSQL CPU 占用 100% 的现象描述 早上帮别人一台服务器解决了 Mysql cpu 占用 100% 的问题。稍整理了一下,将经验记录在这篇文章里:《解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记》
朋友的主机(Windows 2003 + IIS + PHP + MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试,导致 mysqld-nt.exe cpu 占用奇高的是网站A,一旦在 IIS 中将此网站停止服务,CPU 占用就降下来了。一启用,则马上上升。
MYSQL CPU 占用 100% 的解决过程 今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000,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%~100%之间起伏。这表明调整 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 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` ) 建立此索引之后,CPU 马上降到了 80% 左右。看到找到了问题所在,于是检查另一个反复出现在 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> ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` ) 建立此索引之后,CPU立刻降了下来,在 50%~70%之间震荡。
再次调用 show prosslist,网站A 的sql 调用就很少出现在结果列表中了。但发现此主机运行了几个 Discuz 的论坛程序, Discuz论坛的好几个表也存在着这个问题。于是顺手一并解决,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(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。 根据 mysql 的开发文档: 索引 index 用于: 快速找出匹配一个WHERE子句的行 当执行联结(JOIN)时,从其他表检索行。 对特定的索引列找出MAX()或MIN()值 如果排序或分组在一个可用键的最左面前缀上进行(例如,ORDER 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 数据表设计的时候,一定要通盘考虑清楚。
|
分享到:
相关推荐
解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记
解决 MySQL 服务器进程 CPU 占用 100%的技术笔记
朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 ... MYSQL CPU 占用 100% 的解决过程 今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000,PageView 为 3万左右。网站A 用的 database 目前有39个表,记录数
推荐阅读学习:MySQL最全整理(面试题+笔记+导图),面试大厂不再被MySql难倒! 一、引子 对于互联网公司,线上CPU飙升的问题很常见(例如某个活动...执行top命令:查看所有进程占系统CPU的排序。极大可能排第一个的就
#适用于实时查询mysql占用CPU高的语句,循环监控mysql进程情况,当CPU大于一定的前执行中的SQL情况. #执行前,修改ENV认证部分 #编写:Chaoren #2022年3月4日18:38:53 # #对于执行时间非常短的SQL可能监控到的语句...
在某个新服务器上,新建了一个MySQL的实例,该服务器上面只有MySQL这一个进程,但是CPU的负载却居高不下,使用top命令查询的结果如下: [dba_mysql@dba-mysql ~]$ top top - 17:12:44 up 104 days, 20 min, 2 ...
在做性能测试时我们经常需要监控服务器上各种进程占用内存、CPU、IO等的资源使用情况,本文档告诉你如何通过LoadRunner监控MySQL在Apache下的CPU占用情况,帮助你监控分析服务器端的性能情况。
除此之外,还应观注那些占用系统资源(cpu、内存)的进程。 1.使用sar来检查操作系统是否存在IO问题 #sar-u210— 即每隔2秒检察一次,共执行20次。 结果示例: 注:在redhat下,%system就是所谓的%wio。 Linux2.4.21-...
今天下午网站宕了两次机,发工单给阿里云,发现原因是服务器的CPU 100%了。 重启服务器后,使用 top 命令看看是哪些进程消耗那么大的 CPU 使用。盯了有好十几分钟,主要消耗 CPU 的进程有两个,一个是 mysql,另一个...
切换成这个架构才2天,就收到nagios的报警,报警信息显示有一台web服务器负载很高,于是通过SecureCRT登录到服务器上,用top命令看了一下,发现有几个php-cgi进程占用了大量的CPU,如下: 13889 ...
用多线程和C语言实现的mysql能很容易充分利用CPU。 2、mysql可运行在不同的操作系统下。简单地说,mysql可以支持Windows95/98/NT/2000以及UNIX、Linux和SUN OS等多种操作系统平台。这意味着在一个操作系统中实现的...
10-mysql进程-状态-在线修改参数重要知识讲解.avi 11-mysqlbinlog命令介绍及实战讲解.avi 12-mysqldump-master-data参数答疑详解.avi 第六部 MySQL主从复制原理及实战部署(10节) 01-由架构因为引出主从复制的作用...
# MySQL的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,...
从初学者角度出发,通过大量具体应用实例,详细介绍了Linux的安装与启动、Linux的磁盘文件管理、用户与用户组管理、服务与进程管理、软件包管理、网络连接配置、MySQL数据库服务器、Web服务器、FTP服务器、DNS/DHCP...
从初学者角度出发,通过大量具体应用实例,详细介绍了Linux的安装与启动、Linux的磁盘文件管理、用户与用户组管理、服务与进程管理、软件包管理、网络连接配置、MySQL数据库服务器、Web服务器、FTP服务器、DNS/DHCP...
ServerKing 服务器监控王能够实时监控 WWW 服务、 IIS 程序池、网卡流量、异常进程、 CPU 资源占用率、内存使用率、硬盘空间、 MSSQL 内存清空、 MySQL 服务等近 10 多项功能,当检测值达到用户设定的报警阀值时...
用文件执行sql 2 修改文件权限 3 查看文件的类型 3 删除文件夹实例: 3 移动文件命令 4 解压zip文件 4 查看java 进程命令 4 查看apche进程命令 4 查看mysql进程命令 4 查看svn进程命令 4 Sh脚本后台执行 4 启动...
华盾服务器管理专家是一款服务器日常维护管理软件,集华盾独创的“WEB一站式管理”、用户权限管理、信息管理管理、性能管理管理、网站安全管理、主机维护管理、网站备案管理、网站维护管理、网络防火墙管理、系统...
其核心模块包括:服务器群集监视,ES群集监视,CPU监视,内存监视,数据监视(mysql,oracle,pg),服务心跳检测,应用程序进程管理,磁盘IO监视,系统负载监视,监视警报信息推送。 1.采用服务器与客户端的协同...