DB插入PHP脚本随机冻结,没有错误日志

2022-01-15 00:00:00 debugging sql mariadb php sql-insert

我有一个脚本,我每 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.)

相关文章