First, I am very happy with the device. It’s wicked and I am very grateful to the hard work put in by all. I’ve tried hard to be self sufficient and rely on the documentation but now I’m at a point where I need to ask some questions.
We are about to have some electrical work done here so I’m getting ready for dataloss…
I am running a dedicated MySQL server and EmonCMS (on the Emonpi) is using it. I needed to shutdown the SQL server for about 5 minutes earlier for one of my tests and I expected to lose some data from EmonCMS during that time as it wouldn’t have been able to log anything to the database.
I checked the EmonCMS gui and it has either done a very good job at hiding the fact there was no way to log the data or it’s stored it until DB availability.
(1) When the database becomes unavailable what happens to the logging of data, is it stored temp or discarded?
(2) Does the EmonPi retry for DB accessibility by itself once the DB comes back online or do I need to restart some service?
(3) When running a backup, is the underlying program smart enough to pull the SQL DB from the DB server listed in the emoncms settings file or does it specifically point to the old one on local storage?
Didn’t know where to post these questions and hope you can help. I hope not to lose too much data during the upcoming disruptions.
Can you confirm if these are seperate machines? the emonPi also runs a MySQL server. If you are saying that emoncms on one machine(the emonPi) is using the MySQL server on another machine(other MySQL server) how is that configured? have you simply changed the settings in emoncms/settings.php so it pointing to the other MySQL server?
By default emoncms saves it’s data to custom timeseries files rather than MySQL. Only the configuration and meta is stored in the MySQL tables.
As emoncms uses Redis to hold a majority of settings in memory, it’s only if you change something that then needs saving to MySQL or you use a new piece of info that isn’t already held in Redis that emoncms will go to the MySQL tables. The use of Redis (in this case) simply allows emoncms to continue functioning despite MySQL being offline and delays the point where it trips up. So it’s not really a reliable solution, (I think you were lucky on this occasion).
Backup, will AFAIK use the settings.php settings for the currently used MySQL server. However unless you have elected to specifically use MySQL data feeds rather than the default, ALL your data is still only on the emonPi, just your configurations etc are on the MySQL server. Backup will also use the paths defined in the settings.php to locate your timeseries feeds, usually these are found at /home/pi/data/phpfina and /home/pi/data/phptimeseries on an emonPi.
IMO using a remote MySQL server brings no gains when using the standard timeseries feed engines for storing data, it only creates potential pitfalls with connection/networking etc. If it were me I would either go back to using the “old db” to keep emoncms entirely on the Pi or install emoncms to the “other MySQL server” and send data to it using http(s), much like you would send to emoncms.org by configuring emonhub.conf to send to one or more emoncms instances.
There were some recent commits to the emonpi version of emonhub, so if you were to use emonhub to post to a remote emoncms instance (and you are bang up to date) there is now apparently a small amount of buffering, so if you took down the other server, the emonPi would buffer the data in RAM and post it via http when the other server comes back on line or of course if you reverted to using the “old db” the emonPi could carry on without data loss as long as it’s powered, it would not be dependent on a network or the other server.
Thanks for your input - that was exactly the sort of detail I was hoping for in response
To answer your questions:
Can you confirm if these are seperate machines?
Yes, the dedicated DB server is actually running on part of an ESX failover cluster.
the emonPi also runs a MySQL server. If you are saying that emoncms on one machine(the emonPi) is using the MySQL server on another machine(other MySQL server) how is that configured?
Yes, all I did was change the settings in the config.php so, as you said, all I’ve done it move the location where the emonpi stores it’s settings that Redis isn’t supporting.
have you simply changed the settings in emoncms/settings.php so it pointing to the other MySQL server?
As above.
So, thank you indeed for pointing out that I would need to set up the feeds to use MySQL. This was an oversight on my part during the initial implementation of the EmonPi setup here. I think if I am going to change anything I would first want to know whether it’s possible to migrate the data from Pipafina to SQL and then I will have it use the DB server (or use a local copy and setup replication).
I have a question about “_ I would either go back to using the “old db” to keep emoncms entirely on the Pi or install emoncms to the “other MySQL server” and send data to it using http(s), much like you would send to emoncms.org by configuring emonhub.conf to send to one or more emoncms instances._”.
If I were to do this would I still have the issue with the Pi needing access to the DB server for the configuration that isn’t supported by Redis? As there is are 2 dedicated mirrored webservers on the cluster it’s possibly an option.
It isn’t a documented/supported route but it is undoubtedly possible to do with the aid of a custom script of some sort, but to my knowledge there hasn’t been anything published/shared that would do it.
Possibly better with the former rather than the latter as the move to PHPfina was partially to reduce the SDcard wear/failures, so unless you add a spinning disk to the emonPi it might be better to write to a remote SQL server, I say might because I have no experience of running emoncms with anything other than a local SQL server and I do not know how emoncms will cope with any delays or unavailability across the network connection as it wasn’t designed for that application.
As I mentioned previously, it is unknown (to me at least) how emoncms will react if it tries to dip into the SQL tables for a value and the table isn’t accessible, even momentarily. I’m pretty sure the redis implementation might smooth over any temp glitches with writing data to the tables but most code I have looked at tries to get values from redis, if it’s not in redis it tries mySQL and any perceived absence there will result in an error eg “feed doesn’t exist or was deleted” or a value reset eg daily totals - if the last value found (or not found in this instance) isn’t dated today store the current value as the new accumulated daily amount.
Not only values/settings not supported by redis, but also values/settings that are supported by redis but have not yet been called since the last reboot or redis flush, as far as I understand most of the stuff in redis get populated when first called upon, I do not believe redis get fully populated at start up.
The guys to ask are @TrystanLea or @nchaveiro, most of this is above my pay grade and may not be 100% correct, I’m just highlighting where IMO you might have some issues to overcome or at least rule out or test for.
I cannot really disagree with your ambition as I too run all my sites as RO installs and they all report back to a common emoncms server. Although I do have to say the likelyhood of an SD card failure with the emonSD image is quite unlikely, there have been no reports of such for quite a while, the SDcard tech has improved and the emonSD is prodominantly RO and emoncms is “low-write” but it isn’t impossible.
IMO the best OEM images were the “OEM rock solid gateway” images, they were totally RO and only had a stripped down OS plus the OEM gateway software, which was emonHub’s predecessor. This was a forwarding only solution but as the name suggested it was rock solid.
The emonSD image is quite a lot of software to unravel and the way it is specifically set up is for a local emoncms, if you what to go this route I would recommend starting with a bare OS image, make it RO, add emonhub (original not the emonPi variant) and to that you could add a bespoke utility to interface with the LCD and button on the emonPi. That is all you NEED but you can add to it as you wish.
as you can see there’s a lot to unravel. If all you are concerned about is the SDcard then you could as suggested in my first post, just have a remote emoncms and not use emoncms on the emonPi, that is the simplest route and I guess it is the “officially supported” route, but I would be more than happy to help develop a “rock-solid” type forwarding-only (read-only) alternative if built from scratch rather than stripping away from the existing image. personally I would also want to steer clear of splitting the emoncms install across servers as you suggest in this thread, mainly as it would force a one device to one (split) emoncms instance and it’s unlikely to be robust enough for my needs.
