"Normal" Database size and sensor log table location Topic is solved

[blitzkneisser]

After having the malformed database problem I decided to clean things up as described here in the forum (http://www.dosomethinghere.com/2013/02/ ... malformed/)

Generating a new database from the SQL dump was so slow that - at that speed - it would have taken some hours to a day to generate the Database again. My domoticz.db is ~57MB big... is that normal for a Domoticz-System with ~260 devices (not separate devices, also counting multiple entries for different functions of 1 device)?

One culprit is for sure the 7 day sensor log. I found out, that for some sensors (mostly Temperature) this detailed logfile goes back to around 1st of December 2015. Having all values stored since then is for sure causing a gigantic database size. I first found the database table "LightningLog", but this one seems to contain only the last 30 days, so I guess that's the 30 day switches protocol - with ~120.000 entries. I started deleting everything except the last ~20%, already this is running for more than one hour so far ( not on the Raspy, on my Desktop PC).

So... in which table are the detailled sensor values stored? is there a possibility to delete everything but the last 7 days without losing the monthly/yearly statistics?

Edit: Just found it. "Temperature" contains ~530.000 Entries
So I guess the xxx_Calendar Tables contain the values for the monthly and yearly graph then?

[blitzkneisser]

Yay!
Fixing the database worked. It took half a day to generate the db file out of the SQL dump, and as the DB was then repaired, the numerous entries in the sensor log were purged automatically, so doing it automtically was no longer necessary.

The DB file still has the big size... is it purged/compressed automatically at some point in time or will I have to do that manually?

[Egregius]

I don't know when a vacuum accurs in Domoticz.
A backup/restore should do it I guess, or otherwise use a SQLite tool.

And also consider if you really need detailed logfiles of switches and sensors for that long period. Definnitelly with that number of devices.
My DB is only 540KB with 204 devices. Log is set to 2 days for both switches and sensors.

[blitzkneisser]

No vacuum necessary, looks like that happened automatically. Now the DB size is down to 14MB (from 55MB) which is OK.
The long logfile (sensor log) only happened because of that DB error which prevented domoticz from deleting all sensor entries older than 7 days.
The Switch log for 30 days is not that big - but I think I'll reduce it to a shorter period - as well as the sensor log - as the number of devices is still prone to grow
Especially now as I found out what the mysensors platform is actually capable of

[tjabas]

Can i delete all of the text content in the lightninglog without any problems, just to clear the log?
I also noticed that its possible to edit the z-wave table, can i change for example node 5 to 6 instead?
I thougt of rearange them so they will go from 1,2,3,4 and so on, as for now they are 3,8,10 and so on.

Thanks in advance.

[waaren]

Can i delete all of the text content in the lightninglog without any problems, just to clear the log?

If you first stop domoticz that should be OK. Just to stay on the safe side always make a copy of your database before manipulating data.
You can also limit the amount of history in this log by changing the values in [setup] [settings] [log History] tab

I also noticed that its possible to edit the z-wave table, can i change for example node 5 to 6 instead?
I thougt of rearange them so they will go from 1,2,3,4 and so on, as for now they are 3,8,10 and so on.

Please try and tell us if it worked or if it messed up your complete zwave configuration.

[tjabas]

Can i delete all of the text content in the lightninglog without any problems, just to clear the log?

If you first stop domoticz that should be OK. Just to stay on the safe side always make a copy of your database before manipulating data.
You can also limit the amount of history in this log by changing the values in [setup] [settings] [log History] tab

I also noticed that its possible to edit the z-wave table, can i change for example node 5 to 6 instead?
I thougt of rearange them so they will go from 1,2,3,4 and so on, as for now they are 3,8,10 and so on.

Please try and tell us if it worked or if it messed up your complete zwave configuration.

Do you think that changing the order of the z-wave nodes can casue the z-wave system to chrash?
If it casues a chrash i have a backup i can go back to, but do you think that the z-wave stick gen 5 might be fucked up to if i do these changes in the db file?

[waaren]

Do you think that changing the order of the z-wave nodes can cause the z-wave system to crash?

Sorry but I really have no idea and no intention at all to experiment with it to find out.

[tjabas]

Today i tested to delete all content in the lightninglog and the Size of the db log wnt from 26mb to 6mb, how cone that this log has got so big? Shouldt to oldest events in the log get delete automaticlly?

And I didnt dare to change the z-wave config