mysql - 在 MySQL 中，I/O 延迟会导致简单的 UPDATE 花费几秒钟吗？

Question

我的 MySQL 应用程序在运行一些 UPDATE、INSERT 和 DELETE 查询时遇到性能下降。在这个问题中，我将只讨论一个特定的 UPDATE，因为它足以说明问题:

UPDATE projects SET ring = 5 WHERE id = 1

这个 UPDATE 通常足够快，大约 0.2 毫秒，但偶尔(足以成为一个问题)需要几秒钟。这是日志的摘录(查看第 4 行):

 ~ (0.000282) UPDATE `projects` SET `ring` = 5 WHERE `id` = 1
 ~ (0.000214) UPDATE `projects` SET `ring` = 6 WHERE `id` = 1
 ~ (0.000238) UPDATE `projects` SET `ring` = 7 WHERE `id` = 1
 ~ (3.986502) UPDATE `projects` SET `ring` = 8 WHERE `id` = 1
 ~ (0.000186) UPDATE `projects` SET `ring` = 9 WHERE `id` = 1
 ~ (0.000217) UPDATE `projects` SET `ring` = 0 WHERE `id` = 1
 ~ (0.000162) UPDATE `projects` SET `ring` = 1 WHERE `id` = 1

projects 是一个 InnoDB 表，有 6 列 INT 和 VARCHAR，17 行和 id 。它也发生在其他表上，但在这里我关注的是这个。在尝试解决问题时，我确保查询都是顺序的，所以这不是锁定问题。上面的 UPDATE 是在事务上下文中执行的。服务器上的其他信息:

• 具有 4GB RAM(原为 1GB)、12GB 可用磁盘空间的 VPS
• CentoOS 5.8(原为 5.7)
• MySQL 5.5.10(原为 5.0.x)

上面的“was”位表示它在升级之前或之后都不起作用。

到目前为止我已经尝试过但无济于事:

• 将 innodb_flush_log_at_trx_commit 设置为 0、1 或 2
• 打开或关闭 innodb_locks_unsafe_for_binlog
• 打开或关闭timed_mutexes
• 将 innodb_flush_method 从默认更改为 O_DSYNC 或 O_DIRECT
• 将 innodb_buffer_pool_size 从默认值增加到 600M，然后再增加到 3000M
• 将 innodb_log_file_size 从默认值增加到 128M
• 从源代码编译MySQL
• 运行 SHOW PROCESSLIST，它通知我状态正在“更新”
• 运行 SHOW PROFILE ALL，这表明几乎所有时间都花在了“更新”上，并且在该步骤中，没有太多时间花在 CPU 周期上，并且有很多自愿的上下文切换(例如 30)
• 监控 SHOW STATUS Innodb_buffer_pool_pages_dirty 中的更改。被刷新的脏页与慢查询之间可能存在某种关系，但相关性尚不清楚。

然后我决定用 ioping 检查系统的 I/O 延迟。这是我的第一个 VPS，所以看到这个结果我很惊讶:

4096 bytes from . (vzfs /dev/vzfs): request=1 time=249.2 ms
4096 bytes from . (vzfs /dev/vzfs): request=2 time=12.3 ms
4096 bytes from . (vzfs /dev/vzfs): request=3 time=110.5 ms
4096 bytes from . (vzfs /dev/vzfs): request=4 time=232.8 ms
4096 bytes from . (vzfs /dev/vzfs): request=5 time=294.4 ms
4096 bytes from . (vzfs /dev/vzfs): request=6 time=704.7 ms
4096 bytes from . (vzfs /dev/vzfs): request=7 time=1115.0 ms
4096 bytes from . (vzfs /dev/vzfs): request=8 time=209.7 ms
4096 bytes from . (vzfs /dev/vzfs): request=9 time=64.2 ms
4096 bytes from . (vzfs /dev/vzfs): request=10 time=396.2 ms

非常不稳定，我会说。

说了这么多，我问:

1. I/O 延迟是否会偶尔降低 MySQL 性能？我一直认为，当您运行 UPDATE 时，线程会处理该连接不会将数据刷新到磁盘或等待这样的刷新；它会立即返回，刷新将在另一个时间由另一个线程完成。

2. 如果不能是磁盘 I/O，除了租用专用服务器，还有什么我可以尝试的吗？

Answer 1

我用根据您的回答收集的额外数据来回答我自己的问题。

我使用了两台通过无线网络连接的笔记本电脑。在笔记本 A 上，我使用 sshfs 挂载笔记本 B 的目录。然后在笔记本A上我开始了MySQL 将该挂载目录指定为其数据目录。这应该为 MySQL 提供一个非常慢的 I/O 设备。 MySQL 开始于innodb_flush_log_at_trx_commit = 0。

我定义了 3 组查询，每组包含一个更新和一个选择查询重复 10,000 次，没有显式交易。实验是:

• US1SID:在同一表的特定行上进行更新和选择。同一行用于所有迭代。
• US1MID:更新并选择同一表的特定行。该行是一个每次迭代都不同。
• US2MID:更新和选择不同表的行。在这种情况下，表在实验过程中，被选择读取的内容完全没有改变。

每组使用 shell 脚本运行两次(因此时间比我原来的问题慢)，一次在正常条件下，另一次在执行以下命令后:

tc qdisc replace dev wlan0 root handle 1:0 netem delay 200ms

上面的命令在通过 wlan0 传输数据包时增加了 200ms 的平均延迟。

首先，这是前 99% 最快更新和选择的平均时间，以及底部 1% 的更新和选择。

          |        Delay: 0ms        |       Delay: 200ms       |
          | US1SID | US1MID | US2MID | US1SID | US1MID | US2MID |
| top99%u | 0.0064 | 0.0064 | 0.0064 | 0.0063 | 0.0063 | 0.0063 |
| top99%s | 0.0062 | 0.0063 | 0.0063 | 0.0062 | 0.0062 | 0.0062 |
| bot01%u | 1.1834 | 1.2239 | 0.9561 | 1.9461 | 1.7492 | 1.9731 |
| bot01%s | 0.4600 | 0.5391 | 0.3417 | 1.4424 | 1.1557 | 1.6426 |

很明显，即使 I/O 性能非常非常差，MySQL 也设法非常快速地执行大多数查询。但最让我担心的是最糟糕的例，所以这是另一个表，显示了 10 个最慢的查询。一个“你”的意思是一个更新，一个“s”是一个选择。

|          Delay: 0ms         |          Delay: 200ms          |
| US1SID  | US1MID  | US2MID  | US1SID   | US1MID   | US2MID   |
| 5.443 u | 5.946 u | 5.315 u | 11.500 u | 10.860 u | 11.424 s |
| 5.581 u | 5.954 s | 5.466 u | 11.649 s | 10.995 u | 11.496 s |
| 5.863 s | 6.291 u | 5.658 u | 12.551 s | 11.020 u | 12.221 s |
| 6.192 u | 6.513 u | 5.685 u | 12.893 s | 11.370 s | 12.599 u |
| 6.560 u | 6.521 u | 5.736 u | 13.526 u | 11.387 u | 12.803 u |
| 6.562 u | 6.555 u | 5.743 u | 13.997 s | 11.497 u | 12.920 u |
| 6.872 u | 6.575 u | 5.869 u | 14.662 u | 12.825 u | 13.625 u |
| 6.887 u | 7.908 u | 5.996 u | 19.953 u | 12.860 u | 13.828 s |
| 6.937 u | 8.100 u | 6.330 u | 20.623 u | 14.015 u | 16.292 u |
| 8.665 u | 8.298 u | 6.893 u | 27.102 u | 22.042 s | 17.131 u |

结论:

1. 糟糕的 I/O 性能确实会使 MySQL 变慢。不清楚为什么或确切的时间，但它确实发生了。

2. 减速适用于选择和更新，更新受到影响更多。

3. 出于某种原因，甚至在未涉及任何更改的表上进行选择，并且最近被填充，也被放慢了，很明显来自上面的 US2MID。

4. 至于mentatkgs提出的测试用例，貌似更新不同行而不是相同的行确实有一点帮助，但没有解决问题。

我想我要么调整我的软件以容忍这种延迟，要么尝试移动给另一个供应商。为此租用专用服务器太贵了项目。

谢谢大家的评论。