DbLog patch to minimize flash wear

Begonnen von Fuzzy, 27 Februar 2013, 22:02:26

Vorheriges Thema - Nächstes Thema

Fuzzy

Hi,
I'm using SQLite with the DbLog module on my RPi. On a small embedded device like this minimizing writes to reduce wear on flash storage and save IO bandwidth is important. So I have created the attached simple patch to 93_DbLog.pm that
- Moves the 'current' table to memory and modifies a few internal parameters in SQLite to minimize flash wear.
- Makes easier to setup DbLog with SQLite. Database tables are created automatically when the database is opened.


I have also added an event-on-change-reading attribute to all the devices in fhem.cfg  to log only changes e.g.:


define TEMP.Bedroom TRX_WEATHER TFATS34C_4b_14
attr TEMP.Bedroom event-on-change-reading temperature,humidity


Br,
Peter

justme1968

i just saw your patch...

 the index seems to make a huge difference. i think i like it.

one more question: how does the temp table work? should i drop the regular current table?

thanks
  andre
hue, tradfri, alexa-fhem, homebridge-fhem, LightScene, readingsGroup, ...

https://github.com/sponsors/justme-1968

Fuzzy


Temp tables are only visible in which database connection they were created. When you close the database connection the table disappears.  SQLite is not a separate server so it only happens if you restart  FHEM, but no reading data lost as it is kept in the history table...
And yes you have to drop your existing 'current' table.

Br,
Peter

Tobias

i will move your patch to the official dblog module

best regards
Maintainer: Text2Speech, TrashCal, MediaList

Meine Projekte: https://github.com/tobiasfaust
* PumpControl v2: allround Bewässerungssteuerung mit ESP und FHEM
* Ein Modbus RS485 zu MQTT Gateway für SolarWechselrichter

Fuzzy

Thanks. I'm working on a newer patch that combines the inserts into one transaction, and improves the event parsing method.
Shall I post it here or another part of the forum?  

Br, Peter

Tobias

please post here. And additionally a personal message
Maintainer: Text2Speech, TrashCal, MediaList

Meine Projekte: https://github.com/tobiasfaust
* PumpControl v2: allround Bewässerungssteuerung mit ESP und FHEM
* Ein Modbus RS485 zu MQTT Gateway für SolarWechselrichter

Fuzzy

Hi,

Please find attached the promised path.  I have made the following changes:
-Event parsing method: Changed if to elseif to speed it up, added parsing support for OWL energy monitors (TRX_WEATHER), AC level command (TRX_LIGHTING), CUL_HM actuator messages. Fixed indentation levels.
-Log method: Changed to use one transaction for writing all of the readings.
-Get method: Changed to use bind variables with fetch (fastest way to read data), reduced the returned columns to 'timestamp' and 'value'.

Br,
Peter