MYSQL CPU 占用 100% 的现象描述
早上帮朋友一台服务器解决了 Mysql cpu 占用 100% 的问题。稍整理了一下,将经验记录在这篇文章里:《解决一个 MySQL 服务器进程 CPU 占用 100%的技术笔记》 http://www.xiaohui.com/weekly/20070307.htm
朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 MySQL 服务进程 (mysqld-nt.exe) CPU 占用率总为 100% 高居不下。此主机有10个左右的 database, 分别给十个网站调用。据朋友测试,导致 mysqld-nt.exe cpu 占用奇高的是网站A,一旦在 IIS 中将此网站停止服务,CPU 占用就降下来了。一启用,则马上上升。
MYSQL CPU 占用 100% 的解决过程
全文记录: http://www.xiaohui.com/weekly/20070307.htm
今天早上仔细检查了一下。目前此网站的七日平均日 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占用再次降下来了。(2007.07.09 附注:关于 discuz 论坛的具体优化过程,我后来另写了一篇文章,详见:千万级记录的 Discuz! 论坛导致 MySQL CPU 100% 的 优化笔记 http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm)
解决 MYSQL CPU 占用 100% 的经验总结
http://www.xiaohui.com/weekly/20070307.htm
-
增加 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 用于:
开发人员做 SQL 数据表设计的时候,一定要通盘考虑清楚。
分享到:
相关推荐
朋友主机(Windows 2003 + IIS + PHP + MYSQL )近来 ... MYSQL CPU 占用 100% 的解决过程 今天早上仔细检查了一下。目前此网站的七日平均日 IP 为2000,PageView 为 3万左右。网站A 用的 database 目前有39个表,记录数
### 服务器大量php-cgi.exe进程导致CPU占用100%的问题分析及解决方案 #### 一、问题背景 在IT运维领域中,服务器资源管理尤其是CPU利用率的监控至关重要。当出现服务器上的`php-cgi.exe`进程占用大量CPU资源的情况...
MySQL 是一个关系数据库管理系统,广泛应用于各个行业,然而,如果 MySQL 占用 CPU 近 100%,则可能会对服务器性能产生严重影响。因此,了解 MySQL 占用 CPU 近 100% 的原因和解决方法是非常重要的。 原因分析 1....
然而,随着论坛规模的扩大,数据量的增长,可能会遇到一个问题:论坛运行缓慢,MySQL数据库CPU占用率达到100%,严重影响用户体验。本文将针对这一问题进行深入分析,并提出相应的解决策略。 首先,我们需要了解...
【MySQL面试第二弹】面试中遇到MySQL服务CPU占用100%的问题,是数据库性能优化领域常见的挑战。解决这个问题需要一套系统的排查流程。首先,我们要理解CPU飙升的原因可能包括内存管理、代码执行效率、锁机制的不当...
3. **分析TIME_WAIT状态的连接原因**:TIME_WAIT状态是TCP连接生命周期中的一个正常阶段,但它会占用一定的系统资源。在高并发场景下,如果大量的连接都停留在TIME_WAIT状态,可能会导致新的连接无法建立,从而影响...
在某个新服务器上,新建了一个MySQL的实例,该服务器上面只有MySQL这一个进程,但是CPU的负载却居高不下,使用top命令查询的结果如下: [dba_mysql@dba-mysql ~]$ top top - 17:12:44 up 104 days, 20 min, 2 ...
MySQL占用CPU过高的解决方案 在这篇文章中,我们讨论了MySQL占用CPU过高的问题及其解决方案。该问题可能会导致服务器性能下降,影响用户体验。通过实践案例,我们将展示如何诊断和解决该问题。 一、问题描述 ...
文章中提到了一个具体的案例:在部署了一个基于LNMP(Linux + Nginx + MySQL + PHP)架构的网站之后,原本运行平稳的服务器突然出现了CPU占用率飙升至100%的情况。进一步通过`top`命令观察,发现是php-fpm进程导致的...
### MySQL CPU占用高的问题解决方法小结 #### 问题背景 最近发现php网站发布信息比较慢,而且同网站目录下的asp程序经常出现登录后立即重新登录的情况。经过调查,发现这是由于服务器资源占用过高所导致的。具体...
启动 MySQL 服务器后,可以连接到 MySQL 服务器,连接成功后是一个 mysql> 的提示。 错误日志文件: 错误日志文件是 MySQL 服务器的重要组成部分,记录了 MySQL 服务器的错误信息。可以通过查看错误日志文件来了解 ...
MySQL服务器的IO 100%问题通常是一个严重的情况,因为它直接影响到数据库的性能和响应时间,可能导致系统整体效率下降,甚至服务中断。本篇文章主要探讨了如何分析和优化MySQL服务器遇到的IO瓶颈,特别是针对高写入...
LNH_MySQL 04-linux进程占用cpu高的解决方案案例.mp4
mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记
以下是一些解决MySQL CPU过高的策略,特别是通过添加索引来优化。 1. **分析查询性能**: 当MySQL CPU占用过高时,首要任务是确定引起问题的具体查询。通过`SHOW PROCESSLIST`命令可以查看当前正在执行的查询,找...
- **Id**: 进程ID,用于标识每一个单独的进程。 - **User**: 执行进程的用户名称。 - **Host**: 进程的来源主机。 - **db**: 当前连接的数据库名称。 - **Command**: 当前进程执行的命令类型,如Sleep表示进程...
5. mysql_connect(): 打开一个到 MySQL 服务器的连接。 6. mysql_create_db(): 新建一个 MySQL 数据库。 7. mysql_data_seek(): 移动内部结果的指针。 8. mysql_db_name(): 取得结果数据。 9. mysql_db_query(): ...