php - PDO 连接首次查询延迟

Question

我有一个站点使用 PHP 的 PDO 库来访问 mysql 数据库。 mysql 数据库经过高度优化，并具有所有合适的索引以加快查询速度等。尽管与为特定 Web 服务运行的第一个查询有关，但我遇到了一些奇怪的行为。

这个特定的网络服务对数据库运行查询并返回一个 json 响应，然后将其提供给 jquery 自动完成。

第一次在客户端运行的查询需要大约 2 秒才能运行，之后它下降到百分之一秒，大概是由于 innodb 缓存。

如果我在新 session 期间在自动完成框中键入一个条目，那么第一个查询响应可能需要 5 秒以上，之后返回响应变得非常快。如果我随后离开该站点一段时间，即可能一个小时(不是一个精确的度量，但为了争论，一个相对较长的时间段)并返回它，再次观察到同样缓慢的第一次查询行为。

由于连接中的服务器上的连接数量有限，我出于需要使用了持久连接。

我想知道你们是否有任何想法可以让我进一步减少初始延迟。

$DBH = null;

$host = "127.0.0.1";
$db_name = "my_db";
$user_name = "me";
$pass_word = "something";

try {
  # MySQL with PDO_MYSQL
  $DBH = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $user_name, $pass_word, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch(PDOException $e) {
    error_log( $e->getMessage(), 0 );
}

更新了答案

正确的人经过大量测试并彻底检查这不是 dns 问题后，我去检查 innodb 缓冲池路由。不管怎样，我写了一个存储过程，它使用查询为数据库中的每个表生成一个查询，从而使它们被缓存在 innodb_buffer_pool 中。为每个表生成 sql 查询的查询来自 following SO question .我只对该查询进行了一次编辑，并将其放入 database() 函数中，以便它可以从调用它的任何数据库中运行。

我还对其进行了设置，以便可以通过 PHP 调用它而无需等待脚本完成，以便您的正常应用程序继续运行。

我希望这对某人有所帮助。另外，为了提高效率，您可以将 exec 冷包装在一个小函数中，以便仅在特定时间运行它等等。

MySQL 存储过程 SQL

DELIMITER $$

USE `your_db_name`$$

DROP PROCEDURE IF EXISTS `innodb_buffer_pool_warm_up`$$

CREATE DEFINER=`user_name`@`localhost` PROCEDURE `innodb_buffer_pool_warm_up`()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE sql_query VARCHAR(1000) DEFAULT NULL;
DECLARE sql_cursor CURSOR FOR
SELECT 
  CONCAT('SELECT `',MIN(c.COLUMN_NAME),'` FROM `',c.TABLE_NAME,'` WHERE `',MIN(c.COLUMN_NAME),'` IS NOT NULL')
FROM
  information_schema.COLUMNS AS c
LEFT JOIN (
  SELECT DISTINCT
    TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
  FROM
    information_schema.KEY_COLUMN_USAGE
) AS k
USING
  (TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
WHERE
  c.TABLE_SCHEMA = DATABASE()
  AND k.COLUMN_NAME IS NULL
GROUP BY
  c.TABLE_NAME;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN sql_cursor;
read_loop: LOOP
    FETCH sql_cursor INTO sql_query;
    IF done THEN
      LEAVE read_loop;
    END IF;

SET @stmt_sql = sql_query;
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;

END LOOP;
CLOSE sql_cursor;
END$$
DELIMITER ;

PHP调用存储过程

innodb_warm_up_proc_call.php

<?php
$DBH = null;

$host = "localhost";
$db_name = "your_db_name";
$user_name = "user_name";
$pass_word = "password";

try {
  # MySQL with PDO_MYSQL
  $DBH = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $user_name, $pass_word, array(PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
  $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

  $sql = "CALL innodb_buffer_pool_warm_up()";
  $STH = $DBH->prepare( $sql );
  $STH->execute();

    }catch( PDOException $e ) {
        error_log( $e->getMessage() . ' in ' .$e->getFile(). ' on line ' .$e->getLine(), 0 );
    }
?>

PHP 静默运行上述脚本，无需等待它完成

innodb_warm_up.php

<?php

$file_to_execute = dirname(__FILE__) . "/innodb_warm_up_proc_call.php";

//Run the stored procedure but don't wait around for a chat
exec("php -f {$file_to_execute} >/dev/null 2>&1 &");

?>

Answer 1

寻址特定网络服务时，将其域名更改为 IP 地址。
它很可能会消除这种延迟(由 DNS 查找引起)