Is MySQL's SLEEP() function a busy-wait?

Question

I have a user who has inserted a number of "SELECT SLEEP(2);" queries into their code, and I am trying to figure out the impact of this query on database performance. The queries show up in the MySQL slow query log, and mk-tools report the queries as taking a substantial portion of exec time, though it's not clear if that equates to actual CPU time.

Answer 1

It is not a busy-wait, but it is not a useful thing to do either.

The MySQL sleep() function occupies the server thread for the length of the sleep, which means that it can't do other (more useful work) and uses up the thread stack in the mysql server process. These are not desirable things and should not be encouraged.

In some cases (depending on the context) locks may be held while sleep() is sleeping.

Of course the sleep() function is very useful for debugging, but I feel that it should not be in production code.

If a client-thread wants to sleep, it should sleep itself, not ask mysql to do it.