Measure to prevent database growing

[BartSr]

Hi , would it be an idea to let the user decide to Not Log the status of an device eg by a checkmark in that device?
eg
I get the status of my toilet light to start the fan after the light is off. So every status change is logged same as start/stop of the fan.
Lights in rooms ar controlled by timers. Also these status changes are logged. As imho there is not always a log required this makes the database bigger and bigger.
What about this suggestion?
Bart

[waltervl]

There is a setting for this to purge the switch log data after X days (default 30 days).

See menu Setup - Settings, tab Log History https://www.domoticz.com/wiki/Applicati ... og_History

[BartSr]

if i understand well it's all or nothing, for that individual 'nothing' might be more usefull

[waltervl]

if i understand well it's all or nothing, for that individual 'nothing' might be more usefull

No, it is x days (for switches, default 7 it seems). After that x days the log for switches will be purged. The bigger X the bigger your database.
There is also the short log for sensors between 1 and 7 days storage. This log is used for the top graph of a sensor (temperature, barometer etc). The higher the values the bigger your database.

There is also the possibility to set switch "Only add newly received values to the log" And when enabled it will skip logging double entries.

But this is all described in the wiki.

[plugge]

if i understand well it's all or nothing, for that individual 'nothing' might be more usefull

You could use this dzVents script:

Code: Select all

--[[ 
Clear listing logs of devices
Make a note of the device idx you want to clear the log regularly
--]]

local ip = '<your-ip>'
local scriptVar = 'Clear Logs'
local url = 'http://'..ip..':8080/json.htm?type=command&param=clearlightlog&idx='

return {
    active = true,
	on = {
		timer = {'at 00:30 on mon',  -- change this to any time and day you wish, or multiple
		         },
	},
	logging = { marker = scriptVar,
	    },

	execute = function(dz, triggeredItem)
	    -- Clear the following logs:
	    if  triggeredItem.isTimer 
	    then
	        print(' Clearing logs')
    	        dz.openURL(url..'idx') -- here insert the idx number
    	        print(' Logs cleared')
	    end
	end
}

[waltervl]

Why do you want to run this script if Domoticz is cleaning it up for you automatically?

[BartSr]

I keep my suggestion. Why to delete afterwards log of device if you don't need such? A simple on/off log option at every device shouldnot be that difficult. Following the suggestion to use the settings option is, as I said, an all or nothing for the catagory switches so not a desired option.
Plugge's option is in between. It prevents growing of database (fof individal idx) but still the unnecessary logs are written.
Bart

[waltervl]

My database is 21 MB, containing 200 devices and 4-5 year of historical data. The device log part will take a big part of it but what memory problem has to be solved? An RPi should handle this with 2 fingers in the nose (Dutch saying).

If your database is much larger you should think why this is. Deleting the short log data is most likely not the origin of this big database size.

[solarboy]

I keep my suggestion. Why to delete afterwards log of device if you don't need such? A simple on/off log option at every device shouldnot be that difficult. Following the suggestion to use the settings option is, as I said, an all or nothing for the catagory switches so not a desired option.
Plugge's option is in between. It prevents growing of database (fof individal idx) but still the unnecessary logs are written.
Bart

This is a good suggestion, as well as device level logging rather than hardware interface.

[Kedi]

Why to delete afterwards log of device if you don't need such? A simple on/off log option at every device shouldnot be that difficult.

The deleting of logs is done automatically. My biggest table is more then 106.000 records (Temperature_Calendar)
Simple on/off option should not be at every device, so what are you saving a few 100 or 1000 records?
The implementation is extra code and an extra field in the database.
My database was over 40 Mb and with the option "Only add newly received values values to the Log" it shrank to approx. 34 Mb.
I did not notice any performance increase with that smaller database.

[waltervl]

Additional: The xxx_Calendar tables will grow as they contain the history of sensors (not switches!) per day.
The shortlog (for sensors) and the switch log will be purged according the general settings so after settings those on a stable environment (no adding of sensors) the size of this tables will stabilize on a certain level. There is no performance increasement expected when purging these on a individual level.

But if you really want to implement this you are free to change the code and submit a PR on github.

[BartSr]

Gents, more important then a growing database is the write actions on the SD card. Taken into account that I once deleted over 25.000 records of an on/off device you can understand that this is unwanted as well (btw there were more on/off devices with over 20k records). It happened once that after I deleted from within Domoticz the log of an on/off device Domoticz stalled and crashed which almost never happened in the past 5 years.
The idea as suggested to update the source code with my option by myself seems strange to me as I, thereafter I have to do that every time a new release is available.
Thanks for your attention.
-Bart

[waltervl]

It would only help in reducing write actions if you decide not to store any switch log action of a specific device in the database. Storing less records will not reduce write actions. You write a switch record, keep it for x days and remove it. That are 2 write actions as keeping the record for x days is no write action. So something went wrong in your case in the past that the logging of a device went crazy or the automatic purging failed somehow. The clear button in the setup - Settings- log section should fix this.

A PR is a pull request meaning that you can request to merge your code change into the Domoticz repository so everybody can use your enhancement after it is merged into beta builds and the next stable release. That is the power of open source development.

[Kedi]

If you are concerned about write actions, consider using a SSD
Search the Internet for 'SSD 128Gb' and you will get a lot of SSD's for under € 20,00
Or do as I did take 2 SSD 256Gb and put then in raid 1 with btrfs on it and you have a system than can fail 1 drive and have plenty of room on it and can make snapshots of the system every hour without any performance impact.

[BartSr]

Hi Kedi, thanks for your suggestion. Currently my home automation is driven by a raspberry pi 3+. What computer do you have? I had intention to move to NAS but docker seemed no longer available.
Bart

[Kedi]

I use a Raspberry Pi 4, but it could easily been done on a model 3B+
I do some backups to NAS and USB drive like:
1. Domoticz database every 5 minutes to NAS, takes about 2 seconds per backup
2. Important set of directories every 4 hours to USB-drive, takes less then 1 minute per run.
3. Complete image of the '/' every week to NAS, takes about 36 minutes in the middle of the night.

Everything automated by cron, so I am good concerning data and system backup

Snapshots I do every hour of the home directory and of '/' (excl. home) every day.
I keep those snapshots for a few years. The system is so fast that I don't even measure the time it takes.
Even if my Raspberry, with everything in it, is completely destroyed, I am up and running in less then a hour.

[BartSr]

bit late with my reaction but let me say it's quit impressive!
I once had a script doing also a daily backup of db + some maps but there seemed to be kind of bug so that the maps wouldnot backup.
might it be possible to share your scripts?
TIA
-Bart

[BartSr]

BTW, might it be possible to have domoticz etc be boot from ssd ? I have some but no idea how to fix such.

[BartSr]

@Waltervl
re It would only help in reducing write actions if you decide not to store any switch log action of a specific device in the database.
This is exact what I mentioned while starting these posts.

[Kedi]

What really would save write actions is to dive into Linux.
In the file /etc/fstab you could add some options to the mounting of drives, which would save a lot on performance and write actions.
Part of my fstab looks like:

Code: Select all

UUID=651392c4-fdbb-49d2-b426-e63e236cc4d4   /               btrfs   defaults,noatime,nodiratime,commit=60,discard=async,subvol=@root  0  0
UUID=651392c4-fdbb-49d2-b426-e63e236cc4d4   /home           btrfs   defaults,noatime,nodiratime,commit=60,discard=async,subvol=@home  0  0

The saving is in noatime, nodiratime and commit=60
Look in the manual of mount what those options mean.

Code: Select all

man mount

or https://linux.die.net/man/8/mount
The downside of commit=60 might be that in case of power loss you might lose 60 seconds of data. To prevent that I have an UPS.
But what is 60 seconds in a lifetime!