Solved

PHP MySQL Large process

Posted on 2013-06-20
11
482 Views
Last Modified: 2013-06-27
I have a website that has over 700 registered users and we attach specific users to certain jobs.  After that process, we have a form that we send all these specific users an email notifying them that they have been attached to a job.

If the list is large, 100+ users, the process takes a long time and many times returns a timed out page.  We are on a dedicated server and I have been told it might be best to create a cron job that is triggered every minute and checks to see if a flag is set calling for a mass email to be processed to these users.

My first question, will it degrade the performance of the server if a cron job is being ran every minute on a server even if it is not processing a large request?

Secondly, is there a way a php script can be created that calls for a cron job to be initiated? For example, currently we add the users we need emailing to a separate database list, when we are ready to send to them...  a push of a button will start the cron process.  Is that possible?

Third and final, other than third party mass-emailers, what is the best way of sending emails through php to a great number of users. I have tried simply cycling through a list of emails and sending one at a time and I tried batching all the emails into one BCC.  Am I missing some way that is easier and more efficient on a Linux server?
0
Comment
Question by:the-miz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
ID: 39262563
I focus on the second and third options as you are talking about using push button why do you need to call cron process?? If you manually want to start job manage it to multi part like 1-50, 50-100 and start.

Second one is while you are working on mass mailing why not use a mass mailing system understanding it's features as you required.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39262627
Wait a minute... You're looking at numbers in the hundreds (or thousands) and you're seeing slow results?   Something else is wrong.  Maybe the queries need to be optimized!  Check my colleague's article here:
http://www.experts-exchange.com/Database/MySQL/A_1250-3-Ways-to-Speed-Up-MySQL.html

A CRON job running once a minute will only degrade server performance if the CRON job itself is slow.  A once-a-minute event is nothing to modern servers.

If you need to send a lot of emails, consider using phpMailer
0
 

Author Comment

by:the-miz
ID: 39262655
Hundreds, currently we are getting a list with checkboxes. Normally we select all and submit form.  It cycles through the list of emails and sends one email to each user through the php sendmail protocol and then adds the user to a database for later reporting (we can see what mail was successfully sent out).

Should we be doing it another way?

PHPMailer? How does this work better for mass emails?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 35

Accepted Solution

by:
gr8gonzo earned 250 total points
ID: 39262660
This is a pretty broad question, so you're probably not going to get a final solution of code, but rather an approach to use.

1. Regarding this: "My first question, will it degrade the performance of the server if a cron job is being ran every minute on a server even if it is not processing a large request?"

Usually the problem with running a cron job every minute (especially with larger processes) is running into a race condition. Let's say you have a cron job that sends out 700 emails and it takes 3 minutes to run, and it updates each person as it sends out their email.

1:00 PM The cron job begins and it selects your 700 users and starts sending them.

1:01 PM The cron job from 1:00 PM is on email #300, and now a new cron job starts and selects the next batch of users (the remaining 400), and begins to send emails to those users. The remaining 400 users will get TWO emails (one from the first cron job and another from this second cron job).

1:02 PM Yet another cron job kicks off. The first and second cron jobs are now on email #600, so the third cron job selects the remaining 100 and begins sending email. Now the last 100 people will get THREE emails!

This is a race condition - when two or more scripts try to perform the same job on the same data when you only intended the job to be done once.

That said, usually mass mailing engines separate out the tasks of job creation and job performance.

A typical engine will do this:

PHASE 1: JOB CREATION
1. Create a unique ID for the mailing (e..g auto-incremented ID for a "mailing_jobs" table) job.

2. Run an update query on a selection of users that do not have a job ID and that meet some mailing criteria:
    a. Update the user records with the unique job ID.
    b. Update the mailing criteria so once the record won't be reprocessed accidentally later.

3. Re-select all users that contain the unique job ID to pull the users' information.

4. Populate a separate database table (e.g. "mailings") containing the job details (the message to be sent, the email addresses, etc...)

PHASE 2: JOB PERFORMANCE
A cron job runs every minute that processes 1000 (for example) mailings at a time:
1. Generates a unique ID (job processor ID, for example).

2. Update up to 1000 records in the mailings table that do not have a job processor ID and set the job processor ID to the unique ID from step 1.

3. Re-select all records that have the job processor ID from step 1.

4. Loop through each record and send the mailing.


All this said, you should also be very cautious about any mass mailings unless the users have specifically opted in to receive your mailings. Otherwise, you'll just end up getting blacklisted and all your efforts will go to waste (not to mention that you might even lose your hosting account).
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 39262676
Not sure... but I think you would see much better performance from phpMailer.  If you're using the php mail() command, the connection protocols are lengthy and occur once for each time you call the function.  Multiply anything, even a small thing, by a hundred and it can begin to make a difference.

If it were my task, the first thing I would do is try to isolate the part(s) of the code that show performance problems.  This little script contains a class you can use as a timer.  Examples of how to wrap the timer around blocks of code are shown after line 160.  Once you know exactly where the slow code resides you can begin to optimize.

<?php // RAY_class_Stopwatch.php
error_reporting(E_ALL);


// DEMONSTRATE A SCRIPT TIMER FOR ALL OR PART OF A SCRIPT PHP 5+
// MAN PAGE http://php.net/manual/en/function.microtime.php


class StopWatch
{
    protected $a; // START TIME
    protected $s; // STATUS - IF RUNNING
    protected $z; // STOP TIME

    public function __construct()
    {
        $this->a = array();
        $this->s = array();
        $this->z = array();
    }

    // A METHOD TO PROVIDE A FINAL READOUT, IF NEEDED
    public function __destruct()
    {
        $ret = $this->readout();
        if (!$ret) return FALSE;
        echo
          __CLASS__
        . '::'
        . __FUNCTION__
        . '() '
        ;
        echo "<b>$ret</b>";
        echo PHP_EOL;
    }

    // A METHOD TO REMOVE A TIMER
    public function reset($name='TIMER')
    {
        // RESET ALL TIMERS
        if ($name == 'TIMER')
        {
            $this->__construct();
        }
        else
        {
            unset($this->a[$name]);
            unset($this->s[$name]);
            unset($this->z[$name]);
        }
    }

    // A METHOD TO CAPTURE THE START TIME
    public function start($name='TIMER')
    {
        $this->a[$name] = microtime(TRUE);
        $this->z[$name] = $this->a[$name];
        $this->s[$name] = 'RUNNING';
    }

    // A METHOD TO CAPTURE THE END TIME
    public function stop($name='TIMER')
    {
        $ret = NULL;

        // STOP ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                // IF THIS TIMER IS STILL RUNNING, STOP IT
                if ($this->s[$name])
                {
                    $this->s[$name] = FALSE;
                    $this->z[$name] = microtime(TRUE);
                }
            }
        }

        // STOP ONLY ONE OF THE TIMERS
        else
        {
            if ($this->s[$name])
            {
                $this->s[$name] = FALSE;
                $this->z[$name] = microtime(TRUE);
            }
            else
            {
                $ret .= "ERROR: CALL TO STOP() METHOD: '$name' IS NOT RUNNING";
            }
        }

        // RETURN AN ERROR MESSAGE, IF ANY
        return $ret;
    }

    // A METHOD TO READ OUT THE TIMER(S)
    public function readout($name='TIMER', $dec=3, $m=1000, $t = 'ms', $eol=PHP_EOL)
    {
        $str = NULL;

        // GET READOUTS FOR ALL THE TIMERS
        if ($name == 'TIMER')
        {
            foreach ($this->a as $name => $start_time)
            {
                $str .= $name;

                // IF THIS TIMER IS STILL RUNNING UPDATE THE END TIME
                if ($this->s[$name])
                {
                    $this->z[$name] = microtime(TRUE);
                    $str .= " RUNNING ";
                }
                else
                {
                    $str .= " STOPPED ";
                }

                // RETURN A DISPLAY STRING
                $lapse_time = $this->z[$name] - $start_time;
                $lapse_msec = $lapse_time * $m;
                $lapse_echo = number_format($lapse_msec, $dec);
                $str .= " $lapse_echo $t";
                $str .= $eol;
            }
            return $str;
        }

        // GET A READOUT FOR ONLY ONE TIMER
        else
        {
            $str .= $name;

            // IF THIS TIME IS STILL RUNNING, UPDATE THE END TIME
            if ($this->s[$name])
            {
                $this->z[$name] = microtime(TRUE);
                $str .= " RUNNING ";
            }
            else
            {
                $str .= " STOPPED ";
            }

            // RETURN A DISPLAY STRING
            $lapse_time = $this->z[$name] - $this->a[$name];
            $lapse_msec = $lapse_time * $m;
            $lapse_echo = number_format($lapse_msec, $dec);
            $str .= " $lapse_echo $t";
            $str .= $eol;
            return $str;
        }
    }
}



// DEMONSTRATE THE USE -- INSTANTIATE THE STOPWATCH OBJECT
$sw  = new Stopwatch;

// SET A STOPWATCH NAME THAT REFLECTS THE PARTS OF THE SCRIPT WE WANT TO TIME
$g_timer = 'GOOGLE TIMER';

// START A TIMER TO GET ELAPSED TIME FOR A CALL TO GOOGLE
$sw->start($g_timer);

// PERFORM SOME ACTIVITY THAT YOU WANT TO TIME (READS GOOGLE WEB PAGE)
$page = 'https://www.google.com';
$html = file_get_contents($page);

// GET A READOUT OF THE TIMER WHILE IT IS STILL RUNNING
echo nl2br($sw->readout($g_timer));
echo "<br/>" . PHP_EOL;

// PERFORM SOME OTHER ACTIVITY (READS GOOGLE WEB PAGE AGAIN)
$page = 'https://www.google.com';
$html = file_get_contents($page);

// STOP THE TIMER AND GET A READOUT WITH SHORT DECIMALS
$x = $sw->stop($g_timer);
echo nl2br($sw->readout($g_timer, 1));
echo "<br/>" . PHP_EOL;




// START A SECOND TIMER
$y_timer = 'YAHOO TIMER';
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT (ONE IS STOPPED AND ONE IS STILL RUNNING)
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// STOP ALL OF THE STOPWATCHES
$sw->stop();

// REPORT THE STOPWATCHES CONTENT AGAIN
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// TRY TO STOP A TIMER THAT IS NOT RUNNING
$x = $sw->stop($g_timer);
var_dump($x);
echo "<br/>" . PHP_EOL;
echo "<br/>" . PHP_EOL;



// START THIS TIMER OVER AGAIN
$sw->start($y_timer);

// PERFORM SOME OTHER ACTIVITY THAT YOU WANT TO TIME
$page = 'http://weather.yahoo.com/';
$html = file_get_contents($page);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ONE OF THE STOPWATCHES
$sw->reset($g_timer);

// REPORT THE STOPWATCHES CONTENT
echo nl2br($sw->readout());
echo "<br/>" . PHP_EOL;

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";



// REMOVE ALL OF THE STOPWATCHES
$sw->reset();
echo "ALL STOPWATCHES HAVE BEEN REMOVED";

// REPORT THE STOPWATCHES CONTENT (SHOWS NOTHING)
echo nl2br($sw->readout());

// SHOW THE OBJECT
echo "<pre>";
var_dump($sw);
echo "</pre>";

// SHOW THE DESTRUCTOR IN ACTION
$sw->start('Foo');
echo "<pre>";
unset($sw);
echo "</pre>";

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39262684
@Jonathan: You should write that answer into an EE article!  Cheers, ~Ray
0
 

Author Comment

by:the-miz
ID: 39262738
That was a lot to take in gr8gonzo.

So is it possible to run a cron every minute that accesses this database of users needing emailing with a flag that equals 0, the cron first changes all users flag to 1 so the next time it fires of in 60 seconds it will not reattempt the same users. The first cron job takes all users with flag equalling 1 and begins emailing. When each is complete, it is deleted from list and added to a new list for tracking purposes later.

Sounds a bit unorthodox but would it work?

Ray,

Sounds like I would have to adjust the code in our live environment so it writes its output to a file for viewing only by administrators.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39262803
... writes its output to a file for viewing only by administrators.
Yes.  You can probably use error_log() to get a quick implementation.
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39262884
That was a lot to take in gr8gonzo.
Yeah, but a good mass mailing process isn't exactly the easiest thing in the world to build. I didn't even cover things like opt-out compliance, tracking, drip, etc... :) It's been very rare that I've seen someone want to do "just a simple mass mailing" that hasn't turned into a monster later.

I think what you've suggested with a simple 0/1 flag is probably fine. Just remember that having one flag means that you can only do one mailing to those users at a time.

Ray also correctly pointed out that your script really shouldn't time out with 700 users, but if you have low-end hardware or are on a shared server or low-end VPS, you might have some slow performance issues. You can set a higher time limit for your script by adding this to the top of your script:

set_time_limit(###);

where ### is the number of seconds until the script times out. For example, set_time_limit(600); will set the time limit to 10 minutes (600 seconds).

You should write that answer into an EE article!
Or just build it out and throw it on SourceForge...
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39262956
Or set up shop to compete with ConstantContact ;-)
0
 

Author Comment

by:the-miz
ID: 39263034
I tried ConstantContact and JangoMail, both gets expensive. I will let you know how my progress goes.
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question