I'm having problems with my CentOS server. From time to time, mysqld starts acting weird, consuming a lot of CPU, which usually results in it going down. After restart it works slow for some time, and then it either starts working OK or it goes down again. This repeats once in a week or so.
Here's the output from 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)
Top command output:
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
free -m output:
total used free shared buffers cached
Mem: 3892 1217 2674 0 25 275
-/+ buffers/cache: 916 2975
Swap: 6079 0 6079
I asked this question some time ago, but i didn't have problems with the server at that moment so i couldn't get relevant data. Now i restarted it and it drags again so i hope something will point out the problem.
Thanks very much for any hint, really appreciate it!