PHP操作MySQL设置读写超时实践笔记

前一段时间遇到了一个线上慢查询故障,导致应用返回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超时时间

发表评论

电子邮件地址不会被公开。 必填项已用*标注