Solved

PHP MySQL Large process

Posted on 2013-06-20
11
468 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
  • 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 108

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
 
LVL 34

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 108

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

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 108

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 34

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 108

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now