Basic configuration for recorder and db size sensor

Configuration
1

Hi Everyone,

I’m new in HA, maybe someone can help me understand what I missing out. This is how I use to configure the recorder and a sensor in configuration.yaml:

# Loads default set of integrations. Do not remove.
default_config:

# Load frontend themes from the themes folder
frontend:
  themes: !include_dir_merge_named themes

recorder:
  db_url: sqlite:////config/home-assistant_v2.db
  auto_purge: true
  auto_repack: true
  purge_keep_days: 30
  commit_interval: 30

automation: !include automations.yaml
script: !include scripts.yaml
scene: !include scenes.yaml

sensor:
  - platform: sql
    db_url: sqlite:////config/home-assistant_v2.db
    queries:
      name: HA DB size
      query: SELECT ROUND(page_count * page_size / 1024 / 1024, 1) as size FROM pragma_page_count(), pragma_page_size();
      column: size
      unit_of_measurement: MB

After restarting HA, I can’t find the DB sensor. I do not understand what is wrong…
If anyone could enlighten me, I would be very grateful.

I’m using HA OS on Raspberry PI 4 B 2GB.

Thanks in advance

2

No need to add this line (which could be wrong, have not checked myself) when using a default DB.

Never used “platform: sql” sensors - but why not using a simple “filesize” integration?

3

Thank you @Ildar_Gabdullin !

I tried to erase this line, it did not work. Eventually i switched to MariaDB. I will try the recorder integration with this DB.
Generally, what I am trying to do is to reduce the frequency of reading/writing the device I have. Instead of reading it each second I want the HA to read the states each 30 sec e.g.
I tried to modify the commit_interval configuration of the recorder integration, but I didn’t see any effect.

No special reason, I’m trying a few things, mostly for educational purpose.

BTW using SQL integration from UI works fine.

4

Never used Maria DB myself; people say that after plenty of optimizations of the default SQLite DB there is no practical reason to use another DB. But I am not a DB expert, may be still Maria DB has some benefits.

Can advise you nothing regarding “commit interval” etc - but there is one more way to reduce an amount of DB I/O operations - defining rules in Recorder (include/exclude).