SQL Logger and Frequent State Updates

Is there a max number of updates the SQL Logger can handle before becoming unstable? Also, is there a way to include/exclude devices from logging?

I have had a lot of problems with the SQL Logger performance and stability and have it turned off at the moment. I was just thinking this morning that my Security Camera plugin is making several state updates every second or two. Multiply that across 6 cameras and that is a lot of writing to the database. I have some ideas on how to reduce the number of state updates my plugin is making but is it possible to exclude my camera devices completely from getting logged?

There are also other plugins that I use that I’d like to exclude as well.


Sent from my iPhone using Tapatalk

Is there a max number of updates the SQL Logger can handle before becoming unstable? Also, is there a way to include/exclude devices from logging?

I have had a lot of problems with the SQL Logger performance and stability and have it turned off at the moment. I was just thinking this morning that my Security Camera plugin is making several state updates every second or two. Multiply that across 6 cameras and that is a lot of writing to the database. I have some ideas on how to reduce the number of state updates my plugin is making but is it possible to exclude my camera devices completely from getting logged?

This is hard to answer in terms of number of updates as it depends on the SQL solution and the hardware you are using; if you chose to use Postgre SQL you will be a lot better off in terms of performance and size handling than MySQL. Also, in theory ticking the option to only include the changed state would help some as well.

I am working on a solution to filter the devices within SQL Logger (to exclude or include) and may have something next week (going to be in the middle of the woods with Cub Scouts this weekend, not an HA-improvement weekend!)

Adam

Thanks - I might try jumping into Postgre this weekend. Ability to filter out devices would be great - but enjoy the woods...


Sent from my iPhone using Tapatalk

[matt (support)]

Regarding optimizing the state updates, are you using the updateStatesOnServer API (search on updateStatesOnServer here) that allows you to update – with a single call – multiple states (on one device)? The Indigo Server and the SQL Logger plugin will use less CPU if you can combine multiple state updates into a single call.

on my mac pro 2008 with postgres it did not choke on 20 updates per second (w updateState>>s<< ) .. but the db gets big every quickly.

Though when doing a db_dump (for backup) then indigo gets timeouts and then all hell break lose.. have to restart indigo server.

and yes postgres is MUCH faster, more stable ... than sqlite (just one big file as DB)

Karl