主页 / dba / 问题 / 244570
Accepted
Grayda
Asked: 2019-08-06 06:00:39 +0800 CST

在 MySQL 中查找卡住“发送数据”查询的原因

  • 772

我在 1gb DigitalOcean 液滴上运行 MySQL，并且通过运行的 Laravel 框架有许多计划任务。特别是一项任务（似乎）随机导致 MySQL 处于 100% CPU，我不确定从哪里开始追踪原因。

该作业下载一个包含 360 个值的 JSON 文件，并将结果存储（或更新）到数据库中。完成后，它会调用另一个函数来获取过去 4 小时（大约 240 行）的所有结果并将其转换为静态图像。这从 cron 作业每两分钟运行一次，如果作业失败，它会在放弃之前重试作业最多 10 次。

根据show processlist;，查询是：

mysql> show processlist;
+-----+-------+-----------+---------------------+---------+------+--------------+-------------------------------------------------------------+
| Id  | User  | Host      | db                  | Command | Time | State        | Info                                                        |
+-----+-------+-----------+---------------------+---------+------+--------------+-------------------------------------------------------------+
| 271 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 297 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 303 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 308 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 311 | dbusr | localhost | sample_database_com | Execute |    1 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 317 | dbusr | localhost | sample_database_com | Execute |    0 | Sending data | select * from `kp_minutes` where (`updated_at` = ?) limit 1 |
| 318 | dbusr | localhost | sample_database_com | Sleep   |    1 |              | NULL                                                        |
| 324 | dbusr | localhost | NULL                | Query   |    0 | starting     | show processlist                                            |
| 325 | dbusr | localhost | sample_database_com | Sleep   |    3 |              | NULL                                                        |
+-----+-------+-----------+---------------------+---------+------+--------------+-------------------------------------------------------------+
9 rows in set (0.00 sec)

为了解决这个问题，我必须清除 Laravel 中所有排队的作业，然后重新启动 MySQL，我最终每天都会这样做一两次。

我还有其他工作做类似的事情，但数据要多得多。例如，一项作业在数据库中插入（最多）500,000 个项目，然后将这些值拉出并制作成静态图像。这些作业运行得很好，所以我不确定为什么select240 行（看似）简单会导致问题。

我已经尝试将我的数据库繁重的工作推到列表的末尾，以防数十万行导致事情中断。我已经使用 Laravel 的lockForUpdate()函数来锁定kp_minutes行，我在存储数据和生成静态图像之间添加了延迟，我已经阅读了一堆 MySQL 问题试图找出发生了什么，但我已经一无所获。

我将如何追踪 100% CPU 的原因，我能做些什么来使此类“发送数据”进程超时？我的 MySQL 安装是 DigitalOcean LAMP 图像提供的默认设置，所以我认为我没有通过调整值搞砸任何事情。

编辑：回答几个问题：

  • Laravel 应用程序与 MySQL 服务器在同一台服务器上运行。我知道我不应该这样做，但这是（希望是临时的）成本节约措施。
  • Laravel 的任务调度系统使用单个 cron 作业来检查要运行的任务。然后它将这些任务发送到运行它们的队列（例如 Amazon SQS，或者在我的情况下，作为数据库中的一行）。这个“kp_minutes”任务每两分钟运行一次。
  • 有问题的表包含大约 175,000 行，但我只选择了大约 240 个带有 where 子句的行
  • lockForUpdate()内部使用for update：select * fromkp_minutes where (updated_at= ?) limit 1 for update
  • 运行show full processlist给出的结果与我发布的相同。
mysql debugging

1 个回答

  1. Best Answer

    我会给你几种方法来预防，不清除，问题...

    加快查询速度

    听起来你失踪了INDEX(updated_at)。特别是，读取一行（由于WHEREand LIMIT）而不是读取可能的大部分表（假设它们大致按更新顺序，大部分 175K 行）之间的区别。

    如果这还不够，请提供

    SHOW CREATE TABLE kp_minutes;
SHOW TABLE STATUS LIKE 'kp_minutes';
EXPLAIN SELECT ...;   -- using the entire query
SELECT @@version;

    小心cron

    听起来您正在重复执行一项工作。你在使用cron还是类似的？

    计划 A：减少运行工作的频率。

    计划 B：如果作业已经在运行，则添加代码以防止作业运行。听起来，几个小时后，你有这么多的工作副本，他们只是相互绊倒，没有一个能在合理的时间内完成。

    如果工作失败，...

    为什么会失败？修复故障。即使重试是有益的，真的有必要重试 10次吗？

    限制 123000, 1000

    我们看不到整个查询。请使用SHOW FULL PROCESSLIST和/或在代码中找到实际的 SQL。

    如果它使用的是大的OFFSET，那么这就是 CPU（或 I/O）的消耗。它必须读取 (123000+1000) 行以满足上面的示例。如果您使用“偏移量”，请解释一下。“记住你离开的地方”是一个更好的方法；我们可以讨论一下。

    杂项

    “发送数据”是虚假的；它并没有真正提供任何关于正在发生的事情的线索。

    Laravel 的技巧（lockForUpdate 等）需要转化为 SQL，以便我建议它们是帮助还是伤害。

    只要你不交换，不用担心 Laravel 在同一台机器上运行。如果你正在交换，减少innodb_buffer_pool_size一些。

    • 1

相关问题