David Morris – brassedoff.net

Conscious as I have been for a while that I’ve been collecting a lot of records in my CurrentCost energy log table, I thought I’d better do something to tidy it up a bit as I had a bit of spare time this morning.

A quick check showed a gnats over 1.4million records in the log file (one record every six seconds since July) so something needed to be done! My collector script doesn’t add the per-record cost to the data, so I have to run a quick query first to update the ’slice_cost’:

update energy_user set slice_cost = watts * 0.0016667 / 100 * 0.175

(yes, I know I should cancel some of the zeroes out)

I wanted a table summarised by hour showing total watts and cost per hour. My base table is this:

mysql> describe energy_use;
+-------------+----------+------+-----+---------------------+-------+
| Field       | Type     | Null | Key | Default             | Extra |
+-------------+----------+------+-----+---------------------+-------+
| recordtime  | datetime | NO   | PRI | 0000-00-00 00:00:00 |       |
| temperature | float    | YES  |     | NULL                |       |
| watts       | int(11)  | YES  |     | NULL                |       |
| slice_cost  | double   | YES  |     | NULL                |       |
+-------------+----------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)

I’m not really interested in temperature, so I wanted this for a summary table…

mysql> describe total_energy;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| rdate       | date    | YES  |     | NULL    |       |
| rhour       | int(11) | YES  |     | NULL    |       |
| total_watts | int(11) | YES  |     | NULL    |       |
| total_cost  | double  | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Now let me point out here, I’m not an SQL expert by any stretch of the imagination, so forgive me if this is obvious to some people. An aggregate query on the energy_use table would give me the summarised data I wanted, and wrapping that up with an insert query would summarise and build the total_energy table in one fell swoop:

insert into total_energy (rdate, rhour, total_watts, total_cost)
 select  date(recordtime) as rdate, hour(recordtime) as rhour,
  sum(watts) as total_watts, sum(slice_cost) as total_cost
  from energy_use
  group by date(recordtime),hour(recordtime);

…which appears to do the job quite nicely.

All I have to do now is decide how much data I want toremove from the detail table. Realistically, I don’t need anything before October now I have a summary table, so

delete from energy_use where date(recordtime) < '2009-10-01'

…which removed 989000 records (or thereabouts).

Next job is to write some graphs to display the cost profiles…

One of our French users received an email regarding a tax issue with the UK tax authorities (HMRC). The URL started off ok, but turned out to be a little nasty:

(Please don’t click it)

http://online.hmrc.gov.uk.nyyyyase.com/SecurityWebApp/httpsmode/statement.php?{other id stuff redacted}

nyyyyase.com turns out to be a cluster of machines

dmlinux2:/home/david # host nyyyyase.com
nyyyyase.com has address 222.113.210.163
nyyyyase.com has address 61.73.96.212
nyyyyase.com has address 67.164.7.67
nyyyyase.com has address 79.175.103.228
nyyyyase.com has address 83.4.187.5
nyyyyase.com has address 89.134.5.8
nyyyyase.com has address 93.172.209.217
nyyyyase.com has address 110.13.183.155
nyyyyase.com has address 114.180.190.76
nyyyyase.com has address 121.174.9.100
nyyyyase.com has address 121.183.6.137
nyyyyase.com has address 190.139.220.38
nyyyyase.com has address 195.56.205.192
nyyyyase.com has address 210.116.200.91
nyyyyase.com has address 221.165.170.71

…and when you try to browse to the IP address, you’re redirected to microsoft.com, presumably in an attempt to cover tracks. The few that I’ve traced all seem to be Eastern Europe (Poland and Hungary)

Browse a little deeper though and you get a site not dissimilar to the HMRC web site in layout and colour.

Be warned!

I’ve got my CurrentCost php graphs to a point where they’re suitable for a wider audience. The graph takes data from the mySQL database that’s holding the load data coming in from the CurrentCost sensor and display, does a little bit of massaging (all statistically acceptable!) and generates a dynamic graph.

Currently, the graphing routine is automatically scaling. I’m not sure long term whether this is the best option or not. It would probably be better to take a long term maximum and stick with that, but as the scaling rounds up to the nearest 1000 watts above the daily maximum, it should cope with all eventualities except the kettle AND the microwave AND the oven all on at the same time (and the electricity meter going in to overdrive!).

I’ve also added a tweak that allows me to go backwards and forwards a day at a time on the graph.

If you want to see the current load graph, look here. As this isn’t linked to the date or time of this blog posting, depending on when you look at it, there may not be much data on it. A better view would be to look at yesterday (which is always yesterday relative to YOUR today).

There are still a few more things needed, and a proper wrapping page would be nice, when I can get around to it. I’ve also still got the cost calculations to add in (daily cron job just after midnight and results going to another table perhaps?). Also, I’ve not yet given a lot of thought to what’s going to happen when we go into / out of daylight savings, but that’s not for a couple of months yet!

If you want the PHP that’s producing these graphs, it’s available here. (It comes with a  full Government health warning).

It’s official. I have in the house now the scariest gadget imaginable for something of a gadget freak. It’s this little gem – a CurrentCost CC128.

It tells us how much electrickery we’re using and worse, how much it costs. So far, we’ve knocked about £10/month off the bill by being a bit more careful about what we leave switched on. £10/month payback on a £40 investment. That’s a bit of a no-brainer.

There’s more though. This gadget as a serial interface. And there’s a USB serial cable available for it. And it squirts data out in XML format.

There are already several people writing open source software for it, and when I get back from my latest business trip, guess what I’m going to be doing?!

I sense a little bit of Java or PERL as a daemon process, a mySQL table or two and a bit of PHP on the horizon.

For the record, the device has a little battery-powered sensor that clamps around the output from the domestic meter and communicates with the base station using 433MHz wireless. The base station itself is mains powered, estimated at a couple of pence per week to run and in ideal circumstances will pick up up to 30m away from the sensor.

I can’t wait.

No, it’s not a green or environmental post. It’s a techie post. I’ve been exchanging tweets recently with my ISP over their record bandwidth usage lately. The cause of these records? Tennis. Well, indirectly, tennis. In actual fact, BBC iPlayer. The Murray quarter final match yesterday afternoon accounted for a massize 25% of Plusnet’s bandwidth at something like 1.8Gb/s. It’s probably a good job they brought another 155Mb pipe on recently.

There’s a lot of discussion going off in the press at the moment about who should pay. Some advocate pushing charges back to the content provider, but they’re arguing that they’ve paid their whack in getting their server farm connected to the Internet in the first place with the necessary high capacity pipes.

As we look more to Internet delivered services, this argument is going to rumble on and on and users like thee ‘n’ me are going to want to make sure we’re using an ISP that can make the necessary investments in bandwidth to ensure consistent delivery of service. The situation is only going to get worse. Virgin will sell you a 50Mb fibre connection to home. Plusnet and others are trialling or have already rolled out ADSL2+ with up to 20Mb on copper.

Is, therefore, the current charging model the right one moving forward? Commentators more skilled than me will probably be having that argument for years to come. In the meantime, I’ll be catching up with TopGear from the weekend tonight on iPlayer and I hope there’s no tennis on to pinch the bandwidth!

I’ve been struggling for a while with my Macbook, trying to get TimeMachine backups working to my home-built Linux NAS. I’d got AFP working on the NAS, I’d gone through the motions of creating a sparsebundle on the local Mac and moving it on to the NAS. I’d done the

defaults write com.apple.systempreferences TMShowUnsupportedNetworkVolumes 1

…and it still didn’t work. After digging around the blogosphere, I found a post which suggested that the first backup might have to be done over wired LAN rather than wireless. I’d already read that the MAC address in the sparsebundle file had to be the primary MAC address on the machine (i.e. the ethernet MAC address), so I suppose there was a modicum of sense in there. Hopefully, once the first backup has completed, the Macbook will know what the TimeMachine file is called and know where to find it.

Currently therefore, my Macbook is sat on my desk shifting 22.5GB of data to my NAS. Fortunately, there’s 600+GB free on there!

It all started off yesterday when I decided to buy a couple of lottery tickets on-line (as usual). In the last new days, the National Lottery upgraded their web site to something newer and shinier.

Newer, shinier and it doesn’t work withLinux. I assumed it was a prblem with the old version of Firefox 2 on my Linux box, or the Flash version, so I upgraded Flas and Firefox. That sucessfully killed Firefox altogether.

So, it being on a geriatric SuSE 10.2, I decided to download the ISO for openSuSE 11.1 and install that.

Bad move.

First off, the installer complained that my discs were identified by logical device name rather than UUID or physical name. That took a bit of googling to come up with a fix, then after the software update had finished, the system ended up in file system recovery because one of the drives wouldn’t mount. Fortunately, it was the backup drive. I still don’t know why this wouldn’t mount, but that was something to do with naming as well. Forcing that to device naming and forcing a mount point fixed that. Next, no GUI.

That requied the Nvidia repo adding to YAST and new drivers and a kernel update. Now, why didn’t they get added from the DVD I wonder?

The net result was that I had the server (this server) down most of the day when you’d have thought the update process would have been fairly streamlined.

Whatever, it’s still quicker than doing Windows!

Back to the Lottery site, I’ve emailed them and complained. I’ll report back when I’ve got something to report.

This week’s great timewaster has been Bejeweled Blitz on Facebook (thanks for that, Mirielle).

I know I’m notthe most frequent blogger in the world. These days, you’re probably just as likely to find me microblogging via my twitter account as anywhere, but there are some times when I feel the need to write an essay!

I’m in sunny Dusseldorf at the moment. The rest of the office have gone out for lunch so it’s nice and quiet and I have a few minutes to myself. When I left Sheffield yesterday, the new red wine kit was bubbling away to itself in a pleasingly regular fashion. The relocated Killamarsh Forum was working fine on its new hosting service. People have been updating it as well, so I know it’s capable of being found. I did take the liberty of putting a customised 404 error page on my home server to point people at the new location before I renamed the directory containing the forum.

I’ve had the nod to move the Seth Ellis site as well, and I’ll shift that over to wiserhosting.com as soon as I’m happy that the KF site is robust.

The Scimitars are away in Bracknell on Saturday, but I’ll be watching Daniel scuba diving. That’s after I’ve played in the 3rd Section Regional Finals (aka The Area) at Bradford on Saturday morning. We’re rehearsing at something like 8:30 in Bradford, so an early start is in order.

Over the weekend, I started the first stage of the process to get a lot of the web sites off our home server. I’ve been looking around for a cheap full-featured hosting company and eventually found wiserhosting. Time will tell what their availability is like, but they’re a UK company and for £25/year you get a full hosting package with mySQL 4, php 5, sub-domains and a decent control panel.

The first web site to move is the Killamarsh Forum. I’m getting a few problems with DNS, but if you go for killamarsh-forum.co.uk rather than www.killamarsh-forum.co.uk everything’s up and cooking. I daresay the www. bil will work its way through in time. I’ve put a custom 404 error on my web server to point people to the new location, so hopefully that will catch everyone.

Out of the blue yesterday, I got an email from a Geograph user asking whether I’d be prepared to take up the reigns with JUppy again. JUppy is the J2SE-based bulk image uploader I wrote a couple of years ago (is it REALLY that long ago?). I haven’t geographed in ages which is daft when you consider every time I set foot on the boat I come back with a pile of photos. Anyway, when I get back from Germany next week I’ve promised to dust off the old version of the site and software and have a look at it. I seem to remember it needed some refactoring to properly implement the plans I’d got for it. I did start to implement some basic image editing (crop, pan etc) and this is where I got bogged down with the basic class structure. From memory, I’d decided that the best way of moving the thing forward was to set up a dedicated image class. I’m also idly wondering as I write this whether I could get away with using Derby as an embedded SQL server in JUppy for the file handling. It would certainly make things a lot easier; at the moment I think it’s using CSVs. It needs a nice easy way to serialise the image class into and out of the local image database, and the other thing I know needs doing for sure is that the image upload needs to be shifted off the GUI thread and on to its own thread. Ah, the benefits of two years of Java development hindsight!!!

Finally, I hit the brew shop yesterday and I’ve started a red wine kit. It works out at around £2/bottle when complete. It’s a Californian red. If it works out, I can see our wine bill being significantly reduced!

Since I changed the backdrop on the Killamarsh Forum captcha to a photographic backdrop, the spam applications have dropped to zero. Mind you, in the same period I haven’t had ANY applications, so it could be that the captcha is unreadable to everyone, human or machine! With that in mind, I did check earlier today and yes, it is still readable. Score one for the non-spammers.

Oh, I’ve fixed the masthead image as well. I’d been a bit over zealous with the permissions when I installed the new software and had denied access to the theme image directory. Doh!

I also think this theme needs a bit of work to make it compliant with Wordpress widgets. A little light reading is in order over the weekend. When I’ve fixed mine, I can go and fix Mrs Woolforbrains‘ theme as well.