Slow PHP/MySQL script execution

Question

I created a script to monitor several sensor value's, wich 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 allot, 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, Jan 15 '16 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, Jan 15 '16 at 11:03

score 2 · Accepted Answer · answered Jan 15 '16 at 10:56

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 '16 at 10:56

Ninju

2,522
2
15
21

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 Jan 16 '16 at 11:21
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! – Martin Law Jan 18 '16 at 08:05

score 1 · Answer 2 · answered Jan 15 '16 at 10:56

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 '16 at 10:56

Auris

1,309
1
9
18

Best would be to index all columns in your `WHERE` clause. – Peter Jan 15 '16 at 10:59
No that would not be best. Its no improvement to just put an index on everything because write can suffer massive from this, too. – bloodstix Jan 15 '16 at 11:01
@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 Jan 15 '16 at 11:03
For a good read about indexes you could take a look at [this SO question](http://stackoverflow.com/questions/107132/what-columns-generally-make-good-indexes) – Peter Jan 15 '16 at 11:08
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. – Auris Jan 15 '16 at 11:08

Slow PHP/MySQL script execution

2 Answers2