I really like the Database page in mobile-frontend especially to see for how long and with which interval data is stored…
-
Database configuration howto
pimatic rocks!
-
Another question
So with the default configuration, pimatic checks every 2h the database for expired entries and deletes them (in memory) and each second time (every 4h) the changes are synced to disk (deleted and new entries).
I have the impression that nothing is written in the DB anymore, for testing purpose i have reduce deleteExpiredInterval and diskSyncInterval to 1 minute and 3 minutes
"database": { "client": "sqlite3", "connection": { "filename": "pimatic-database.sqlite" }, "deviceAttributeLogging": [ { "deviceId": "*", "attributeName": "*", "expire": "7d", "type": "*" }, { "deviceId": "pin-switch", "attributeName": "*", "expire": "0", "type": "*" }, { "attributeName": "contact", "expire": "31d", "type": "*" }, { "deviceId": "rasptime", "attributeName": "uptime", "expire": "0", "type": "*" }, { "deviceId": "syssensor", "attributeName": "*", "interval": "5m", "expire": "7d", "type": "*" }, { "deviceId": "*", "attributeName": "temperature", "interval": "5m", "expire": "1y", "type": "*" }, { "deviceId": "*", "attributeName": "humidity", "interval": "5m", "expire": "1y", "type": "*" }, { "deviceId": "gas-sensor", "attributeName": "*", "interval": "10m", "expire": "31d", "type": "*" }, { "deviceId": "conso-gaz-24h", "attributeName": "*", "interval": "1h", "expire": "1y", "type": "*" }, { "deviceId": "elec-sensor", "attributeName": "*", "interval": "10m", "expire": "31d", "type": "*" }, { "deviceId": "conso-elec-24h", "attributeName": "*", "interval": "1h", "expire": "1y", "type": "*" } ], "messageLogging": [ { "level": "*", "tags": [], "expire": "7d" }, { "level": "debug", "tags": [], "expire": "0" } ], "deleteExpiredInterval": "1m", "diskSyncInterval": "3m", "debug": true },
I can’t see any message in pimatic log about the DB sync and nothing on the timestamp of the DB : so my impression is that nothing will go to disk (pimatic-database.sqlite-wal remains empty too)
pimatic rocks!
-
@Yves911 said:
Another question
So with the default configuration, pimatic checks every 2h the database for expired entries and deletes them (in memory) and each second time (every 4h) the changes are synced to disk (deleted and new entries).
I have the impression that nothing is written in the DB anymore, for testing purpose i have reduce deleteExpiredInterval and diskSyncInterval to 1 minute and 3 minutes
Thanks for reporting. The min deleteExpriedInterval was 5min (you should have a warning about that in your log), If it is less than that it is automatically set to 5min.
I can’t see any message in pimatic log about the DB sync and nothing on the timestamp of the DB : so my impression is that nothing will go to disk (pimatic-database.sqlite-wal remains empty too)
In addition here was an bug, so that the disk sync wasn’t triggered if diskSyncInterval was not a multiple of deleteExpriedInterval (what was true in your case, because the auto set to 5min).
I lowered the lower bound to 1min and fixed the bug. Could you retest with 0.8.39?
EDIT: One more fix in 0.8.40!
-
With 0.8;39 i can see
20:39:52.483 [pimatic] debug: deleteing expired logged values
5 minutes after pimatic startup (=deleteExpriedInterval) => So all good for that.
Now i can’t tell you about diskSyncInterval because of that
20:39:32.624 [pimatic-homeduino] debug: generic: { id: 1111, type: 2, positive: true, value: 774152 } 20:39:44.211 [pimatic-homeduino] debug: data: "RF receive 595 1923 3829 7648 0 0 0 0 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303" 20:39:44.224 [pimatic-homeduino] debug: received: [ 595, 1923, 3829, 7648 ] 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303 20:39:44.237 [pimatic-homeduino] debug: weather3: { id: 122, channel: 3, temperature: 18.2, humidity: 55 } 20:39:49.500 [pimatic-homeduino] debug: data: "RF receive 689 4263 1970 10072 0 0 0 0 010201020102020101020102010202010102020101020201020102010102010202010102020101020102010201020102010201020102010201020201010202010201020102010102020101020102010201020102010201020102020102010102010203" 20:39:49.511 [pimatic-homeduino] debug: received: [ 689, 1970, 4263, 10072 ] 020102010201010202010201020101020201010202010102010201020201020101020201010202010201020102010201020102010201020102010102020101020102010201020201010202010201020102010201020102010201010201020201020103 20:39:49.524 [pimatic-homeduino] debug: generic: { id: 1111, type: 2, positive: true, value: 774156 } 20:39:52.483 [pimatic] debug: deleteing expired logged values 20:40:09.589 [pimatic-homeduino] debug: Couldn't connect (operation timed out after 5000 ms), retrying...
When pimatic clean expired logged values CPU jump to 100% for a while (~1 min) and then pimatic-homeduino crash…
4008 root 20 0 151m 110m 9m S 97,7 29,8 4:00.59 /usr/local/bin/node /root/pimatic-git/node_modules/pimatic/pimatic.js start
I don’t understand what’s happen here?
pimatic rocks!
-
@sweetpi said:
“filename”: “pimatic-database.sqlite”
If i want to put the database on a external usb drive, can i just change it to something like “filename”:"/media/usbstick/pimatic-database.sqlite" ?
-
@Yves911 said:
With 0.8;39 i can see
20:39:52.483 [pimatic] debug: deleteing expired logged values
5 minutes after pimatic startup (=deleteExpriedInterval) => So all good for that.
Now i can’t tell you about diskSyncInterval because of that
20:39:32.624 [pimatic-homeduino] debug: generic: { id: 1111, type: 2, positive: true, value: 774152 } 20:39:44.211 [pimatic-homeduino] debug: data: "RF receive 595 1923 3829 7648 0 0 0 0 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303" 20:39:44.224 [pimatic-homeduino] debug: received: [ 595, 1923, 3829, 7648 ] 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303 20:39:44.237 [pimatic-homeduino] debug: weather3: { id: 122, channel: 3, temperature: 18.2, humidity: 55 } 20:39:49.500 [pimatic-homeduino] debug: data: "RF receive 689 4263 1970 10072 0 0 0 0 010201020102020101020102010202010102020101020201020102010102010202010102020101020102010201020102010201020102010201020201010202010201020102010102020101020102010201020102010201020102020102010102010203" 20:39:49.511 [pimatic-homeduino] debug: received: [ 689, 1970, 4263, 10072 ] 020102010201010202010201020101020201010202010102010201020201020101020201010202010201020102010201020102010201020102010102020101020102010201020201010202010201020102010201020102010201010201020201020103 20:39:49.524 [pimatic-homeduino] debug: generic: { id: 1111, type: 2, positive: true, value: 774156 } 20:39:52.483 [pimatic] debug: deleteing expired logged values 20:40:09.589 [pimatic-homeduino] debug: Couldn't connect (operation timed out after 5000 ms), retrying...
When pimatic clean expired logged values CPU jump to 100% for a while (~1 min) and then pimatic-homeduino crash…
4008 root 20 0 151m 110m 9m S 97,7 29,8 4:00.59 /usr/local/bin/node /root/pimatic-git/node_modules/pimatic/pimatic.js start
I don’t understand what’s happen here?
That:s really strange. How big is your database? Can you test with an empty one? What does crash mean?
-
@sweetpi my DB is about 60 MB, i just tried with a new empty DB there is no problem
Here is the error message
12:44:59.488 [pimatic] deleteing expired logged values 12:45:35.436 [pimatic-homeduino] Couldn't connect (operation timed out after 5000 ms), retrying... TypeError: Cannot read property 'length' of undefined at Function.Buffer.concat (buffer.js:491:20) at afterRead (/root/pimatic-git/node_modules/pimatic-homeduino/node_modules/homeduino/node_modules/serialport/serialport.js:323:36) at /root/pimatic-git/node_modules/pimatic-homeduino/node_modules/homeduino/node_modules/serialport/serialport.js:342:9 at Object.wrapper [as oncomplete] (fs.js:454:17)
EDIT, i think (due to the error message) this is the continuation of the story here:
https://github.com/pimatic/pimatic/issues/443
except that now:
-pimatic is NOT stable when running (crash after some minutes or very few hours)
-pimatic crash *even if the raspberry has been freshly rebooted…pimatic rocks!
-
I Moved the database to a USB stick but after 8 hours:
Sd writes: 110000
USB writes: 420Whats going wrong here? What makes al the writes On the sd card?
-
Hello,
my problem is that the database is only saved once.
No interval is following.“database”: {
“client”: “sqlite3”,
“connection”: {
“filename”: “pimatic-database.sqlite”
},
“deviceAttributeLogging”: [
{
“deviceId”: “",
“attributeName”: "”,
“type”: “",
“interval”: “0”,
“expire”: “7d”
},
{
“deviceId”: "”,
“attributeName”: “",
“type”: “continuous”,
“interval”: “5min”,
“expire”: “7d”
},
{
“deviceId”: "”,
“attributeName”: “temperature”,
“type”: “number”,
“expire”: “1y”
},
{
“deviceId”: “",
“attributeName”: “humidity”,
“type”: “number”,
“expire”: “1y”
}
],
“messageLogging”: [
{
“level”: "”,
“tags”: [],
“expire”: “7d”
},
{
“level”: “debug”,
“tags”: [],
“expire”: “0”
}
],
“deleteExpiredInterval”: “1m”,
“diskSyncInterval”: “2m”,
“debug”: true
}, -
@Andre-Bensch could you try with
"deleteExpiredInterval": "10m", "diskSyncInterval": "20m",
pimatic rocks!
-
Thanks for your reply.
I did as you said, but still the saving just happens once.
Nothing happens later on. -
@Andre-Bensch Thanks for reporting. This should be fixed in pimatic v0.8.50.
-
From this thread I learned that for intervals the units are
- s for seconds
- m for minutes
- h for hours
- y for years
Hope this is right. Is there also an unit letter for month?
Would be nice if this thread can ne moved to the how-to section or even better to extend the pimatic doocumentation.
-
Is it possible to disable some attributes completely from being logged in the database? I would like to exclude the battery attribute of my temperature sensor.
pimatic-hap - pimatic HomeKit bridge
pimatic-echo - Amazon echo integration
pimatic-dash-button - Amazon dash button support
pimatic-alarm - pimatic alarm system
Like my work? Then consider a donation
Follow me: https://twitter.com/michaelkotten -
@michbeck100 said in Database configuration howto:
Is it possible to disable some attributes completely from being logged in the database? I would like to exclude the battery attribute of my temperature sensor.
@sweetpi said
All values with expire set to “0” are not written to the database at all. This also applies to the deviceAttributeLogging config.
-
i want to delete some old entries like “No device with the ID “XX” found.” and “Device “Battery” has no attribute with the name “battery_10” found.”. When i use the database-page i get an timeout … is there a trick do do it?
pimatic + MySensors + Homeduino + z-way
https://github.com/n3roGit/MySensors_n3ro -
Can I use the wildcards in attributeName?
temperature.*
does not work. I have several devices that have several temperatures.
For example:{ "attributes": [ { "name": "temperature.fishtank", "topic": "mh/gf/lab/wemosd1r2/fishtank/temperature", "type": "number", "unit": "°C", "acronym": "fishtank", "messageMap": {} }, { "name": "temperature.freshtank", "topic": "mh/gf/lab/wemosd1r2/freshwater/temperature", "type": "number", "unit": "°C", "acronym": "freshwater", "messageMap": {} }, { "name": "temperature.room", "topic": "mh/gf/lab/wd1mini-9/si7021/temperature", "type": "number", "unit": " °C", "acronym": "room", "messageMap": {} } ], "xAttributeOptions": [ { "name": "temperature.room", "displaySparkline": false } ], "id": "fishtank-temp", "name": "Temperature", "class": "MqttSensor" }
Pimatic = Smart Home
-
you could try it with this one for the fishtank-temp device to log all data with this id.
with the below example, you need to create a database entry for each device.
if a wildcard is possible i cannot tell, but maybe @sweetpi can ?{ "deviceId": "fishtank-temp", "attributeName": "*", "type": "number", "interval": "5min", "expire": "1y" }
pimatic v0.9 has been released!
Support Pimatic and get some free stickers
Like us on Facebookmake it so !
-
Is it possible to read out the pimatic database via Grafana as it offers MySQL and now Microsoft SQL Server datasource features? Sorry for the noob question as i am still new to databases but i guess the answer to my question would be “no” as pimatic isnt offering a database server ? I am just trying to find a way to get the pimatic database file into grafana as i don´t want to feed all the pimatic data in my influx db and having twice the data…