Energy database issues

[sooty]

Over recent months I've added a few Z-Wave devices with energy reporting features so I've been having a play with the energy graphing features of HS3.

OH DEAR....

Some of the "energy" capable devices appeared to work OK and the charts were producing meaningful results until a few days ago. I added a new Aeon smart energy switch but it didn't appear in the device selector drop down on the energy page. I tried restarting HS3 a couple of times but still no joy so I decided to investigate the energy database in my quest to see why it wasn't there.

Seems like the device name as well as the ref is stored in the Energy table. I renamed the new Aeon device shortly after adding it but the DB still had the old name. I changed the name to match the current name of the device and hey-presto it appeared in the list and was chartable. Seems a bit strange to me to be using the name to verify the device for inclusion in the selector drop down. I thought the dvref would have been used here as it is unique.

Another issue is that the database is not kept in sync with HS3 devices, so if a device is removed from HS3 then it stays in the DB which could be interesting if/when that dvref is reused for a new device.

Everything seemed to work OK for a couple of weeks until yesterday. On viewing a chart for my new Aeon device I noticed it had a negative value which I know couldn't have been true. Back into the database and a see a right old mish-mash of data for this device. The device in question has reporting set to 20 minutes and image 1 is a snippet of resulting data stored in the EnergyData table.

Image 2 shows another snippet of data for the same device where things seems to have gone hay-wire. As you can see there are 8 entries in the DB for the same device for the same time period. On further investigation it would appear that this has happened for other devices at various times and appears to cause corruption with the charts.

I may well be wrong but I suspect the energy data is queued and then de-queued into the DB and the problem occurs in this procedure.

My only reason for looking at the integrated charting was for ease of getting charts into HSTouch but in light of these findings I have decided to abandon this approach and investigate the third party alternatives before embarking on my own creation.

Unless I'm missing something there does not appear to be a way of switching off energy reporting to the DB for a device. Anyone know if setting the energy database size to 0 switches the whole thing off?

Paul..

[rmasonjr]

This has been one of my pain points since Energy was released. There is no way to turn a device OFF from being included in the Energy database. HST should at least give us the option to turn a device off from being included. Rant over.

That said, I actually had to repair my Energy database today. One of my graphs reported negative values. I went into Navicat and found there was one offending entry that had a seriously negative value. I removed it and now my graph looks much better.

You can remove the energy database and set the size to zero, but the Z-Wave plugin will still attempt to communicate with the Energy database. The reason this bothers me so is that HST should want their controllers to run as efficiently as possible, and this is just overhead that just shouldnt be there.

[rschein]

I have two of the aeon switches. One didn't appear until with 3.0.1.0 it suddenly did (I take no credit) The other is on a heat tape line and will only report if it goes on. For no particular reason it gives negative values. I'm not sure if it is a Z-Wave misc. error or in the database. If you are getting values without the unit being on it may be the same thing. When it gets warmer I may swap the two and see if that fixes things. What is the utility you used to mange the DB?

[sooty]

Looking at all the "X is Z-Wave certified, Y is Z-Wave certified" activity on the board, I suspect a lot of HST resources have been used there recently.

Now you mention it, I remember reading that energy capable devices can't be prevented from recording. Hideous oversight IMO.

I know we're not the only ones seeing this and it worries me why these multiple entries and negative values happen which effectively render the whole "energy" feature useless for anyone who is not able to manually repair their database. It looks like a fair amount of work has gone into the energy feature but in typical HST fashion they moved onto something else (IFTTT perhaps) without finishing it.

I actually wrote my own plug-in for recording any sort of device values to DB some time ago which has amassed a couple of hundred MB of data now but I never got round to actually doing anything with the data in the HS interface.

I see there are a couple of other charting options out there now but not sure if / how they integrate with HSTouch. Time for some research me thinks.

Paul..

[rmasonjr]

I have two of the aeon switches. One didn't appear until with 3.0.1.0 it suddenly did (I take no credit) The other is on a heat tape line and will only report if it goes on. For no particular reason it gives negative values. I'm not sure if it is a Z-Wave misc. error or in the database. If you are getting values without the unit being on it may be the same thing. When it gets warmer I may swap the two and see if that fixes things. What is the utility you used to mange the DB?

I used Navicat Lite from Premiumsoft. There are loads of other SQLite interfaces though. When I used FireFox, there was even a SQLite plugin there, go figure

[sooty]

I have two of the aeon switches. One didn't appear until with 3.0.1.0 it suddenly did (I take no credit) The other is on a heat tape line and will only report if it goes on. For no particular reason it gives negative values. I'm not sure if it is a Z-Wave misc. error or in the database. If you are getting values without the unit being on it may be the same thing. When it gets warmer I may swap the two and see if that fixes things. What is the utility you used to mange the DB?

I really don't know why this happens

The particular utility I used for the screenshots is self written but I aslo use SQLiteStudio.

Paul..

[randy]

I have about 20 or more Aeon energy reporting devices that show on the Edit Graphs and Exit Graph layers tabs. I've never been able to get it to generate any graph or save any settings. I have mostly negative values under Statistics.

All of the devices show up under Energy in the HS3Touch beta, but none show a graph or any energy usage.

I've never seen anything published that would indicate how this feature is supposed to work.

[mrhappy]

I posted a few issues here http://bugzilla.homeseer.com/bugzill...ug.cgi?id=2180 and had a couple of answers but then it appeared to fall by the wayside. It is a feature that could be useful I think providing some work is done on it.

I know when I was writing the Current Cost plugin to include the Energy API that I was battling issues that I eventually think came down to incorrect documentation and bugs I just had to work around (and suggest features - look at the Energy Set Data). I think it now works OK with the API but there's still the layer page that I simply don't understand what it is doing, I added an option in my plugin to log the data as it can fill the DB very quickly.

Here is hoping some work goes into it at some point and it is not abandoned.

[sooty]

Not just me then. First impressions of the energy feature for me was "What a mess".

I don't know why many people seem preoccupied with keeping databases small. In my previous business we were handling tables with many millions of rows. Nothing was archived for over 15 years and whilst there was a decrease in performance over time when querying large tables (50+ million rows) it wasn't what I would call slow even on hardware of 10+ years ago.

Paul..

[mrhappy]

Not just me then. First impressions of the energy feature for me was "What a mess".

I don't know why many people seem preoccupied with keeping databases small. In my previous business we were handling tables with many millions of rows. Nothing was archived for over 15 years and whilst there was a decrease in performance over time when querying large tables (50+ million rows) it wasn't what I would call slow even on hardware of 10+ years ago.

Paul..

I'm nowhere near a database expert but I think in this case there is an issue surrounding how efficient the calculations are to get the data back, you can tell when the energy database is full that it is slow to build the charts and whilst it is clearly doing some work I was surprised at how slow it was in my case and that was not with a full data set. I was waiting 10+ seconds on my PC to build a chart for the day, I would dread to know how slow it would be on something like the Zee.

[Furious]

Can someone post a quick screen shot of what the energy table (devices) looks like for them please? I've been trying to get it to actually work, all mine does is allow 2 devices to be selected and that's it - no data ever. I've cleared the data, still no dice.

[sooty]

Can someone post a quick screen shot of what the energy table (devices) looks like for them please? I've been trying to get it to actually work, all mine does is allow 2 devices to be selected and that's it - no data ever. I've cleared the data, still no dice.

The easiest way I found to get it working was to stop Homeseer, delete the Energy.hsd file and re-start HS3. It will re-create the database and work for a while until something gets corrupted.

I have attached a screenshot of some of the data in the Energy table from my system.

As you can see there are some inconsistencies. Rows 1-6 were created automatically when the DB was rebuilt following deletion. Rows 7-9 were created when I added another new device. All the devices in the screenshot are Aeon smart energy modules all the same type and version but for some reason the later (manually added) unit seems to also add the watts device into the table.

I expect this is due to changes in later versions of the Z-Wave plug-in and / or HS3. Either way it's a mess and you can't just ask the user to delete the DB and start over which appears to be the stock answer from HST if you have issues with the energy functions.

IMO, as a minimum, they (HST) need to make it optional for devices to be added to the energy db and then make it work properly before letting it loose on the general public.

Paul..

[Furious]

Cheers for that. It's been a while since I deleted the db, so went ahead again to try and trigger the rebuild, of course it only adds them upon device update/poll rather than scan all devices and rebuild.

Some internal logic or static list decides on what is actually stored I think, as comparing logged to non-logged devices capabilities is literally meter v3 (working) vs meter v2 (not working)

Not sure why we can't have a flag for logging on devices, should be simple to add an extra column and tweak the process code to ignore devices with the flag.

[TomTom]

How often do you guys have these Aeon Energy devices set to report?

I've had to set mine to report between 6-12mins. Two devices(range and dryer) that I need keep tabs on constantly I've got reporting every 3mins. With 12 Aeon devices if I set them all to 3 or 5mins, my set up slows down or becomes unresponsive(hst). I gave up on the energy graphs completely at this point.

[sooty]

How often do you guys have these Aeon Energy devices set to report?

I've had to set mine to report between 6-12mins. Two devices(range and dryer) that I need keep tabs on constantly I've got reporting every 3mins. With 12 Aeon devices if I set them all to 3 or 5mins, my set up slows down or becomes unresponsive(hst). I gave up on the energy graphs completely at this point.

I only have 6 devices with energy reporting capabilities and they are set to update between 10 and 20 minutes. I also had a play with adding some of my own data from other devices into the built in energy DB but this was less than satisfactory.

I've not noticed a slow down in overall system performance that I could attribute to energy reporting but I reckon if I had say 20 or more devices with energy capability then things would deteriorate.

Paul..