1

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       

2 Answers 2

1

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.

4
  • is it possible that this came from an endless php process? for example from a set_timeout(0) etc.
    – exim
    Jan 2, 2013 at 13:12
  • definately possible but mysql should have killed it off after wait_timeout or interactive_timeout passed?
    – Jason
    Jan 2, 2013 at 13:20
  • I just reviewed my.cnf and saw this: wait_timeout = 1000 :|
    – exim
    Jan 2, 2013 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, 2013 at 13:37
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .