php - PDOStatement->在生产服务器上执行比在开发服务器上慢 14 倍

Question

我在一个使用 Zend Framework 的网站上工作，其中一个页面在生产服务器上的加载速度比在开发服务器上慢得多。经过一些挖掘和分析(使用 xdebug)后，我发现 PDOStatement->execute 调用在生产服务器上运行需要 11134 毫秒，在开发服务器上需要 780 毫秒。

两台服务器上的数据库相同，两台服务器上的 MySQL 版本相同 (5.5.32)，两台服务器上的 MySQL 配置也相同(一直到 my.cnf 中的行)。两台服务器都运行 Ubuntu 12.04.2 LTS，尽管与生产服务器相比，开发服务器确实有额外的软件。我能想到的唯一区别是，开发服务器在 SSD 上运行，而生产服务器在 RAID 0 中运行两个常规 SATA 驱动器。然而，在 phpMyAdmin 中运行查询会产生大约相同的时间(0.0003-0.0005 秒，尽管我不相信 phpMyAdmin 的执行时间报告)。

---

这是我运行的查询:

$query = "SET @rank = 0";
$statement = $db->query($query);

$query = "SET @previous_result = -1";
$statement = $db->query($query);

$query = "
    SELECT
        @group_id := IFNULL(g.id, -1)
    FROM
        `branch_statistics` bs
    JOIN
        branch_to_wave btw ON bs.branch_to_wave_id = btw.id
    JOIN
        branch_to_group btg ON bs.branch_id = btg.branch_id
    JOIN
        `group` g ON btg.group_id = g.id
    WHERE
        btw.wave_id = $wave_id AND bs.branch_id = $branch_id";
$statement = $db->query($query);

$query = "DROP TABLE IF EXISTS group_members";
$statement = $db->query($query);

$query = "CREATE TEMPORARY TABLE group_members (id INT PRIMARY KEY)";
$statement = $db->query($query);

$query = "DROP TABLE IF EXISTS group_members2";
$statement = $db->query($query);

$query = "CREATE TEMPORARY TABLE group_members2 (id INT PRIMARY KEY)";
$statement = $db->query($query);

$query = "
    INSERT INTO
        group_members
        SELECT
            btw.id
        FROM
            branch_to_wave btw
        JOIN
            branch_to_group btg ON btw.branch_id = btg.branch_id
        WHERE
            btw.wave_id = $wave_id AND
            btg.group_id = @group_id
        GROUP BY
            btw.id";
$statement = $db->query($query);

$query = "INSERT INTO group_members2 SELECT id FROM group_members";
$statement = $db->query($query);

$query = "
    SELECT @number_of_results :=
        COUNT(result)
    FROM
        (
        SELECT
            ROUND(points / maximum_points, 4) as result
        FROM
            `branch_statistics` bs
        JOIN
            group_members2 gm2 ON gm2.id = bs.branch_to_wave_id
        GROUP BY
            result
        ) results
";
$statement = $db->query($query);

$query = "
    EXPLAIN SELECT
        *,
        @number_of_results as number_of_results
    FROM
        (
            SELECT
                *,
                IF(@previous_result != result, @rank := @rank + 1, @rank) as rank,
                @previous_result := result
            FROM
                (
                SELECT
                    bs.branch_id,
                    ROUND(points / maximum_points, 4) as result,
                    (
                        SELECT
                            AVG(ROUND(points / maximum_points, 4)) as average
                        FROM
                            `branch_statistics` bs
                        JOIN
                            group_members2 gm2 ON gm2.id = bs.branch_to_wave_id
                    ) as average
                FROM
                    `branch_statistics` bs
                JOIN
                    group_members gm ON gm.id = bs.branch_to_wave_id
                GROUP BY
                    bs.branch_id
                ORDER BY
                    result DESC
                ) complete
        ) results
    WHERE branch_id = $branch_id
";
$statement = $db->query($query);

$statement->setFetchMode(Zend_Db::FETCH_NUM);
$results = $statement->fetchAll();

---

我不知道服务器规范是否相关，但无论如何它们在这里:

生产服务器:

Intel(R) Core(TM)2 Duo CPU E7500 @ 2.93GHz 带 3.4Gb RAM

开发服务器(在以下硬件上运行的虚拟机，以及 5-6 个其他虚拟机):

AMD FX(tm)-8120 八核处理器，3Gb RAM

---

所以我的问题是:我怎样才能查明造成这种差异的原因？

---

编辑 #1 (2013-08-16 @ 11:23):对于那些不熟悉 Zend Framework 的人，这里是导致 PDOStatement->execute 的调用堆栈:

1. $db->query($query);
2. Zend_Db_Adapter_Pdo_Abstract->查询
3. Zend_Db_Statement->执行
4. Zend_Db_Statement_Pdo->执行
5. PDOStatement->执行

---

编辑#2 (2013-08-16 @ 13:09):这是两台机器上的分析结果。

Status                              Duration DEV    Duration PROD
starting                            0.000032        0.000010
Waiting for query cache lock        0.000010        0.000006
checking query cache for query      0.000183        0.000074
checking permissions                0.000010        0.000007
checking permissions                0.000009        0.000006
checking permissions                0.000009        0.000006
checking permissions                0.000011        0.000007
Opening tables                      0.000030        0.000017
System lock                         0.000140        0.000063
optimizing                          0.000016        0.000010
statistics                          0.000035        0.000016
preparing                           0.000020        0.000011
Creating tmp table                  0.000028        0.000015
executing                           0.000009        0.000006
Copying to tmp table                0.000424        0.000133
Sorting result                      0.000039        0.000017
Sending data                        0.000015        0.000008
optimizing                          0.000014        0.000009
statistics                          0.000022        0.000012
preparing                           0.000019        0.000010
executing                           0.000011        0.000007
Sending data                        0.000171        0.000110
optimizing                          0.000006        0.000007
statistics                          0.000006        0.000008
preparing                           0.000006        0.000007
executing                           0.000004        0.000006
Sending data                        0.000042        0.000035
removing tmp table                  0.000007        0.000009
Sending data                        0.000006        0.000008
init                                0.000011        0.000013
optimizing                          0.000005        0.000008
statistics                          0.000006        0.000008
preparing                           0.000006        0.000009
executing                           0.000004        0.000006
Sending data                        0.000016        0.000019
end                                 0.000005        0.000007
query end                           0.000005        0.000007
closing tables                      0.000004        0.000006
removing tmp table                  0.000005        0.000007
closing tables                      0.000004        0.000006
removing tmp table                  0.000006        0.000007
closing tables                      0.000006        0.000008
freeing items                       0.000013        0.000275
logging slow query                  0.000004        0.000007
cleaning up                         0.000006        0.000007

---

我还测试了 mysqlslap 并发现了一些非常有趣的结果，如下所示(时间是平均查询时间)。您可以看到，虽然同时进行更多查询确实会增加平均查询时间，但与开发服务器相比，生产服务器上的相同查询仍然慢 24 倍。

我真的不明白为什么在 phpMyAdmin 中运行查询需要 0.0005 秒，而使用 mysqlslap 的相同查询需要 0.299 秒，但我怀疑这是我问题的核心。

Mysqlslap settings                      Dev         Prod
iterations = 10, concurrency = 50       1.253       14.129
iterations = 10, concurrency = 25       0.513       7.153
iterations = 10, concurrency = 10       0.141       3.133
iterations = 10, concurrency = 1        0.014       0.299

Answer 1

今天终于明白了!问题是临时表不是在生产服务器的内存中创建的。我不知道为什么，因为配置文件明确指出它应该，但我通过将 ENGINE = MEMORY 添加到所有 CREATE TEMPORARY TABLE 调用解决了这个问题。

例如:

CREATE TEMPORARY TABLE group_members (id INT PRIMARY KEY) ENGINE = MEMORY