SQL integration: limit the query to once a day

hi, there is a nice integration for SQL to do a query on the database. the only disadventage it has is that the query runs continuously and in result the cpu usage goes up. i tested it with a query for my database size, and in the screenshot below you see the difference in cpu usage.
image
so does anyone know how we can limit the execution to once a day for example?
i think it’s just missing from that integration, right?..
or could it be possible to do a query in an automation?

I can think of a way, but it might not be the best way.

Create a dummy sensor in HA to record the value,

Write a script (in bash, perl, python, whatever) which is run once a day by cron, or even by HA via automation and a timer. That script connects to the database, runs your query, collects the answer and sends that to HA to update the dummy sensor.

That would work okay, but the DBA part of me would prefer to either investigate why the query is causing so much load on the database. That might be possible to reduce that through tweaking the query, the table’s schema, or the server’s database settings.

Alternatives: Put the database on another machine if you have one or throw hardware at the problem and upgrade from an rpi to a more powerful server.

1 Like

Hi Digdilem,
thanks for your reply!
you describe a lot of things that i have not used yet, so after some searching i found that i could use an automation to run a python script. like you suggested.
the python script will be available as a service after you put the .py file in the folder /config/python_scripts
described here: Python Scripts - Home Assistant

for the content of the .py file i found this thread:

maybe that’s something i could use?
not sure why they put this in configuration.yaml, because the activation is by the service.

shell_command:
  script1: "python3 /config/python_scripts/script1.py"

but after the script has run, how do we get the result in an entity?

the query i did is:

SELECT SUM("pgsize")*0.0000009999999999999999 as "size"  FROM "dbstat";

it takes some time to process, but i think that’s normal because it has to calculate the size of all files and folders. and did this continuously. the cpu has still enough power, but if this is done once a day it’s certainly no problem.

Turn off the automatic polling (every 30 sec) under the SQL integration’s settings. Then create an automation for your preferred time and call the homeassistant.update_entity service on your sensor.

1 Like

Where is this option found? I’m looking in integrations\sql but I’m not seeing an option to disable the constant polling.

YES! this is the solution i was looking for. so simple and it still uses the SQL integration which is very easy to use. thanks!

@IOTNinja just open the SQL integration, press the 3 dots, system settings.

image

and the automation looks like this:

- alias: 'Update entity'
  trigger:
    - platform: time
      at: "05:00:00"
  action:
    - service: homeassistant.update_entity
      data: {}
      target:
        entity_id: 
          - sensor.database_size
3 Likes

Thanks! :white_check_mark:

1 Like