什么可能导致PHP和MySQL之间出现奇怪的查询超时？

Question

什么可能导致PHP和MySQL之间出现奇怪的查询超时？

13

我是一名高级开发人员，负责开发一个被许多不同客户使用的软件即服务应用程序。我们的软件运行在一组由Apache / PHP应用服务器组成的集群上，由MySQL后端提供支持。在软件的一个特定实例中，当客户拥有超过29个类别时，用于查询类别名称列表的PHP代码会超时。我知道这没有任何意义；数字30并没有什么特殊之处，不会导致此问题，其他客户拥有的类别数量远远超过30个，然而，当这个安装实例拥有30个或更多类别时，问题就会100%重现，并且当类别少于30个时问题消失。涉及的表格为：

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  `title` varchar(128) NOT NULL,
  `parent` int(10) unsigned NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` enum('Active','Inactive','_Deleted','_New') NOT NULL default 'Active',
  `style` enum('_Unknown') default NULL COMMENT 'Autoenum;',
  `order` smallint(5) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `modified_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `parent` (`parent`),
  KEY `created_at` (`created_at`),
  KEY `modified_at` (`modified_at`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='R2' AUTO_INCREMENT=33 ;

这段代码递归地查询表格以获取所有的分类。它发出一个

SELECT * FROM `categories` WHERE `parent`=0 ORDER BY `order`,`name`

然后对返回的每一行重复执行此查询，但每次使用WHERE parent=$category_id。（我相信这个过程可以改进，但那可能是另一个问题）据我所知，以下查询似乎永远挂起：

SELECT * FROM `categories` WHERE `parent`=22 ORDER BY `order`,`name`

我可以在服务器上的mysql客户端中完美地执行这个查询，也可以在PHPMyAdmin中执行它而没有问题。请注意，问题不是那个具体的查询。如果我执行“DELETE FROM categories WHERE id=22”，那么一个类似上面的不同查询将会挂起。此外，上面的查询在我手动运行时返回零行。我怀疑表可能损坏了，所以我尝试了“REPAIR TABLE”和“OPTIMIZE TABLE”，但是这两个都没有报告问题或解决问题。我删除了表并重新创建，但问题又出现了。这个表结构和PHP代码与其他客户使用的完全相同，其他客户包括有超过30个类别的客户都没有任何问题。 PHP代码并不是无限递归。（这不是无限循环） MySQL服务器正在运行CentOS Linux，mysqld版本为5.0.92-community for pc-linux-gnu on i686（MySQL Community Edition (GPL)）。 MySQL服务器的负载很低：平均负载为0.58、0.75、0.73，CPU使用率为4.6%us、2.9%sy、0.0%ni、92.2%id、0.0%wa、0.0%hi、0.3%si、0.0%st。几乎没有使用交换空间（448k）。我应该如何解决这个问题？有什么可能导致这种情况的建议吗？更新：我使用TRUNCATE命令清空了表，并插入了30行虚拟数据。

INSERT INTO `categories` (`id`, `name`, `title`, `parent`, `keywords`, `description`, `status`, `style`, `order`, `created_at`, `modified_at`) VALUES
(1, 'New Category', '', 0, '', '', 'Inactive', NULL, 1, '2011-10-25 12:06:30', '2011-10-25 12:06:34'),
(2, 'New Category', '', 0, '', '', 'Inactive', NULL, 2, '2011-10-25 12:06:39', '2011-10-25 12:06:40'),
(3, 'New Category', '', 0, '', '', 'Inactive', NULL, 3, '2011-10-25 12:06:41', '2011-10-25 12:06:42'),
(4, 'New Category', '', 0, '', '', 'Inactive', NULL, 4, '2011-10-25 12:06:46', '2011-10-25 12:06:47'),
(5, 'New Category', '', 0, '', '', 'Inactive', NULL, 5, '2011-10-25 12:06:49', NULL),
(6, 'New Category', '', 0, '', '', 'Inactive', NULL, 6, '2011-10-25 12:06:51', '2011-10-25 12:06:52'),
(7, 'New Category', '', 0, '', '', 'Inactive', NULL, 7, '2011-10-25 12:06:53', '2011-10-25 12:06:54'),
(8, 'New Category', '', 0, '', '', 'Inactive', NULL, 8, '2011-10-25 12:06:56', '2011-10-25 12:06:57'),
(9, 'New Category', '', 0, '', '', 'Inactive', NULL, 9, '2011-10-25 12:06:59', '2011-10-25 12:06:59'),
(10, 'New Category', '', 0, '', '', 'Inactive', NULL, 10, '2011-10-25 12:07:01', '2011-10-25 12:07:01'),
(11, 'New Category', '', 0, '', '', 'Inactive', NULL, 11, '2011-10-25 12:07:03', '2011-10-25 12:07:03'),
(12, 'New Category', '', 0, '', '', 'Inactive', NULL, 12, '2011-10-25 12:07:05', '2011-10-25 12:07:05'),
(13, 'New Category', '', 0, '', '', 'Inactive', NULL, 13, '2011-10-25 12:07:06', '2011-10-25 12:07:07'),
(14, 'New Category', '', 0, '', '', 'Inactive', NULL, 14, '2011-10-25 12:07:08', '2011-10-25 12:07:09'),
(15, 'New Category', '', 0, '', '', 'Inactive', NULL, 15, '2011-10-25 12:07:11', '2011-10-25 12:07:12'),
(16, 'New Category', '', 0, '', '', 'Inactive', NULL, 16, '2011-10-25 12:07:13', '2011-10-25 12:07:14'),
(17, 'New Category', '', 0, '', '', 'Inactive', NULL, 17, '2011-10-25 12:09:41', '2011-10-25 12:09:42'),
(18, 'New Category', '', 0, '', '', 'Inactive', NULL, 18, '2011-10-25 12:09:47', NULL),
(19, 'New Category', '', 0, '', '', 'Inactive', NULL, 19, '2011-10-25 12:09:48', NULL),
(20, 'New Category', '', 0, '', '', 'Inactive', NULL, 20, '2011-10-25 12:09:48', NULL),
(21, 'New Category', '', 0, '', '', 'Inactive', NULL, 21, '2011-10-25 12:09:49', NULL),
(22, 'New Category', '', 0, '', '', 'Inactive', NULL, 22, '2011-10-25 12:09:50', NULL),
(23, 'New Category', '', 0, '', '', 'Inactive', NULL, 23, '2011-10-25 12:09:51', NULL),
(24, 'New Category', '', 0, '', '', 'Inactive', NULL, 24, '2011-10-25 12:09:51', NULL),
(25, 'New Category', '', 0, '', '', 'Inactive', NULL, 25, '2011-10-25 12:09:52', NULL),
(26, 'New Category', '', 0, '', '', 'Inactive', NULL, 26, '2011-10-25 12:09:53', NULL),
(27, 'New Category', '', 0, '', '', 'Inactive', NULL, 27, '2011-10-25 12:09:54', NULL),
(28, 'New Category', '', 0, '', '', 'Inactive', NULL, 28, '2011-10-25 12:09:55', NULL),
(29, 'New Category', '', 0, '', '', 'Inactive', NULL, 29, '2011-10-25 12:09:56', NULL),
(30, 'New Category', '', 0, '', '', 'Inactive', NULL, 30, '2011-10-25 12:09:57', NULL);

没有任何父级，所有类别都在顶层。问题仍然存在。以下查询由PHP执行，但失败了：

SELECT * FROM `categories` WHERE `parent`=22 ORDER BY `order`,`name`

这是EXPLAIN的内容：

mysql> EXPLAIN SELECT * FROM `categories` WHERE `parent`=22 ORDER BY `order`,`name`;
+----+-------------+------------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table      | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+----+-------------+------------+------+---------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | categories | ref  | parent        | parent | 4       | const |    1 | Using where; Using filesort | 
+----+-------------+------------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

更新 #2：我现在已经尝试了以下所有方法：

我将这个表格和数据复制到了另一个使用相同软件的网站。问题并没有随着表格一起出现，似乎只限于这个数据库。
我按照gbn的建议更改了索引，但问题仍然存在。
我删除了该表格，并重新创建了一个InnoDB表格，并插入了相同的30个测试行。问题依然存在。

我怀疑问题可能与这个数据库有关...

更新 #3：我完全删除了数据库，并以新名称重新创建，并导入了她的数据。问题仍然存在。

我发现导致程序卡住的实际PHP语句是对mysql_query()的调用。此后的语句都不会被执行。

当这个调用卡住时，MySQL将该线程标记为睡眠状态！

mysql> show full processlist;
+-------+------------------+-----------------------------+----------------------+---------+------+-------+-----------------------+
| Id    | User             | Host                        | db                   | Command | Time | State | Info                  |
+-------+------------------+-----------------------------+----------------------+---------+------+-------+-----------------------+
|  5560 | root             | localhost                   | problem_db           | Query   |    0 | NULL  | show full processlist |  
                          ----- many rows which have no relevancy; only rows from this customer's app are shown ------
| 16341 | shared_db        | oak01.sitepalette.com:53237 | shared_db            | Sleep   |  308 |       | NULL                  | 
| 16342 | problem_db       | oak01.sitepalette.com:60716 | problem_db           | Sleep   |  307 |       | NULL                  | 
| 16344 | shared_db        | oak01.sitepalette.com:53241 | shared_db            | Sleep   |  308 |       | NULL                  | 
| 16346 | problem_db       | oak01.sitepalette.com:60720 | problem_db           | Sleep   |  308 |       | NULL                  |  
+-------+------------------+-----------------------------+----------------------+---------+------+-------+-----------------------+

更新 #4：我已经缩小范围，确定问题出在两个表的组合上，即上面详细介绍的categories表和一个包含556行的media_images表。如果media_images表少于556行，或者categories表少于30行，问题就会消失。就像是我在这里遇到了某种MySQL限制...

更新 #5：我刚刚尝试将数据库完全迁移到另一个MySQL服务器上，问题就解决了...所以这与我的生产数据库服务器有关...

更新 #6：以下是每次卡住的相关PHP代码：

    public function find($type,$conditions='',$order='',$limit='')
    {
            if($this->_link == self::AUTO_LINK)
                    $this->_link = DFStdLib::database_connect();

            if(is_resource($this->_link))
            {
                    $q = "SELECT ".($type==_COUNT?'COUNT(*)':'*')." FROM `{$this->_table}`";
                    if($conditions)
                    {
                            $q .= " WHERE $conditions";
                    }
                    if($order)
                    {
                            $q .= " ORDER BY $order";
                    }
                    if($limit)
                    {
                            $q .= " LIMIT $limit";
                    }

                    switch($type)
                    {
                            case _ALL:
                                    DFSkel::log(DFSkel::LOG_DEBUG,"mysql_query($q,$this->_link);");
                                    $res = @mysql_query($q,$this->_link);
                                    DFSkel::log(DFSkel::LOG_DEBUG,"res = $res");

这段代码在生产环境中运行良好，并且在所有其他安装上都正常工作。只有在一个安装中，在$res = @mysql_query($q,$this->_link);处卡住了。我知道是因为在调试日志中看到了mysql_query，而没有看到res =，当我使用strace跟踪PHP进程时，它在read(处卡住了。

更新 #不管怎么说-我讨厌这个问题！现在又发生在我的两个客户身上。我刚刚启动了tcpdump，似乎MySQL的响应没有完整发送。TCP流似乎在完全发送MySQL响应之前就停滞了。（不过我还在继续调查）

更新 #我已经彻底疯了但是现在好像可以了：好吧，这没有任何意义，但是我找到了一个解决办法。如果我给MySQL服务器的eth2接口分配第二个IP地址，并且使用一个IP用于NFS流量，另一个IP用于MySQL，那么问题就消失了。就好像我以某种方式...过载了IP地址，如果NFS+MySQL流量都发送到同一个IP地址。但这毫无道理，因为你不能“过载”一个IP地址。饱和一个接口倒是可以，但它是同一个接口。

你有任何想法这里到底发生了什么？这可能是一个unix.SE或ServerFault的问题...（至少现在可以工作了...）

更新 #为什么啊为什么：这个问题仍然存在。即使使用两个不同的IP也开始出现这个问题。我可以继续创建新的私有IP，但显然有些问题。

- Josh

这是一个关于在MySQL中进行递归层次查询的潜在问题的链接。 - Derek Downey

@DTest 当然，我马上加上。谢谢你提供的另一个链接！ - Josh

我们正在聊天中积极尝试解决这个问题，欢迎任何发现这个问题的人加入聊天室。 - Josh

嗨，Josh。你说在你的MySQL客户端和PHPMyAdmin中查询正常运行？只有PHP应用程序出现问题？ - marcio

@marcioAlmada 是的，没错。我对整个情况感到非常困惑。 - Josh

@Josh 如果是这样的话，可能是你的脚本里出了问题。 - genesis

我有一种感觉，数据库或数据库服务器都不是真正的问题所在。我认为应用程序以非常规的方式处理数据库连接/交互。我正在努力找出一个确定性的测试方法。 - marcio

@marcioAlmada 我非常确定你是正确的...我相信PHP没有将查询发送到MySQL。我几乎确信这是一个PHP问题，而不是MySQL问题，但是为什么当我切换MySQL服务器时它能够工作呢？我打算在有机会时使用tcpdump来调试这个问题。 - Josh

6个回答

3

想法（不确定是否适用于MyISAM，因为我使用的是InnoDB）

将索引“parent”更改为包含3个列：parent、order和name。这与WHERE子句和ORDER BY子句相匹配

移除SELECT *。只选择需要的列。将其他列添加到索引“parent”中

这将允许优化器仅使用索引，因为它现在是全覆盖的。目前情况下，你必须读取整个表，因为索引对该查询而言没有用处

- gbn

更改parent索引为(parent, order, name)后问题仍然存在。 - Josh

3

我会在生产数据库服务器上检查几个事项。

检查 #1：确保/var/lib/mysql所在的数据卷没有坏块。这可能需要停机时间来执行fsck（文件系统检查）
检查 #2：确保表不会受到DML（INSERT/UPDATE/DELETE）或SELECT语句的重负荷
- 在MyISAM下，每个DML语句都会发出一个完整的表锁
- 在InnoDB下，会生成大量用于事务隔离的MVCC数据，以及轻微的集群索引锁定威胁
检查 #3：确保PHP正确地发出mysql_close()并且应用程序不依赖Apache来关闭数据库连接。否则，当PHP尝试使用已被MySQL有效关闭的DB连接资源时，可能会出现某种竞争条件。
检查 #4：确保DB服务器的操作系统在netstat连接列表中没有大量处于TIME_WAIT状态的连接，这些连接在PHP和MySQL的眼中已经关闭，但操作系统仍然保持着。您可以使用netstat | grep -i mysql | grep TIME_WAIT命令查看。
检查 #5：确保您不使用mysql_pconnect。关于持久连接无法正确关闭的问题仍然存在一个未解决的错误报告。我不敢想象尝试访问这些连接的情况。
检查 #6：确保生产数据库服务器和其他外部服务器之间通过负载均衡器、交换机、防火墙和DNS服务器的数据库流量吞吐量是相同的。个人而言，我不喜欢在mysql.user和mysql.db的主机列中使用DNS名称。我通常让客户将其删除并替换为硬IP地址。我还添加了skip-host-cache和skip-name-resolve来绕过mysqld对DNS的使用。因此，我可以理解@marcioAlmada的答案作为一个检查点。

如果你觉得这些检查都没有用，请尽快评论并告诉我，这样我就可以删除我的回答。

- RolandoMySQLDBA

我绝对认为这是一个有用的答案！我不确定是否关闭了所有连接，所以我可以尝试一下。我不认为/var有任何坏块（它在RAID10上），但我可能错了。我会检查netstat，那是个好主意！我没有使用mysql_pconnect，但会检查网络/ DNS /等等。 - Josh

@Josh：如果你看到了坏块，dmesg 中会有很多关于它们的消息。除非你使用硬件 RAID，否则请检查你的硬件 RAID 监控程序。 - derobert

当这种情况发生时，有时我会看到一个单独的 TIME_WAIT MySQL 连接（但并非总是如此）。数量并不多……该表的活动程度也不大。 - Josh

2

a) 嗨，乔什。你说在MySQL客户端和PHPMyAdmin中查询正常运行？只有PHP应用程序卡住了？ b) @marcioAlmada 是的，没错。我想你可能遇到了schrödinbug问题。你可以尝试在查询之后或之前使用die()命令，并检查你的代码中是否有很少发生的if语句。没有你的代码，很难说是什么导致了卡顿。编辑：目前我认为可能是这一行代码。

$this->_link = DFStdLib::database_connect();

我猜每次调用函数时都会创建连接。这可能是问题所在。你的my.cnf文件中的max_connections是多少？

- genesis

我清楚地知道它挂在哪里：它永远无法通过对mysql_query()的调用。 - Josh

1你能贴出大约10行的代码吗？ - genesis

好的。接下来几天我打算用tcpdump来调试这个问题。如果确实是PHP的问题，那么我应该在Stack Overflow上发布一个新的问题。 - Josh

@Josh: 我更新了我的回答 - genesis

谢谢@genesis... 但这并不是，有两个原因。1. 只有当我使用"自动建立数据库链接"功能时，才会调用该代码，这是通过将$this->_link设置为一个常量self::AUTO_LINK来实现的。2. 即使我真的使用了这个功能，该代码也位于一个if语句中： if($this->_link == self::AUTO_LINK)，下一行$this->_link = DFStdLib::database_connect();会改变$this->_link的值，所以if语句不会再次执行。我确信每个线程只有一个与数据库的连接。（请查看进程列表） - Josh

1

我几乎相信这是一个PHP问题，而不是一个MySQL问题，但为什么当我切换MySQL服务器时它却能正常工作呢？一些尝试： - 防火墙？是否有任何防火墙阻止您的应用程序，并阻止其向生产数据库服务器发出任何请求，或者反之亦然？ - 在连接配置中使用域名还是IP地址？使用域名可能会稍微减慢数据库交互速度，再加上短暂的PHP最大脚本执行时间，会导致永久挂起。这个最后的建议似乎解释了在切换数据库服务器时奇怪的变量行为。其中一个服务器的响应速度可能比另一个快得多，由于每找到一条记录都会有一个次要查询，这个假设可以解释为什么应用程序只在查询结果超过30条时才延迟。至少我们得出了一个初步结论。问题绝对不是MySQL服务器本身的问题。我查看了文档，似乎没有适合您特定情况的功能限制，而且我从未遇到过递归表和特定数量条目的任何问题。希望对您有所帮助。

- marcio

0

你尝试过将mysql_query()命令更新为PHP5原生驱动mysqli::query()吗？虽然不确定是否会有所作用，但或许值得一试。

- DevelumPHP

相关问题
7 为什么那个查询会导致锁等待超时？

- Derek Downey · Accepted Answer

对于查询计划中正在发生的情况的一般概述，你可以尝试使用PROFILING。

它基本上可以帮助你确定问题出在哪里。

当然，这只适用于已经使用enable-profiling编译了MySQL的情况。