database size and growth Topic is solved

Topics (not sure which fora)
when not sure where to post, post here and mods will move it to right forum.
Post Reply
wlaarhov
Posts: 14
Joined: Saturday 22 October 2016 17:51
Target OS: Raspberry Pi
Domoticz version: V3.5877
Location: Netherlands
Contact:

database size and growth

Post by wlaarhov » Sunday 20 November 2016 11:16

Hello Experts,

I am running Domoticz now for couple of months, but at SOME point (don't know when anymore) the database has grown quite significant. From de default to 8GB. (on a 32G card this is significant)
I am running Domoticz on a Raspberry and this 8GB database is quite "heavy on the small box" especially with making a backup.
There are about 12 Fibaro devices that result in a higher number of devices obviously, 3 temp-sensors on the 1-wire bus, 3 Raspi-GPIO in/out, 4 Zavio camera’s and a weather underground connection.
so all-in-all not too impressive, and for sure should not result in an 8GB database

So a couple of questions here:
- What could have filled up the DB so dramatically
- Is there a way (even outside Domoticz) to "purge" the database
- Can I get some "feedback from the field" to what DB size would be "normal"

Thanks,

Wim,

PS: the Zavio camera's work great by the way. streaming, snapshots, and all, and streaming even works on some "mobile clients". I never have seen questions or remarks about the Zavio IP camera's, but if there is an interest I can do a post on how I got that all operational.
System: Raspberry 3, Aeotec USB OpenZwave.
Version: 3.5877
Switches/Sensors: mostly Fibaro, Raspi on-board

User avatar
Egregius
Posts: 2747
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: Beta
Location: Beitem, BE
Contact:

Re: database size and growth

Post by Egregius » Sunday 20 November 2016 11:21

Use a SQLite tool to see wich table holds the most records.
Check your settings at Setup > Settings > Log History
If they are high database is of course bigger.
I don't care about switch history so that's on 1 day, also the shortlog of sensors I don't use so that's also at 1 day. I store the thermometer data I'm interested in seperately in a mysql database.
That way, even with 316 devices my database is only 800KB large :lol:

wlaarhov
Posts: 14
Joined: Saturday 22 October 2016 17:51
Target OS: Raspberry Pi
Domoticz version: V3.5877
Location: Netherlands
Contact:

Re: database size and growth

Post by wlaarhov » Sunday 20 November 2016 11:30

Thanks for the ultrafast reply. So I was "about right" that the DB is quite "heavy". Perhaps it wants to look like the owner ;) .

I have logging also at 1 day since most stuff "works or it does not" and only use it for trouble-shooting during install/configure.

So I will for sure Google around if I can find a post to divert stuff like "temp readings" to a different DB.

I will do some digging in the DB and for amusement value post here what the content is "bloating" the DB.
It must be "some sort of data", because even after compressing the file after backup it still is 900+MB :o

Wim,
System: Raspberry 3, Aeotec USB OpenZwave.
Version: 3.5877
Switches/Sensors: mostly Fibaro, Raspi on-board

User avatar
Egregius
Posts: 2747
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: Beta
Location: Beitem, BE
Contact:

Re: database size and growth

Post by Egregius » Sunday 20 November 2016 11:45

I use Adminer.php to explore the database, there are also other tools available.
Look wich table has the most records and that'll indicate where it goes wrong.

Cleaning unused stuff can be done by a query like:

DELETE FROM MultiMeter WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)
If the device isn't used why bother saving the history?
Above query can be executed easily in a php script:

Code: Select all

<?php
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$clean = strftime("%G-%m-%d %k:%M:%S",time()-86400);
$tables = array('MultiMeter','MultiMeter_Calendar','Percentage','Percentage_Calendar','Rain','Rain_Calendar','Temperature','UV','UV_Calendar','Wind','Wind_Calendar');
foreach($tables as $table) {
    $query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)");
    if ($query) {
        $rows = $db->changes();
        if($rows>0) echo $rows." rows removed from $table<br/>";
    }
    $query=$db->exec("DELETE FROM $table WHERE Date < '$clean'");
    if ($query) {
        $rows=$db->changes();
        if($rows>0) echo $rows." rows removed from $table<br/>";
    }
}
$sql = 'VACUUM;';
if(!$result = $db->exec($sql)){ die('There was an error running the query [' . $db->error . ']');}
 
This deletes all unused and stuff older than 1 day. At the end a VACUUM is sent to free the empty database space.

wlaarhov
Posts: 14
Joined: Saturday 22 October 2016 17:51
Target OS: Raspberry Pi
Domoticz version: V3.5877
Location: Netherlands
Contact:

Re: database size and growth

Post by wlaarhov » Sunday 20 November 2016 11:56

OK :o 12 MILION entries in "LightningLog" did the trick. :lol:
AND I actually now know what caused it.

I had a switch to one of the Raspberry GPIO pins, but (just lazy ;) ) figured "I do the pull-up resistor later".
When I noticed all the log entries on the exact date-time I remembered the "incident".
Pull-up now is done through the initialization of the pins in the Domoticz.sh script. So no uncontrolled flapping of switches anymore :)

So I will (temporary) lower the logging history interval (because I am still building the stuff I am not too worried about logging at this moment) and then the only thing is the "vacuum".

THANKS! for putting me in the right direction AND the clean-up script :) much appreciated.
System: Raspberry 3, Aeotec USB OpenZwave.
Version: 3.5877
Switches/Sensors: mostly Fibaro, Raspi on-board

User avatar
Egregius
Posts: 2747
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: Beta
Location: Beitem, BE
Contact:

Re: database size and growth

Post by Egregius » Sunday 20 November 2016 12:15

You're welcome ;)
Whar's the size now?

wlaarhov
Posts: 14
Joined: Saturday 22 October 2016 17:51
Target OS: Raspberry Pi
Domoticz version: V3.5877
Location: Netherlands
Contact:

Re: database size and growth

Post by wlaarhov » Sunday 20 November 2016 12:26

DB size does not look like the owner anymore at all. It is little under 1MB. :)

I had a different post where I asked why backup was not working, so, that was also because of the 8GB db. So I updated that post as well with root-cause because also now backup is working fine.

Thanks again :)
System: Raspberry 3, Aeotec USB OpenZwave.
Version: 3.5877
Switches/Sensors: mostly Fibaro, Raspi on-board

User avatar
Egregius
Posts: 2747
Joined: Thursday 09 April 2015 12:19
Target OS: Linux
Domoticz version: Beta
Location: Beitem, BE
Contact:

Re: database size and growth

Post by Egregius » Sunday 20 November 2016 13:14

Wow, what a difference!
Backup will run smoothly now.

OldPensionado
Posts: 57
Joined: Wednesday 01 March 2017 10:07
Target OS: Raspberry Pi
Domoticz version: 3.8153
Location: The Netherlands
Contact:

Re: database size and growth

Post by OldPensionado » Friday 21 April 2017 17:58

Although this topic is rather old, the problem was existing, at least in my situation.

I got a tip from @ubee about clearing the log which exists with every switch. Just select the tab "Switches" and select the "Log" button. Depending of the number of entries this could take a while. On the right hand side there is a button "Clear". Perform this action for every switch you want.
I tested this for all my switches and the results were amazing:
  • - my database was about 2,7 MB in size
    - the largest table holds 34.064 entries
After the 'clear' action my database was only 430 kB!
By the way: the size of the database can be seen after making a backup. Not during the backup, the figure which is shown before actually saving the file on disk, is much smaller and I don't know what it means.
In the tab Setup > Settings > Log History I have set a period of 5 days. Hopefully the database will not grow too fast.
RPi 1, model B
Raspbian Jessie Lite
Domoticz V3.8153

Well, such is life. And it's getting sucher and sucher...

qwerk
Posts: 293
Joined: Tuesday 22 July 2014 7:21
Target OS: Raspberry Pi
Domoticz version: beta
Location: Netherlands
Contact:

Re: database size and growth

Post by qwerk » Monday 08 May 2017 21:02

my database is about 26M. it looks ok, normal data in there, no errors.

my automatic backup takes about 1 minute at 0:00 o'clock,
at other hours it takes 30 seconds. according to the log

it feels longer more like 2 minutes, because it freeze domoticz kinda...


what are your experiences ? how large is your DB and what is your backup time ?

OldPensionado
Posts: 57
Joined: Wednesday 01 March 2017 10:07
Target OS: Raspberry Pi
Domoticz version: 3.8153
Location: The Netherlands
Contact:

Re: database size and growth

Post by OldPensionado » Sunday 04 March 2018 16:01

Hi qwerk,

Sorry for my very, very late response. I've been busy renewing our kitchen and some other stuff last summer and the rest of the year. Finally I logged into this forum and saw your question.
My database was 688 kB and after clearing all the switch log data it was only 424 kB. It takes less than one second to make the backup.
RPi 1, model B
Raspbian Jessie Lite
Domoticz V3.8153

Well, such is life. And it's getting sucher and sucher...

Dr4co
Posts: 1
Joined: Tuesday 04 December 2018 20:48
Target OS: Windows
Domoticz version: 3.8942
Location: Stockholm
Contact:

Re: database size and growth

Post by Dr4co » Tuesday 04 December 2018 21:02

Oh wow, then my DB must be "broken"... :lol:
My DB has grown and (was 125 MB) is 99 MB big now..

When I first installed it, I wanted a long history of the light switch history (logs),
so I increased the Log History for Light/Switches to 1000 days.. :P And Short Log Sensors to 7 days.

I now realize that I don't have the use for it..
Anyhow, I have had some issues with the Logs for the Motion sensors, the logs was so huge, that Domoticz freezed and I wasn't able to view or clear the log.

So today I finally tried out sqlite3 for the first time! :)
Downloaded sqlite3 and extracted it to the Domoticz folder.

Code: Select all

sqlite3 domoticz.db
sqlite> DELETE * from [LightingLog] where [DeviceRowID] = 290 AND [Date] <= '2018-12-01';
sqlite> vacuum;
sqlite> .exit
Suddenly the DB was "only" 99 MB afterwards, from previous 125 MB, I believe I need to clean-up more logs for other devices now!

Long story short: It takes me approximately 1 minute and 30 seconds to export a Domoticz DB-backup.

Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests