Database tips

---

Here is a “how to” that will help you to reduce the size of your pimatic DB.
pimatic will be faster and your filesystem will thanks you!

Be carrefull make a full backup before following those steps

First thing is to STOP pimatic

cd pimatic_app
./node_modules/pimatic/pimatic.js stop

Next we copy the current database to a backup file (named pimatic-database.sqlite.backup)

cp pimatic-database.sqlite pimatic-database.sqlite.backup

We can now export DB content (data + tables structures) into a file called pimatic-database.sql

sqlite3 pimatic-database.sqlite .dump > pimatic-database.sql

Now we can delete the old huge database

rm pimatic-database.sqlite

Let’s now recreate the DB and reimport the data

sqlite3 pimatic-database.sqlite < pimatic-database.sql

You can now restart pimatic using the DB recreated

./node_modules/pimatic/pimatic.js start

The windows version:

here is what I did to recover:

Download http://www.sqlite.org/download.html and extract it to say c:\temp

SSH to the pi and shutdown pimatic

sudo service pimatic stop

Then make a copy of db file

sudo cp /home/pi/pimatic-app/pimatic-database.sqlite /home/pi/pimatic-app/pimatic-database.sqlite.old

Now FTP to the Pi and download you pimatic-database.sqlite to c:\temp

Launch an MS-Dos Command
Then go to your database directory(c:\temp), and launch SQLite:

sqlite3 pimatic-database.sqlite

You can check the database integrity by: (mind the semicolon at the end

pragma integrity_check;

If it’s OK, your database is good.

you can quit sqlite by : .quit

If you have error messages, quit sqlite and at your DOS prompt type:

echo .dump | sqlite3.exe pimatic-database.sqlite.sqlite > pimatic-database.sqlite.sql

Rename the old database

ren pimatic-database.sqlite pimatic-database.old

Then create a new database with your old data

sqlite3.exe -init pimatic-database.sqlite.sql pimatic-database.sqlite.sqlite

Thats it, FTP the pimatic-database.sqlite.sqlite file back to the Pi and start pimatic:

sudo service pimatic start

Source of the solution
http://community.spiceworks.com/how_to/1468-how-to-fix-corrupt-sqlite-database

little hint to reduce database writings when using pimatic-systemsensor devices.
i noticed that the systemsensors are updating every 10 secs, thats way to much if you are logging db-size, temp and diskusage.

therefore you can set an interval for this (eg. 3600000 for once an hour) :

   {
      "class": "SystemSensor",
      "id": "db-size",
      "name": "Pimatic DB Size",
      "attributes": [
        {
          "name": "dbsize",
          "interval": 3600000
        }
      ]
   }

check it out

how much size is big?
My actually database is 5mb.
Is it big?

Plugins [15]
Devices [37]
Rules [48]
Variables [5]

@slimmy not at all, mine is about 60 MB

Next to the above tips it is also good to reorganize your database from time to time.
This is done by the vacuum command. Make sure to first stop pimatic.

First install sqlite3 (if you haven’t done that yet):
sudo apt-get install sqlite3

and then in the folder where your pimatic database (pimatic-database.sqlite) resides:

cd <your_path>/pimatic_app
sudo service pimatic stop
sqlite3 pimatic-database.sqlite  "VACUUM;"
sudo service pimatic start

Of course I also forget to do this on regular base , but last time it went from 51MB to 26MB and the time before that, it went from 43MB to 23MB.
It should make your database slightly faster and it makes your backups smaller and faster (of course you do make backups don’t you? )

Vacuum is in the API https://pimatic.org/api/actions/

and according to the code it should vacuum by itself.
https://github.com/pimatic/pimatic/blob/1cb23602e28a4194811c69a779b4eba7e61ea2d3/lib/database.coffee#L100

PRAGMA auto_vacuum=FULL;

But that might make things worse.

Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse
https://www.sqlite.org/pragma.html#pragma_auto_vacuum

EDIT

I just tried it via the API and it seems to do something:

http://192.168.x.x/api/database/vacuum

{"result":[],"success":true}

I didn’t know that. But I would not use that on an active database even though it is supposed to do that on an open database. It is nice to reclaim some space every now and then and to optimize your database, but I can’t imagine a database this size needs an automatic vacuum every <what frequency anyway?>. Normally you have enough space on your sd-card.
Next to that: it adds even more write actions to your SD-card.

@harry-van-der-wolf

pi@raspberrypi:~/pimatic-app $ sqlite3 pimatic-database.sqlite "VACUUM;"
Error: attempt to write a readonly database
pi@raspberrypi:~/pimatic-app $

Filesystem readonly maybe?

Hi all, I found this thread about how to downsize the sqlite data base of Pimatic, but did not get real good results with the approaches.

Reason why I in general tested the approaches is the fact, that I accidentially recongnized, that the size of my database meanwhile reached >900MByte.

With the “VACCUM” command I was in minimum able to “downsize” to something like 860MByte.
By testing then the import/export approach I stopped it, when I got an data/structure export of 1.5GB in the end. Means, I didn’t test the re-import after that.

I now went into the config.json file and checked the data base parameters there:

Do I have the right understanding that, if I change e. g. “interval”: “0” to “interval”: “5min” in the deviceId section, now the respective time frame is set to 5 minutes and not any longer to kind of permanently (~each second?). ?

And do I also have the right understanding that, if I change e. g. “expire”: “0” to “expire”: “7d” in the messagelogging section, now loggings will not be kept longer than 7 days, in comparison to maybe ‘for ever’ with a ‘0’ in?

If I’m right with my assumptions, I guess that hopefully my data base size will increase whenever whatever mechanism checks how old the respective data (with now changed parameters) are.

Thanks a lot for your help, befor my installation might ‘explode’

Hi again, my parameter-changes seems working. The data base size now decreased to ~520MB, so significantly less than yesterday…
Let see how this continues …

… after 5 days now, down to ~480MB. So I will check further on the config.parameters