projects
asked on
Replace cron jobs with special mysql functions?
I have an application which is starting to add up quite a lot in terms of having to add cron jobs to monitor certain things.
An example is that when a certain data is inserted into the database, a cron job looks at this data, then looks at a setting in another table to know if it should send a notice to an email address configured.
Now, we need to add another similar function where if a critical piece of data comes into the db and if there is a 1 in another table column, that again, an email should be sent. The problem is that this test needs to be done every single time data is inserted which means I would have to run this cron task every second of the day, 24/7.
That seems unbelievably stupid to me so I wanted to ask here because I think those telling me it's the only way to do this are perhaps not aware of some things.
An example is that when a certain data is inserted into the database, a cron job looks at this data, then looks at a setting in another table to know if it should send a notice to an email address configured.
Now, we need to add another similar function where if a critical piece of data comes into the db and if there is a 1 in another table column, that again, an email should be sent. The problem is that this test needs to be done every single time data is inserted which means I would have to run this cron task every second of the day, 24/7.
That seems unbelievably stupid to me so I wanted to ask here because I think those telling me it's the only way to do this are perhaps not aware of some things.
ASKER
If I am understanding correctly, the last part means something like this...
We might add some code in our initial data entry point which creates a queue of some sort which another application could monitor.
That second application which does nothing but wait to see if anything comes into the queue. When something does, it is the code that reads the database to find out if something should be sent by comparing the table/column values etc.
Something along those lines?
The URL you gave is closed to subscribed users only or something.
We might add some code in our initial data entry point which creates a queue of some sort which another application could monitor.
That second application which does nothing but wait to see if anything comes into the queue. When something does, it is the code that reads the database to find out if something should be sent by comparing the table/column values etc.
Something along those lines?
The URL you gave is closed to subscribed users only or something.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
However, in my mind, a better way to accomplish this without hurting performance would be to keep your cron job. Having a separate process that checks and processes the email notifications is best. It's better than trying to do everything in the database during an insert. If necessary, you could do the check in the trigger and update a flag of an email needs to be sent. A mail server could be down at the time of the insert, and doing the emailing from a separate process triggered by the from timer allows you to do more complex and robust email processing with retries and templates and all that.
The very best approach, since you perhaps want to get rid of the cron job, is to use a message queue. This is a more complex setup, best suited for high volume scenarios. It's a waste to run cron jobs when there probably aren't any inserts. With the message queue option, your application would write a message to the queue as soon as it had done and insert. You would have an application on the other end of the queue, the consumer, which would be sitting around waiting for messages. As soon as a message is written to the queue, the consumer will immediately see it and run the logic to check whether emails need to be sent or not. You can have multiple queues. Look here https://www.syncfusion.com/resources/techportal/ebooks/rabbitmq