Extremely High CPU Usage

[OcR Envy]

The following cron job script has been reported to me by my host as using a lot of CPU. To the point where our site has started to return error messages while it is running. I am looking for suggestions on how to optimize any way possible.

PHP Code:

// ---------------------------------------------------
// Start Set PHP Environment
// ---------------------------------------------------

error_reporting(E_ALL & ~E_NOTICE);
if (!is_object($vbulletin->db))
{
    echo "No vBulletin Found!";
    exit;
}

// ---------------------------------------------------
// Start Cronjob
// ---------------------------------------------------

global $vbulletin;
global $db;


$updatetime=time(); 
$usersoffline=0;
putenv("TZ=US/Eastern");
$checktime=date("h:i A", time());
$checkdate=date("m/d/y", time());

$vbulletin->db->query_write("UPDATE xblusers SET time='$checktime', date='$checkdate' WHERE title='checked'");

if ($xml = simplexml_load_file("http://xboxapi.duncanmackenzie.net/gamertag.ashx?GamerTag=outcast%20reborn")) {
    $account=$xml->AccountStatus;
    if($account=="Gold") {
        $vbulletin->db->query_write("UPDATE xblusers SET time='00:00PM', date='00/00/00' WHERE title='crashed'");
        $logmsg = "Whos on XBL Updated";

    } else {
        $offline="true";
    }
} else {
        $offline="true";
}

if($offline=="true") {

    $grabchecked = $vbulletin->db->query("SELECT time, date FROM xblusers WHERE title='crashed'");
    $gchecked=$vbulletin->db->fetch_array($grabchecked);
    $checkedt=$gchecked['time'];
    
    
    if($checkedt=="00:00PM") {
        $vbulletin->db->query_write("UPDATE xblusers SET time='$checktime', date='$checkdate' WHERE title='crashed'");
    }
    $logmsg = "Whos on XBL Not Updated: Stream Down<br />";
    log_cron_action($logmsg, $nextitem, 0);
    die;
}


$gamertaggrab=$vbulletin->db->query("SELECT userfield.field5, userfield.field11, userfield.userid FROM userfield INNER JOIN user ON user.userid = userfield.userid AND userfield.field5 > '' WHERE usergroupid IN (6,48,52,51,26,58,60,55,46)");
while($userrow = $vbulletin->db->fetch_array($gamertaggrab)) {    
    $gamertag=$userrow['field5'];
        
    if ($gamertag) {
        if ($xml = @simplexml_load_file("http://xboxapi.duncanmackenzie.net/gamertag.ashx?GamerTag=". urlencode($gamertag))) {
            $coretag=$xml->Gamertag;                
            $tagvalid=$xml->PresenceInfo->Valid;        
            $onlinestatus=$xml->PresenceInfo->Online;        
            $statustext=$xml->PresenceInfo->StatusText;        
            $title=$xml->PresenceInfo->Title;        
            $info=$xml->PresenceInfo->Info;        
            $info2=$xml->PresenceInfo->Info2;        
            $lastseen=$xml->PresenceInfo->LastSeen;                
                    
            $statustext=addslashes($statustext);        
            $title=addslashes($title);        
            $info=addslashes($info);        
            $info2=addslashes($info2);
            
            if ($onlinestatus=="true") {            
                $online=1;
                $logmsg = $logmsg . "<li><a href='http://www.outcastreborn.com/member.php?u=" .$userrow['userid']. "'>" . $gamertag . "</a>: " . " " . $statustext . " " . $title . " " . $info2 . " </li>";        
                $sms_string = $sms_string + $gamertag . " ";
            } else {            
                $online=0;
                $usersoffline = $usersoffline + 1;
            }                    
                
            foreach ($xml->xpath('//Game') as $gameset) {  
                $gamename=$gameset->Name;            
                $gametile=$gameset->Image32url;                        
                $gamename=addslashes($gamename);

                $vbulletin->db->query_write("INSERT IGNORE INTO xblgames (name,tile) VALUES ('".$gamename."','".$gametile."')");                                        
            }    

            if ($tagvalid=="true")    {
                $vbulletin->db->query_write("INSERT INTO xblstatus (userid,gamertag,online,statustext,title,info,info2,lastseen,updatetime)             
                VALUES ('".$userrow['userid']."','$coretag','$online','$statustext','$title','$info','$info2','$lastseen',FROM_UNIXTIME($updatetime)) ON DUPLICATE KEY             
                UPDATE userid='".$userrow['userid']."',gamertag='$coretag',online='$online',statustext='$statustext',title='$title',info='$info',info2='$info2',
                lastseen='$lastseen',updatetime=FROM_UNIXTIME($updatetime)");                                    
                        
                $vbulletin->db->query_write("UPDATE userfield SET field12='$statustext',field13='$title',field14='$info2' WHERE userid=".$userrow['userid']);
            }
        }    
    }

    $gamertag=$userrow['field11'];    
        
    if ($gamertag) {

        if ($xml = @simplexml_load_file("http://xboxapi.duncanmackenzie.net/gamertag.ashx?GamerTag=". urlencode($gamertag))) {
            $coretag=$xml->Gamertag;                
            $tagvalid=$xml->PresenceInfo->Valid;        
            $onlinestatus=$xml->PresenceInfo->Online;        
            $statustext=$xml->PresenceInfo->StatusText;        
            $title=$xml->PresenceInfo->Title;        
            $info=$xml->PresenceInfo->Info;        
            $info2=$xml->PresenceInfo->Info2;        
            $lastseen=$xml->PresenceInfo->LastSeen;    
                
            $statustext=addslashes($statustext);        
            $title=addslashes($title);        
            $info=addslashes($info);        
            $info2=addslashes($info2);                            
                    
            if ($onlinestatus=="true") {            
                $online=1;
                $logmsg = $logmsg . "<li><a href='http://www.outcastreborn.com/member.php?u=" .$userrow['userid']. "'>" . $gamertag . "</a>: " . " " . $statustext . " " . $title . " " . $info2 . " </li>";        
                $sms_string = $sms_string + $gamertag . " ";
            } else {            
                $online=0;
                $usersoffline = $usersoffline + 1;
            }                
                        
            if ($tagvalid=="true")    {
                $vbulletin->db->query_write("INSERT INTO sxblstatus (userid,gamertag,online,statustext,title,info,info2,lastseen,updatetime)             
                VALUES ('".$userrow['userid']."','$coretag','$online','$statustext','$title','$info','$info2','$lastseen',FROM_UNIXTIME($updatetime)) ON DUPLICATE KEY             
                UPDATE userid='".$userrow['userid']."',gamertag='$coretag',online='$online',statustext='$statustext',title='$title',info='$info',info2='$info2',
                lastseen='$lastseen',updatetime=FROM_UNIXTIME($updatetime)");
                    
                $vbulletin->db->query("UPDATE userfield SET field15='$statustext',field16='$title',field17='$info2' WHERE userid=".$userrow['userid']);                                                            
            }                        
        }
    }
}
$logmsg = $logmsg . "<li> Users offline: " . $usersoffline ."</li></ul>";
log_cron_action($logmsg, $nextitem, 0); 


The main SQL

PHP Code:

SELECT userfield.field5, userfield.field11, userfield.userid FROM userfield INNER JOIN user ON user.userid = userfield.userid AND userfield.field5 > '' WHERE usergroupid IN (6,48,52,51,26,58,60,55,46) 


Returns 87 Results. The while statement runs 87 times.


I am not giving permission to reuse any of this code at this time.

[IdanB]

what's the current cronjob interval ? do you run it every 1m ? 1h ?
What's your current DB/table size (is it over few million of records) ?

If you are using big DB's with over millions of records, the queries can hog some high cpu if db structure not properly configured (specific lack of index / key fields)

Also, i see in your script you are fetching data off a 3rd-party website. Depands on internet traffic/connectivity to your hosting, should operation can take few seconds to perform, it might peak the cpu during it as well.

In general, if possible, try to reduce number of times this cronjob runs.

[OcR Envy]

It currently runs once every 15 mins.

The DB/table size isn't large enough that I would see any issues.

Do you think cURL might be better than simpleload_xml_file? At least then I can set a time out I suppose.

[IdanB]

Originally Posted by OcR Envy

Do you think cURL might be better than simpleload_xml_file? At least then I can set a time out I suppose.

Yea, worth trying.

Also, if you own the box it's running on, try to run "top" command, to see which process spikes up the cpu, so you'll have some basic knowledge of what's wrong in code.
If it's mysql or some other thing, etc.