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

 

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

UPDATE projects SET ring = 5 WHERE id = 1

这个UPDATE通常足够快，大约0.2ms，但是偶尔（足以成为问题） 需要几秒钟的时间 。 以下是日志的摘录（请看第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是包含6列INT和VARCHAR类型的InnoDB表，17行和id上的索引。 它也发生在其他桌子上，但我在这里专注于此。 当试图解决问题时，我确保查询全部是连续的，所以这不是锁定问题 。 上面的UPDATE是在事务的上下文中执行的。 服务器上的其他信息 ：

  • 具有4GB RAM的VPS（1GB），12GB可用磁盘空间
  • 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

    相当不稳定，我会说。

    说完所有这些，我问：

  • I / O延迟能否偶尔导致MySQL性能下降？ 我一直认为，当您运行UPDATE ，处理该连接的线程不会将数据刷新到磁盘或等待此类刷新; 它会立即返回并在另一时间由另一个线程完成冲洗。

  • 如果它不能成为磁盘I / O， 还有什么我可以尝试的，但租用专用服务器的时间还没有？

    • 我使用根据您的答案收集的其他数据回复了我自己的问题。

    我使用了两台通过无线网络连接的笔记本电脑。 在笔记本A上，我使用sshfs挂载了一个笔记本B的目录。 然后在笔记本上，AI启动了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个最慢的查询。 “u”表示这是更新，“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 |

    结论：

  • 糟糕的I / O性能的确会让MySQL慢慢爬行。 目前尚不清楚究竟为什么或何时发生，但确实发生。

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

  • 出于某种原因，即使在没有涉及任何变化并且最近已经填充的表格上进行选择，也减慢了速度，如从上面的US2MID清楚的那样。

  • 至于mentatkgs提出的测试用例，似乎更新不同的行而不是相同的行确实有一些帮助，但并不能解决问题。

    • 我想我会让我的软件适应这种延迟，或者尝试转移到另一个提供商。 租用专用服务器对于这个项目来说太昂贵了。

    谢谢大家的意见。

    当您在云中托管您的VPS时，您可能会遇到完全无法控制的问题。

    VPS受制于运行它们的主机服务器的奇思妙想。 例如，Rackspace Cloud的CPU周期优先级根据VPS的大小进行加权。 您的VPS越大，您的应用顺利执行的可能性就越大。 如果您正在使用的主机上有更大的VPS，则有可能导致加权突发。 这很难说。

    你有没有试过在你自己的机器上本地运行？ 如果它在你自己的系统上完美运行，并且你需要有保证的性能，那么你最好的选择就是转向专用服务器。

    你有一个VPS相关的IO问题。 这不是MySQL的错。

    您是否有机会在亚马逊或可能使用RDS的情况下使用Elastic Block Store？ 这两种都使用远程存储和IP协议层来与存储进行通信; 他们有时会有令人讨厌的滞后。

    链接地址: http://www.djcxy.com/p/58225.html

    上一篇: Can I/O latency cause a simple UPDATE to take seconds in MySQL?

    下一篇: Bootloader Strange Behavior