Here’s what I ended up doing because I could not find an acceptable solution to using the HA database as the primary source. With 9 power monitor sensors sending data to HA every second, the HA database was being hammered. It was increasing by about 800MB per day (currently just under 10GB) and rendering the History and Logbook useless due to the forever hang when opening. It was also generally effecting responsiveness. For example, my cameras were often not showing in the UI.
For those curious I am using the Circuit Setup Split Single Phase Real Time Power meter board connected to an Arduino UNO with Ethernet shield. Was sending the data to HA via MQTT as a JSON string.
I rewrote the Arduino firmware to send the data directly to MySQL via the aforementioned MySQL_Connector Arduino library. It saves data to a MySQL DB “energy_monitor” with table “energy_log”. The table just has a primary key incrementing ID column, timestamp column, then a bunch of float columns to hold the data.
Here’s the very basic Arduino code. I still need to clean it up a bit, implement some error checking, etc. But it has been working now for over 24 hours without issue.
/*
Name: CircuitSetup_Split_Phase_Power_Meter.ino
Created: 12/22/2019 10:55:36 AM
Author: Aaron Cake
Energy monitor based on CircuitSetup split phase energy monitor board:
https://circuitsetup.us/index.php/product/split-single-phase-real-time-whole-house-energy-meter-v1-4/
Libraries:
ATM90E32: https://github.com/CircuitSetup/ATM90E32
Version History:
1: Basic functionality of sending energy data via MQTT in JSON string
2: Rewrite to save directly to MySQL database
*/
#include <avr/wdt.h> //watch dog timer
#include <MySQL_Connection.h> //MySQL Connectector
#include <MySQL_Cursor.h>
#include <MemoryFree.h> //library with free memory function
//Ethernet library for ENC28J60
//#include <UIPEthernet.h>
//libraries needed for W5500
#include <SPI.h>
//#include <Ethernet2.h>
#include <Ethernet.h>
#include <ATM90E32.h> // for ATM90E32 energy meter
//MySQL variables
IPAddress MySQLServer(192, 168, 107, 11); // IP of the MySQL *server* here
char MySQLUser[] = "energymon"; // MySQL user login username
char MySQLPassword[] = "energymon"; // MySQL user login password
//query source string to format with sprint. Store in PROGMEM to save ~300 bytes
const char InsertQuerySource[] PROGMEM = "INSERT INTO energy_monitor.energy_log (volt_a,volt_c,total_volt,amps_ct1,amps_ct2,total_amps,ct1_watts,ct2_watts,total_watts,pwr_factr,line_freq,temp) VALUES ('%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d','%d.%02d')";
char InsertQuery[300]; //SQL query to run
const float FirmwareVersion = 2; //firmware version
//variables and constants for send timer
unsigned long EnergyMonTelePreviousMillis = 0; //the last time the send interval check ran
const int EnergyMonitorTelemetryInterval = 2000; //the interval to send, milliseconds
//uptime counter and heartbeat variables
int UptimeSeconds = 0; //uptime counter seconds, 0-60
int UptimeMinutes = 0; //uptime counter minutes, 0-60
int UptimeHours = 0; //uptime counter hours, 0 - 24
int UptimeDays = 0; //uptime counter days, 0 - maxint...32,000 day uptime? Unlikely
unsigned long UptimePreviousMillis = 0; //the last time the uptime counter updated
bool SendHeartbeat = false; //flag to send the heartbeat. Set true in uptime loop to send a heartbeat
int UptimeInterval = 1000; //how often in mS to run uptime function. Every second.
char HeartBeatPublishString[100];
//ATM90E32 Calibration Variables
/***** CALIBRATION SETTINGS *****/
/*
* 4485 for 60 Hz (North America)
* 389 for 50 hz (rest of the world)
*/
unsigned short lineFreq = 4485;
/*
* 0 for 10A (1x)
* 21 for 100A (2x)
* 42 for between 100A - 200A (4x)
*/
unsigned short PGAGain = 21;
/*
* For meter <= v1.3:
* 42080 - 9v AC Transformer - Jameco 112336
* 32428 - 12v AC Transformer - Jameco 167151
* For meter > v1.4:
* 37106 - 9v AC Transformer - Jameco 157041
* 38302 - 9v AC Transformer - Jameco 112336
* 29462 - 12v AC Transformer - Jameco 167151
* For Meters > v1.4 purchased after 11/1/2019 and rev.3
* 7611 - 9v AC Transformer - Jameco 157041
*/
unsigned short VoltageGain = 37495; //my 9VAC power adapter, "OEM"
/*
* 25498 - SCT-013-000 100A/50mA
* 39473 - SCT-016 120A/40mA
* 46539 - Magnalab 100A
*/
unsigned short CurrentGainCT1 = 25498;
unsigned short CurrentGainCT2 = 25498;
const int CS_pin = 8; //CS pin for ATM90E32
//ATM90E32 Data Variables
float VoltageA = 0;
float VoltageC = 0;
float TotalVoltage = 0;
float CT1Current = 0;
float CT2Current = 0;
float TotalCurrent = 0;
float TotalWattage = 0;
float PowerFactor = 0;
float ATM90E32Temp = 0;
float LineFrequency = 0;
float CT1Wattage = 0;
float CT2Wattage = 0;
unsigned short sys0 = 0;
unsigned short sys1 = 0;
unsigned short en0 = 0;
unsigned short en1 = 0;
//*************************************************
// Make sure to assign a unique MAC to each board!
//*************************************************
byte mac[] = { 0x21, 0xCA, 0x33, 0x4A, 0xED, 0x52 };
//Initialize ethernet client as ethClient
EthernetClient ethClient;
MySQL_Connection MySQLDatabase((Client*)& ethClient); //initialize MySQL_Connection as MySQLDatabase passing Ethernet client
//initialize the ATM90E32
ATM90E32 eic{}; //initialize the IC class
void setup() {
//wdt_enable(WDTO_8S); //enable the watchdog timer for 8 seconds
//PetTheDog(); //pet the watchdog to reset the timer
//initialize all the pins used
//open the serial port for debugging
//Serial.begin(9600);
Serial.begin(115200);
delay(100);
Serial.println(F("BOOT..."));
// start the Ethernet and obtain an address via DHCP
Serial.println(F("ETHERNET: Begin..."));
if (Ethernet.begin(mac) == 0) {
Serial.println(F("ETHERNET: FAIL. No DHCP."));
if (Ethernet.hardwareStatus() == EthernetNoHardware) {
Serial.println(F("ETHERNET: Hardware not found."));
}
else if (Ethernet.linkStatus() == LinkOFF) {
Serial.println(F("ETHERNET: No link."));
}
delay(5000);
//Reboot();
}
Serial.print(F("ETHERNET: Success. IP Addresss: "));
Serial.println(Ethernet.localIP());
delay(1500);
Serial.print(F("MYSQL: Attempting connection: "));
Serial.print(MySQLServer[0]);
Serial.print(MySQLServer[1]);
Serial.print(MySQLServer[2]);
Serial.print(MySQLServer[3]);
Serial.println(F("..."));
if (MySQLDatabase.connect(MySQLServer, 3306, MySQLUser, MySQLPassword)) {
delay(1000);
Serial.println(F("MYSQL: Connection succeeded."));
}
else {
Serial.println(F("MYSQL: FAIL. Connection failed."));
}
/*Initialise the ATM90E32 & Pass CS pin and calibrations to its library -
*the 2nd (B) current channel is not used with the split phase meter */
Serial.println(F("ATM90E32: Begin..."));
eic.begin(CS_pin, lineFreq, PGAGain, VoltageGain, CurrentGainCT1, 0, CurrentGainCT2);
Serial.println(F("ATM90E32: Success..."));
delay(2000);
}
void loop() {
unsigned long CurrentMillis = millis(); //get the current time count
//reconnect if connection is lost
//add reconnect to MYSQL here
if (CurrentMillis - UptimePreviousMillis >= UptimeInterval) {
UptimePreviousMillis = CurrentMillis;
UptimeCounter();
}
if (CurrentMillis - EnergyMonTelePreviousMillis >= EnergyMonitorTelemetryInterval) {
EnergyMonTelePreviousMillis = CurrentMillis; //update the time the check last ran
sys0 = eic.GetSysStatus0(); //EMMState0
sys1 = eic.GetSysStatus1(); //EMMState1
en0 = eic.GetMeterStatus0(); //EMMIntState0
en1 = eic.GetMeterStatus1(); //EMMIntState1
Serial.print(F("STATUS: System: S0:0x: "));
Serial.println(sys0, HEX);
Serial.print(F("STATUS: System: S1:0x: "));
Serial.println(sys1, HEX);
Serial.print(F("STATUS: Meter: E0:0x: "));
Serial.println(en0, HEX);
Serial.print(F("STATUS: Meter: E1:0x: "));
Serial.println(en1, HEX);
//if true the MCU is not getting data from the energy meter
if (sys0 == 65535 || sys0 == 0) {
Serial.println(F("ERROR: Not receiving data from energy meter - Check connection."));
}
//get voltage
VoltageA = eic.GetLineVoltageA();
VoltageC = eic.GetLineVoltageC();
if (lineFreq = 4485) {
TotalVoltage = VoltageA + VoltageC; //is split single phase, so only 120v per leg
}
else {
TotalVoltage = VoltageA; //voltage should be 220-240 at the AC transformer
}
//get current from both transformers and add for a total current measurement
CT1Current = eic.GetLineCurrentA();
CT2Current = eic.GetLineCurrentC();
TotalCurrent = CT1Current + CT2Current;
//get power. There seems to be no documumentation however guesses:
TotalWattage = eic.GetTotalActivePower(); //total wattage
PowerFactor = eic.GetTotalPowerFactor();
CT1Wattage = eic.GetActivePowerA(); //CT1 wattage
CT2Wattage = eic.GetActivePowerC(); //CT2 wattage
ATM90E32Temp = eic.GetTemperature();
LineFrequency = eic.GetFrequency();
Serial.print(F("ENERGY: Voltage A: "));
Serial.println(VoltageA);
Serial.print(F("ENERGY: Voltage C: "));
Serial.println(VoltageC);
Serial.print(F("ENERGY: Total Voltage: "));
Serial.println(TotalVoltage);
Serial.print(F("ENERGY: Current CT1: "));
Serial.println(CT1Current);
Serial.print(F("ENERGY: Current CT2: "));
Serial.println(CT2Current);
Serial.print(F("ENERGY: Total Current: "));
Serial.println(TotalCurrent);
Serial.print(F("ENERGY: CT1 Wattage: "));
Serial.println(CT1Wattage);
Serial.print(F("ENERGY: CT2 Wattage: "));
Serial.println(CT2Wattage);
Serial.print(F("ENERGY: Total Wattage: "));
Serial.println(TotalWattage);
Serial.print(F("ENERGY: Power Factor: "));
Serial.println(PowerFactor);
Serial.print(F("ENERGY: Temperature: "));
Serial.println(ATM90E32Temp);
Serial.print(F("ENERGY: Frequency: "));
Serial.println(LineFrequency);
//generate the SQL statement to insert into the database
sprintf_P(InsertQuery, InsertQuerySource,
int(VoltageA), int(VoltageA * 100) % 100, //sprintf_p doesn't do floats, so need to convert it to two ints to retain decimal
int(VoltageC), int(VoltageC * 100) % 100,
int(TotalVoltage), int(TotalVoltage * 100) % 100,
(int)CT1Current, (int)(CT1Current * 100) % 100,
(int)CT2Current, (int)(CT2Current * 100) % 100,
(int)TotalCurrent, (int)(TotalCurrent * 100) % 100,
(int)TotalWattage, (int)(TotalWattage * 100) % 100,
(int)CT1Wattage, (int)(CT1Wattage * 100) % 100,
(int)CT2Wattage, (int)(CT2Wattage * 100) % 100,
(int)PowerFactor, (int)(PowerFactor * 100) % 100,
(int)LineFrequency, (int)(LineFrequency * 100) % 100,
(int)ATM90E32Temp, (int)(ATM90E32Temp * 100) % 100);
Serial.print(F("MYSQL: Begin INSERT: "));
Serial.println(InsertQuery);
MySQL_Cursor* MySQLCursor = new MySQL_Cursor(&MySQLDatabase);
MySQLCursor->execute(InsertQuery);
delete MySQLCursor; //no results expected so delete cursor
Serial.println(F("MYSQL: End INSERT"));
}
}
//function to reset the watchdog timer. Call often
void PetTheDog() {
wdt_reset(); //reset the watchdog timer.
}
//function to use the watch dog timer to reboot the processor immediately
void Reboot() {
wdt_disable(); //disable any existing watchdogs
wdt_enable(WDTO_15MS); //enable with a 15MS timeout
for (;;); //loop forever so watchdog will reset processor
}
//function to manage the uptime
void UptimeCounter() {
UptimeSeconds++; //increase uptime seconds by 1
if (UptimeSeconds >= 60) { //if seconds are greater than 60 that's a minute
UptimeMinutes++; //increase minutes by 1
UptimeSeconds = 0; //reset seconds to zero
SendHeartbeat = true; //set the flag to send the heartbeat publish
}
if (UptimeMinutes >= 60) { //if minutes > 60 that's an hour
UptimeHours++; //increase hour count
UptimeMinutes = 0; //reset minutes to zero
}
if (UptimeHours >= 24) {
UptimeDays++;
UptimeHours = 0;
}
if (SendHeartbeat == true) { //if the heartbeat flag is set (on minute changeover), send heartbeat
Heartbeat();
SendHeartbeat = false; //set the flag false to it doesn't run until next minute
}
}
void Heartbeat() {
//ArduinoJSON exists as a library to generate and serialise JSON. Not doing anything complicated so can do it in less memory manually
//Bunch of sprintfs/strcats to build the heartbeat string.
//create an uptime in ISO 8601 format:
/*P is the duration designator(for period) placed at the start of the duration representation.
Y is the year designator that follows the value for the number of years.
M is the month designator that follows the value for the number of months.
W is the week designator that follows the value for the number of weeks.
D is the day designator that follows the value for the number of days.
T is the time designator that precedes the time components of the representation.
H is the hour designator that follows the value for the number of hours.
M is the minute designator that follows the value for the number of minutes.
S is the second designator that follows the value for the number of seconds.
For example, "P3Y6M4DT12H30M5S" represents a duration
of "three years, six months, four days, twelve hours, thirty minutes, and five seconds".*/
//generate JSON string with sprintf_p . Format string stored in PROGMEM
sprintf_P(HeartBeatPublishString, PSTR("{\"ip\":\"%d.%d.%d.%d\",\"uptime\":\"P%dDT%dH%dM%dS\",\"ver\":\"%d.%02d\",\"freemem\":\"%d\"}"),
Ethernet.localIP()[0], Ethernet.localIP()[1], Ethernet.localIP()[2], Ethernet.localIP()[3],
UptimeDays, UptimeHours, UptimeMinutes, UptimeSeconds,
(int)FirmwareVersion, (int)(FirmwareVersion * 100) % 100, //sprintf_p doesn't do floats, so need to convert it to two ints to retain decimal
(int)freeMemory());
Serial.print(F("HEARTBEAT: ")); //print it out to the serial for debugging
Serial.println(HeartBeatPublishString);
}
The table data looks like this with the log_time being added automatically by MySQL since it is a timestamp() field. ID incrementing and indexed.
(My Watts values are nonsense. I think I have one of the current clamps backwards)
Over 24 hours of data occupies a whole 25MB.
Pulling that into HA is just done with some SQL sensors.
sensor powermonsense:
- platform: sql
db_url: mysql://energymon:energymon@localhost/energy_monitor?charset=utf8
queries:
- name: Power Voltage Phase 1 Avg 10s
query: "SELECT ROUND(AVG(volt_a),2) AS avg_volt_a FROM (SELECT volt_a FROM `energy_monitor`.`energy_log` ORDER BY log_time DESC LIMIT 5) t"
column: 'avg_volt_a'
unit_of_measurement: V
- name: Power Voltage Phase 2 Avg 10s
query: "SELECT ROUND(AVG(volt_c),2) AS avg_volt_c FROM (SELECT volt_c FROM `energy_monitor`.`energy_log` ORDER BY log_time DESC LIMIT 5) t"
column: 'avg_volt_c'
unit_of_measurement: V
scan_interval: 10
Note I perform all the calculations in the query. Since I’m saving data every 2 seconds to MySQL, I do a 10 second average selecting the column ordered descending by log_time, limited to 5 records. Then average the wanted fields, rounded 2 decimal places. HAQ supports only 1 result from a SQL query so this works out well. The data is then available as the sensor state.
MySQL says these queries take between 0.0021 and 0.0031 seconds to complete because all the work is done in the DB engine very efficiently.
