How to send large (10,000+) number of emails via Php/MySQL?


The background:

We have a large community base, more than 1Mill and rapidly increasing, that we regularly need to send large number of emails to, generally about 25,000 emails at a time (per day).

The community records, emails, are in a MySQL DB.
We used to send these emails via JSP but for various reasons we need to redo it in Php. The code in JSP has modules (classes) that would regulate how many emails were sent out every few minutes, keeping it at about 100 emails per 5 minutes.

The mail server is Qmail.

The objective:

Write the Php code that sends emails out every 5 minutes, 100 emails at a time.

The problem:

If you write a Php code that gets the emails from MySQL and then loops through them to send them out 100 emails at a time via the mail function and then stops for 5 minutes via sleep command, this will kill the MySQL DB from where the emails are selected from for Hours and Hours which means kills all dynamically generated web pages of the Web site.

So what is the solution?
What do you suggest is the right way of achieving the above objective?


Simply, cron.

Create a table to hold the email addresses (or id) of processed users, then create a cron job to run every 5 minutes which selects 100 unprocessed users, mails them, then marks them as processed.

No sleep, and no timeouts.

Bish. Bash. Bosh. :wink:

Do bare in mind though, that processing 100 users every 5 minutes, only allows 3 seconds per user before an overlap would occur.

Hello Anthony,

You see the problem with the Cron idea is that the select process for the email list changes all the time.

Do you have a cgi version of php installed (some call it cli version). I mean, if you can start the program from command like, then you can invoke your cli php script via exec() from your web application.
exec(/path/to/php -q /path/to/emailscript.php > /dev/null 2>&1 &);
That’s it.
You can have this done from inside the loop that sends 1000 emails then sleeps 1 minute.

Hi Lamp,

No, we do not have cgi version of Php. At least I am 99% sure we do not.

You may still have it. By default php installs as apache module and as cli
you can try it. Find the path to your php binary, usually it’s in /usr/bin/php or in /usr/local/bin/php
the from command like type /usr/bin/php -i
If you see the readable output, then you got the cli version.

Remember to put single quotes around the command inside the exec(), I forgot to include them. Also without the cli version you will not be able to use the cron solution.

Then your other option will be to write a program in perl. You do have perl on your server, right?

We use the idea of cron jobs, but in a different way:

  1. we create a new mysql table “emails_queued”
  2. we dump all the emails to be sent into this table, including all the info in separate columns (to, to-name, from, from-name, subject, body, html body, attachments links, etc)
  3. we run the cron every 5 mins to send next 100 emails from the queue
  4. when an email is sent, delete the row from the table or mark it sent

works perfectly.

That’s basically the method I use. When sending huge numbers of emails you need to be able to track which ones you have sent and which are yet to be sent whichever way you set things up anyway.

With the JSP version if the JSP crashed after sending out 103452 emails how did you tell the script that those ones had been sent and to start from the next one when you reran the script?

The only difference with using a cron is that you deliberately stop the script every so many emails so as to release resources rather than having all the resources tied up and only occassionally crashing the script before all the emails are sent. The advantage of the cron is that it restarts automatically whereas if your single tie up all the resources until it crashes script needs to be manually restarted.

1st, Thanks for all the suggestions.
I guess there is no pure Php solution to get this Job done! That is a shame :frowning:
I guess from the majority of answers the best solution for getting this Job
done is to go the Cron way.

But you know the problem with going the Cron way is that the emails that we send are very complex, that is many Web selections are made before an email
campaign is complete. And the emails have variables in them which variables get their values from the MySQL DBs. I mean these are not simple Text or HTML emails that we are sending but are emails that have many user specific information in them.

Anyway, about the Cron idea, would this be the way to get this job done:

1- Create the mailing list based on selections made via current Web page
2- Enter the text of the email to be sent via a form
3- Write all the emails selected into a temp MySQL table, call it Temp
4- call the Cron then to execute a Php program which Php page will
simply load 100 emails from Temp which are not marked as sent, send these 100 emails, mark them as sent and then be done.
5- Cron keeps running every 5 mins until all emails are sent.

A- How do we invoke the Cron at Step 4?
B- How do we pass to the Php program that the cron is calling the text of the email that was created ?


Would you blame a hammer if you use it in place of a wrench?
I wonder why one don’t use mail server to handle email, and uses web server instead.
What a really shame.

Please advise the OP on where they can go to learn about setting up and using a mail server for this particular task.

A - you have it backwards - the cron invokes the PHP and the PHP looks to see what emails there are to send out and if it finds some then it sends out the specified number of emails flagging those ones as sent. The next time the cron invokes the PHP the process is repeated. If there are no emails to send the PHP should just stop once it has determined that each time it is invoked when there is nothing to send.

You would just set up the cron to run the PHP every five minutes of every hour of every day.

B - set up the manual process part to store the emails to be sent in a database (your steps one and two - you can’t use step three because the temp tables would be gone before step four - you need to store the info in tables where the data stays at least until after the emails have been sent). You either flag the emails as sent using an extra field in the table when they are actually sent or you could just delete the entries.

The way I work it for newsletters that I send is that I have two tables - one holds the content for each newsletter, the second has all trhe email addresses, an identifier as to which newsletter to send to that address, and a flag to indicate it has been sent. The PHP called from the cron reads email addresses, builds and sends the appropriate newsletter then flags that email as having been sent. It then repeats for the next email address until the specified number of emails have been sent or there are no more to send at which point the PHP checks if there are any newsletters where all the emails have been sent - if sop then that newsletter and all the email addresses it was sent to are deleted. The PHP then stops.

Hello Stephen,

So I set the Php program exactly as you specified below to be called
by the Cron. And it all works fine if the Cron is calling it once every hour,
but if I specify it to be called every 5 minutes then it is not called!

So how do you set Cron to run a program every 5 minutes?

Is’nt this the way that you set cron to run a program every 5 minutes?

5,10,15,20,25,30,35,40,45,50,55,60 * * * * /usr/bin/links -dump 1 > /dev/null

if not, how do you tell the Cron to run a program every 5 minutes?


Note that PHP’s mail() function opens a new socket for every mail sent. If you’re looping through a large recipient list and sending a message to each one individually, you could quickly run out of sockets. If this happens to you, use the BCC: field to send mail to many recipients in one shot.

We have completely addressed this as per last comments of this thread, that is I am having a Cron fire a Php program every few minutes which sends only few (like 50) emails per few minutes.

Google cron every 5 minutes

Nope, try this way

*/5 * * * * /usr/bin/links -dump 1 > /dev/null

Thank you.