Hello,
Following a significant experience feedback regarding the usage and size of the DB, I propose this new Feature Request and I think it will quickly become urgent. Let me explain:
-
I have 92 Devices and 143 Device Features composed of:
- 4 cameras (with a poll between 10s and 1 minute - for some, it would be necessary to have real-time or every 2 seconds sometimes, but this is not possible: makes the interface too slow),
- 2 devices / 9 device features Z-Wave,
- 9 devices / 18 device features Philips Hue,
- 19 devices / 27 device features Tasmota,
- 58 devices / 87 device features MQTT,
-
On top of that, 25 scenes are running.
-
Today, my device_feature_state table consists of more than 900,000 lines. And my variable
DEVICE_STATE_HISTORY_IN_DAYSis set to 30 days for testing purposes. But I would normally need to keep certain data at all times (today I do it on Gladys V3), such as three-phase electricity consumption, etc. Today it’s impossible. -
The DB weighs approximately 250 MB for 30 days, since I have been doing these tests for 9 months, if I had selected « Always keep history » I would be at almost 3GB of DB today.
Every 30 days for 6 months, I have to start over with a complete installation of Gladys V4 because:
- Randomly but very regularly (about 15 times a day), the dashboard takes 5 minutes to display,
- Randomly, even if the dashboard is displayed, pressing a command on the dashboard no longer moves the cursor and the command does not pass.
- Randomly, the scenes no longer trigger on time and on time, same for the scenes triggered by motion detectors, but trigger 5 minutes or sometimes 30 minutes after the time.
- After a reboot of the pi, nothing is resolved,
- After a stop/start of the docker container nothing is resolved,
- If I directly delete all the states of the features in the Database, gladys works well again, but the Gladys Plus backups no longer work.
Soon I should be able to add (once the service is released):
- 25 devices / 122 device features to the new Netatmo service.
Today, this problem seems to only concern me, perhaps because I have a lot of equipment, but I think that very quickly many people will find themselves with this kind of problem.
@pierre-gilles we had already discussed this, but it seems to me that a good solution would be to add:
- The possibility to modify the « keep_history » for each feature of each service (for example personally, I do not need to keep the values of my motion detectors in each room - except perhaps if I go on vacation, no need to keep the battery values, etc.) very often the « last_value » is sufficient accompanied by the « last_value_changed ».
- A checkbox under each feature to say « I want to keep the new values if and only if they are different from the previous one » (for example: for the Philips Hue service and soon for Netatmo, we perform « polls » to get the data. These are therefore permanently rewritten « every minute » for example in a new line. However, in 99.99% of cases we do not need a new line in the « state » table, only the change of the « last_value_changed » column is sufficient which it would be necessary to be able to compare with the « created_at » column of the « device_feature_state » table which actually has 2 columns that are always identical today when they could find 1 interest here.
- Finally, a last point that would greatly help to free up space: the independent selection for each feature of the amount of history to keep. Indeed, the backup of 1 week or 2 may be sufficient for all binary type features. Maybe the backup of temperatures / hygrometries may be sufficient for 1 month. But for all electrical and water consumption I would then need to be able to establish the graphs over the year or over 10 years for example for a solar installation.
