indigo timers and SQL Logger - bad interaction?

Posted on
Sat Mar 18, 2017 2:22 pm
dduff617 offline
Posts: 398
Joined: Jul 05, 2006

indigo timers and SQL Logger - bad interaction?

i use use indigo timer objects to implement certain behaviors. an example is detecting when no heartbeat signal has been received from an insteon leak detector device.

i was poking around in the indigo sql database and i discovered that every state change to an indigo object results in a record being added to the sql logs... my timer objects have hundreds of times more records in the database than any other type of object. i have over a dozen of them. thus, i have hundreds of millions of records in SQL representing these (largely meaningless) counter decrements.

the way timer objects are implemented in indigo would seem to be that the devices' attributes are updated every second. several device attributes change including longStatusString, and timeLeftSeconds. just as an implementation issue, i'm a bit puzzled by why a timer object should change its state every second. it seems more intuitive to me that a timer object, once set, remains essentially static (until perhaps it undergoes a change from active to inactive or something). it will get checked regularly against current time (i.e. at some fixed interval such as once per second), but i don't see much value continually updating object state (or multiple states, in this case).

i noticed that i have "store device state changes in database" checked in the plugin configuration for SQL Logger. is this the standard configuration? i may have modified this years ago and forgotten about it... offhand, seems like my system is spending a *lot* of cycles continually writing these meaningless changes to the database... (and then pushed to backups, etc.)

this seems useful for debugging, but i wonder - do i lose much if i turn this off? is this needed for example to have indigoplotd plot thermostat values and such?

my system has been doing this for years and i never really paid much attention to it. perhaps i should just forget about it and let it keep going. glad i am not paying per CPU cycle or per byte written to disk. on second thought, however, i have on a few occasions had my entire sql log db get corrupted - perhaps that was made more likely by the heavy volume of database updates i've been generating...

Posted on
Sun Mar 19, 2017 12:35 pm
jay (support) offline
Site Admin
User avatar
Posts: 14391
Joined: Mar 19, 2008
Location: Austin, Texas

Re: indigo timers and SQL Logger - bad interaction?

Primarily, we implemented it that way so that 1) you could put the countdown on a control page so you could see how much time was left and 2) you could trigger off of any value as the timer counts down. Now, we could have implemented #2 in a different way such that the states didn't have to change. However, since we wanted #1, it didn't make sense to make #2 more complicated.

As for the amount of data in SQLogger, all I can say is that the plugin could be a bit smarter and allow users to exclude devices. But, honestly, given the low number of users who actually use that plugin adding that functionality is pretty low on the list.

offhand, seems like my system is spending a *lot* of cycles continually writing these meaningless changes to the database... (and then pushed to backups, etc.)

Define "spending a *lot* of cycles. Is it effecting performance of your system (other than filling up the db file)? SQLite is pretty hardened so it's unlikely that the number of writes is what's causing the db issue (but if the disk it's writing to isn't on a UPS and power is removed, that could definitely do it). You might want to write a script to periodically delete stuff from the DB however rather than just letting it grow continuously.

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest