Should a MySQL instance be added to the project?

[youzer-name]

As mentioned in #113 and #82 I have added a MySQL instance to my Powerwall Dashboard setup to solve two issues:

1. I can create monthly charts in MySQL that are difficult or impossible to replicate in InfluxDB.
2. I can use Time of Use pricing and display accurate costs for any time period. The project currently has hard-coded rates in the costs/savings calculations that are applied to any time period that is selected.

I expect that going forward I'll find additional uses for the replicated data and the additional functionality that MySQL has over InfluxDB.

The challenges I see to adding this to the project as part of the default setup are:

• Different environments may require different MySQL databases. On my 32 bit Raspberry Pi Docker, I am using MariaDB 10.6.9. If different hosts require different MySQL versions, that may complicate the setup process. It shouldn't really matter what MySQL version is used, but the data source in Grafana has to be configured to connect to whatever database is being used.
• I'm not sure if all the steps can be automated, again possibly complicating the installation process.
  • The database container needs to be installed and configured (username, password)
  • The python script for the initial data replication needs to be configured (database connection info) and run.
    • If there are any duplicate timestamps in the data, the keys/constraints have to be set after initially copying the data and cleaning that up
  • The python script for keeping the data in sync needs to be configured and set to run on a schedule.
  • The datasource needs to be configured in Grafana
  • The panels need to be modified to use the new data source.

I understand if there is no desire to complicate the project's default installation by adding this. I'm happy to share what I've done with anyone else who feels, as I do, that the cost of having to manually deal with project updates (because the setup script doesn't apply) is worth the benefit of having the the additional data.

More discussion of my situation below, but you can stop reading here if you just want to weigh in on whether you think this is something worth pursuing.

My situation is that I have a once-per-year escalating cost for my solar PPA, and my grid cost is different from my grid credit (but in a good way). I can only produce about 60% of my annual electricity needs from my current solar array, but I subscribe to a community solar project that charges me 95% of the current utility rate for the power they generate for me. I am credited 100% of the current utility rate (including all taxes and fees) for each kWh produced by my share of the community solar provider. I'm slightly over-subscribed, so I usually run a negative balance on my electric bill and once a year the utility sends me a check for the difference and zeros out the account.

The utility rate changes at least 2x per year (seasonally) plus whatever rate increases happen. The closest approximation I can make to my actual cost per kWh is to look at the rate I paid per kWh for the community solar on each bill, and divide that by .95 to get the utility rate for that billing period. Since I got my Powerwall in February that rate has fluctuated from ~ $0.1328 to ~$0.1838.

I went through this morning and added entries in my TOU table for the solar_price, grid_price, and grid_credit for each billing period. Rather than having Grafana do the math every time by joining the tables in the panel's query, I decided to add columns to my kwh table for cost_solar, cost_grid, and credit_grid where I could store the values for each row. I then ran an update query to calculate those columns for all the historical data, and added triggers for the kwh table BeforeInsert and BeforeUpdate events that will calculate the data as it flows into the MySQL database.

Finally, I modified my panels in Grafana to use the MySQL data in my Cost panel.

Since I really don't know my cost until after the bill arrives, I am using the most recent bill's costs to calculate the current values. Each month when my electric bill arrives, I'll add a new row to the TOU table to hold the correct prices for the previous billing period and run an update query that will adjust the prices in the kwh table to the correct values based on the bill. So my current values will always be based on last month's rates, but my historical values will be accurate. If you know your rates in advance, you wouldn't have to run that update query and your current values will always be correct.

If you're still reading this and are interested in trying to do the same thing, let me know. I can update #82 to add the instructions for TOU pricing.

[BJReplay]

I'm very interested.

I have what sounds like similar pricing in most aspects, other than the community feed in component (year escalation is there, more as an effect of the market), differing feed in vs. grid consumption rates, and ToU rates that can (but are currently not) varying seasonally.

I also have the potential for free periods during the middle of the day (a solar sponge) to soak up excess solar.

The other reason I'm interested is that I can also put other data into that DB and chart that as well - e.g. (electricity market) Pool Price - which becomes interesting for the potential to participate in pool price feed in opportunities (I'm in a market where the pool price varies from -100c/kWh to +1,500 c/kWh). Having historical prices loaded into a DB and then being able to a) chart them, and b) potentially use those to assess what would have happened makes the consideration of this pricing plan more interesting.

[youzer-name]

@BJReplay I just wrote a short novel over on #82 that provides step-by-step instructions for setting up MySQL and time of use queries.

I was trying to reconstruct all the steps I did, which of course I didn't fully document as I did them. If you try it and hit any snags, please let me know so I can update that post.

[BJReplay]

Nice - a weekend project! Just scanned that, and I'll take notes of any snags so that I can provide feedback / editing suggestions for your novel. I know exactly what you mean; I start off with good intentions of documenting what I'm doing, but very quickly end up just doing it!

[jasonacox]

I confess, I'm tempted to consider changing the default datastore to a MySQL variant for all the capability it would add to the project. Unfortunately, as @youzer-name highlighted, there are significant complications in automating and making the project easily viable for all platforms.

Community members are running this dashboard on Raspberry Pis, Linux host, VMs, MacOS, Windows 10/11 hosts and even Synology NAS. We spend a lot of time helping people troubleshoot their installation or making the setup.sh process closer and closer to "push button" easy. The current required stack (pyPowerwall, Telegraf, InfluxDB and Grafana) has been very successful in achieving that easy-to-install-and-use goal. Having said that, the TOU calculations does highlight the platform's weakness in supporting more complex queries.

While I'm still open to being convinced otherwise, my proposal is that we keep the stack as-is and offer up an "add-on" where users can add MySQL as a secondary datasource and unlock the more advanced features (e.g. complex TOU calculations). @youzer-name has paved a lot of the road to get there. If that makes sense, I need to explore further to see how to set that up as a add-on setup script.