主页 / dba / 问题 / 64206
Accepted
syneticon-dj
Asked: 2014-05-01 01:18:09 +0800 CST

为什么 MySQL 会做串行同步 I/O？

  • 772

在查看一个特别烦人的 MyISAM 表查询时，它在很多情况下都需要很长时间才能执行，我注意到 MySQL 似乎暴露了一种相当奇怪的 I/O 模式：当执行单个查询并且必须执行大量操作时I/O 量（例如，对于表扫描或由于echo 3 > /proc/sys/vm/drop_caches需要首先从磁盘加载索引而导致缓存为空时），底层块设备的队列大小接近值 1，性能极差仅 4-5 MB/s：

root@mysql-test:~# iostat -xdm 5 /dev/sda
Linux 3.2.0-40-generic (mysql-test)  04/30/2014      _x86_64_        (4 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.14    24.82   18.26   88.79     0.75     4.61   102.56     2.83   26.39   19.29   27.85   2.46  26.31

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    69.29  151.52   72.73     5.31     0.59    53.95     1.21    5.39    7.84    0.29   4.39  98.51

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   153.06  144.29  174.69     4.96     1.36    40.54     1.39    4.36    8.91    0.60   3.15 100.49

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   105.75  150.92  109.03     4.53     0.85    42.41     1.29    4.96    8.15    0.54   3.90 101.36

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    48.89  156.36   51.72     5.28     0.76    59.38     1.28    6.16    8.02    0.55   4.77  99.23

虽然 150 IOPS 只是给定配置中的单个磁盘在随机 I/O 方面能够提供的，但结果仍然让我感到惊讶，因为我希望 MySQL 能够运行异步 I/O 进行读取并获取一个同时处理大量块而不是一个一个地读取和评估它们，有效地忽略了 RAID 配置中可用的并行化增益。什么设计决策或配置选项对此负责？这是特定于平台的问题吗？

虽然我已经用大型 MyISAM 表对此进行了测试，但我发现将相同的表转换为 InnoDB 会产生类似的效果（虽然还不错，示例查询仍然需要 20-30 秒，其中大部分时间都花在读取磁盘上在我重新启动 mysql 守护程序后，队列长度为 1)，因此缓冲池为空。我还验证了同样的问题在 5.6 GA 和当前的 5.7 里程碑 14 上仍然存在——只要我使用单个查询线程，MySQL 似乎无法并行化查询处理所需的 I/O 操作。

根据请求，有关该场景的一些其他详细信息。可以通过多种查询类型观察到该行为。我随意选择了一个进行进一步测试，内容有点像这样：

SELECT herp.id, herp.firstname, herp.lastname, derp.label, herp.email, 
(SELECT CONCAT(label, " (", zip_code, " ", city,")" ) FROM subsidiaries WHERE subsidiaries.id=herp.subsidiary_id ) AS subsidiary, 
(SELECT COUNT(fk_herp) from herp_missing_data WHERE fk_herp=herp.id) AS missing_data
FROM herp LEFT JOIN derp ON derp.id=herp.fk_derp
WHERE (herp.fk_pools='123456')  AND herp.city LIKE '%Some City%' AND herp.active='yes' 
ORDER BY herp.id desc LIMIT 0,10;

我知道它有一些优化空间，但出于多种原因我决定将其留在那儿，并专注于为我看到的意外 I/O 模式寻找一般解释。

使用过的表中确实有一堆数据：

mysql> select table_name, engine, table_rows, data_length, index_length from information_schema.tables WHERE tables.TABLE_SCHEMA = 'mydb' and tables.table_name in ( 'herp', 'derp', 'missing_data', 'subsidiaries');
+-------------------------+--------+------------+-------------+--------------+
| table_name              | engine | table_rows | data_length | index_length |
+-------------------------+--------+------------+-------------+--------------+
| derp                    | MyISAM |      14085 |     1118676 |       165888 |
| herp                    | MyISAM |     821747 |   828106512 |    568057856 |
| missing_data            | MyISAM |    1220186 |    15862418 |     29238272 |
| subsidiaries            | MyISAM |       1499 |     6490308 |       103424 |
+-------------------------+--------+------------+-------------+--------------+
4 rows in set (0.00 sec)

现在，当我在这些表上运行上面的查询时，我得到的执行时间超过 1 分钟，而系统显然一直忙于使用单个线程从磁盘读取数据。

示例查询执行的配置文件（在此示例中耗时 1 分 9.17 秒）如下所示：

mysql> show profile for query 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000118 |
| Waiting for query cache lock   |  0.000035 |
| init                           |  0.000033 |
| checking query cache for query |  0.000399 |
| checking permissions           |  0.000077 |
| checking permissions           |  0.000030 |
| checking permissions           |  0.000031 |
| checking permissions           |  0.000035 |
| Opening tables                 |  0.000158 |
| init                           |  0.000294 |
| System lock                    |  0.000056 |
| Waiting for query cache lock   |  0.000032 |
| System lock                    |  0.000116 |
| optimizing                     |  0.000063 |
| statistics                     |  0.001964 |
| preparing                      |  0.000104 |
| Sorting result                 |  0.000033 |
| executing                      |  0.000030 |
| Sending data                   |  2.031349 |
| optimizing                     |  0.000054 |
| statistics                     |  0.000039 |
| preparing                      |  0.000024 |
| executing                      |  0.000013 |
| Sending data                   |  0.000044 |
| optimizing                     |  0.000017 |
| statistics                     |  0.000021 |
| preparing                      |  0.000019 |
| executing                      |  0.000013 |
| Sending data                   | 21.477528 |
| executing                      |  0.000070 |
| Sending data                   |  0.000075 |
| executing                      |  0.000027 |
| Sending data                   | 45.692623 |
| end                            |  0.000076 |
| query end                      |  0.000036 |
| closing tables                 |  0.000109 |
| freeing items                  |  0.000067 |
| Waiting for query cache lock   |  0.000038 |
| freeing items                  |  0.000080 |
| Waiting for query cache lock   |  0.000044 |
| freeing items                  |  0.000037 |
| storing result in query cache  |  0.000033 |
| logging slow query             |  0.000103 |
| cleaning up                    |  0.000073 |
+--------------------------------+-----------+
44 rows in set, 1 warning (0.00 sec)
mysql performance

2 个回答

  1. Best Answer

    首先让我澄清一下，确认 MyISAM 不执行异步 I/O，但 InnoDB 执行并将默认从 MySQL 5.5 开始。在 5.5 之前，它通过使用工作线程来使用“模拟 AIO”。

    我认为区分三种情况也很重要：

    1. 一次执行多个查询
    2. 并行执行的单个查询
    3. 表扫描的某种逻辑预读/清除下一页众所周知的情况。

    对于 (1) I/O 将能够为此并行执行。MyISAM 有一些限制：表锁定和保护key_buffer（索引缓存）的全局锁。MySQL 5.5+ 中的 InnoDB 在这里非常出色。

    对于 (2) 这目前不支持。一个很好的用例是分区，您可以在其中并行搜索每个分区表。

    对于 (3) InnoDB 如果读取 >56 页（这是可配置的），则 InnoDB 具有线性预读以读取完整范围（64 页组），但还有进一步增强的空间。Facebook 已经写了关于在他们的分支中实现逻辑读取头（在表扫描上有 10 倍的性能增益）。

    • 8

  2. 我希望missing_data不是 MyISAM，因为一个空的 MyISAM 表通常有 1024 字节.MYI。MyISAM 需要非零字节大小。零字节.MYI对我来说听起来有点令人毛骨悚然。

    如果您运行此元数据查询

    select table_name, table_rows, data_length, index_length, engine
from information_schema.tables
WHERE tables.TABLE_SCHEMA = 'mydb'
and tables.table_name = 'missing_data';

    那个表的引擎是MyISAM，你需要修复它。

    旁注：如果engine是NULL，则为视图。如果它是一个视图或者它不是 MyISAM，请忽略我帖子的其余部分并将该信息添加到问题中。如果表是 MyISAM，请继续阅读...

    根据你的元数据查询，missing_data.MYD大概是46M。

    首先，运行这个

    SHOW CREATE TABLE mydb.missing_data\G

    您将获得表格描述或一条错误消息，内容如下

    ERROR 126 (HY000): Incorrect key file for table ...

    如果你得到表描述并且它是 MyISAM，请运行

    OPTIMIZE TABLE mydb.missing_data;

    它将重新创建没有碎片的表并计算新的索引统计信息。如果这不起作用，请尝试：

    REPAIR TABLE mydb.missing_data;

    这应该为 MyISAM 重新生成索引页。

    为了安全起见（如果使用 MySQL 5.6），修复后运行它

    FLUSH TABLES mydb.missing_data;

    你的问题

    如果 MySQL 查询优化器决定不使用，则表的索引可能不会加载到内存中。如果您的 WHERE 子句指示必须从索引中读取大量行，则 MySQL 查询优化器将在构建 EXPLAIN 计划时看到这一点，并决定改用全表扫描。

    MyISAM 表上的并行 I/O 操作是无法实现的，因为它是不可配置的。

    可以像这样调整 InnoDB 以提高性能。

    • 2

相关问题