Are you sorting the data (ORDER BY) on the primary key? If you're using another column you'll be wanting to add an index to it. MySQL will really thank you if you do.

You might get better performance by storing the date and time as MySQL's native datetime column and using the MySQL UNIX_TIMESTAMP() function to convert it to a Unix timestamp for Flot, just don't order on the function results as MySQL isn't very good at that, i.e.

SELECT UNIX_TIMESTAMP(datetime_column) as unix_timestamp FROM table ORDER BY datetime_column DESC.

 

This is the query I'm using...

$query = "SELECT * FROM (SELECT * FROM outside ORDER BY id DESC LIMIT $limit) AS foo ORDER BY id ASC";

	    $result = mysql_query($query);
	    while($row = mysql_fetch_assoc($result))
	    {
	    $dataset1[] = array(($row['unixtime'] * 1000 ),$row['temperature']);
            }

 

Hahaha I really can't use the code tags :'D

That's better....  I think.

The number of users active on my Guild Wars 2 Bounty Tracker app. The numbers have been a bit... wrong the past few days as I've been experimenting with different sync methods and some people apparently haven't refreshed their browser since Saturday :-/

There's no point in updating it in real-time, really – it'd just mean a huge amount of data that wouldn't really provide any more useful information than the current polling 2-minute interval.

Some thoughts:

1) drop shadow on a graph looks like wank and makes it harder to read. I keep thinking my eyes are failing to focus properly. WHERE DID YOU LEARN HOW TO GRAPH, POWERPOINT LOL?

2) increase the number of grid lines, 5 degrees apart seems a bit much.

c) I am picky

iv) that seems like an awfully big temperature variance, for optimum results your weed farm should remain between about 21 C and 26 C.

a) I agree. It's the default setting.

ii) I am going to increase the grid lines.

γ) There's nothing wrong with being picky

4) The sun reaches my balcony at about 5pm

That looks fine. How are you limiting the results when selecting the different scales, are you just assuming there will always be a maximum number of readings a day and adjusting the limit?

Yeah maximum plus 20 more to give few extra at the start.

1hour - 80
2 hours - 140
6 hours - 380
12 hours - 740
1 day - 1440
1.5 days - 2180
2 days - 2900
7 days - 10100

It defaults to 2900

To me graphs are about being able to see trends and anything less than 1 day doesn't really tell you much so I might get rid of them when I have a decent amount of data.

• Good

1. I'm glad

Soon you will be able to tell us all that it has been the hottest day in the history of hot!

FWIW: http://caer.me:2988/server.js

That's the source to the Node app that's serving the monitoring page.

Also, in the time between writing the above paragraph and starting to write this one, I rewrote a big chunk of both the monitoring, server and client-side code. The monitoring code now stores counts etc. in the database (previously it appended to a log file), while the server code now sends the data in a nicer format. The client code has been updated to take that into account, and is now simpler as a result.

Oh and Redis is cool, you should try it. Much more lightweight and straightforward than *SQL.

edit:

oops, just noticed my MIME type thing is a bit stupid.

edit 2:

How come you're not requesting the graph data via AJAX? Seems a simpler way to do it, imo, and less for your server to do.

AJAX...because I don't know how to. I've never knowingly used any AJAX code in anything I've made. Basically all I tend to do when I make something online is tack together the bits of things I already know and I've already made else where and then learn a little bit more to get whatever I want working sufficiently :$

It's really simple. First you make a PHP thing to serve the data:

<?php
// this is get_data.php
$time = $_GET['time'];
$dataset1 = magic_function_to_fetch_outdoors_data_from_db($time);
$dataset2 = magic_function_to_fetch_inoors_data_from_db($time);

$data = array(
  "dataset1" => $dataset1,
  "dataset2" => $dataset2
);

echo json_encode($data);
?>

Then you modify your HTML links like so:

<ul id="period">
  <li><a href="get_data.php?time=1h">1 hour</a></li>
  <li><a href="get_data.php?time=2h">2 hour</a></li>
  <li><a href="get_data.php?time=6h">6 hour</a></li>
</ul>

Then you have some JS that looks something like this:

var $chart = $("#placeholder"),
    options = {
        lines: { show: true },
        points: { show: false }
        // etc.
    };

function plotChart(data) {
    $.plot($chart, [
        { data: data.dataset2, color: "#DE1B55", label: "Indoors" },
        { data: data.dataset1, color: "#5F2BCF", label: "Outdoors" }
    ], options);
}

$("#period").on("click", "a", function () {
    var $link = $(this);

    $link.addClass("working");

    $.ajax({
        url: this.href,
        type: "GET",
        dataType: "json"
    }).done(function (data) {
        plotChart(data);
    }).fail(function () {
        alert("O noes!");
    }).always(function () {
        $link.removeClass("working");
    });

    return false; // stops the browser from trying to follow the link
});

And that's basically it.

Not really directly relevant but I saw this today, seems pretty coool: https://plot.ly/plot

Might you not speed it up with fewer samples? Every minute seems excessive (maybe you needed that for developing/debugging).

That choked my dual core 2.5g.

There isn't really any developing/debugging as such. I've been playing with temperature sensors with my RPis and I wanted to put one outdoors and I decided it would be nice to look at the data in a graph. Every minutes is definitely overkill but it's very quick when I access the data locally, I think it would be slow even if the data was held on the hosting server, especially when it gets to weeks and months worth of data.

At that rate, 24 hours' worth is only 60k or so – 1440 data points per series, which is not a huge amount, and like I said earlier, even my phone can handle that with no noticeable slowing.

No drop shadow! :O