database size and growth Topic is solved

[wlaarhov]

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.

[Egregius]

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

[wlaarhov]

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

Wim,

[Egregius]

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]

OK 12 MILION entries in "LightningLog" did the trick.
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.

[Egregius]

You're welcome
Whar's the size now?

[wlaarhov]

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

[Egregius]

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

[OldPensionado]

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.

[qwerk]

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]

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.

[Dr4co]

Oh wow, then my DB must be "broken"...
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.. 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.

[assenzuid]

Today I run PRAMGA Integrity_check on my database.

The result is ;

Code: Select all

 wrong # of entries in index ll_id_date_idx

How to clean LightingLog ;

Code: Select all

sqlite> SELECT COUNT(*) FROM LightingLog;
2467305

No clue how to fix this

[waaren]

Today I run PRAMGA Integrity_check on my database.
The result is ;

Code: Select all

 wrong # of entries in index ll_id_date_idx

No clue how to fix this

Same way as fixing a malformed database. Have a look at this post

[assenzuid]

Database fixed, but how to clean the LightingLog?

Code: Select all

sqlite> SELECT COUNT(*) FROM LightingLog;
2467305

[waaren]

Database fixed, but how to clean the LightingLog?

Code: Select all

sqlite> SELECT COUNT(*) FROM LightingLog;
2467305

My approach would be to first investigate why I have such a large amount of records in this table.
Start with making a copy of your domoticz database

Code: Select all

sudo cp <domoticzdir>/domoticz.db ff.db
sudo sqlite3 ff.db
sqlite> select count(*) from lightinglog where date < '2019-05-01';  
select count(*) from lightinglog where date < '2019-06-01'; 
select count(*) from lightinglog where date < '2019-06-16';
select count(*) from lightinglog where date < '2019-06-18';
select count(*) from lightinglog where date < '2019-06-20';
select count(*) from lightinglog where date < '2019-06-22';
select count(*) from lightinglog where date < '2019-06-24';

Is my setting in domoticz for this as I intend and does it comply with my findings using sqlite3 ?
[setup][settings][Log History] tab

syntax for deleting records using sqlite3 (stop domoticz first)

Code: Select all

sqlite> delete from lightinglog where date < '2019-06-01' ; 

[assenzuid]

Thanks, I will investigate.

Seems from the last few months the grow of the log.