search

ioBroker / ioBroker.sql

Store history data in SQL Database: MySQL, PostgreSQL or SQLite
MIT License
45 stars 24 forks source link

How often is data written to DB, how up-to-date should the data in the db be ? #298

Open NoPlayBack opened 1 year ago

NoPlayBack commented 1 year ago

It seems to me as if the values within IOBroker are not immediately stored into the mySQL database. I use MobileAlerts to collect weatherdata. The new data is visible in the MobileAlerts-Adapter. Then it needs 5 minutes until this value is stored in the mysql-database. Is this per design?

When i restart or shutdown the adapter or complete IOBroker, then the values pending are not written into the mysql-database.

Describe the solution you'd like First of all clarification if that behaviour is correct. If yes, then i would like to have the pending data written to the DB when the system shuts down, if possible.

Describe alternatives you've considered none

Apollon77 commented 1 year ago

This depends on your settings. If you have specified a nukber for the "datapoint buffering" then the adapter collects these number of data per datapoint and write then in bulk. If you use 0 there aka "write directly" then the data are written directly which is more load for the DB. On adapter stop all open data should be written in any case.

if something of this is not working please provide a debug log

NoPlayBack commented 1 year ago

I have german version, so i assume this is the setting "Maximale Anzahl von im RAM gespeicherten Werten" for each datapoint? Have standard value 10 in there. That would explain the delay, but not the loss of data in case of raspberry restart. How do i create a log?

Apollon77 commented 1 year ago

Log: Admin - Expertmode - Instances - expand sql.0 instance - set Loglevel to "debug" Use Logfile that is written to disk /opt/iobroker/log/... There should be no data loss ... so lets see

NoPlayBack commented 1 year ago

Log attached.

Did use „iobroker stop“ at 09:14 to stop iobroker and did restart approximately 2 min. later.

The data comes from MobileAlerts, a system to collect weather data. This system fetches data every 7 min. which works very stable. After stop and start of iobroker i see a data-gap over 20 minutes.

Thanks for reviewing !

IObrokerLog_SQL_Debug.txt

Apollon77 commented 1 year ago

I see a 9:13 stopping and "starting sql.0" in the log ... Then I see last logged data 9:14 ... then I see a "Host SIGTERM" 9:16 or iobroker ... so that could be the "stop" command, but this is not the usual "iob stop" way ... Then 16:57 also it starts to write all missing data into the DB

So timewise that all do not reallymtch to what you wrote.

Please lets start easy:

Start the sql adapter with enabled debug, let it run for 22 mins (so that your weather data are 3 times or such in the "ram"), then just stop the instance, not the whole iobroker. Then lets check the logs to see if this is working fine.

When this is ok then we need to check for the "host stop".

Pleasde also run "iob fix" and do a reboot before all next steps

NoPlayBack commented 1 year ago

Ok, next trial....

13:18 stop SQL adapter 13:19 switched to debug and start SQL adapter

new data fetched from mobile alerts on 13:21 / 13:28 / 13:35 / 13:42

13:47 stop SQL adapter

copied log and did start SQL adapter again.

Did not do "iob fix" as i have no idea what that is and did see in a quick search that some people had trouble to get iobroker running after doing that...

IObrokerLog_SQL_Debug_02.txt

Apollon77 commented 1 year ago

Did not do "iob fix" as i have no idea what that is and did see in a quick search that some people had trouble to get iobroker running after doing that...

Sorry but this can not be because it is exactly therefor to do the system relevant "setup" of iobroker in a linux system and registers the services and correctly file system permissionsnand other stuff. WHhere you found that "iob fix" caused issues? Would love to see that ...

NoPlayBack commented 1 year ago

as said, did not really read thouroghly through it and it is on my todo list:

https://forum.iobroker.net/topic/20212/diskussion-zum-neuen-installation-fixer/243?lang=en-GB scrolling around that entry shows some issues. Even if they are old, it indicated to me that i should be cautious and maybe first understand what that does before i use it. I am still in the learning curve.

Apollon77 commented 1 year ago

The post is from 2019 as the fixer was just added to the system, so honestly 1000% outdated ;-) In the meantime the fixer is "the default way" and included in any relevant upgrade guides, so use it please. Thank you

NoPlayBack commented 1 year ago

so, now i did iobroker stop iob fix 15:55 iobroker start MobileAlerts runtimes: 15:56 16:00 16:07 16:14

I am checking the data with the ID 5 (randomly picked) At 16:02 i did still see the value from 15:42 as last value in the db. At 16:06 i did see that the values grabbed at 15:54 15:56 16:00 are in the db At 16:08 Still the last value was from 16:00 iobroker stop iobroker start Log downloaded and stopped debug-level At 16:11 still the last value in the db was from 16:00 At 16:22 i did see 2 new values with timestamp 16:11:19 and 16:14:16 in the db

Let's see if this data is something which gives a hint. No hurry... it is weekend, i do not need an answer within short time...

IObrokerLog_SQL_Debug_03 - small.txt