我正在使用 Apache 和 mod_php 来调优 Web 服务器上的 MySQL,以提高速度,因此我运行了mysqltuner结果如下:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 255M (Tables: 120)
[--] Data in InnoDB tables: 5M (Tables: 2)
[!!] Total fragmented tables: 14
-------- Security Recommendations -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'lovecpokladu'@'localhost' for table 'user'
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9h 37m 22s (6M q [185.754 qps], 286K conn, TX: 72B, RX: 767M)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (11% of installed RAM)
[OK] Slow queries: 0% (105/6M)
[OK] Highest usage of available connections: 33% (50/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/48.9M
[OK] Key buffer hit rate: 99.9% (378M cached / 497K reads)
[OK] Query cache efficiency: 83.2% (4M cached / 5M selects)
[!!] Query cache prunes per day: 84442
[OK] Sorts requiring temporary tables: 6% (10K temp sorts / 159K sorts)
[!!] Joins performed without indexes: 403
[OK] Temporary tables created on disk: 25% (79K on disk / 312K total)
[OK] Thread cache hit rate: 99% (936 created / 286K connections)
[!!] Table cache hit rate: 0% (64 open / 102K opened)
[OK] Open file limit used: 10% (106/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] Connections aborted: 49%
[OK] InnoDB data size / buffer pool: 5.6M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (> 16M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)
我的 PDO 初始化脚本:
$pdo = new PDO("mysql:host=localhost;dbname=...;charset=utf8", $user, $pass, array("SET NAMES utf8"));
$pdo->exec("SET CHARACTER SET utf8");
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
它说[!!] Connections aborted: 49%
,我应该担心吗?我使用 PDO 从 PHP 进行非持久性数据库连接。连接应该在脚本结束时关闭,所以我不明白为什么连接中断率这么高。或者即使它真的很重要。
多谢!
答案1
如果 mysql 客户端(在本例中是您的 PHP 脚本)在退出之前未能执行 mysql_close,MySQL 将记录中止的连接并增加中止的客户端状态变量。
来自 MySQL 文档:通信错误和中止连接
如果客户端成功连接但后来不当断开连接或终止,则服务器将增加 Aborted_clients 状态变量,并将 Aborted 连接消息记录到错误日志中。原因可能是以下任一原因:
The client program did not call mysql_close() before exiting. The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server. See Section 5.1.4, “Server System Variables”.
我不是 PHP 程序员,但是我从系统管理员方面的工作中知道,如果您没有正确关闭 MySQL 连接,您可以在 MySQL 日志中看到这些消息。
另请参阅文档:连接和连接管理
成功连接到数据库后,PDO 类的一个实例将返回到您的脚本。该连接在该 PDO 对象的生命周期内保持活动状态。要关闭连接,您需要销毁该对象,方法是确保删除对该对象的所有剩余引用 - 您可以通过将 NULL 分配给保存该对象的变量来执行此操作。如果您没有明确执行此操作,PHP 将在脚本结束时自动关闭连接。
尽快关闭连接
实际上,我建议您在完成后立即明确关闭 MySQL 连接。
想象一下在高负载条件下的情况:
- PHP 脚本启动
- MySQL 连接
- MySQL 查询执行
- PHP代码执行
- PHP 脚本退出
在这种情况下,如果由于高负载,PHP 代码执行需要 5 秒,则 MySQL 连接处于打开状态。将此时间乘以每秒 1000 个请求,您很容易耗尽 MySQL 连接。通过明确关闭连接,您可以避免此问题和其他问题。