CODEWITHSUNDEEP
phpmysql
TamTam
TamTam

Reputation: 849

PHP, MySQL and "sleeping" connections

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.

Upvotes: 4

Views: 4115

Answers (3)

Mas
Mas

Reputation: 1317

change wait_timeout and interactive_timeout variables to small values

maybe changed in my.cnf file in centos

Upvotes: 0

thomasrutter
thomasrutter

Reputation: 117401

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.

Upvotes: 2

Mchl
Mchl

Reputation: 62395

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

Upvotes: 0

Related Questions