Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
It's great that you've posted so much information unfortunately there is a 30,000 character limit on questions and answers. Some of the information you provided may have been truncated. –  Iain Mar 19 '12 at 11:03

1 Answer 1

Stop using MyISAM, and tune your MySQL config to reduce the number of times you've got to copy to a tmp table to service a query. That'll improve performance considerably. General tuning would also do you good, no doubt, as always.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.