我的 CentOS 服务器出了问题。mysqld 有时会出现异常，消耗大量 CPU，这通常会导致其宕机。重新启动后，它会运行一段时间，然后开始正常运行，或者再次宕机。这种情况大约每周重复一次。
以下是 mysql show processlist 的输出：
mysql> show processlist;
+------+------------------+-----------+-------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------------------+-----------+-------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 1019 | my_user | localhost | my_db | Sleep | 37 | | NULL |
| 1030 | my_user | localhost | my_db | Query | 2 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1031 | my_user | localhost | my_db | Query | 2 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1042 | my_user | localhost | my_db | Query | 2 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1043 | my_user | localhost | my_db | Sleep | 124 | | NULL |
| 1049 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1054 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1057 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1060 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1063 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1064 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1065 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1067 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1073 | my_user | localhost | my_db | Query | 4 | Locked | SELECT a.*,cc.description as catdesc, cc.title as cattitle,s.description as secdesc, s.title as sect |
| 1081 | my_user | localhost | my_db | Query | 4 | Locked | SELECT a.*,cc.description as catdesc, cc.title as cattitle,s.description as secdesc, s.title as sect |
| 1082 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1085 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1086 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1087 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 1097 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1098 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1099 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1106 | my_user | localhost | my_db | Query | 6 | Locked | SELECT a.*,cc.description as catdesc, cc.title as cattitle,s.description as secdesc, s.title as sect |
| 1108 | my_user | localhost | my_db | Query | 3 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1110 | my_user | localhost | my_db | Query | 3 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1111 | my_user | localhost | my_db | Query | 3 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1112 | my_user | localhost | my_db | Query | 3 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1114 | my_user | localhost | my_db | Query | 3 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1115 | my_user | localhost | my_db | Query | 3 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1117 | my_user | localhost | my_db | Query | 1 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1118 | my_user | localhost | my_db | Query | 3 | Locked | SELECT a.title AS title,a.id as id FROM cntc_content as a LEFT JOIN cntc_sections AS s ON a.sectio |
| 1125 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1131 | my_user | localhost | my_db | Query | 9 | 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 |
| 1132 | my_user | localhost | my_db | Query | 9 | 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 |
| 1139 | my_user | localhost | my_db | Query | 10 | 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 |
| 1140 | my_user | localhost | my_db | Query | 9 | 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 |
| 1141 | my_user | localhost | my_db | Query | 9 | 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 |
| 1142 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1143 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1144 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1146 | my_user | localhost | my_db | Query | 11 | Copying to tmp table | SELECT cc.title AS category, a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.secti |
| 1147 | my_user | localhost | my_db | Query | 10 | 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 |
| 1149 | my_user | localhost | my_db | Sleep | 1 | | NULL |
| 1150 | my_user | localhost | my_db | Query | 1 | Sending data | select oldurl, newurl, id, dateadd from cntc_redirection where newurl <> "" AND soundex(oldurl) = s |
| 1151 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=107 |
| 1152 | my_user | localhost | my_db | Query | 9 | 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 |
| 1153 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1154 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1155 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1156 | my_user | localhost | my_db | Query | 6 | Locked | UPDATE cntc_content SET hits = ( hits + 1 ) WHERE id='8659' |
| 1157 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1158 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_fireboard&Itemid=1 |
| 1159 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1160 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_fireboard&Itemid=1 |
| 1161 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, dateadd FROM cntc_redirection WHERE newurl = 'index.php?option=com_content&Itemid=96& |
| 1162 | my_user | localhost | my_db | Query | 1 | Locked | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug, |
| 1163 | my_user | localhost | my_db | Query | 9 | 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 |
| 1164 | my_user | localhost | my_db | Query | 2 | Locked | SELECT a.*, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug |
| 1165 | my_user | localhost | my_db | Query | 1 | Locked | SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section, CASE WHEN CHAR_L |
| 1166 | my_user | localhost | my_db | Query | 1 | Locked | SELECT oldurl, newurl FROM cntc_redirection WHERE oldurl = 'stefan-karganovic-seselj-nece-biti-oslob |
| 1167 | my_user | localhost | my_db | Query | 1 | Locked | SELECT * FROM cntc_redirection WHERE oldurl = 'vesti/36-politika/8600-ostoja-simeti-jedno-pokajniko- |
| 1168 | my_user | localhost | my_db | Query | 1 | Locked | UPDATE cntc_redirection SET cpt=(cpt+1) WHERE `newurl` = 'index.php?option=com_content&Itemid=1&form |
+------+------------------+-----------+-------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
62 rows in set (0.00 sec)
顶部命令输出：
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1659 mysql 20 0 182m 52m 4552 S 124.9 1.4 33:13.22 mysqld
2911 apache 20 0 63000 30m 6812 R 16.3 0.8 0:09.07 httpd
3533 apache 20 0 93720 60m 6816 R 12.3 1.6 0:06.94 httpd
2973 apache 20 0 93720 58m 6820 S 12.0 1.5 0:11.72 httpd
3408 apache 20 0 61976 30m 6804 S 11.3 0.8 0:06.03 httpd
3671 apache 20 0 73240 41m 6820 S 7.3 1.1 0:05.96 httpd
2409 apache 20 0 95768 64m 6832 S 6.6 1.6 0:17.51 httpd
1132 root 20 0 0 0 0 S 3.7 0.0 1:40.61 flush-253:0
2906 apache 20 0 93720 59m 6820 S 3.0 1.5 0:10.38 httpd
422 root 20 0 0 0 0 S 0.3 0.0 0:03.31 jbd2/dm-0-8
2959 apache 20 0 57876 25m 6820 S 0.3 0.7 0:14.38 httpd
4112 apache 20 0 56852 24m 6764 S 0.3 0.6 0:03.21 httpd
4161 apache 20 0 50680 19m 6500 S 0.3 0.5 0:00.33 httpd
1 root 20 0 2832 1392 1188 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.04 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:00.40 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.00 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.10 kblockd/0
21 root 20 0 0 0 0 S 0.0 0.0 0:00.01 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
27 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ata_aux
28 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ksuspend_usbd
29 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khubd
30 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kseriod
33 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khungtaskd
34 root 20 0 0 0 0 S 0.0 0.0 0:00.10 kswapd0
35 root 25 5 0 0 0 S 0.0 0.0 0:00.00 ksmd
36 root 20 0 0 0 0 S 0.0 0.0 0:00.00 aio/0
37 root 20 0 0 0 0 S 0.0 0.0 0:00.00 aio/1
38 root 20 0 0 0 0 S 0.0 0.0 0:00.00 crypto/0
39 root 20 0 0 0 0 S 0.0 0.0 0:00.00 crypto/1
44 root 20 0 0 0 0 S 0.0 0.0 0:00.00 pciehpd
46 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kpsmoused
47 root 20 0 0 0 0 S 0.0 0.0 0:00.00 usbhid_resumer
78 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kstriped
156 root 20 0 0 0 0 S 0.0 0.0 0:00.00 i915
157 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kslowd000
158 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kslowd001
282 root 20 0 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_0
免费-m输出：
total used free shared buffers cached
Mem: 3892 1217 2674 0 25 275
-/+ buffers/cache: 916 2975
Swap: 6079 0 6079
我前段时间问过这个问题，但当时服务器没有问题，所以我无法获取相关数据。现在我重启了服务器，它又开始卡了，所以我希望有人能指出问题所在。
非常感谢您的任何提示，真的很感激！
答案1
停止使用 MyISAM，并调整 MySQL 配置以减少您必须复制到临时表以服务查询的次数。这将大大提高性能。毫无疑问，常规调整也会对您有好处，一如既往。