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

what is the meaning of sleep in mysql proccess and how could avoid it because of highload?

Id  User    Host    db  Command Time    State   Info
93714   mydb    localhost   xyz Sleep   2       NULL
93715   root    localhost   NULL    Query   0   NULL    show full processlist

here is highload:

top - 00:31:35 up 21 days,  9:27,  1 user,  load average: 12.67, 6.68, 2.94
Tasks:  97 total,   1 running,  96 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.3%us,  0.6%sy,  0.0%ni, 94.3%id,  3.8%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1022732k total,   614528k used,   408204k free,    84316k buffers
Swap:        0k total,        0k used,        0k free,   135992k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            
  322 mysql     20   0  413m 173m 7452 S   99 17.4  97:42.25 mysqld             
    1 root      20   0  8356  688  560 S    0  0.1   0:15.61 init       
share|improve this question

2 Answers 2

Sleep indicates that either your process is waiting for something (for instance I/O). Sleeping is the normal state when a process is doing nothing. However in your case it seems (considering the high load and high CPU) that the process is sleeping because you do not have enough CPU power. I would advice to increase your CPU capacity.

share|improve this answer

Sleeping processes are not normally something to be concerned about until you have so many that you no longer have any available connections on the mysql server.

All it basically means is that a process is still holding a connection open, typically a script which opened the connection has not yet closed it because it hasn't ended yet or is doing some processing before using the connection again. It could also be that your client is configured to use connection pooling in which case connections are left open to save the overhead of creating new connections all the time.

MySQL automatically kills of these processes after wait_timeout or interactive_timeout seconds has passed anyway.

share|improve this answer
    
is it possible that this came from an endless php process? for example from a set_timeout(0) etc. –  exim Jan 2 '13 at 13:12
    
definately possible but mysql should have killed it off after wait_timeout or interactive_timeout passed? –  Jason Jan 2 '13 at 13:20
    
I just reviewed my.cnf and saw this: wait_timeout = 1000 :| –  exim Jan 2 '13 at 13:28
1  
that's probably a bit high :) I would leave it at 30s and change it per session in the specific scripts which require it to be longer. –  Jason Jan 2 '13 at 13:37

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.