Autor Thema: DbLog patch to minimize flash wear  (Gelesen 4004 mal)

Fuzzy

  • Gast
DbLog patch to minimize flash wear
« am: 27 Februar 2013, 22:02:26 »
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

Offline justme1968

  • Developer
  • Hero Member
  • ****
  • Beiträge: 20884
Aw: DbLog patch to minimize flash wear
« Antwort #1 am: 27 Februar 2013, 22:45:58 »
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
FHEM5.4,DS1512+,2xCULv3,DS9490R,HMLAN,2xRasPi
CUL_HM:HM-LC-Bl1PBU-FM,HM-LC-Sw1PBU-FM,HM-SEC-MDIR,HM-SEC-RHS
HUEBridge,HUEDevice:LCT001,LLC001,LLC006,LWL001
OWDevice:DS1420,DS18B20,DS2406,DS2423
FS20:fs20as4,fs20bs,fs20di
AKCP:THS01,WS15
CUL_WS:S300TH

Fuzzy

  • Gast
Aw: DbLog patch to minimize flash wear
« Antwort #2 am: 28 Februar 2013, 07:02:12 »

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

Offline Tobias

  • Developer
  • Hero Member
  • ****
  • Beiträge: 3924
Aw: DbLog patch to minimize flash wear
« Antwort #3 am: 05 März 2013, 20:06:24 »
i will move your patch to the official dblog module

best regards
FHEM auf ASRock J3455-ITX im 19" Rack mit Homematic, MAX, PCA301, Panstamps, RPi für BLE Bodenfeuchtesenoren, Text2Speech.
Maintainer der Module: Text2Speech, TrashCal, MediaList

Meine Projekte auf https://github.com/tobiasfaust
u.a. PumpControl v2: allround Bewässerungssteuerung mit ESP und FHEM

Fuzzy

  • Gast
Aw: DbLog patch to minimize flash wear
« Antwort #4 am: 06 März 2013, 13:56:11 »
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

Offline Tobias

  • Developer
  • Hero Member
  • ****
  • Beiträge: 3924
Aw: DbLog patch to minimize flash wear
« Antwort #5 am: 06 März 2013, 20:22:44 »
please post here. And additionally a personal message
FHEM auf ASRock J3455-ITX im 19" Rack mit Homematic, MAX, PCA301, Panstamps, RPi für BLE Bodenfeuchtesenoren, Text2Speech.
Maintainer der Module: Text2Speech, TrashCal, MediaList

Meine Projekte auf https://github.com/tobiasfaust
u.a. PumpControl v2: allround Bewässerungssteuerung mit ESP und FHEM

Fuzzy

  • Gast
Aw: DbLog patch to minimize flash wear
« Antwort #6 am: 10 März 2013, 17:25:29 »
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