我的 CentOS 服务器出了问题。有时，页面打开速度太慢，最终通常会导致 mysqld 停止运行。我知道这些信息不够充分，我不知道哪些是相关的，哪些不是，所以请问有人能告诉我需要提供哪些信息才能更好地理解这个问题吗？
以下是 top 命令的输出：
Tasks: 175 total, 3 running, 172 sleeping, 0 stopped, 0 zombie
Cpu(s): 60.5%us, 28.9%sy, 0.0%ni, 0.0%id, 7.6%wa, 0.2%hi, 2.8%si, 0.0%st
Mem: 3985660k total, 2095932k used, 1889728k free, 52048k buffers
Swap: 6225912k total, 4632k used, 6221280k free, 545296k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4076 mysql 20 0 159m 36m 4540 S 121.4 0.9 108:49.64 mysqld
12694 apache 20 0 53636 21m 6492 S 7.3 0.5 0:00.77 httpd
12519 apache 20 0 54688 22m 6784 S 7.0 0.6 0:02.33 httpd
12575 apache 20 0 54688 23m 6796 S 5.3 0.6 0:01.80 httpd
11986 apache 20 0 68000 37m 6816 S 5.0 1.0 0:04.70 httpd
12595 apache 20 0 53636 21m 6636 S 4.0 0.6 0:00.95 httpd
11564 apache 20 0 62884 32m 6812 R 3.3 0.8 0:09.09 httpd
12690 apache 20 0 52628 20m 6424 S 3.3 0.5 0:00.22 httpd
12212 apache 20 0 59812 26m 6800 S 3.0 0.7 0:02.69 httpd
12536 apache 20 0 58788 24m 6716 S 3.0 0.6 0:01.64 httpd
11995 apache 20 0 92580 58m 6780 S 2.7 1.5 0:05.50 httpd
12069 apache 20 0 73124 42m 6812 S 2.7 1.1 0:06.60 httpd
11587 apache 20 0 62884 31m 6816 S 2.3 0.8 0:12.11 httpd
11971 apache 20 0 65952 35m 6816 S 2.0 0.9 0:04.60 httpd
12236 apache 20 0 57760 25m 6808 S 2.0 0.6 0:05.85 httpd
12539 apache 20 0 53636 21m 6664 S 2.0 0.6 0:01.59 httpd
12520 apache 20 0 59812 26m 6796 S 1.3 0.7 0:02.43 httpd
12686 apache 20 0 54688 23m 6776 S 1.3 0.6 0:01.58 httpd
981 root 20 0 0 0 0 S 1.0 0.0 7:42.72 flush-253:0
11978 apache 20 0 68000 37m 6816 S 1.0 1.0 0:04.76 httpd
11424 apache 20 0 62884 30m 6804 S 0.7 0.8 0:10.38 httpd
9586 apache 20 0 61860 29m 6796 S 0.3 0.8 0:02.36 httpd
12269 apache 20 0 73124 41m 6816 R 0.3 1.1 0:03.49 httpd
12395 apache 20 0 53636 21m 6564 S 0.3 0.6 0:01.68 httpd
12397 apache 20 0 57760 25m 6780 S 0.3 0.7 0:02.22 httpd
12594 apache 20 0 51588 19m 6552 S 0.3 0.5 0:00.42 httpd
12727 root 20 0 2660 1148 872 R 0.3 0.0 0:00.01 top
1 root 20 0 2832 1204 1080 S 0.0 0.0 0:00.85 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:00.15 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
6 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
7 root 20 0 0 0 0 S 0.0 0.0 0:01.24 ksoftirqd/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 events/0
10 root 20 0 0 0 0 S 0.0 0.0 0:00.04 events/1
11 root 20 0 0 0 0 S 0.0 0.0 0:00.00 cpuset
12 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper
13 root 20 0 0 0 0 S 0.0 0.0 0:00.00 netns
14 root 20 0 0 0 0 S 0.0 0.0 0:00.00 async/mgr
15 root 20 0 0 0 0 S 0.0 0.0 0:00.00 pm
16 root 20 0 0 0 0 S 0.0 0.0 0:00.00 sync_supers
17 root 20 0 0 0 0 S 0.0 0.0 0:00.00 bdi-default
18 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/0
19 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/1
20 root 20 0 0 0 0 S 0.0 0.0 0:00.36 kblockd/0
21 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kblockd/1
22 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpid
23 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpi_notify
24 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpi_hotplug
25 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ata/0
26 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ata/1
非常感谢，任何帮助都将不胜感激！
以下是 mysql 进程列表输出：
mysql> SHOW PROCESSLIST;
+------+------------------+-----------+-------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------------------+-----------+-------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 3565 | mydbusername | localhost | mydbname | Sleep | 5371 | | NULL |
| 6793 | mydbusername | localhost | mydbname | Sleep | 2375 | | NULL |
| 9090 | mydbusername | localhost | mydbname | Sleep | 3 | | NULL |
| 9094 | mydbusername | localhost | mydbname | Sleep | 2 | | NULL |
| 9102 | mydbusername | localhost | mydbname | Query | 0 | preparing | SELECT a.*,cc.description as catdesc, cc.title as cattitle,s.description as secdesc, s.title as sect |
| 9103 | mydbusername | localhost | mydbname | Query | 0 | preparing | SELECT a.*,cc.description as catdesc, cc.title as cattitle,s.description as secdesc, s.title as sect |
| 9104 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9107 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9108 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9115 | mydbusername | localhost | mydbname | Query | 2 | Copying to tmp table | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 9116 | mydbusername | localhost | mydbname | Query | 1 | preparing | SELECT a.*,cc.description as catdesc, cc.title as cattitle,s.description as secdesc, s.title as sect |
| 9123 | mydbusername | localhost | mydbname | Query | 0 | Sorting result | SELECT a.*,cc.description as catdesc, cc.title as cattitle,s.description as secdesc, s.title as sect |
| 9127 | mydbusername | localhost | mydbname | Query | 3 | Copying to tmp table | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 9128 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 9130 | mydbusername | localhost | mydbname | Query | 3 | Copying to tmp table | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 9131 | mydbusername | localhost | mydbname | Query | 1 | Copying to tmp table | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 9132 | mydbusername | localhost | mydbname | Query | 0 | Copying to tmp table | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 9133 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9134 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9135 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9136 | mydbusername | localhost | mydbname | Query | 0 | Sending data | SELECT id FROM cntc_content WHERE `title`="__404__" |
| 9137 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9138 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
| 9139 | mydbusername | localhost | mydbname | Sleep | 0 | | NULL |
+------+------------------+-----------+-------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
24 rows in set (0.00 sec)
以下是 free -m 的输出：
free -m
total used free shared buffers cached
Mem: 3892 2284 1608 0 64 817
-/+ buffers/cache: 1401 2490
Swap: 6079 4 6075
谢谢大家，非常感谢！
答案1
我敢打赌，您遇到的查询问题并不理想，但优化程度不高。您的进程列表中有很多查询正在写入临时表，然后进行排序并返回。通常，这是查询缺少索引的标志。
测试查询的一个好方法是运行如下命令：
登录 MySQL 客户端。
运行带有 describe 的查询。
以下是您将看到的一个非常简单的示例：
mysql> describe select * from <table> where lockpid = 1234;
+----+-------------+-----------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | <table> | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
+----+-------------+-----------------------------+------+---------------+------+---------+------+------+-------------+
上面的 SQL 语句表明它没有使用索引，而是仅使用 where 子句返回。该表只有 9 行，因此必须搜索并返回所有行。在大型表中，这可能很糟糕！
mysql> describe select * from <table> where server = 'spgui01alncr';
+----+-------------+-----------------------------+------+----------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+------+----------------------+----------------------+---------+-------+------+-------------+
| 1 | SIMPLE | <table> | ref | server_last_used_idx | server_last_used_idx | 16 | const | 4 | Using where |
+----+-------------+-----------------------------+------+----------------------+----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
如果您的查询正确使用了索引，您应该会看到列出的可能的键以及返回和排序的较少的行。
还有其他技巧可以采用，我建议阅读有关 SQL 优化和 MySQL 性能调整的资料。