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.

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 guess 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.

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 'healthcheck.py' 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.

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 !

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.