Friday, February 23, 2018

Local Power Politics

I live in Arizona USA just north of Phoenix in the foothills. Power here is supplied by a company that calls itself "Arizona Public Service," that is an owned subsidiary of Pinnacle West Capital Corporation. I'm bringing this up because they've managed to get two rate increases in the last 12 months. This is actually not that unusual in this country, power companies are considered a "public utility" and given a service area in which they are a monopoly. Since they are a monopoly, they are heavily regulated and watched by some political entity; in the case of Arizona, it's the Arizona Corporation Commission. Additionally, the commission is responsible for making sure the utilities they oversee make a reasonable profit.

What an incredibly wonderful position to be in. To have a public commission that will guarantee you a profit no matter how bad a business you run. That MUST be worth a few regulations that may be somewhat annoying. I bet it also accounts for some of the high salaries they report...

Boring right? You probably clicked off the site about the middle of the first paragraph; I don't blame you.

The reason this is important to me is illustrated by the following screen shot of a rate calculator APS (the aforementioned jerk company Arizona Public Service) put on their web site:

Zoom in if you have trouble reading it.

This is a comparison of two of their rate plans compared with the plan I currently have. Notice the "difference" column for the two new rate plans, "premier choice large" and "saver choice max" and how much the difference is between the money I'm paying now and the money they project I will pay in the future. No, that isn't a typo and I didn't take photoshop to the screenshot, I'm projected to see an increase in over $1700 for a years worth of power.

Kinda makes you wonder why such a thing got past the regulating committee AZCC (Arizona Corporate Commission) doesn't it? Of course it couldn't be because all but one of the Commissioner's campaigns was heavily financed by APS could it? Of course not; we have the best commission money can buy.

Like most of this country our power bills are cluttered with a ton of little charges to disguise what is actually being charged. There is a charge for renting the meter we're required to have, a charge for reading the meter, a charge if we don't have a 'smart' meter which is read remotely, a charge for billing us, a charge for preparing the bill, ... you get the idea. As a matter of fact, my well power bill is literally half these charges and the other half is actual power used. (Yes, I can prove it.) I'm still wondering why I pay to rent a smart meter that is read remotely and being charged for reading it. Something there doesn't make much sense.

Leaving the whining aside, what the heck is with an increase of over $1700 a year? When they presented the rate plans they predicted a 4.5% increase that should average out to around $6 a month. This is the kind of thing they managed to push through the AZCC. It took them over a year of hearings and testimony to get the increase, and part of the settlement was the rate calculator they created. The calculator was designed so people could make an informed decision which plan they would go with in the future since APS is removing all the other plans people have had for years and years.

Well, after a few phone calls where I had to convince the APS representative that I was right, I finally figured out what the heck was going on. The calculator is pure crap. The calculator takes into account the actual time usage of the home and applies it to the new rates. Since I control my power and keep my peak usage way down, the calculator goes nuts.

That probably means that my bill will be considerably less than they predict, but it also means the calculator thousands of people use to make an informed decision is wrong. Nice job folks.

It also means that thousands of bills will be calculated by the same computers and the same software written by the same programmers that came up with this mess.

Yes, I filed a complaint with the AZCC and wrote letters to both of my state representatives about this very thing. Never had much luck with either of those, but

Wish me luck.

Friday, February 9, 2018

Grafana: Getting close to having something nice

I spent a few hours getting a Pi 3B running properly for this Grafana project. I actually think it is going to worth the effort. Grafana seems to work well and I have all the code on my own machine.

What I had to do was first set up a usb drive for the machine so I didn't depend on a silly SD card. I went into this with my teeth clamped down because previously it had been a pain to implement <link>. This time, it was a piece of cake.

What the folk did at Raspberry Pi was take the idea of a usb drive seriously. You enable a bit in the configuration, read the SD card you already have running into a file, write it out to the usb drive, plug it in and boot the Pi. OK, it's a little bit more than that, but not much.

I followed the instructions for setting the bit that allows boot from usb drive in the instructions on the Pi site <link> and found out the bit was already set. Then I remembered doing it when I was experimenting before. Then I took out the SD card, stuck it in my laptop and copied an image to a file. Next, I plugged in the usb ssd I already had from last time and copied the image over to it.

I walked across the room and plugged the usb drive into the PI, plugged in the power and went back to the laptop. Putty connected to the Pi on the first try. Notice I didn't say anything about plugging in an SD card?

Nope, the machine is running just fine without an SD card at all. You do realize that this makes the Pi into a full blown computer that you can actually rely on! Yep, this may justify making an enclosure for the combination so it looks pretty on the shelf. Right now it looks a little forlorn over there:

Looks a little strange hanging by its wires doesn't it? Notice the unopened Echo Dot boxes right by it? That's for another project I hope to get to some day.

I created a special user in the database that can only read data from certain tables to protect against the scary "SQL injection" attacks by hackers so my database is OK. I forwarded a port to the machine from my router so it can be seen from outside when I finally trust it enough to do so; I may be ready to show this thing off in a few days.

Then I spent a little time adding a few new items to the Grafana dashboard so I had more things I could check on just by glancing at the display. It looks like this right now:

I have the appliance power usage and battery level of the room temperature sensors charted so I can follow them over time. There's other things I can chart for fun and some other indicators that would be nice to see from time to time, but I think I'll use this arrangement for a while to see how it feels.

This entire project has been a success. I gues it was time for my readers to nudge me again about using some of the public tools. Remember this all started when I wrote about Xively a little while ago <link>. Maybe I'll look at Home Assistant next.

Or maybe I'll just make cool charts for a week or two.

Thursday, February 8, 2018

My Freezer: Right in the middle of installing Grafana

So, I'm installing Grafana on my laptop and pull up a chart that looks like this for my monitored appliances:

What the heck? the freezer in the house is getting hot! About a minute later, an email comes in that I actually check since I'm setting right there and it tells me that the house freezer is over 50 F.

Yes, my freezer sends me email. I set this up about 4 months ago when the door didn't get closed completely and was open for HOURS before I noticed it. It didn't cost me much to replace the stuff that had thawed out, I cooked it instead, but having it happen got me to thinking about alarming that kind of thing. I have a process called '' that I run as a daemon (all the time) that monitors the other processes and many of the devices that are reporting around the house. Things like the XBee coordinator process, the thermostats and the septic tank float are watched for problems and an email is sent every hour that they don't perform up to snuff. I just added code to this process to watch the temperature in the appliances to make sure they were OK.

I got up, walked over to the freezer, put the frozen french fries back on the shelf correctly, and closed the door. Problem solved and crises averted. All the scoffers out there that poo-poo'd me about putting a temperature sensor in the appliances ... No, none of the other folk that are automating and monitoring their houses had disparaging comments, it was neighbors and friends that just let the house react to things and then complain wondered why I bothered with that much work.

Really ?

What was really unexpected though was the aftermath of letting the freezer warm up like that. Take a look at this chart of the aftermath:

I suppressed the other appliances and expanded this to show a little normal operation on both sides. The big deal is how long it took for the freezer to return to normal. Because I left the door open for about two and a half hours the temperature went all the way from below zero up to 63 degrees. Then it took over 24 hours for the thing to get back to normal.

Wow, I gotta pay more attention to that door. What the heck was happening with the power usage during this time?

Here's the power chart for the same period, and I see that shortly after I closed the door the freezer went into a defrost cycle. That's a good thing because there had to be a bunch of ice all over the evaporator coil. The compressor was on trying cool a freezer with an open door and humidity from the house was freezing all over the coil lowering its efficiency. Then the ice maker took off to fill the empty ice bucket that I had emptied of sludge. The compressor ran for the entire 24 hour period getting the internal temperature back down where it should be, but the defrost cycles and ice maker ate some of the energy during this time.

So, the power bill will be a little higher this month, but notice that the freezer only uses around 150 watts. It won't cost me much at all. Once I started watching the appliances, it really impressed me how little power they use. A little Honda generator could power the appliances around my house for an extended power failure. That will almost certainly become a project in the future.

There's a couple of other things I want to point out to readers. First, the reason I left the door open was that I was playing with Grafana and got distracted. Second, the graph I was playing with actually told me something useful: the stupid freezer was getting hot. Third, I was immediately able to research the results of my screw up. What went on with the temperature recovery in the freezer and how that affected my power usage.

That is so stinkin' COOL. (pun intended)

So, I really recommend saving data for some period. I've never gone back more than a few months looking for some change or problem, but as shown here, a couple of weeks can really be valuable. I personally carry years of data, but that doesn't mean I have to; it also slows down my database queries. So, balance your own needs to what you want to do. Over time, you'll work it out.

Also, Grafana allowed me to look at different time frames and data really quickly. It took me just a few minutes to prowl through the data to understand what was going on. I could have done the exact same thing with other charting tools I've tried, but it would have taken me way longer. Man I'm glad they implemented MySQL in this tool.

I probably should go clean the freezer now, but at least it's working.

Tuesday, February 6, 2018

Grafana: Now to get it working on a Raspberry Pi

Last post I talked about how I got Grafana working on my desktop talking to my database. It actually worked pretty well. Unfortunately, the laptop is NOT where I want it to run; I want it to be running on one of my Raspberry Pi machines. Since I have one of the Pi 3b's and, it isn't doing much of anything, I put it there.

Like almost everything related to linux, this was a pain. It seems that since I last played with that little machine, there have been two major updates to the OS. They went from Jessie to Pixel to Stretch. WTF?? Are the folks at Raspberry Pi running out of things to do?

Fine, I downloaded the minimal image of Stretch and wrote it onto an SD card, made a couple of changes to the boot init files to run headless and booted it up. That worked first try. They really did make some improvements in that area. No gritting your teeth and closing your eyes before rebooting; it just worked. Then I went looking for a Raspberry Pi version of Grafana ... Guess what? Grafana doesn't make one !  Fortunately, there's a person out there that took this job on for the rest of us. The github user 'fg2it' has created a github repository that holds Grafana for the various versions of Pi OS's and it's relatively easy to use them --- after you figure out what the heck he is doing. The repository he created is here <link>, and in the instructions he does a cool trick of updating where Grafana is installed from, then used apt-get to install it. I stumbled over that for a while before I got it working. Really, really clever way to do it.

Aside: I ran into instruction after instruction for installing Grafana on the Pi, and almost all of them referenced back to fg2it's repository but, tried to take the credit for all the work. The slimeballs only referenced fg2it in the various commands. You'll see what I mean when you go looking around. Thank you fg2it.

I installed the 'Stretch' version of Grafana on the Pi I had just updated and made the simple changes necessary and it worked. I could log into Grafana and create charts that I copied from the laptop version I had used previously. Everything worked pretty well. Next, I added some more charts and explored a bit. Here's the way it looks right this minute when I load the graphs up on my laptop:

This is produced by Grafana running on a Raspberry Pi displaying on a Windows laptop and getting stored data from a MySQL database running on a QNAP network appliance. Cool !

Yes, since it's only a web page, it works on a phone as well:

You do have to scroll down on the screen to see all four of the charts, but that's the way it should work on a small device.

There's a ton of features I haven't played with yet, and I'll get to some of those over the next few days, but first I want to protect my database and figure out some way of offering this up on the web. It may get complicated since this machine can't be seen from out there.

Also, this machine only has an SD card on it. I had to leave the usb solid state drive out of the installation to get this much running. That will have to change since I've never had good luck with the SD cards. Three - four months and they crap out leaving you with a days work getting it running again.

Note that I have no dependency on any cloud service at all. It's my database, and all the software runs right here in the house.

Take that Xively !

Monday, February 5, 2018

Grafana: So, how well does this thing work?

As it turns out, pretty darn well. For those of you just getting into house control and monitoring, Grafana is a general purpose set of graphing tools. However, its documentation is daunting. They start right off using terms I never heard of and I couldn't tell what I needed to do. So, in typical Dave form, I downloaded it and started playing around.

I actually followed the steps they suggested and had my first graph running in about an hour. Never mind that the data was mixed up and in the wrong order, I actually had a graph I could look at.

Unlike most folk that delve into this tool, I'm using MySQL as a database manager to store my data. Most folk use a time-value series that is faster and smaller than a full blown database manager. But, I started with sqlite and moved to MySQL because I wanted the flexibility that was available. Of course that meant I had to create queries to the data base for gathering data, but they gave me examples to modify and it was somewhat easier.

Once I got the hang of the basics, I added my room temperature sensors as a 'panel' inside a 'dashboard'. This was cool, I could isolate sections of the graph for examination and change the span of time displayed just by clicking. Then I added the chart I look at a lot: outside temperature vs power usage. This is the key chart I use to keep power usage down to a reasonable amount.

I had about five hours into this by now, so I just let it run, updating every minute overnight. It worked fine. First thing when I looked at it, it showed the temperature drop for the night and when the heater kicked on. Nice.

Now, the problem is that I have to make it work for real. By that I mean that I want to be able to get to it from any device I happen to be using. I use a tablet a lot for this kind of thing and occasionally my phone. Haven't made that stuff work yet.

My plan is to install the software on a Raspberry Pi and allow it to be seen outside the house. That means I have to worry about security since there are SQL queries in there that can be messed with. I'm not much worried about someone watching my data to see if I'm home, I live in the middle of nowhere down a dirt road, so getting here is beyond the effort most thieves are willing to invest. Plus, if they make a mistake and I'm home ... shotgun practice!

Since I installed it on my laptop for convenience, I get to start all over again, so I don't want to develop it too much further before getting it set up for real use.

Oh, I also remembered why I haven't looked deeply into this earlier. They only enabled it for use with MySQL last year. Every time I looked before, it wouldn't do the job for me. MySQL seems to work fine now.

Here's a screenshot of the very first panel I made:

Like I said, this took a about five hours to get going, but that included installing it and figuring out how to connect it to my data base. Quite a bit of time was spent playing with colors and text as well, so your mileage may vary.

Nice tool if I can make it work outside the house on a phone. We'll see.

Monday, January 29, 2018

Xively: See I told you so...

Over and over again I've ranted about how I hate cloud storage of data, or reliance on cloud services for control around the house, and again I was justified. Earlier this month Xively (formerly Cosm (formally Pachube)) turned off free access. Never mind that years ago Pachube promised access forever for its early users and promised again when they became Cosm. Xively didn't honor the promise.

So much for promises. Remember the wonderful free service DynDNS that had its software installed on new routers? That went away a while ago and I had to change away from that service, now I get to stop the update process for Xively and pull the data from my local database instead.

At least I'm ready to do the switch, and I wish I had bothered to do it a year ago, but I was lazy. Teach me...

They were pretty nasty about it too. They sent emails to some fraction of their free users, and ignored the rest. Even the users that got the mail only got about two weeks notice and were unable to suck the data off the cloud service in time. The others (like me) found out from friends or just noticed one day that the service (and all their accumulated data) had disappeared from the site.

They couldn't even log in, and mails seemed to disappear into the same black hole as their data.

I don't recommend any product from LogMeIn. For software to avoid, see the Wiki page on the company and avoid all their offerings. The nasty way they did this should be a nice hint for folk.

I'm not going to recommend a replacement because this situation is only going to get worse over time. Hold your own data at someplace that belongs to you, not some profit driven jerks.

Now, where was that charting software I like.

Wednesday, August 23, 2017

Room Temperature Sensor; got to use the 3D printer for real

I used my 3D printer on a real project, the room temperature sensor. I spent a ton of time messing around with various drawing tools and learning a little about slicing software, but didn't actually do anything that could be considered useful. Unless you count a stand for my Star Trek Enterprise that I have on the kitchen window sill:

I got the files for the stand off Thingiverse <link> and just printed it. Thingiverse is wonderful for getting things that other people have worked on, but it isn't the same as coming up with your own idea and stepping through the entire process.

I decided on the basics of an enclosure for the sensor weeks ago, but it took a while to decide on a tool to use to design it, then the actual design process. I tested a couple of enclosure ideas for a few weeks and finally, a couple of days ago, put four more of the sensor electronics together and printed enclosures for all of them. I have five areas of the house being monitored and recorded in my home database that I can chart and examine.

Yes, they follow each other really well over the period charted, but remember, a temperature change of a small amount will make a room feel hot. It's interesting to watch this phenomenon. One room is 75F and the next is 78F, not enough to matter, but it feels really hot in the warmer room. It doesn't help much to put a thermometer in the warmer room so you can see the actual temperature; you just think the thermometer is wrong. Strange.

Here's what the entire assembly looks like on the wall:

It consists of a base plate that you mount to the wall and a cover that slides over the base to protect the wiring. Here's the two pieces before mounting:

Most of the engineers out there could have drawn this up in about 10 minutes, but frankly, this process was tough. Every little item had to be measured and thought out carefully. I had many, many test prints of sections to see if various things actually fit together. I kept running into little unexpected problems. The plastic would be a little too rough in some spots and pieces wouldn't fit together. The model would glue itself to the base of the printer and it flatly refused to come off. I didn't put holes in the sensor PC board and had to come up with a mounting solution for it. How tall EXACTLY should a stand off be for the various pieces?

Most of this stuff was trial and error. Lots of errors. Just look at the pile of various attempts that will go in the trash after I post this:

One thing I discovered towards the end of the project was that I could cut the model and only print part of it. That was a real time saver. I could print the section I was trying to settle on and test it without waiting hours for the printer to finish the entire thing. I didn't read about using this capability anywhere on the web. Lots of folk talked about cutting the model, but didn't mention how it could be used to test a section of it. I guess most folk use 3D printers to make single piece items.

It's surprising how many little things creep up and have to be solved. The battery holder:

was initially a bit of a problem. I didn't want to mount it flat to the base plate because the mounting screws would go through the plastic, so I put in a couple of standoffs for the two screws that go through the holes in the middle of the assembly. Of course, that made it rock on the screws when I messed with the batteries. I solved that by putting in four more standoffs that kept it from moving.

Th circuit board with the electronics on it didn't have holes for mounting screws (oops).

So I had to work out special standoffs that could hold the board in place. Of course I didn't have washers that would fit the standoffs, so I just printed them as part of the base. Here's what the base wound up looking like after I solved these problems:

The battery holder has six standoffs two of them for actual mounting and the others to keep it steady. The standoffs for the PC board have a cutout for the board and I use little washers to hold it down securely:

The little dark spot in the center-left is a moth that was attracted to the heat and became part of one of the sensors. Note to self: close the printer door stupid.

I slid the lid up on one of the sensors so I could show off the construction of the assemblies:

If you zoom in on the picture you'll be able to seee the mounting arrangement more closely. They were simple to install since they only take two #6 drywall screws and the entire device doesn't weigh much.

This project has been a lot of fun over the last couple of years. Yes, YEARS. And it isn't finished yet. I still want to use the sensors to replace the two house thermostats by taking the readings of each room and turning on heat, cool, or just fans to control the internal environment. I want to chart the outside temperature along with the rooms to see how it affects things as the day passes from season to season (notice, I only have two seasons here: nice and too-darn-hot). I may even create outdoor sensors to measure the wall temperature on some of the outside walls. That will prove the insulation effectiveness.

Who knows what I'll do with this in the future.