如何确定哪个 PHP 代码打开了未关闭的 MySQL 连接
我们的应用程序由几个现成的 PHP 应用程序（ExpressionEngine 和 XCart）以及我们自己的自定义代码组成。
我没有进行实际分析，所以我不知道它是如何确定的，但是听到有太多 MySQL 连接未关闭，我并不感到惊讶（我并不感到惊讶，因为我在我们的开发中看到了严重的内存泄漏服务器，在一两天的时间里，从首次启动时的 100MB 开始，整个 RAM 都被消耗掉，并且只有很少的一部分被缓存）。
那么，我们如何准确确定哪个 PHP 代码是罪魁祸首呢？我之前有使用 XDebug 的经验，并建议，当我们获得相当稳定的单独的临时环境时，我们在开发上改造 XDebug 并使用它来进行一些分析。这是否合理，和/或其他人是否有更具体和/或额外的建议？
We have an application that is comprised of a couple of off the shelf PHP applications (ExpressionEngine and XCart) as well as our own custom code.
I did not do the actual analysis so I don't know precisely how it was determined, but am not surprised to hear that too many MySQL connections are being left unclosed (I am not surprised because I have been seeing significant memory leakage on our dev server, where over the course of a day or two, starting from 100MB upon initial boot, the entire gig of ram gets consumed, and very little of it is cached).
So, how do we go about determining precisely which PHP code is the culprit? I've got prior experience with XDebug, and have suggested that, when we've gotten our separate, staging environment reasonably stable, that we retrofit XDebug on dev and use that to do some analysis. Is this reasonable, and/or does anybody else have more specific and/or additional suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您可以使用
SQL 命令查看正在运行的进程。这将告诉您每个进程正在使用的用户名、主机、数据库等。这应该能让您了解发生了什么，特别是当您有多个数据库被访问时。
更多信息请参见： https://dev.mysql.com/doc /refman/8.0/en/show-processlist.html
You can use the
SQL command to see what processes are running. That will tell you the username, host, database, etc that are in use by each process. That should give you some idea what's going on, especially if you have a number of databases being accessed.
More here: https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html
这不应该是由 php 代码引起的，因为 mysql 连接应该自动关闭。
cf : http://www.php.net/manual/function.mysql- connect.php ：
一些建议：
This should not be caused by a php code because mysql connections are supposed to be automatically closed.
cf : http://www.php.net/manual/function.mysql-connect.php :
Some suggestions :
当页面结束时，PHP 会自动关闭所有 mysql 连接。 PHP Web 应用程序具有太多未关闭的 mysql 连接的唯一原因是 1) 您正在使用连接池，或者 2) mysql 服务器或连接器中存在错误。
但如果您确实想查看代码以查找其连接位置，请参阅 http://xdebug.org/docs /分析器
PHP automatically closes any mysql connections when the page ends. the only reason that a PHP web application would have too many unclosed mysql connections is either 1) you're using connection pooling, or 2) there's a bug in the mysql server or the connector.
but if you really want to look at your code to find where it's connecting, see http://xdebug.org/docs/profiler
正如其他人所说，PHP 终止通过 mysql_connect 或 msqli/PDO 等效项创建的 MySQL 连接。
但是，您可以使用 mysql_pconnect 创建持久连接。它将查找打开的现有连接并使用这些连接；如果找不到，它将打开一个新的。如果您同时收到大量请求，则可能会导致大量连接打开并保持打开状态。
您可以降低最大连接数，或降低持久连接的超时时间。有关更多信息，请参阅手册页底部的注释细节。
As others said, PHP terminates MySQL connections created through
mysql_connector the msqli/PDO equivalents.However, you can create persistent connections with
mysql_pconnect. It will look for existing connections open and use those; if it can't find one, it will open a new one. If you had a lot of requests at once, it could have caused loads of connections to open and stay open.You could lower the maximum number of connections, or lower the timeout for persistent connections. See the comments at the bottom of the man page for more details.
我曾经运行一个轮询 SHOW STATUS 来获取线程计数的脚本，我注意到使用 mysql_pconnect 总是会鼓励大量线程。我发现这非常令人不安，因为那时我无法判断我的连接速率实际上何时下降。所以我确保集中所有调用 mysql_connect() 的地方并消除 mysql_pconnect()。
我做的下一件事是查看连接超时并将其调整为 30 秒左右，因为。因此，我将 my.cnf 调整为
connect-timeout=30，
这样我实际上可以看到连接数下降。要确定需要打开的连接数，取决于您正在运行的 apache 工作线程数量乘以它们各自将打开的数据库连接数。
我开始做的另一件事是在查询中添加注释，以便在 SHOW PROCESSLIST 或 mytop 中找到它们，我会在结果中添加注释列，例如：
这将向我显示当我查看 mytop 时发出查询的文件， 使用那样破坏 MySQL 查询缓存。
它并没有像在查询开始时
I used to run a script that polled SHOW STATUS for thread count and I noticed that using mysql_pconnect always encouraged high numbers of threads. I found that very disconcerting because then I couldn't tell when my connection rate was actually dropping. So I made sure to centralize all the places where mysql_connect() was called and eliminate mysql_pconnect().
The next thing I did was look at the connection timeouts and adjust them to more like 30 seconds because. So I adjusted my my.cnf with
connect-timeout=30
so I could actually see the number of connections drop off. To determine the number of connections you need open is dependent on how many apache workers you're running times the number of database connections they each will open.
The other thing I started doing was adding a note to my queries in order to spot them in SHOW PROCESSLIST or mytop, I would add a note column to my results like:
This would show me the file issuing the query when I looked at mytop, and it didn't foil the MySQL query cache like using
at the start of my query would.
我想我可以做的另外几件事，关于一般内存问题，与 MySQL 不同，特别是在我们自己的自定义代码的上下文中，将通过调用其中一个或另一个来包装我们的代码。以下 PHP 内置函数：
memory_get_usagememory_get_peak_usage特别是因为我目前正在从一些自定义代码进行日志记录，所以我可以记录内存使用情况我正在做
I suppose another couple of things I can do, with regard to the general memory issue, as opposed specifically to MySQL, and particularly within the context of our own custom code, would be to wrap our code with calls to one or the other of the following PHP built-in functions:
memory_get_usagememory_get_peak_usageIn particular since I am currently working on logging from some custom code, I can log the memory usage while I'm at it