关于 mysql 对于 CPU附载过高有什么办法?

5566 发表于 2010-11-17 12:53:27

由于近期论坛人口有增加的趋势

但也注意到

mysql 对于 CPU附载过高

不知道，对于目前使用 LNMP + linodw 512 的主机

有什么优化的办法??

升级 linode 7XX 系列 ??

licess 发表于 2010-11-17 13:50:31

回复 1# 的帖子

这个不好说什么原因，按小辉的方法试试：http://www.xiaohui.com/dev/server/20070701-discuz-mysql-cpu-100-optimize.htm

5566 发表于 2010-11-17 15:43:06

mysql> show processlist;
+-------+------------+-----------+-----------+---------+------+-------+------------------+
| Id | User    | Host    | db    | Command | Time | State | Info          |
+-------+------------+-----------+-----------+---------+------+-------+------------------+
| 48438 | root    | localhost | NULL    | Query | 0 | NULL| show processlist |
| 48831 | user | localhost | bbs | Sleep | 18 |    | NULL          |
| 48859 | user | localhost | bbs| Sleep | 7 |    | NULL          |
+-------+------------+-----------+-----------+---------+------+-------+------------------+
3 rows in set (0.00 sec)

===========================

查询结果这样好似看不出异状??

==========================================

PID USER    PRNIVIRTRESSHR S %CPU %MEM TIME+COMMAND
7105 mysql 20 0 5854429m 4760 S 11425.912065:23 mysqld
20857 www    20 0 2645210m 4708 S 22.1 0:32.19 php-cgi
17379 www    20 0 2596410m 4908 S 12.1 1:18.17 php-cgi
7848 www    20 0 1658412m832 S 02.6 0:13.99 nginx
1 root    20 01984568540 S 00.1 0:06.08 init
2 root    20 0 0 0 0 S 00.0 0:00.00 kthreadd
3 root    RT 0 0 0 0 S 00.0 0:00.57 migration/0
4 root    20 0 0 0 0 S 00.0 0:08.82 ksoftirqd/0
5 root    RT 0 0 0 0 S 00.0 0:00.63 migration/1
6 root    20 0 0 0 0 S 00.0 0:01.71 ksoftirqd/1
7 root    RT 0 0 0 0 S 00.0 0:01.19 migration/2
8 root    20 0 0 0 0 S 00.0 0:00.64 ksoftirqd/2
9 root    RT 0 0 0 0 S 00.0 0:00.30 migration/3
10 root    20 0 0 0 0 S 00.0 0:00.27 ksoftirqd/3
11 root    20 0 0 0 0 S 00.0 0:00.24 events/0
12 root    20 0 0 0 0 S 00.0 0:05.67 events/1
13 root    20 0 0 0 0 S 00.0 0:01.30 events/2

[ 本帖最后由 5566 于 2010-11-17 15:45 编辑 ]

5566 发表于 2010-11-17 22:02:05

经过一下午的查询
目前找到可能是下方的SQL语句造成的结果

$query = $db->query("SELECT p.pid, t.fromuser, t.fromuseruid FROM {$tablepre}posts p LEFT JOIN {$tablepre}thanklog tON p.tid=t.tid WHERE p.tid='$tid' AND (".($discuz_uid ? "authorid='$discuz_uid'" : "authorid=0 AND useip='$GLOBALS'")." OR t.fromuseruid='$discuz_uid') LIMIT 1");

不知道如何优化 SQL表?? 建立引索?

licess 发表于 2010-11-17 22:15:01

回复 4# 的帖子

dz官网问问吧

页: [1]

VPS侦探论坛's Archiver