PHP, MySQL and "sleeping" connections

Question

Hi Everyone,

I am running a web service powered by PHP/MySQL and recently noticed a strange issue.

When I run "show full processlist;" SQL command via SSH terminal, I notice several "sleeping" connections:

| Id        | User    | Host      | db      | Command | Time | State | Info                  |
+-----------+---------+-----------+---------+---------+------+-------+-----------------------+
| 218121282 | test_user | localhost | test_db | Sleep   |    0 | NULL  | NULL                  |

Are these "Sleeping" connections happening because of not using mysql_close() at the end of scripts?

If this is the reason, can I solve this issue by using "register_shutdown_function" PHP function and running mysql_close() on the callback function?

Thanks for your suggestions.

Answer 1

Are you using persistent connections ( mysql_pconnect() )? If not configured properly they have this habit of staying around indefinitely.

Answer 2

Are these "Sleeping" connections happening because of not using mysql_close() at the end of scripts?

No. MySQL connections are closed at the end of the script anyway, unless they're persistent.

These sleeping connections mean that you are either using persistent connections, or there is an issue somewhere causing your MySQL connections to remain open for a long time, either because the PHP script is running very long, or something else.

If you don't know whether you need persistent connections it might be a good idea to turn them off. Contrary to popular advice, in a lot of situations they may actually create more problems than they solve, such as causing you to run out of resources a lot more quickly. However, there are some specific situations in which they're needed, but you'd need to know what you are doing and what not to do with them to avoid situations where you might be opening and leaving hundreds of connections sleeping for a long time, etc.