Level 122
if you are calling it every minute, make sure it runs and finishes in that same minute. it sounds like you are launching more and more jobs
Feb 3, 2022
15
Level 1
Difference between using scheduler and commands over cronjobs
Recently I ran into a problem I have not encountered so far. The requirement is to update one table based on various data every minute. As I used this in another project already I created said function, created a command for it and created a cronjob on shared hosting to call said command every minute.
However, some time after that I get the SQL Error that there too many connections. As I dont think the "normal" requests are the issue here (as it run without any problems) I heavily suspect the new function in combination with the command + cronjob.
It was just then when I read about the scheduler. So my question is: Is there a difference between running a cronjob for the scheduler to work every minute vs a cronjob that calls command every minute?
Level 1
It finishes within 10 seconds max. That is why I am heavily confused here as to why it happens in the first place
Level 3
@aescaru
If the task did not finish in one minute ... another task will start in the next minute ... in the long run ... this will cause too many connections error
Laravel’s ‘Command Scheduler’ allows you to easily define the schedule of the commands within Laravel itself. When using the scheduler, only one Cron entry is needed on the server.
If you are using using cronjob to execute commands directly ... you will need to write a cronjob entry for each command and dealing with scheduling those commands from there
Level 1
@ehab.aboshehab the part with only having to write one cronjob is the one I got from the docs, forgot to mention it.
When using the additional ->withoutOverlapping() the command then should not be executed if the former one is still running?
Level 122
@aescaru i would have suggested that but you insist the command finishes in 10 seconds
Level 1
@Snapey it seemingly does. or doesnt now that I think about it. the log has been in place for a few hours now, so far No noticable decrease in performance regarding the time. But as this only occurs every few days I guess it might get clogged up over time
Level 122
@aescaru Where does the command get the data from that it needs? At 10 seconds I assume you are calling some external API.
If from the local database then you might need to consider some targetted indexing.
Level 1
@Snapey It is all internal. I sadly cant alter the table I get the data in order to update the my table. in that table the references to my table are stored as an array in a column. In order to update my table I have to get all items over Items::all() then get all data from the other table (naming it refernceTable here for simplification).
I then loop over my items collection and over the referenceTable in the foreach loop and check if the items are present in the array-column of referenceTable. I then have to run some simple checks to select the correct entry in referenceTable and then update the respective row in items.
Level 122
@aescaru maybe the data is larger or smaller at different times of the month, leading the job to take longer and longer until it overlaps with itself. Two threads doing the same thing will then make each take longer, increasing the chance that neither will be finished in the next minute causing a third job to be running, again making the other two run slower, and suddenly your server starts to run out of resources.
Best to add the without overlapping just in case.
Is this really something you need to run every minute?
Level 1
@Snapey Sadly yes. This would have been way easier to solve with simple relations but got to work with what I have + Otherwise I would have most likely set up a socket but its not possible at the hoster. But switching to scheduler looks like the way to go anyways. Running the scheduler every minute is fine though I guess?
Level 3
- I would suggest to use
withoutOverlapping(), - you can create new column with boolean flag
is_checkedfor example in order to prevent checking the raw two times (If that's the case). - Why not consider using the column datatype
json insteadofarray, it has more flexibility inlaravelto filter in
Level 1
@ehab.aboshehab Like i said: I sadly cant alter the table that has the array in it. otherwise yes. But things I will consider next time I have to set sth up like this from ground
Please or to participate in this conversation.