Archived Forum

Hi,

Been having highs and lows playing with a dozen or so temperature sensors on a central heating system using an arduino logging to an SD Card.

Desperate for finding a real time solution I found emoncms so I broke out the arduino ethernet shield and raspberry pi.

I used the emoncms SD Card download ready to go. And sure enough it is!

Modded my arduino sketch to post to emoncms! Got it set up all in an evening. Fantastic stuff!

But...

Next day the multigraph I had set up was taking AGES to load - 10 seconds or so. Could it be too much for the rasperry pi? I wondered.

To cut a long story short I ended up in the file feed_model.php and the function get_feed_data() which pulls the data for plotting on the multigraph.

Now I may be new to Raspberry PIs and linux but I know a thing or two about MSSQL databases (I confess...) and that fetching individual records via multiple select queries inside a PHP script is not the fastest way to do things when the sql engine can get your data ready in one recordset in a fraction of the time.

So here's a new version of get_feed_data() which uses mysql to do the aggregating work...

function get_feed_data($feedid,$start,$end,$dp)
  {
    $feedname = "feed_".trim($feedid).""; // The table name in the db.

    if ($end == 0) $end = time()*1000;
    $start = $start/1000; $end = $end/1000; // start and end in seconds now.

    $data = array();

   // So find out how many points our time range will return.
   $resultcount = db_query("SELECT count(`time`) as samplecount FROM $feedname WHERE `time` >$start AND `time` <$end");
   if($resultcount) {
    $rowcount=db_fetch_array($resultcount);
    $count=$rowcount['samplecount'];
 
    // time * 1000 in the sql query because we need points in milliseconds and sql can do this calc much faster.
    if ($count < 1000) {  // Not many datapoints so use them all.
      $result = db_query("select time*1000 as bigtime, data from $feedname WHERE time>$start AND time<$end order by time Desc");
        while($row = db_fetch_array($result)) {
           $data[] = array($row['bigtime'], $row['data']);
        }
    } else { // Too many datapoints so thin them down... we average 50/count datapoints which should give approx 1000 sample points of graph data.
      $result = db_query("select avg(time)*1000 as bigavgtime, avg(data) as avgdata from $feedname WHERE time>$start AND time<$end group by floor(time*50/$count) order by time Desc ");
        while($row = db_fetch_array($result)) {
            $data[] = array($row['bigavgtime'], $row['avgdata']);
     }
     }
   }
    return $data;
  }

And now multigraph loads in a second or two with 8 plots assigned... I can zoom around to my heart's content...

I confess I added a 2H and 4H zoom buttons in multigraph.php while I was tinkering too..

Also got multigraph_manual.php working... but for another post...

This stuff rocks guys...

A.