前一段时间遇到了一个线上慢查询故障,导致应用返回504, 最终通过优化索引彻底解决了问题。后来就想,可不可以设置一个功能自动降级机制,如果查询时间过长,就自动断开,隐藏相关功能模块。做了一下调查,通过设置MySQL查询超时就可以满足这个自动降级机制。
PDO扩展可以设置读超时,MySQLi可以设置读写超时。
第一部分 PDO设置读超时
一、确认PHP是否安装了mysqlnd扩展,以及pdo启动了mysqlnd扩展。
php -i |grep -C 10 -i pdo
如果Client API 显示mysqlnd, 那么就没问题,否则需要重新编译PHP, 带上一下编译参数
--with-pdo-mysql=shared,mysqlnd
二、设置读超时时间
通过mysqlnd.net_read_timeout来配置,单位是秒。
0、如果PHP version 小于 7.2.0,修改范围PHP_INI_SYSTEM,即只能在php.ini中修改。
# 单位是s mysqlnd.net_read_timeout=1
1、如果是version >= 7.2.0,修改范围是PHP_INI_ALL,可以在任何地方修改。
可以在代码中通过ini_set来设置
ini_set('mysqlnd.net_read_timeout', 1);
详情可参考PHP手册上的说明MySQL Native Driver Configuration Options
mysqlnd.net_read_timeout "86400" PHP_INI_ALL Available since PHP 5.3.0. Before PHP 7.2.0 the default value was "31536000" and the changeability was PHP_INI_SYSTEM
2、测试例子
以下代码保存文件pdo_read_timeout.php
ini_set('mysqlnd.net_read_timeout', 1); class MySQL_PDO_Helper { public function readTimeoutTest() { $dsn = 'mysql:dbname=test;host=127.0.0.1;port=3306'; $user = 'root'; $password = ''; $startTime = microtime(true); try { $dbh = new PDO($dsn, $user, $password); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sth = $dbh->query('select sleep(5)'); $row = $sth->fetch(); $dbh = null; } catch (PDOException $e) { $msg = 'MySQL Error: (' . $dsn . ';password)' . $e->getCode() . '=>' . $e->getMessage(); } $endTime = microtime(true); $intervalTime = round(($endTime - $startTime) * 1000, 3) . 'ms'; var_dump($intervalTime, $row, $msg); exit; } } // test case $db = new MySQL_PDO_Helper(); $db->readTimeoutTest();
执行结果:
php7 pdo_read_timeout.php PHP Warning: PDO::query(): MySQL server has gone away PHP Warning: PDO::query(): Error reading result set's header string(10) "1002.239ms" NULL string(136) "MySQL Error: (mysql:dbname=test;host=127.0.0.1;port=3306;password)HY000=>SQLSTATE[HY000]: General error: 2006 MySQL server has gone away"
说明:
需要设置ATTR_ERRMODE属性,才能捕捉到异常。(异常信息是MySQL server has gone away,并不是很清晰的提示查询连接断开)
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
3、对比测试结果可以通过修改sql语句。
例如:
$sth = $dbh->query('select sleep(5), name, rank from language limit 5;');
第二部分 MySQLi设置读写超时。
通过设置MYSQL_OPT_READ_TIMEOUT和MYSQL_OPT_WRITE_TIMEOUT来完成, 这两个常量对于的值分别是11和12,在MySQLi中没定义,需要自己定义。
一、测试代码
以下代码保存文件mysqli_read_timeout.php
class MySQL_MySQLi_Helper { public function readTimeout() { // 自己定义读写超时常量 if (!defined('MYSQL_OPT_READ_TIMEOUT')) { define('MYSQL_OPT_READ_TIMEOUT', 11); } if (!defined('MYSQL_OPT_WRITE_TIMEOUT')) { define('MYSQL_OPT_WRITE_TIMEOUT', 12); } // 设置超时 $mysqli = mysqli_init(); $mysqli->options(MYSQL_OPT_READ_TIMEOUT, 5); $mysqli->options(MYSQL_OPT_WRITE_TIMEOUT, 1); // 连接数据库 $mysqli->real_connect('127.0.0.1', 'root', '', 'test'); if ($mysqli->connect_errno) { printf('Connect failed: %s' . PHP_EOL, $mysqli->connect_error); exit(); } // 执行查询 sleep 1秒不超时 printf('Host information: %s' . PHP_EOL, $mysqli->host_info); if (!($res = $mysqli->query('select sleep(1)'))) { echo 'Query1 error: ' . $mysqli->error . PHP_EOL; } else { echo 'Query1: query success' . PHP_EOL; } // 执行查询 sleep 9秒会超时 if (!($res = $mysqli->query('select sleep(9)'))) { echo 'Query2 error: ' . $mysqli->error . PHP_EOL; } else { echo 'Query2 success: ' . PHP_EOL; } $mysqli->close(); echo 'close mysql connection ' . PHP_EOL; } } $db = new MySQL_MySQLi_Helper(); $db->readTimeout();
执行结果
[root@izj6cfhaw27k49x8usszs3z db]# php7 mysqli_read_timeout.php Host information: 127.0.0.1 via TCP/IP Query1: query success PHP Warning: mysqli::query(): MySQL server has gone away in /home/chuanbo7/unit_test/db/mysqli_read_timeout.php on line 34 Warning: mysqli::query(): MySQL server has gone away in /home/chuanbo7/unit_test/db/mysqli_read_timeout.php on line 34 PHP Warning: mysqli::query(): Error reading result set's header in /home/chuanbo7/unit_test/db/mysqli_read_timeout.php on line 34 Warning: mysqli::query(): Error reading result set's header in /home/chuanbo7/unit_test/db/mysqli_read_timeout.php on line 34 Query2 error: MySQL server has gone away close mysql connection
二、说明
0、超时设置单位为秒,最少配置1秒
1、mysql底层的read会重试两次,所以实际会是3秒(重试两次 + 自身一次 = 3倍超时时间)
第三部分 思考
一、读超时的场景比较场景,写超时什么时候会触发?
二、读超时后,如果记录日志,提示信息并不是很明确。
第四部分 错误总结
0、Getting error mysqli::real_connect(): (HY000/2002): No such file or directory
测试连接MySQL的时候尽量用’127.0.0.1’代替localhost
参考:
风雪之隅:为MySQL设置查询超时
CSDN黑夜路人:PHP访问MySQL查询超时处理
博学无忧:php中如何设置mysql查询读取数据的超时时间
Petrie:PHP设置连接mysql超时时间