Is cronjob the right way to send a lot of notification emails via PHPMailer using SMTP?

Question

I have a web app (PHP/MYSQL) where each time someone submits an entry in my database via a form in my app, it will send a notification email to everyone associated with that entry that chose to be notified.

For example, there's a football picking pool with 15 people in it. When the 16th person submits their entry, anywhere from 0-15 people will get a notification that there is a new entry. Could be 1, could be all 15, likely will be about half of them.

But my site could have 100s of football pools, some with 5 people, some with 100 people.

So I setup a separate table that records basic info about each entry and has a column of "sent" set to "0".

Then I have the cronjob below run every MINUTE, which looks for all entries that have a "0" (which means the people associated with that entry haven't been notified yet) and loops through and sends a unique email to each person associated. I send unique emails because each email has an unsubscribe link and info specific to that person. After sending the email, it updates all of those entries "sent" column to be "1" so it will be ignored moving forward.

I send emails using PHPMailer SMTP via Amazon SES.

<?php
require_once("includes/session.php");
require_once("includes/connection.php");
require_once("includes/functions.php");
require 'phpmailer/PHPMailerAutoload.php';

//find all new entries with sent = 0, loop thru and send emails
$stmt = $pdo->prepare("SELECT * FROM cron_email_notify WHERE sent = 0");
$stmt->execute();
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    //bunch of variables set here (which i'll leave out for brevity's sake) to be used in the emails (like firstname, etc)

        //Next, send a unique email to all people who have chosen to be notified
        $stmt = $pdo->prepare("SELECT * FROM entries WHERE poolid = ? AND notify = 'yes'");
        $stmt->execute([$poolid]);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            //get emails into array
            $notifyemailsarray[$row['email']]=$row;
        }

        /// Send notification email to all people who wish to be notified, if any
        $mail = new PHPMailer;
        //$mail->SMTPDebug = 3;                               // Enable verbose debug output
        $mail->isSMTP();                                      // Set mailer to use SMTP
        $mail->SMTPKeepAlive = true;  //Helps with speed for multiple emails
        $mail->Host = 'tls://email-smtp.us-east-1.amazonaws.com';  // Specify main and backup SMTP servers
        $mail->SMTPAuth = true;                               // Enable SMTP authentication
        // and so on with other phpmailer info which I cut out for brevity's sake

        if (!empty($notifyemailsarray)) {
            foreach($notifyemailsarray as $email => $details)   {
                // Assemble the fullname here
               $fullname = $details['firstname'] . ' ' . $details['lastname'];
               $mail->addAddress($details['email'], $fullname);
               $mail->Body    =  "(leaving out for brevity's sake)";
               $mail->AltBody = "(leaving out for brevity's sake)"; 
                if(!$mail->send()) {
                    echo 'Message could not be sent.';
                    echo 'Mailer Error: ' . $mail->ErrorInfo;
                } else {
                    //email sent
                }
                // Clear all addresses for next loop
                $mail->clearAddresses();
            }
        } else {
            //no people  are set to be notified 
        }
        $mail->SmtpClose();  
    //update cron_email_notify table's sent field to 1, so we know not to send again
    $stmt = $pdo->prepare("UPDATE cron_email_notify SET sent = 1 WHERE poolid = ? and sent = 0");
    $stmt->execute([$poolid]);
}
?>

Everything works fine in testing, but I only have tested where it sends maybe 10 emails (i.e. small potatoes)

My app is going to get a lot of submissions and should keep growing over time so I just want to find out of if this could be a potential problem (I have very little experience with having cronjobs running in the background, so not sure how insane the action/traffic would have to be to affect site's performance).

I'm concerned because the cronjob script above seemed to take about 5 seconds to finish when sending 10-15 emails.

Now, let's say a cronjob starts on my live site after there was an influx of entries and it has to send 200 emails. If the cronjob takes over a minute to run, the next cronjob will start (since they run every minute) and couldn't that just totally bog down my system? Or can the same cronjob run while the other is still running without a problem?

Obviously, many sites send tons of notification emails so this has to be a common need that I'm sure has a good solution, so just curious anyone's thoughts/advice/etc. Maybe the way I'm doing it is fine until I get so much traffic that it would be a great problem to have. Or maybe there is something more I could do now to help offset possible bogged down system?

Answer 1

I worked at a company that sends notifications for over half the public schools in the USA. We sent on the order of 10 million emails per day.

Your code is single-threaded, so it will send emails serially, one by one, and this might take a while. You asked what happens if it takes longer than the interval between your cron jobs? That is, if your cron job runs every minute, but at some point it starts your script while the previous script is still running. Then two invocations of your script would both be processing the same batch of emails, and your users will start receiving duplicate emails.

For this reason, it's probably better to run a daemon. That just means that instead of cron starting your PHP script every minute, you code the PHP script to run a while (true) loop and never exit. At the end of the loop, make the script sleep() for up to 60 seconds. Then it goes to the top of the loop, checks the database to see if there's a batch of emails to run, and processes the batch. Then it goes back to sleep, for a number of seconds equal to 60 minus the time it took to process the batch (but no less than zero seconds).

That way it will never run two apps at the same time if there's a surge, even if one batch of emails takes for example 75 seconds. Your daemon's sleep calculation would make it sleep the minimum of 0 seconds, and then it would just start on the next batch immediately. Hopefully the surge is temporary, and the app can catch up at some point. Otherwise your app will keep falling further and further behind schedule.

Your app should write out to a log file how many emails it is sending, and how long it is sleeping between batches. You should monitor this file so if it is unable to keep up with the demand, you'll know. Once you get really professional, you'll put this data into a Grafana dashboard or something similar.

If your traffic grows to the point where the single-threaded app consistently can't send a batch of emails in 60 seconds, then you'll have to run multiple email sender apps in parallel. PHP is lousy at multi-threaded programming, so you'll probably want to use Java or Go.

Trying to make multiple threads poll the database is a bad idea, because you get race conditions and lock contention. What we did at my company was make one thread poll the database. When it found a batch of emails to send, it pushed them into a message queue (we used ActiveMQ, but there are others). Message queues make it easy for multiple threads to pull items off the queue. Then it's easy for multiple concurrent email-sending app threads to process batches of emails in parallel.

P.S.: Do you realize that your code will mark the pool with sent=1 even if some or all of the emails failed to be sent?