Slow PHP/MySQL script execution

Question

I created a script to monitor several sensor value's, which reads them out of a MySQL database, does some calculations om them and then displays the data/plots a graph. The script works as designed however the execution is very slow (avg 60+ seconds).

The Database table consist out of about 500K records, with 20 entry's added every 5 minutes. So either my code is very inefficient or the large table is the cause. I however cannot see how I can improve the code a lot, so any help on that is welcome. I did not include the graph class code because removing the graphs does not have any effect on script execution time.

Below I pasted the code for review. Thanks in advance for any tips!

Index.php

<html>
</head>
<style>
form
{
    float:left;
}
p {
        font-family: Verdana;
        font-size: 12;
        border: 0;
        }
<?php
require_once('template.css');
?>
</style>
</head>
<body>
<?php
//Connect to MySQL DB
require_once('config.inc.php');
require_once('functions.inc.php');
$mysqli = new MySQLi("$dbhost", "$dbuser", "$dbpass", "$db");
require_once ('F:\wamp\www\winlog\phpgraphlib\phpgraphlib.php');

//interval selection forms
echo "<table><tr><td><p><b>Energieverbruik Volta in KW.</b><br>Kies Interval: </p>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"300\"><input type=\"submit\" value=\"5 Min\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"3600\"><input type=\"submit\" value=\"1 Uur\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"86400\"><input type=\"submit\" value=\"24 Uur\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"604800\"><input type=\"submit\" value=\"1 Week\"></form>";
echo "<form name=\"interval\" action=\"\" method=\"post\"><input type=\"hidden\" name=\"interval\" value=\"2419200\"><input type=\"submit\" value=\"1 Maand\"></form>";
echo "</td></tr></table>";
// $needles[] = "9679"; //Meeting Totaal
// $needles[] = "9680"; //Voeding A-FEED
// $needles[] = "9839"; //Voeding B-FEED
// $needles[] = "9840"; //Koeling
// $needles[] = "9841"; //INPUT UPS1
// $needles[] = "9843"; //VOEDING SDB ALG
// $needles[] = "9844"; //VERDEELKAST V01.UDB

$sensors = GetSensorIDs("1");
//Set interval from form of standard if not set
if(!isset($_POST['interval'])){
    $interval = 86400;
    $max = 1200;
}
else{
    $interval = $_POST['interval'];
}
if($interval == 3600){
    $max = 50;
}
if($interval == 86400){
    $max = 1200;
}
if($interval == 604800){
    $max = 7500;
}
if($interval == 300){
    $max = 5;
}
//get timestamps for selected interval
$timestamps = GetTimestamps($interval);

echo "<table class=CSSTableGenerator>";
echo "<tr>";
echo "<td>Datum:</td>";
foreach($timestamps as $timestamp){
    echo "<td><center>" . date("d/m/Y", $timestamp) . "<br>" . date("H:i", $timestamp) . "</center></td>";
    
}
echo "</tr>";
foreach($sensors as $sensor){
    echo "<tr>";
    $data = GetDataForSensor($sensor, $timestamps, "1");
    
    $i = 0;
    $old = 0;
    foreach($data as $datapoint){
        echo "<td align=\"right\">";
        if($interval >= 86400){
            $graph_timestamp = date("d/m",$datapoint['timestamp']);
        }
        else{
            $graph_timestamp = date("d/m h:i",$datapoint['timestamp']);
        }
        if(!isset($old)){
            $old = 0;
        }
        $new = $datapoint['value'];
        $usage = $old - $new;
        if($i != 0){
            if($interval == 604800){
                echo "<font color=\"gray\">" . round($old, 2) . "</font><br>";
            }
            echo round($usage, 2);
            ${"graphline".$sensor}[$graph_timestamp] = intval(round($usage, 2));
        }
        else{
            echo GetSensorName($sensor);
        }
        $old = $new;
        echo "</td>";
        ++$i;
    }
    echo "</tr>";
}

//PUE
echo "<tr>";
$i = 0;
foreach($timestamps as $timestamp){
    if(!isset($total_old)){
        $total_old = 0;
    }
    if(!isset($a_old)){
        $a_old = 0;
    }
    if(!isset($b_old)){
        $b_old = 0;
    }
    $PUEvars = GetDataForTimeStamp($timestamp, "1");
    $PUE = ($PUEvars['9679'] - $total_old) / (($PUEvars['9680'] - $a_old) + ($PUEvars['9839'] - $b_old));
    echo "<td><br><br>";
    if($i != 0){
        echo "<b>" . round($PUE, 3) . "</b>";
        //pass data to array for graph
        if($interval >= 86400){
            $date = date("d/m",$timestamp);
        }
        else{
            $date = date("d/m H:i",$timestamp);
        }
        $graph2_data[$date] = round($PUE, 2);
    }
    else{
        echo "<b>PUE</b>";
    }
    echo "</td>";
    $total_old = $PUEvars['9679'];
    $a_old = $PUEvars['9680'];
    $b_old = $PUEvars['9839'];
    ++$i;
}
echo "</tr>";
echo "</table>";
echo "Legende:<br>Meting Totaal: <font color=\"red\">ROOD</font><br>INPUT UPS1: <font color=\"green\">GROEN</font><br>A-FEED: <font color=\"blue\">BLAUW</font><br>B-FEED: <font color=\"purple\">PAARS</font><br>KOELING: <font color=\"aqua\">AQUA</font><br>";

//graphcolors black, silver, gray, white, maroon, red, purple, fuscia, green, lime, olive, navy, blue, aqua, teal
$graph = new PHPGraphLib(900,300,"img.png");
$graph->addData($graphline9679, $graphline9680, $graphline9839, $graphline9840, $graphline9841);
$graph->setTitle('Power in KWh');
$graph->setBars(false);
$graph->setLine(true);
$graph->setLineColor('red', 'blue', 'purple', 'aqua', 'teal');
$graph->setDataPoints(false);
$graph->setDataPointColor('maroon');
// $graph->setDataValues(true);
// $graph->setDataValueColor('maroon');
// $graph->setGoalLine(1.3);
$graph->setRange($max,0);

$graph->setGoalLineColor('red');
$graph->createGraph();

$unimg = time();
echo "<img src=\"img.png?$unimg\">";


$graph2 = new PHPGraphLib(900,300,"img2.png");
$graph2->addData($graph2_data);
$graph2->setTitle('PUE');
$graph2->setBars(false);
$graph2->setLine(true);
$graph2->setDataPoints(true);
$graph2->setDataPointColor('maroon');
$graph2->setDataValues(true);
$graph2->setDataValueColor('maroon');
$graph2->setGoalLine(1.3);
$graph2->setRange(2,1);

$graph2->setGoalLineColor('red');
$graph2->createGraph();

$unimg = time();
echo "<img src=\"img2.png?$unimg\">";

?>
</body>
</html>

functions.inc.php

<?php
function GetSensorName($sensor_id){
    global $mysqli;
    if($stmt1 = $mysqli->prepare("SELECT sensor_name FROM winlog_sensors WHERE sensor_id = ? ")){
        $stmt1->bind_param("i", $sensor_id);
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $sensor_name);
        while (mysqli_stmt_fetch($stmt1)) {
            $sensor_name = $sensor_name;
        }
    }
    return $sensor_name;
}

function GetSensorIDs($category){
    global $mysqli;
    $sensors = array();
    if($stmt1 = $mysqli->prepare("SELECT sensor_id FROM winlog_sensors WHERE category_id = ? ")){
        $stmt1->bind_param("i", $category);
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $sensor_id);
        while (mysqli_stmt_fetch($stmt1)) {
            $sensors[] = $sensor_id;
        }
    }
    return $sensors;
}

function GetTimestamps($interval){
    global $mysqli;
    $timestamps = array();
    if($interval == 604800){
        if(date("w") == 5 AND date("H") >= 12){
            $latest = intval(strtotime("today 12:00"));
        }
        else{
            $latest = intval(strtotime("last friday 12:00"));
        }
    }
    else{
        $latest = intval(GetLatestTimestamp());
    }
    $timestamps[] = $latest;
    $i = 0;
    $n = 1;
    while($i < 24 AND $n < 50){
        $deduct = intval($interval) * $n;
        $q_timestamp = $latest - $deduct;
        //Get calculated timestamp from DC
        if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data WHERE timestamp = ? LIMIT 1")){
            $stmt1->bind_param("i", $q_timestamp);
            mysqli_stmt_execute($stmt1);
            mysqli_stmt_store_result($stmt1);
            mysqli_stmt_bind_result($stmt1, $db_timestamp);
            $exists = 0;
            while (mysqli_stmt_fetch($stmt1)) {
                $timestamps[] = intval($db_timestamp);
                $exists = 1;
                ++$i;
            }
            //if it does not exist, take the previous one
            if($exists == 0){
                if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data WHERE timestamp < ? ORDER BY timestamp DESC LIMIT 1")){
                    $stmt1->bind_param("i", $q_timestamp);
                    mysqli_stmt_execute($stmt1);
                    mysqli_stmt_store_result($stmt1);
                    mysqli_stmt_bind_result($stmt1, $db_timestamp);
                    $exists = 0;
                    while (mysqli_stmt_fetch($stmt1)) {
                        $timestamps[] = intval($db_timestamp);
                        ++$i;
                    }
                }
            }
        }
        ++$n;
    }
    return $timestamps;
}

function GetLatestTimestamp(){
    global $mysqli;
    $timestamps = array();
    if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data ORDER BY timestamp DESC LIMIT 1")){
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $timestamp);
        while (mysqli_stmt_fetch($stmt1)) {
            $timestamp = $timestamp;
        }
    }
    return $timestamp;
}

function GetPreviousTimestamp($timestamp){
    global $mysqli;
    $timestamps = array();
    if($stmt1 = $mysqli->prepare("SELECT DISTINCT timestamp FROM winlog_data WHERE timestamp < ? ORDER BY timestamp DESC LIMIT 1")){
        $stmt1->bind_param("i", $timestamp);
        mysqli_stmt_execute($stmt1);
        mysqli_stmt_store_result($stmt1);
        mysqli_stmt_bind_result($stmt1, $prev_timestamp);
        while (mysqli_stmt_fetch($stmt1)) {
            $prev_timestamp = $prev_timestamp;
        }
    }
    return $prev_timestamp;
}

function GetDataForTimeStamp($timestamp, $category){
    global $mysqli;
    $data = array();
    $exists = 0;
    $start_ts = $timestamp;
    $stop_ts = $timestamp +1;
    if($stmt2 = $mysqli->prepare("SELECT wd.value, wd.sensor_id FROM winlog_data wd, winlog_sensors ws WHERE ws.sensor_id = wd.sensor_id AND wd.timestamp >= ? AND wd.timestamp <= ? AND ws.category_id = ? ")){
        $stmt2->bind_param("iii", $start_ts, $stop_ts, $category);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_store_result($stmt2);
        mysqli_stmt_bind_result($stmt2, $value, $sensor_id);
        while (mysqli_stmt_fetch($stmt2)) {
            $data[$sensor_id] = $value;
            $exists = 1;
            $data['timestamp'] = $timestamp;
        }
    }
    return $data;
}

function GetDataForSensor($sensor_id, $timestamps, $category){
    global $mysqli;
    $data = array();
    $i = 0;
    $highest = 0;
    foreach($timestamps as $q_timestamp){
        if(!isset($lowest)){
            $lowest = $q_timestamp;
        }
        if($q_timestamp > $highest){
            $highest = $q_timestamp;
        }
        if($q_timestamp < $lowest){
            $lowest = $q_timestamp;
        }
    }
    if($stmt2 = $mysqli->prepare("SELECT DISTINCT wd.value, wd.timestamp FROM winlog_data wd, winlog_sensors ws WHERE wd.sensor_id = ? AND wd.timestamp >= ? AND wd.timestamp <= ? AND ws.category_id = ? ORDER BY wd.timestamp DESC")){
        $stmt2->bind_param("iiii", $sensor_id, $lowest, $highest, $category);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_store_result($stmt2);
        mysqli_stmt_bind_result($stmt2, $value, $timestamp);
        while (mysqli_stmt_fetch($stmt2)) {
            foreach($timestamps as $q_timestamp){
                if($q_timestamp == $timestamp){
                    $data[$i]['value'] = $value;
                    $data[$i]['timestamp'] = $q_timestamp;
                }
            }
            ++$i;
        }
    }
    return $data;
    
}
    
?>

Could you somehow say which of our queries is slow? You can take the time before and after every query and log it, to see which one is slow. I don't believe all this queries are slow. — bloodstix
– bloodstix, Commented Jan 15, 2016 at 10:59
A quick way to see what's taking time is either to attach a profiler like XDEBUG or just wrap your SQL statements in microtime() and work out the diffrence. — Sam Collins
– Sam Collins, Commented Jan 15, 2016 at 11:03
What do the execution plans of these queries tell you? If you ran them directly on the database (through the shell, or Workbench), is there any performance difference? This could help to check whether PHP is part of the problem at all — Nico Haase
– Nico Haase, Commented Sep 8 at 5:23

Ninju · Accepted Answer · 2016-01-15 10:56:34Z

2

Use Indexes

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

How to create an index in SQL:

Here’s what the actual SQL would look like to create an index on the Employee_Name column from our example earlier:

CREATE INDEX name_index
ON Employee (Employee_Name)

How to create a multi-column index in SQL:

We could also create an index on two of the columns in the Employee table , as shown in this SQL:

CREATE INDEX name_index
ON Employee (Employee_Name, Employee_Age)

answered Jan 15, 2016 at 10:56

Ninju

2,5322 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Law Over a year ago

First, thx at everyone for the tips. I have not indexed the table yet, so i will try out these suggestions on monday as this is for work :). I will get back with results after indexing and testing query's with microtime()

Martin Law Over a year ago

I have added microtime() and first indexed timestamp in winlog_data. This decreased execution time from 60+ to 25 seconds. Then i added extra index on value and sensor_id but this did not give me better preformance. Using microtime i could determine that the PUE calculations take up allmost 0.7 seconds per field so for 24 value's thats roughly 16 seconds of the total 25. While still slow, this is enough of an improvement to work with. The PUE is the most important calc so i cannot dump that. If anyone sees any impovements in the code for that feel free to let me know. Thanks!

Auris · Accepted Answer · 2016-01-15 10:56:55Z

1

first make sure your DB table is indexed correctly: your timestamp table should have an index on 'timestamp' field. Your winlog_sensors table should have indexes for sensor_id and category_id.

if indexing does not help try profiling the script and see what exactly takes up your time. You could do that with Xdebug but that takse some practice. My favorite tool is NewRelic, has its downsides but gives a nice script execution breakdown so you can see if it's your queries or your code that's to blame.

answered Jan 15, 2016 at 10:56

Auris

1,3291 gold badge9 silver badges18 bronze badges

5 Comments

Peter Over a year ago

Best would be to index all columns in your WHERE clause.

bloodstix Over a year ago

No that would not be best. Its no improvement to just put an index on everything because write can suffer massive from this, too.

Peter Over a year ago

@bloodstix Have you read only the first three words? I said: "in your WHERE clause". So that would be: the timestamp columns, the sensor_id etc.

Peter Over a year ago

For a good read about indexes you could take a look at this SO question

Auris Over a year ago

Guys, it is hard to say what he needs to index without seeing the full DB. Dont forget that he also has to write a lot to a huge table so the more indexes you have the more reindexing has to be done on every write and that is very resource hungry. Best indexing practice is to index the columns that you use most often to query. Overindexing can be just as bad as no indexes at all.

Collectives™ on Stack Overflow

Slow PHP/MySQL script execution

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related