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 am having a problem with my CentOS server. From time to time, pages are opening way too slow, which in the end usually results with mysqld stop functioning. I know this ain't sufficient info, i don't know what is relevant and what's not, so please, can someone tell me what information do i need to provide for better understanding of the problem?

Here is the output from the top command:

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 

Thanks very much, any help is very appreciated!

Here is the mysql processlist output:

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)

and here is the output of 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

Thanks guys, really appreciate it!

share|improve this question
    
Your mysql daemon is using up a mysterious amount of cpu time, which (usually) has to do with a huge query of a kind. Can you log into your mysql and check the output of show processlist;? It might just be that there is a particular query your Mysql server is not liking –  qweet Feb 24 '12 at 13:51
    
Can you provide output for free -m? –  Bart De Vos Feb 24 '12 at 13:59
    
Thanks guys, i added mysql processlist and free -m output. Now, thing is, this doesn't last forever, it goes down once or twice a day. I should probably do this when it's not working correctly, and give you the info which is collected then, at the right time. Hope this can tell you something too. best regards! –  undernaut Feb 24 '12 at 14:42

1 Answer 1

I'm willing to bet that you are having an issue with a query that is well, less than optimized. Your processlist has a lot of queries that are writing to a temp table to then be sorted and returned. Typically, this is a sign of a query with a lack of indexes.

A good way to test your query would be to run somethign like this:

Log into the MySQL Client.
Run your query with a describe in front of it.

Here is a very quick example of what you will see:

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 |
+----+-------------+-----------------------------+------+---------------+------+---------+------+------+-------------+

This above SQL statement shows that it is not using an index and returning simply using a where clause. The table only has 9 rows in it so all rows have to be search through and returned. On a large table, this can be UGLY!

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)

If your query is using the index properly, you should see a possible keys listed and fewer rows that are returned and sorted.

There are other tricks that can be done and I'd recommend reading up on SQL optimization and MySQL performance turning.

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.