Building Energy Modelling part 2 - First attempts and research

It was not long after reading the Whole House Book that Glyn and I went to a green hackathon in London (January 2012) and I spent my time creating a small web app that implemented a really basic model that produced estimates of 20 year savings of implementing various building fabric improvement measures, its still up here:

Source code:

I had not quite realised the importance of solar and internal gains at that point or indeed utilisation factors and heating patterns so the green hackathon retrofit calculator will only be remotely close to actual heating demand in very leaky buildings, with few windows and consistently heated to an even set temperature all year. Paul Tanner pointed out the heating pattern flaw at the time in a tweet but it wasnt until more recently that I got how significant the difference is.

After the green hackathon retrofit calculator I tried to develop the idea of a calculator that would output a list of proposed measures, their energy savings and financial payback but had low confidence in the accuracy of its output so decided to shelve it for a while.

At around the same time I did a little work on dynamic simulation of heat conduction through a wall that can be found here: but was also unsure of its accuracy, needing to brush up on my calculus to check precisely by computing an exact solution via the fourier method.

I had by now read through the SAP worksheet (The UK's standard assessment procedure for assessing the energy performance of domestic buildings) many times but thought it a little too long to embark on implementing it, although basing an energy model on a standard approach supported widely seemed like the best approach.

Searching for open source SAP implementations I found a useful open source version of the 2009 SAP worksheet implemented as a open office spreadsheet by Wookey here: which if you prefer spreadsheets to code is certainly worth looking at. There was also a thread on the greenbuildingforums calling for good open source building energy modelling software with a lively debate:

In my searching I also came across a fully dynamic open source building energy model called esp-r developed by the University of Strathclyde and created a page on how to install it on ubuntu on the openenergymonitor site which I never linked in here:

I'm also more recently aware that there is another open source dynamic building energy modelling project called OpenStudio which looks like it has a nice front end, OpenStudio is a National Renewable Energy Laboratories project and has sketchup integrations, looks nice!

Next blog post: Carbon Coop and the open source SAP 2012

Building Energy Modelling part 1 - The Whole House Book

In the blog post what kind of information can we extract from power measurements I concluded that I needed a simple model of my electricity use in addition to the monitored data in order to ultimately get to a list of actions that I need to do to optimise my electricity use.

The other conclusion was that to significantly reduce electricity consumption further I would need to change the heating system and to take a whole house view: improve the building fabric performance by increasing insulation levels, reducing draughts and improving solar gain in order to reduce heating demand in the first place.

For a while now I have had a growing interest in looking at heating energy consumption and how to reduce heating energy demand through building fabric improvements in addition to electricity consumption.

In the energy study of local households that we did in 2011-2012 heating energy came out as the largest energy user, we recorded details about house construction, levels of insulation, perceived comfort, temperature, draught levels, amount of glazing etc but didn't have the tools or knowledge to be able to give any sort of tailored advice, relate general statements that we're all familiar with like insulation can reduce energy demand by x% to a particular households situation, it wasn't in the remit of the project but we could see that that was what was needed next.

At home it was also clear that heating was the largest user of energy and the area with the most potential to achieve significant energy savings and carbon reduction especially as electricity use is already optimised as far as possible.

I started to read up on low energy building design to get a better understanding of the subject. I had an Aha moment when I read chapter 7 of The Whole House Book by Pat Borer & Cindy Harris which outlines a really simple building energy model:

The model starts by calculating how many Watts are lost through the building fabric per degree Kelvin (same for Celsius) temperature difference between the inside and outside temperature. This is calculated by multiplying the area of the various building elements such as walls, windows, roof and floor by their U-values. The model also takes into account the amount of heat lost via draughts (infiltration). The model then uses the concept of degree-days to calculate the annual heating demand.

Before reading this I had thought that to get any sort of useful estimated output on effect of adding insulation to a building a full dynamic simulation would be needed. But this simple model showed that you can get some surprisingly informative estimates from some quite straightforward calculations.

For the rest of the book Pat Borer & Cindy Harris use a more detailed calculation that also takes into account solar gains, internal (casual) gains and water heating requirements. If your looking for a good book on low energy building design, self-build and energy calculations Id really recommend this book.

Carbon Coop and Houseahedron meetup

Just got back from a couple of days visiting the Carbon Coop team in Manchester and Houseahedron in Liverpool.

On tuesday/wednesday Matt and I spent some time working on the open source SAP calculator, improving the domestic hot water section, fixing a bug where if the solar hot water section was not complete the calculations would break and another bug that kept causing us to loose data after having completed one of the most tedious data entry parts: putting in all the heat loss elements. A check was added to the server side code to ensure that the json string containing the sap data was not corrupted by a broken transmission or whatever that was causing the string to get corrupted.

try it out:

We spent time entering several SAP calculations that had already been done in a Mac Numbers spreadsheet implementation of SAP developed by Charlie Baker of URBED to check that results agree, the results don't quite agree yet as there are a couple sections in this new open source SAP implementation that are not fully complete but we're almost there.

The EcoHome_Lab event was on the Wednesday evening and James Pul, Julian Todd and John Donovan of Houseahedron came over from Liverpool and gave a demo of their work.

What they are working on is really impressive and it ties in really well with recent openenergymonitor developments such as the open source SAP model and the idea that quite a few people have been talking about of integrating temperature and energy sensing into a building energy model in order to cross check and reduce the number of assumptions needed.

What the houseahedron guys are working on would really take this idea to another level, going beyond the 1D steady state modelling that is the SAP model, implementing a fully 3D dynamic model of heat flow in a building and then visualising it with rich 3D webgl graphics.

They want it to be possible to walk through a house and explore wall temperatures on a tablet in realtime with data coming from multiple temperature sensors placed all over the house and then be able to explore what it would look like if you added say 100mm of insulation to the walls.

One of the best bits is the way they are creating the 3d model of the house using a laser scanner! which generates a point cloud from which they generate the model. The point cloud is used as a sketch on which to draw the model manually at the moment as its too difficulty a problem to generate the 3d model completely automatically but its a pretty incredible way of getting all the dimensions you need to build a 3d model of a house and it only takes 6-7 minutes. Here's a screenshot of an example point cloud of an office building they scanned:

The best bit is that they are planning on releasing all the software for this as open source under GPL, its all written in python + flask, javascript and webGL and they are keen to use openenergymonitor hardware for the sensing.

Here's their twitter and website:

Getting the Air Quality Egg to work with the OpenEnergyMonitor system and emoncms

I spent a bit of time today putting a sketch together to get the AirQuality Egg Shield and Air Quality Egg Remote node to work as a standard sensor node with the OpenEnergyMonitor system, so that I could log data into emoncms to visualise changes over time and use the existing raspberrypi + rfm12pi base station setup thats already receiving data from an emontx and low power temperature nodes.

I placed the shield on top of a NanodeRF as it has the RFM12 RF module but Im not actually using the Ethernet part, Im just using the nanode as an arduino with an RFM12 module to form a remote sensor node. This is the same setup as is found inside the Air Quality Egg Remote node.

The sketch is adapted from the PollEggBus_EPA sketch developed by Wicked Device who develop and make the Air Quality Egg and just puts the NO2, CO, temperature and humidity data in the standard data structure of a series of integer values that the emontx and other nodes use.

Download the sketch here to try it out:

I've now been logging for about 3 hours and there's already some interesting data to look at, I got back from being out on my bike and had a shower at 20:50 you can see the RH shoot up, there also seems to be a spike in NO2 and a dip in CO not sure what that means, any ideas?

Check out the live dashboard here:

Air quality is not something I know much about but after discussing it with the Carbon Coop guys recently it sounds like a fascinating and important area to understand, Matt from Carbon Coop explained that high levels of CO2 (not monitored on the AQE shield above but something that would be great to add - but more expensive) in buildings create problems around drowsiness and ability to concentrate and so monitoring these things could be important when designing and making sure a building after having undergone retrofit work has healthy air. It would also be interesting to understand in greater detail the longer term health implications of air quality and the aspects of this that we can measure. There certainly seems to be a lot of resources on this on the Air Quality Egg wiki here which should make interesting reading:

What kind of information can we extract from power measurements?

The most direct representation of power data is power over time which is what we get if we simply log the power values transmitted from a monitor. With this we can see what our power consumption or power generation looks like throughout a day, you can see the relative magnitude of different loads, identify visually different appliances from kettle's to LED light bulbs.

The power vs time visualisation can be found under the Vis tab in emoncms by selecting the rawdata visualisation.
Its a useful visual tool and with a little extra stats overlayed on the graph such as how many kwh are contained in the window being viewed and the average power you can start to do things like calculate roughly how much energy individual appliances use such as a fridge or a particular evenings immersion heater use.

Beyond the basic power vs time graph we can gain more information by processing the incoming power data into kwh per day data. kwh per day data can be calculated as either the average power in watts for a day x 0.024 or the sum of the time spent at different powers divided by the energy in 1 kwh. A graph of kwh per day vs time makes it easy to compare one day against another and see trends over months or years.
The daily visualisation can be found under the Vis tab in emoncms by selecting the bargraph visualisation.
That's pretty good but we can extract more information from the power data if we process the power data to record the energy used at different power levels in a particular day, how much energy is used at 100W, 200W or 3000W. This is what the histogram input processor does in emoncms. The following graph shows the result of recording energy used at different power levels with a 25W resolution over 7 months from my own house. The spike at 2.5kW is an electric heater which we used a lot throughout the winter, the 3.5kW spike is the immersion heater and 0-500W is lights, laptops, hifi etc.

Note: The histogram visualisation can be found under the Vis tab by selecting histgraph. The default histogram has smaller resolution bars at higher power levels, i need to add an option for higher detailed histogram data calculation in the input process model to make this easier to achieve. To produce the graph above I actual converted the power data feed to histogram data using a conversion module that can be found here. The code that produces the histogram as data is coming in can be found on lines 543 to 608 in the process_model.php.
The histogram data can also be visualised within the kwh per day type graph so that we can see how a particular days energy consumption breaks down into energy used at less than say 1kw, energy used between 1 and 3 kW and 3kW up but then also easily compare one day to the next:

From the emoncms report module.
For me, what I want to get out of monitoring my electricity consumption is to first understand how I use electricity and then work out what definite actions I can take to reduce any my electricity consumption and then be able to confirm that those actions did indeed produce the expected result. After all optimisations are done, Id like the monitor and reporting tools to help keep me informed that I am maintaining an optimum electricity use level overtime, that it does not creep up again.

To answer these questions directly I found I needed one other thing that goes hand in hand with the monitoring and visualisations above, I needed a rough model of my electricity consumption that breaks electricity consumption down on a per appliance basis. Any actions that we take are on an per appliance basis and so we really need a per appliance level of specificity.  In the absence of per appliance monitoring or working applianceinference which would be the ultimate solution, this can be done with a simple model where a list of all the appliances in a house is entered manually including how many watts they use (which the energy monitor can be used to find out) and roughly how many hours per day they are used. This produces an estimate for how much electricity is used in a day, what the baseload should be and how much energy is consumed at different powers which can then be cross checked with the actual monitored data, the model can then be adjusted until the values align.

The reporting module in emoncms bring all this together on one page, the screen below shows the monitored data and model for the openenergymonitor lab and the small cottage next door:

You can find the reporting module in by clicking on Extras and then report. For local installations install the report module from github here
To align the model to the monitored data its a matter of aligning the model pie chart to the monitored data pie chart. In our case electricity consumption is highly variable day to day, month to month so the model can only apply to either the average of the days in the selected view or a single particular day, its informative to adjust the model for different times of the year. Once the model aligns well, its then possible to experiment with changing the appliances in the model and how many hours they are used for to see what effect that might have on total daily consumption.

The conclusion of our electricity consumption investigation is that we could save 5-10% by changing all CFL's to LED's and turning standby items off at the plug (especially the hifi and soldering iron station), continuing to optimise kettle use and immersion heater is also super important as leaving a 2800W kettle on for an extra minute uses as much as a 6W LED left on for 7-8 hours and so can easily remove any savings achieved by turning lights off. 

To significantly reduce electricity consumption further we would need to change the heating system and to take a whole house view: improve the building fabric performance by increasing insulation levels, reducing draughts and improving solar gain in order to reduce heating demand in the first place which is where recent work on temperature sensing, building energy modelling, the SAP calculator comes in but I will come back to that in further blog posts.

It would be nice to add additional tabs to the report module so that current and projected consumption levels can be easily compared, Id also like to add a feature to send a regular report email that keeps me updated on my week to week electricity consumption so that I can get at a glance information that minimises the effort I need to go to to see how I'm doing.

Emontx Shield SMT with new layout in development

Several weeks ago we got an email with a series of pictures showing the problem that the emontx shield does not stack well with many of the base boards and other shields it may be used in conjunction with.

It got me thinking about different ways the shield could be arranged that would make it stack better with a variety of different boards. The main idea I had was to move the AC-AC Adapter and CT inputs over to the other side of the PCB away from the side the Ethernet and USB sockets are usually located on most of the Arduino boards and the NanodeRF. The second idea was to mount these sockets on the underside of the PCB which allows for a really thin profile NanodeRF + EmonTx shield stack which I think is pretty neat if a little unconventional. Here's the concept drawing:

As you may be aware I have not really ventured into PCB design much before (I usually work on software) but its been something I wanted to do for a while so I thought Id have a go at the emontx shield redesign as its one of the simplest layouts. With Glyn's helpful guidance I started by loading the existing schematic and board files for the through hole emontx shield in eagle, I then ripped up all the existing PCB tracks, rearranged the components in different layouts and switched the resistors and capacitors over to SMT packages. Here's a screenshot of the resultant board design:

I spend most of my dev time writing lines of code which becomes a visual output in a second step, PCB design is a different experience as its all immediately visual, dragging and dropping pcb tracks, trying to see if this track that way or another in its place looks neater, its a nice change. If your intrigued to have a go I'd recommend installing eagle, loading up one of the board designs that are all up on solderpad and experiment with routing tracks, maybe adding/removing a temperature sensor.

The PCB's arrived on Tuesday, I built it up and have started to test it, so far everything seems to be working, energy monitoring part, RFM12, Led, DS18B20 temperature sensor, Arduino Uno, Arduino Duemilanove. Next I will test the NanodeRF and Id also like to test an Arduino official Ethernet shield.

Things to improve
There is a very slight misalignment on the ISP header that needs fixing although it still stacks together fine and Glyn pointed out that the option to choose different SS (SEL) and IRQ pins for the RFM12 could be achieved using a manual jumper rather than solderable pads. Id also like to add a terminal for the DS18B20 on the edge of the board for neater connection of temperature sensor strings.

Once these are fixed we will get a run of boards made, it will probably be another month or two at least until its in the shop as these things take time.

Open Hardware Schematics and Board files
All available on solderpad here:

Removing redundant datapoints – algorithm 1

You could probably say that the aim of an algorithm that removes redundant datapoints is to create a line plot who's standard deviation compared to the raw data line is as small as possible while minimising the number of datapoints. This is probably a good rough criteria for evaluating a prospective algorithm.

Here's one idea for an algorithm, I think it can certainly be improved upon but its a start.

If we start with our monitor that's posting once every 10 seconds. If we note down the first datapoint received and then as subsequent datapoints come in draw a line between the first and the last datapoint.
Then lets say there is a step change, a light goes on, suddenly the line is much higher than the actual data for most datapoints:
If we measure the standard deviation of the raw datapoints vs the line to begin with it will be small, once that step change happens it will become much larger.

At this point we note a significant change has happened (stdev is larger than a threshold) and create a datapoint one back from the last datapoint (the bottom of the step).

We can then move our 'start' datapoint forward to this created datapoint and repeat the process. This should do a pretty good job of tracking the raw data.

To test it I have created a visualisation that applies this algorithm using javascript in the browser here is the result:

It works pretty well reducing 1064 datapoints to 52, but it does look messy in places.
To try out the code for the above, make a backup of the script:

emoncms/Modules/vis/visualisations/rawdata.php in your local emoncms installation. 

and replace with this:

If you can think of a good algorithm to do this, It would be great to hear about it.

Removing redundant datapoints - part 1

As I mentioned before another idea for reducing disk use is compression by removing redundant datapoints. Describing our plot with the least possible number of datapoints. Before energy monitoring I first learnt programming writing some basic 3d games and physics simulations using OpenGL, I was always fascinated by trying to create 3d landscapes, one of the techniques used to generated large landscapes is called level-of-detail terrains where you increase the polygon count where there is lots of detail I.e a mountain ridge or reduce the polygon count where there isnt ie a flat plain. I've been wondering for a while whether a similar approach could be taken to describe timeseries data so we increase the datapoint rate where there is a big change (ie when the kettle, light, fridge goes on) so that we get that event exactly at the right point in time and reduce the rate when not much is happening.

I tried to do something along these lines a few years back where the hardware would only post to the server on a step change in the power signal, the code is still up here as part of the appliance inference work I was experimenting with:

The problem was that it was failing when events happened too frequently, ie a thermostat controlled cooker cycling on and off. The event detection algorithm relied upon two 5 datapoint segments next to each other, when the difference between the average of the segments where larger than a threshold an event would be registered. A 10 datapoint segment spanning ~ 100s at 10s per datapoint or 50 seconds at 5s is too large and will miss events that happen to frequently. The other problem with this approach is that it wont work for temperature data which is more a change of gradient than power data steps.

Anyway with the problem of large emoncms disk space demand I have been thinking about this idea again. Could it be used to reduce disk space use significantly without loosing vital timing on events that would happen by just reducing the post interval rate. I had a good conversation with the guys at houseahedron came to visit last week about this, they had been thinking along similar lines and saw parallels with an approach used in path plotting, they took a couple of example datasets back with them to see if they can find a way to parse it.

The screenshot below shows the solar hot water pump coming on for 40 mins at the beginning of a bright sunny day. The raw data would use 720 datapoints at a 10s post interval to describe the plot. Overlayed on the raw data plot I have drawn a second line that only has datapoints where needed to keep the standard deviation between the lines roughly within an acceptable limit, in this case 10 datapoints seems to be enough to do this. If this kind of datapoint reduction rate is typical then a 60Mb mysql table with the current emoncms implementation might only take up 0.83Mb of space per year.

Zooming in a bit, rather than 270 datapoints, this could be described with 8. (at this datapoint reduction rate 60Mb would compress to ~1.8Mb)

In addition to reducing disk space, it may be possible to use this technique to increase the resolution of our measurement as we are oversampling in regions where there are no large changes

This atmel appnote describes how to use oversampling and decimation to achieve greater measurement resolution:

I think there are two main development questions facing this idea:
  1. Can a good enough algorithm be developed to compress the data while retaining the detail we want.
  2. What are the implications for data query speeds?
The above said, I think timestore looks like the leading solution at the moment for data storage and fast query speeds. With timestore we can achieve an 80% reduction in disk space demand from 60Mb per 10s feed per year to 12.3 Mb, this would reduce the disk space use on from 47GB to 9.6GB, disk space use would increase at 20GB per year (costing £96 per 20GB stored inc backup instead of £480/year) and most of its already there in terms of implementation.

It might just be interesting to explore the datapoint reduction idea in parallel to see if further disk space reductions can be achieved but without sacrificing on query speeds which is the open question. If feeds could be compressed to 1.8Mb from 60Mb disk use would shrink from 47GB to 1.2GB and disk space would increase at a rate of 2.6 GB a year which would make server disk space costs pretty negligible.

Timestore timeseries database

The first and most developed solution to both the query speed problem and disk space problem is timestore.

Timestore is a lightweight time-series database developed by Mike Stirling. It uses a NoSQL approach to store an arbitrary number of time points without an index.

Query speeds
Timestore is fast, here's the figures given by Mike Stirling on the documentation page:

From the resulting data set containing 1M points spanning about 1 year on 30 second intervals:

Retrieve 100 points from the first hour: 2.6 ms
Retrieve 1000 points from the first hour (duplicates inserted automatically): 6.2 ms
Retrieve 100 points over the entire dataset (about a year worth): 2.5 ms
Retrieve 1000 points over the entire dataset: 7.0 ms

Disk use

Timestore uses a double as a default data type which is 8 bytes. The current emoncms mysql database stores data values as floats which take up 4 bytes, its easy to change the data type in timestore so for a fair comparison we can change the default datatype to a 4-byte float:

Layer 1: 10s layer = 3153600 datapoints x 4 bytes = 12614400 bytes
Layer 2: 60 layer1 datapoints averaged = 52560 datapoints x 4 bytes = 210240 Bytes
Layer 3: 10 layer2 datapoints averaged = 5256 datapoints x 4 bytes = 21024 bytes
Layer 4: 6 layer3 datapoints averaged = 876 datapoints x 4 bytes = 7008 bytes
Layer 5: 6 layer4 datapoints averaged = 146 datapoints x 4 bytes = 1168 bytes
Layer 6: 4 layer5 datapoints averaged = 36 datapoints x 4 bytes = 288 bytes
Layer 7: 7 layer6 datapoints averaged = 5 datapoints x 4 bytes = 40 bytes

total size = 12854168 Bytes or 12.26Mb

The current emoncms data storage implementation uses 60Mb to hold the same data as it saves both the timestamp and an associated index. Timestore therefore has the potential to reduce diskuse by 80% for realtime data feeds.

Interestingly all the downsampled layers created by timestore only come too 0.23 Mb. Before doing the calculation above I used to think that adding all the downsampled layers would add to the problem of disk space significantly but evidently it a very small contribution compared with the full resolution data layer.

Emoncms timestore development branch

I made a start on integrating timestore in emoncms, there's still a lot to do to make it fully functional but it works as a demo for now, here's how to get it setup:

1) Download, make and start timestore

$ git clone
$ cd timestore
$ make
$ cd src
$ sudo ./timestore -d

Fetch the admin key

$ cd /var/lib/timestore
$ nano adminkey.txt

copy the admin key which looks something like this: POpP)@H=1[#MJYX<(i{YZ.0/Ni.5,g~<
the admin key is generated anew every time timestore is restarted.

2) Download and setup the emoncms timestore branch

Download copy of the timestore development branch

$ git clone -b timestore timestore

Create a mysql database for emoncms and enter database settings into settings.php.

Add a line to settings.php with the timestore adminkey:
$timestore_adminkey = "POpP)@H=1[#MJYX<(i{YZ.0/Ni.5,g~<";

Create a user and login

The development branch currently only implements timestore for realtime data and the feed/data api is restricted to timestore data only which means that daily data does not work. The use of timestore for daily data needs to be implemented.

The feed model methods implemented to use timestore so far are create, insert_data and get_data.

Try it out

Navigate to the feeds tab, click on feed API helper, create a new feed by typing:

It should return {"success":true,"feedid":1}

Navigate back to feeds, you should now see your power feed in the list.
Navigate again to the api helper to fetch the insert data api url

Call the insert data api a few times over say a minute (so that we have at least 6 datapoints - one every 10 seconds). Vary the value to make it more interesting:

Select the rawdata visualisation from the vis menu

zoom to the last couple of minutes to see the data.

I met Mike Stirling a little over a month ago in Chester for a beer and a chat after Mike originally got in contact to let me know about timestore. We discussed data storage, secure authentication, low cost temperature sensing and openTRV the project Mike is working on. I think there could be great benefit to work on making what we're developing here with openenergymonitor interoperable with what Mike and others are developing with openTRV, especially as we develop more building heating and building fabric performance monitoring tools. This could all develop into a super nice open source whole building energy (both electric and heat) monitoring and control ecosystem of hardware and software tools.

Check out Mike's blog here:

The current emoncms feed storage implementation

Following on from the last blog post on server load and disk use, lets look at the current emoncms implementation of feed storage in a bit more depth before going on to look at how it can be improved.

Emoncms currently stores realtime feed data in a mysql database, every feed has its own mysql table. A feed table contains two fields: timestore and data value. Feed data is usually on a regular time interval, ie: 5,10,60s data. The time interval is set by the posting sensor node rather than emoncms.

Calculating feed disk use
We can calculate the estimated feed table size using the current implementation used in emoncms.

Lets say we want to store a year of 10s data. There are 31536000 seconds in a year and so 3153600 datapoints at a 10s data rate.

A single datapoint is made up of a timestamp which is stored as an unsigned integer, which takes up 4 bytes, and a float data value which also takes up 4 bytes.

3153600 datapoints x 8 bytes per datapoint (table row) = 24 Mb

In addition to the feed data we also have a table index which speeds up queries considerably. The worst case index size can be estimated with the equation detailed on this page:

index row size = (key_length+4) / 0.67

The key we are using is the time field which is 4 bytes and so the index row size is = (4 + 4) / 0.67 =~ 12 bytes

The index size for 3153600 datapoints is therefore approximately = 3153600 * (4 + 4) / 0.67 = 36Mb

The total feed table size will therefore be approximately 60Mb.

Feed query speeds
As emoncms has developed a fair bit of work has gone into improving the method that realtime data is queried. At first improvements seem promising, see this documentation page for detailed discussion on the query implementation and query speeds:

But growing server demand on and feed table size means they have often only staved off an eventual slow down. 

I think the last idea I had of using a php for-loop to request a single row at given intervals that originally  reduced query times by about 10x is no longer working well on, it still gives the 1.6s query time on my local installation of emoncms but on Im getting a mixture of short query times 500ms and much longer query times 20s+ (in the more than 55 hour timewindow). The reason for this I think is due to the php for loop having to wait when the server is under heavy load for other mysql queries to complete. I think another solution is needed.

In the next few blog posts I will look at some of the potential solutions to both disk use and query speeds.