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 RAM的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日志，并看到在脚本进度日志停止的那一秒，该脚本正在向两个DB表中插入约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),
    ...

您是否检查错误? SHOW ENGINE INNODB STATUS;.未检测到的死锁可能导致数据丢失，但不会挂起.

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屋！