DB插入PHP脚本随机冻结，没有错误日志 [英] DB insertion PHP script freezing randomly, no error log

问题描述

我有一个脚本，我每 X 分钟运行一次 cron 作业.

I have a script that I run as a cron job every X minutes.

它正在使用 Apache、Centos、Maria DB 10.2、6 核和 8GB 内存的 VPS 上运行.

It is being run on VPS using Apache, Centos, Maria DB 10.2, 6 Cores and 8GB of RAM.

如果当前运行尚未完成，我应用了一种机制来防止脚本再次运行.

I applied a mechanism to prevent the script from running again if the current run didn't finish yet.

它似乎在大约 99% 的时间内运行良好.

It seems to run fine about 99% of the time.

但是，有时脚本会随机冻结".脚本正在创建的日志文件将在 EOE 之前的某一点停止，或者将创建一个 0 字节的日志文件而不填充.没有错误日志，也没有记录 MySQL 错误.

However, in a random manner, sometimes the script would just "freeze". The log file that the script is creating will either stop at one point before the EOE, or a 0 bytes log file will be created without getting populated. There are no error logs and no MySQL errors logged.

今天我启用了 mysqlbinlog 日志，并看到在脚本的进度日志停止的那一秒，脚本在两个数据库表中进行了大约 290 次插入，其中一个表有约 40 列，另一列约 85 列.

Today I enabled mysqlbinlog log, and saw that at the exact second in which the progress log of the script has halted, the script was making about ~290 Inserts into two DB tables, one table has ~40 columns and another have ~85 columns.

但这是脚本的正常行为.

But this is normal behavior for the scripts.

我咨询过一些开发人员，但没有一个人清楚地知道会发生什么.有人只是建议我可能 RAM 不足以容纳脚本.但是，我不知道这是否有意义，因为大多数时候脚本确实成功完成，并且它没有报告内存问题，它只是冻结.

I have consulted few developers and none of them had a clear idea of what can be happening. I was simply suggested that maybe the RAM is not enough for the script. However, I don't know if this makes sense since most of them time the script does finish successfully, and it doesn't report a memory problem, it simply freezes.

您能建议一种调试方法吗?

Can you suggest a way to debug this?

推荐答案

使用批量插入；这将快 10 倍左右，从而至少减少冲突的频率.

Use bulk insert; that will be about 10 times as fast, thereby at least decreasing the frequency of conflict.

INSERT INTO tbl (a,b) VALUES
    (1,2),
    (33,44),
    ...

您检查错误吗?显示 ENGINE INNODB 状态；.未检测到的死锁可能会导致数据丢失，但不会导致挂起.

Do you check for errors? SHOW ENGINE INNODB STATUS;. An un-detected deadlock could lead to missing data, but not a hang.

挂起可能来自阻止 cron 作业的操作.这可能会在 50 秒后超时；你等了那么久吗?请务必检查错误并记录它们.

A hang could come from an action blocking the cron job. This might time out after 50 seconds; did you wait that long? Be sure to check for errors and log them.

0.5G -- 如果多个 cron 作业堆积起来怎么办?PHP 是否与 MySQL 在同一台机器上运行?他们会为 RAM 而战吗?

0.5G -- what if several cron jobs pile up?? Is PHP running on the same machine as MySQL? Could they be fighting for RAM?

(我怀疑你的防碰撞代码有错误.)

(I suspect your anti-collision code has a bug.)

这篇关于DB插入PHP脚本随机冻结，没有错误日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！