SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Hi,

    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?

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

    Do bare in mind though, that processing 100 users every 5 minutes, only allows 3 seconds per user before an overlap would occur.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello Anthony,

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

    Quote Originally Posted by AnthonySterling View Post
    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.

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

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi Lamp,

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

    Quote Originally Posted by lampcms.com View Post
    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.

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  7. #7
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  8. #8
    SitePoint Member
    Join Date
    Nov 2007
    Location
    UK
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Which Web Design Company
    Review and Rate Web Design Companies around the world!

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,869
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by wwdc View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  10. #10
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    1st, Thanks for all the suggestions.
    I guess there is no pure Php solution to get this Job done! That is a shame
    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.

    Questions:
    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 ?

    Regards,

    Anoox search engine volunteer

    www.anoox.com

  11. #11
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    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.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  13. #13
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,869
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by WorldNews View Post
    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 ?
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  14. #14
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 http://www.anoox.com/xdir/emails.php -dump 1 > /dev/null


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

    Regards,


    Quote Originally Posted by felgall View Post
    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.

    Anoox search engine volunteer

    www.anoox.com

  15. #15
    We're from teh basements.
    Join Date
    Apr 2007
    Posts
    1,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  16. #16
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Quote Originally Posted by World Wide Weird View Post
    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.

    Anoox search engine volunteer

    www.anoox.com

  17. #17
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by WorldNews View Post
    So how do you set Cron to run a program every 5 minutes?
    Google cron every 5 minutes

    Quote Originally Posted by WorldNews View Post
    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 http://www.anoox.com/xdir/emails.php -dump 1 > /dev/null
    Nope, try this way

    */5 * * * * /usr/bin/links http://www.anoox.com/xdir/emails.php -dump 1 > /dev/null
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  18. #18
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you.

    Quote Originally Posted by pmw57 View Post
    Google cron every 5 minutes


    Nope, try this way

    */5 * * * * /usr/bin/links http://www.anoox.com/xdir/emails.php -dump 1 > /dev/null

    Anoox search engine volunteer

    www.anoox.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •